Skip to content
GATETECH
Official PYQ · 2014Set 1 · Q54Tuple Calculus and SQLMCQ1 mark

GATE CS 2014 Set 1 Q54 — Tuple Calculus and SQL

Explain with AI

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.\text{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?

Choose one option
View worked solution

Answer: B

The SQL query executes successfully and produces the correct result. Let us analyze the query step-by-step:

  1. Subquery: 
    SELECT dept-id, MAX(hire-date) 
FROM employees JOIN departments USING(dept-id) 
WHERE location-id = 1700 
GROUP BY dept-id
    This subquery joins the employees and departments tables on dept-id, filters for departments located at location-id = 1700, groups the records by dept-id, and returns a set of pairs containing each department's ID along with its latest hire date (using MAX(hire-date)).
  2. Outer Query: 
    SELECT last-name, hire-date  
FROM employees 
WHERE (dept-id, hire-date) IN (...)
    The outer query uses pairwise comparison (dept-id, hire-date) IN (...) to retrieve the last name and hire date of employees whose department and hire date match any of the (dept-id, MAX(hire-date)) pairs returned by the subquery. This correctly identifies the latest hires in their respective departments for the specified location.

Since pairwise comparison and GROUP BY with joins are standard, valid SQL constructs, the query executes without errors and achieves the intended goal.

Free practice · Your answers on this page are not saved.

Published
Report a correction
Keep exploringMore Tuple Calculus and SQL questions