Oracle 1Z0-047 問題集

質問 1：
View the Exhibit and examine DEPARTMENTS and the LOCATIONS tables.
Evaluate the following SOL statement:
SELECT location_id, city FROM locations I WHERE NOT EXISTS (SELECT location_id FROM departments WHERE location_id <> I. location_id);
This statement was written to display LOCATIONJD and CITY where there are no departments located. Which statement is true regarding the execution and output of the command?

A. The statement would not execute because the = comparison operator is missing in the WHERE clause of the outer query.
B. The statement would execute and would return the desired results.
C. The statement would not execute because the WHERE clause in the outer query is missing the column name for comparison with the inner query result.
D. The statement would execute but it will return zero rows because the WHERE clause in the inner query should have the = operator instead of <>.
正解：D

質問 2：
View the Exhibit and examine the description of the PRODUCT_INFORMATION table.
SELECT product_name, list_price, min_price, list_price - min_price Difference FROM product_information
Which options when used with the above SQL statement can produce the sorted output in ascending order of the price difference between LIST_PRICE and MIN_PRICE? (Choose all that apply.)

A. ORDER BY LIST_PRICE
B. ORDER BY MIN_PRICE
C. ORDER BY 4
D. ORDER BY DIFFERENCE
E. ORDER BY LIST_PRICE - MIN_PRICE
正解：C,D,E

質問 3：
Evaluate the following SQL statement:
SELECT 2 col1,ycol2 FROM dual UNION SELECT 1 ,'x' FROM dual
UNION SELECT 3 .NULL FROM dual ORDER BY 2;
Which statement is true regarding the output of the SQL statement?
A. It would not execute because the number 2 in the ORDER BY clause would conflict with the value 2 in the first SELECT statement.
B. It would execute and the order of the values in the first column would be 3,2,1.
C. It would execute and the order of the values in the first column would be 1,2,3.
D. It would not execute because the column alias name has not been used in the ORDER BY clause.
正解：C

質問 4：
View the Exhibit and examine the description of the EMPLOYEES table.
You want to know the EMPLOYEE_ID and FIRST_NAME of all the records in the EMPLOYEES table wherein the JOB_ID column has ST_CLERK or ST_MAN values, the DEPARTMENT_ID column has value 30, and the SALARY column has a value greater than 3,000.
Which SOL statement would get you the desired result?

A. SELECT employee_ id, first_name
FROM employees
WHERE (job_id like '%MAN' OR job_id like '%CLERK')
AND department_id = 30 AND salary > 3000;
B. SELECT employee_ d, first_name
FROM employees
WHERE job_id like '%MAN' OR job_id like '%CLERK'
AND (department_id = 30 OR salary > 3000);
C. SELECT employee_id, first_name
FROM employees
WHERE job_id like 'MAN%' OR job_id like 'CLERK%'
AND department_id = 30 AND salary > 3000;
D. SELECT employee_ id, first_name
FROM employees
WHERE (job_id like '%MAN' AND job_id like '%CLERK')
AND department_id = 30 OR salary > 3000;
正解：A

質問 5：
The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues the following
GRANT command:
GRANT ALL
ON orders, order_items
TO PUBLIC;
What correction needs to be done to the above statement?
A. WITH GRANT OPTION should be added to the statement.
B. Separate GRANT statements are required for ORDERS and ORDER_ITEMS tables.
C. PUBLIC should be replaced with specific usernames.
D. ALL should be replaced with a list of specific privileges.
正解：B

質問 6：
Which two statements are true regarding multiple-row subqueries? (Choose two.)
A. They use the < ALL operator to imply less than the maximum.
B. They can be used to retrieve multiple rows from a single table only.
C. They always contain a subquery within a subquery.
D. They can contain group functions.
E. They should not be used with the NOT IN operator in the main query if NULL is likely to be a part of the result of the subquery.
正解：D,E