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.