- Home
- Database
- Oracle 12 C
- OCA Oracle 12 C SQl Fundamentals(1Z0-061)
36.
The EMPLOYEES table has EMPLOYEE_ID, DEPARTMENT_ID, and FULL_NAME columns. The
DEPARTMENTS table has DEPARTMENT_ID and DEPARTMENT_NAME columns. Which two of the
following queries return the department ID, name, and employee name, listing department
names even if there is no employee assigned to that department? (Choose two.)
- A.SELECT d.department_id, d.department_name, e.full_name FROM departments d NATURAL LEFT OUTER JOIN employees e;
- B.SELECT department_id, department_name, full_name FROM departments NATURAL LEFT JOIN employees;
- C.SELECT d.department_id, d.department_name, e.full_name FROM departments d LEFT OUTER JOIN employees eUSING (d.department_id);
- D.SELECT d.department_id, d.department_name, e.full_name FROM departments d LEFT OUTER JOIN employees eON (d.department_id =e.department_id);
- Answer & Explanation
- Report
Answer : [B, D]
Explanation :
Explanation :
| Option A does not work because you cannot qualify column names when using a natural join. Option B works because the only common column between these two tables is DEPARTMENT_ID. The keyword OUTER is optional. Option C does not work, again because you cannot qualify column names when specifying the USING clause. Option D works because it specifies the join condition explicitly in the ON clause. |
37.
Which two operators are not allowed when using an outer join operator in the query?
(Choose two.)
- A.OR
- B.AND
- C.IN
- D.=
- Answer & Explanation
- Report
Answer : [A, C]
Explanation :
Explanation :
| OR and IN are not allowed in the WHERE clause on the columns where an outer join operator is specified. You can use AND and = in the outer join. |
38.
Which SQL statements do not give an error? (Choose all that apply.)
- A.SELECT last_name, e.hire_date, department_id FROM employees e JOIN (SELECT max(hire_date) max_hire_date FROM employees ORDER BY 1) me ON (e.hire_date = me.max_hire_date)
- B.SELECT last_name, e.hire_date, department_id FROM employees e WHERE hire_date = (SELECT max(hire_date) max_hire_date FROM employees ORDER BY 1)
- C.SELECT last_name, e.hire_date, department_id FROM employees e WHERE (department_id, hire_date) IN (SELECT department_id, max(hire_date) hire_date FROM employees GROUP BY department_id)
- D.SELECT last_name, e.hire_date, department_id FROM employees e JOIN (SELECT department_id, max(hire_date) hire_date FROM employees GROUP BY department_id) me USING (hire_date)
- Answer & Explanation
- Report
Answer : [A, C]
Explanation :
Explanation :
| Options A and B have an ORDER BY clause used in the subquery. An ORDER BY clause can be used in the subquery appearing in the FROM clause, but not in the WHERE clause. Options C and D use the GROUP BY clause in the subquery, and its use is allowed in FROM as well as WHERE clauses. Option D will give an error because the DEPARTMENT_ ID in the SELECT clause is ambiguous and, therefore, doesn’t need to be qualified as e.DEPARTMENT_ID. Another issue with option D is that since you used the USING clause to join, the column used in the USING clause cannot be qualified; e.hire_date in the SELECT clause should be hire_date. |
39.
Jim is trying to add records from the ORDER_DETAILS table to ORDER_DETAIL_HISTORY
for orders placed before the current year. Which INSERT statement would accomplish
his task?
- A.INSERT INTO ORDER_DETAIL_HISTORY VALUES (SELECT * FROM ORDER_DETAIL WHERE ORDER_DATE < TRUNC(SYSDATE,'YY'));
- B.INSERT FROM ORDER_DETAIL INTO ORDER_DETAIL_HISTORY WHERE ORDER_DATE < TRUNC(SYSDATE,'YY');
- C.INSERT INTO ORDER_DETAIL_HISTORY FROM ORDER_DETAIL WHERE ORDER_DATE < TRUNC(SYSDATE,'YY');
- D.INSERT INTO ORDER_DETAIL_HISTORY SELECT * FROM ORDER_DETAIL WHERE ORDER_DATE < TRUNC(SYSDATE,'YY');
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
| When inserting from another table using a subquery, the VALUES clause should not be included. Options B and C are invalid syntaxes for the INSERT statement. |
40.
Which of the following statements will not implicitly begin a transaction?
- A.INSERT
- B.UPDATE
- C.DELETE
- D.SELECT FOR UPDATE
- E.None of the above; they all implicitly begin a transaction, if not started already.
- Answer & Explanation
- Report
Answer : [E]
Explanation :
Explanation :
| If a transaction is not currently open, any INSERT, UPDATE, MERGE, DELETE, SELECT FOR UPDATE, or LOCK statement will implicitly begin a transaction. |
|
|
||||||||||||||||||||||||||||
