The GATE Grind

GATE 2014 CS (CS1) – Question 64

Databases · Relational Model: Relational Algebra, Tuple Calculus, SQL · 2 marks · Multiple choice

Schema: employees(emp-id, first-name, last-name, hire-date, dept-id, salary); departments(dept-id, dept-name, manager-id, location-id). To display last names and hire dates of all latest hires in each department at location 1700, this SQL is issued:

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?

  1. Executes but gives an incorrect result
  2. Executes and gives the correct result
  3. Error because of pairwise comparison
  4. Error because GROUP BY cannot be used with table joins in a subquery

Practise this question in The GATE Grind →

Show answer and explanation

Correct answer: (B) Executes and gives the correct result

Explanation

The inner join filters departments at location1700 and groups by department to obtain each maximum hire date. The outer row-value IN comparison selects employees matching both that department and date, retaining tied latest hires. Thus **B**, it executes and returns the intended result, under the source’s conventional identifier notation.