Oracle 1Z0-051 問題集

質問 1：
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 GROUP BY prod_name);
B. 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);
C. 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;
D. SELECT prod_name FROM products WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products);
正解：D

質問 2：
View the Exhibit and examine the data in the PROMOTIONS table.

You need to display all promo categories that do not have 'discount' in their subcategory.
Which two SQL statements give the required result? (Choose two.)
A. SELECT promo_category FROM promotions MINUS SELECT promo_category FROM promotions WHERE promo_subcategory <> 'discount';
B. SELECT promo_category FROM promotions MINUS SELECT promo_category FROM promotions WHERE promo_subcategory = 'discount';
C. SELECT promo_category FROM promotions INTERSECT SELECT promo_category FROM promotions WHERE promo_subcategory <> 'discount';
D. SELECT promo_category FROM promotions INTERSECT SELECT promo_category FROM promotions WHERE promo_subcategory = 'discount';
正解：B,C

質問 3：
View the Exhibit and examine the structure of the PRODUCTS table.
You want to display only those product names with their list prices where the list price is at least double the minimum price. The report should start with the product name having the maximum list price satisfying this
condition.
Evaluate the following SQL statement:
SQL>SELECT prod_name,prod_list_price
FROM products
WHERE prod_list_price >= 2 * prod_min_price
Which ORDER BY clauses can be added to the above SQL statement to get the correct output?
(Choose all that apply.)

A. ORDER BY prod_list_price DESC, prod_name;
B. ORDER BY prod_list_price DESC, prod_name DESC;
C. ORDER BY prod_name DESC, prod_list_price DESC;
D. ORDER BY (2*prod_min_price)DESC, prod_name;
E. ORDER BY prod_name, (2*prod_min_price)DESC;
正解：A,B
解説: (Topexam メンバーにのみ表示されます)

質問 4：
Which three statements are true about multiple-row sub queries? (Choose three.)
A. They can return multiple columns as well as rows.
B. They can contain group functions and GROUP BY and HAVING clauses.
C. They can contain group functions and the GROUP BY clause, but not the HAVING clause.
D. They can contain a subquery within a sub query.
E. They cannot contain a sub query within a sub query.
F. They can return only one column but multiple rows.
正解：A,B,D

質問 5：
Examine the structure of the EMPLOYEES and DEPARTMENTS tables:

You want to create a report displaying employee last names, department names, and
locations. Which query should you use to create an equi-join?
A. SELECT last_name, department_name, location_id FROM employees , departments ;
B. SELECT employees.last_name, departments.department_name,
departments.location_id FROM employees e, departments D WHERE e.department_id =d.department_id;
C. SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE e.department_id =d.department_id;
D. SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE manager_id =manager_id;
正解：C
解説: (Topexam メンバーにのみ表示されます)

質問 6：
View the Exhibit and examine the structure of the CUSTOMERS table.
In the CUSTOMERS table, the CUST_LAST_NAME column contains the values 'Anderson'
and 'Ausson'.
You issue the following query:
SQL> SELECT LOWER(REPLACE(TRIM('son' FROM cust_last_name),'An','O'))
FROM CUSTOMERS
WHERE LOWER(cust_last_name) LIKE 'a%n';
What would be the outcome?
A. an error because the LOWER function specified is not valid
B. 'Oder' and 'Aus'
C. an error because the TRIM function specified is not valid
D. an error because the REPLACE function specified is not valid
正解：C
解説: (Topexam メンバーにのみ表示されます)

質問 7：
What is true about sequences?
A. The start value of the sequence is always 1.
B. The maximum value of descending sequence defaults to 1.
C. A sequence always increments by 1.
D. The minimum value of an ascending sequence defaults to 1.
正解：D