Ace Test Series: Databases-Sql Query

Question

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 $1000.$ 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 $= 1000$

     GROUP BY dept-id);

What is the outcome?

$1.$It generates an error because the GROUP BY clause cannot be used with table joins in a sub-query

 
$2.$It generates an error because of a pairwise comparison.

 
$3.$It executes but does not give the correct result.

 
$4.$It executes and gives the correct result.

Comments

mention in the title that, from which test series this question had took.

---

ACE.

---

i mean, edit your question and update the test series name

Answer 1

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


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


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