2016 June Oracle Official: 1Z0-051: Oracle Database 11g: SQL Fundamentals I Exam Questions New Updated Today! Braindump2go.com Offers 1Z0-051 PDF and VCE Dumps 303q for Free Downloading!
NEW QUESTION 41 – NEW QUESTION 50:
QUESTION 41
View the Exhibit and examine the structure of CUSTOMERS and GRADES tables.
You need to display names and grades of customers who have the highest credit limit.
Which two SQL statements would accomplish the task? (Choose two.)
A. SELECT custname, grade
FROM customers, grades
WHERE (SELECT MAX(cust_credit_limit)
FROM customers) BETWEEN startval and endval;
B. SELECT custname, grade
FROM customers, grades
WHERE (SELECT MAX(cust_credit_limit)
FROM customers) BETWEEN startval and endval
AND cust_credit_limit BETWEEN startval AND endval;
C. SELECT custname, grade
FROM customers, grades
WHERE cust_credit_limit = (SELECT MAX(cust_credit_limit)
FROM customers)
AND cust_credit_limit BETWEEN startval AND endval;
D. SELECT custname, grade
FROM customers , grades
WHERE cust_credit_limit IN (SELECT MAX(cust_credit_limit)
FROM customers)
AND MAX(cust_credit_limit) BETWEEN startval AND endval;
Answer: BC
QUESTION 42
Examine the structure of the PRODUCTS table:
You want to display the names of the products that have the highest total value for UNIT_PRICE * QTY_IN_HAND.
Which SQL statement gives the required output?
A. SELECT prod_name
FROM products
WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products);
B. SELECT prod_name
FROM products
WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products
GROUP BY prod_name);
C. SELECT prod_name
FROM products
GROUP BY prod_name
HAVING MAX(unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products
GROUP BY prod_name);
D. SELECT prod_name
FROM products
WHERE (unit_price * qty_in_hand) = (SELECT MAX(SUM(unit_price * qty_in_hand)) FROM products)
GROUP BY prod_name;
Answer: A
QUESTION 43
View the Exhibit and examine the structure of the PRODUCTS table.
Evaluate the following query:
What would be the outcome of executing the above SQL statement?
A. It produces an error.
B. It shows the names of all products in the table.
C. It shows the names of products whose list price is the second highest in the table.
D. It shows the names of all products whose list price is less than the maximum list price.
Answer: C
QUESTION 44
View the Exhibit and examine the structure of the PROMOTIONS table.
You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the ‘INTERNET’ category.
Which query would give you the required output?
A. SELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begin_date > ALL (SELECT MAX(promo_begin_date) FROM promotions )AND
promo_category = ‘INTERNET’;
B. SELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begin_date IN (SELECT promo_begin_date
FROM promotions
WHERE promo_category=’INTERNET’);
C. SELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begin_date > ALL (SELECT promo_begin_date
FROM promotions
WHERE promo_category = ‘INTERNET’);
D. SELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begin_date > ANY (SELECT promo_begin_date
FROM promotions
WHERE promo_category = ‘INTERNET’);
Answer: C
QUESTION 45
View the Exhibit and examine the structure of the PRODUCTS table.
You want to display the category with the maximum number of items.
You issue the following query:
SQL>SELECT COUNT(*),prod_category_id
FROM products
GROUP BY prod_category_id
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM products);
What is the outcome?
A. It executes successfully and gives the correct output.
B. It executes successfully but does not give the correct output.
C. It generates an error because the subquery does not have a GROUP BY clause.
D. It generates an error because = is not valid and should be replaced by the IN operator.
Answer: C
QUESTION 46
View the Exhibit and examine the structure of the CUSTOMERS table.
You issue the following SQL statement on the CUSTOMERS table to display the customers who are in the same country as customers with the last name ‘KING’ and whose credit limit is less than the maximum credit limit in countries that have customers with the last name ‘KING’:
Which statement is true regarding the outcome of the above query?
A. It executes and shows the required result.
B. It produces an error and the < operator should be replaced by < ALL to get the required output.
C. It produces an error and the < operator should be replaced by < ANY to get the required output.
D. It produces an error and the IN operator should be replaced by = in the WHERE clause of the main
query to get the required output.
Answer: A
QUESTION 47
Evaluate the following SQL statement:
SQL> SELECT cust_id, cust_last_name
FROM customers
WHERE cust_credit_limit IN
(select cust_credit_limit
FROM customers
WHERE cust_city =’Singapore’);
Which statement is true regarding the above query if one of the values generated by the subquery is NULL?
A. It produces an error.
B. It executes but returns no rows.
C. It generates output for NULL as well as the other values produced by the subquery.
D. It ignores the NULL value and generates output for the other values produced by the subquery.
Answer: C
QUESTION 48
View the Exhibit and examine the structure of the PROMOTIONS table.
Evaluate the following SQL statement:
SQL>SELECT promo_name,CASE
WHEN promo_cost >=(SELECT AVG(promo_cost)
FROM promotions
WHERE promo_category=’TV’)
then ‘HIGH’
else ‘LOW’
END COST_REMARK
FROM promotions;
Which statement is true regarding the outcome of the above query?
A. It shows COST_REMARK for all the promos in the table.
B. It produces an error because the subquery gives an error.
C. It shows COST_REMARK for all the promos in the promo category ‘TV’.
D. It produces an error because subqueries cannot be used with the CASE expression.
Answer: A
QUESTION 49
View the Exhibit and examine the structure of the PRODUCTS tables.
You want to generate a report that displays the average list price of product categories where the average list price is less than half the maximum in each category.
Which query would give the correct output?
A. SELECT prod_category,avg(prod_list_price)
FROM products
GROUP BY prod_category
HAVING avg(prod_list_price) < ALL
(SELECT max(prod_list_price)/2
FROM products
GROUP BY prod_category);
B. SELECT prod_category,avg(prod_list_price)
FROM products
GROUP BY prod_category
HAVING avg(prod_list_price) > ANY
(SELECT max(prod_list_price)/2
FROM products
GROUP BY prod_category);
C. SELECT prod_category,avg(prod_list_price)
FROM products
HAVING avg(prod_list_price) < ALL
(SELECT max(prod_list_price)/2
FROM products
GROUP BY prod_category);
D. SELECT prod_category,avg(prod_list_price)
FROM products
GROUP BY prod_category
HAVING avg(prod_list_price) > ANY
(SELECT max(prod_list_price)/2
FROM products);
Answer: A
Explanation:
Using the ANY Operator in Multiple-Row Subqueries
The ANY operator (and its synonym, the SOME operator) compares a value to each value returned by a subquery.
<ANY means less than the maximum.
>ANY means more than the minimum.
=ANY is equivalent to IN
Using the ALL Operator in Multiple-Row Subqueries
The ALL operator compares a value to every value returned by a subquery.
>ALL means more than the maximum and
<ALL means less than the minimum.
The NOT operator can be used with IN, ANY, and ALL operators.
QUESTION 50
View the Exhibits and examine the structures of the COSTS and PROMOTIONS tables.
Evaluate the following SQL statement:
SQL> SELECT prod_id FROM costs
WHERE promo_id IN (SELECT promo_id FROM promotions
WHERE promo_cost < ALL
(SELECT MAX(promo_cost) FROM promotions
GROUP BY (promo_end_date-
promo_begin_date)));
What would be the outcome of the above SQL statement?
A. It displays prod IDs in the promo with the lowest cost.
B. It displays prod IDs in the promos with the lowest cost in the same time interval.
C. It displays prod IDs in the promos with the highest cost in the same time interval.
D. It displays prod IDs in the promos with cost less than the highest cost in the same time interval.
Answer: D
2016 Valid Oracle 1Z0-051 Exam Study Materials:
1.| Latest 1Z0-051 PDF and VCE Dumps 303Q&As from Braindump2go: http://www.braindump2go.com/1z0-051.html [100% Exam Pass Guaranteed!]
2.| NEW 1Z0-051 Exam Questions and Answers: https://drive.google.com/folderview?id=0B75b5xYLjSSNVGxLT202clFMbjA&usp=sharing
MORE Practice is the Most Important IF You want to PASS 1Z0-051 Exam 100%!
————— Braindump2go.com
————— Pass All IT Exams at the first Try!