Skip to content
Related Articles

Related Articles

Improve Article

GATE | GATE-CS-2014-(Set-1) | Question 65

  • Last Updated : 15 Oct, 2019

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:

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?
(A) It executes but does not give the correct result.
(B) It executes and gives the correct result.
(C) It generates an error because of pairwise comparison.
(D) It generates an error because the GROUP BY clause cannot be used with table joins in a subquery


Answer: (B)

Explanation: The given query uses below inner query.

SELECT dept-id, MAX(hire-date)
     FROM employees JOIN departments USING(dept-id)
     WHERE location-id = 1700
     GROUP BY dept-id

The inner query produces last max hire-date in every department located at location id 1700.

The outer query simply picks all pairs of inner query. Therefore, the query produces correct result.



SELECT last-name, hire-date
     FROM employees
     WHERE (dept-id, hire-date) IN
     (Inner-Query); 


Quiz of this Question

Attention reader! Don’t stop learning now.  Practice GATE exam well before the actual exam with the subject-wise and overall quizzes available in GATE Test Series Course.

Learn all GATE CS concepts with Free Live Classes on our youtube channel.

My Personal Notes arrow_drop_up
Recommended Articles
Page :