GATE CS 2014 Set 1 Q.64 (CS Q.54): SQL Queries and Integrity Constraints

Given 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:

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?

It executes but does not give the correct result.

It executes and gives the correct result.

It generates an error because of pairwise comparison.

It generates an error because the GROUP BY clause cannot be used with table joins in a subquery.

Official answer and solution

Official answer: B
From: Official GATE 2014 answer key

Solution

Analyze SQL pairwise comparison syntax and semantic correctness of the GROUP BY subquery for latest hires.

Analyze the given SQL query:

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);

Subquery analysis:
- The subquery joins employees and departments using dept-id.
- It filters for location-id = 1700 and groups rows by dept-id.
- For each department at location 1700, MAX(hire-date) computes the latest hire date.
- This is syntactically valid SQL.
Pairwise comparison analysis:
- (dept-id, hire-date) IN (...) is standard SQL pairwise comparison (row value constructor comparison).
- It matches employee rows whose department ID and hire date match the department's latest hire date.
Execution outcome:
- The query executes without syntax or runtime error.
- It returns the last names and hire dates of all employees who were hired on the latest hire date in departments located at location 1700.
- Therefore, it executes and gives the correct result.

Final answer

Common trap

Assuming that pairwise comparisons (a, b) IN (...) are invalid in SQL, whereas they are part of standard ANSI SQL.

The answer is the one in the official GATE answer key. The solution is this site's own explanation.

Source

Paper: GATE 2014 Computer Science and Information Technology (CS) Session 1
Question: 64 of 65, printed as CS Q.54
Section: Computer Science and Information Technology

Report an error in this question

GATE questions are the work of the organising institutes. This site is independent and is not affiliated with them.

Question

Solution

Source