1Z0-047 - Question 3

Filed under: , by:

View the Exhibit and examine the structure of the EMPLOYEES and DEPARTMENTS tables. You want to display the last names and hire dates of all latest hires in their respective departments in the location ID 1700. You issue the following query:

SQL>SELECT last_name, hire_date
FROM employees
WHERE (department_id, hire_date) IN
(SELECT department_id, MAX(hire_date)
FROM employees JOIN departments
USING(department_id)
WHERE location_id = 1700
GROUP BY department_id);

What is the outcome?

A. It executes but does not give the correct result
B. It executes successfully and gives the correct result
C. It generates an error because of the pairwise comparison
D. It generates an error because the GROUP BY clause cannot be used with table joins in a subquery

Exhibit:

EMPLOYEES table:

DEPARTMENTS table:

Theory:

Since the query from the question is a SELECT statement with IN condition and a subquery, which uses INNER JOIN that uses USING keyword, so the here is what you need to know to answer that question - SELECT, SUBQUERY, IN CONDITION and JOINS.

A SUBQUERY or INNER QUERY is a SELECT statement that exists within another SQL statement (QUTER QUERY). A subquery can be used within SELECT, INSERT, UPDATE and DELETE statements. A subquery is non-correlated when it can be executed successfully on its own, whereas a correlated subquery uses values from the outer query. Any valid SELECT statement can qualify as a subquery include those that retrieve data from one or more tables or views, those that use complex expressions or aggregate functions, and those that include the WHERE and GROUP BY clauses, HAVING or ORDER BY clauses, or any other other features available to SELECT statements. IN operator compares one or multiple expressions on the left side of the operator to a set of one or more values on the right side of the operator. When using multiple expressions (like 2 column - pairwise comparison), the number and datatypes of expressions in the list must match on both sides of the operator. When you are specifying an equijoin of columns that have the same name in both tables, the USING (columns) clause indicates the columns to be used. You can use this clause only if the join columns in both tables have the same name. Within this clause, do not qualify the column name with a table name or table alias.

Answers:

A - Wrong - the query is correct and does give the correct results as it displays all employees that were hired last within the department with the location id =1700
B - Correct
C - Wrong - pairwise comparison is correct
D - Wrong - a subquery can be any valid SELECT query with any JOIN and GROUP BY clause

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)