Project ForgeGiven the following schema:
employees(emp-id, first-name, last-name, hire-date, dept-id, salary) departments(dept-id, dept-name, manager-id, location-id)
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 (dept-id, hire-date) IN (SELECT dept-id, MAX(hire-date) FROM employees JOIN departments USING(dept-id) WHERE location-id = 1700 GROUP BY dept-id);
What is the outcome?
- AIt executes but does not give the correct result.
- It executes and gives the correct result.
- CIt generates an error because of pairwise comparison.
- DIt generates an error because the GROUP BY clause cannot be used with table joins in a sub-query.
Explanation
The inner query correctly joins employees with departments, filters to location-id 1700, groups by dept-id, and finds each such department's latest hire-date — a valid use of GROUP BY over a joined result. The outer query then matches (dept-id, hire-date) pairs against that result using the standard multi-column IN predicate, which SQL supports for pairwise tuple comparison. The query is syntactically valid and semantically correct, so it executes and gives the correct result.
Source: GATE CS 2014 (Session 1) (2014)
This question is part of a 15-day revision cycle — sign in and Project Forge will bring it back the day before you'd forget it.