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

Answer 1

Correct Answer - Option 2 : It executes and gives the correct result.

Concept:

In inner query and outer query are correct and following all properties:

• GROUP BY clause is used with the select statement.
• GROUP BY clause is placed after the where clause.
• Whatever attribute is present in GROUP BY clause remaining all attribute should be associated with aggregate functions in select statement.
• Two value can be compared simultaneously using IN  but any and some cannot.
• To join two table we should have one common attribute . ( here dept-id is common )

Hence this query executes and gives correct answer; hence option 2 is the correct answer