51.
When using ANSI join syntax, which clause is used to specify a join condition?
- [A]JOIN
- [B]USING
- [C]VON
- [D]VWHERE
- Answer & Explanation
- Report
Answer : [C]
Explanation :
Explanation :
| The join condition is specified in the ON clause. The JOIN clause specifies the table to be joined. The USING clause specifies the column names that should be used in the join. The WHERE clause is used to specify additional search criteria to restrict the rows returned. |
52.
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 e USING (d.department_id);
- [D]
SELECT d.department_id, d.department_name, e.full_name FROM departments d LEFT OUTER JOIN employees e ON (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. |
53.
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. |
54.
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 hence 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. |
55.
Which line of the code has an error?
1 SELECT department_id, count(*) 2 FROM employees 3 GROUP BY department_id 4 HAVING COUNT(department_id) = 5 (SELECT max(count(department_id)) 6 FROM employees 7 GROUP BY department_id);
- [A]Line 3
- [B]Line 4
- [C]Line 5
- [D]Line 7
- [E]No error
- Answer & Explanation
- Report
Answer : [E]
Explanation :
Explanation :
| There is no error in the statement. The query will return the department number where the most employees are working and the number of employees in that department. |
|
|
||||||||||||||||||||||||||||
