GATE CSE 2014 | Set 1 | Question: 54

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 $\text{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 of the GROUP BY clause cannot be used with table joins in a sub-query.

Comments

The two values can simultaneously be compared using IN. ANY or SOME cannot compare two values.

---

How to know that it executes and gives the correct result?

Answer 1

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

This inner query will give the max hire date of each department whose location_id $=1700$

and outer query will give the last name and hire-date of all those employees who joined on max hire date. 
answer should come to (B) no errors.
And we can use group by and where together, who said we can not :(

Example: create table departments(dept_id number, dept_name varchar2(25), location_id number);
Query: select d1.dept_name,max(d1.location_id)
from departments d1, departments d2
where  d1.dept_name = d2.dept_name
and d1.dept_name='AA'
group by d1.dept_name;

will give output.

Comments

@rohith1001 I guess we just assume that multiple departments are in one location but not the inverse. Gate makes this fuzzy vague unclear questions is what I don't like really.

---

@PreyumKr  Its not vague they have already cleared that they are selecting record from location =1700. So its pretty easy we have to create sample example where location would be 1700 in every tuple but departments would be different.

---

generates an error because of pairwise comparison. ? what does this mean like

Answer 2

We are given two relations:

• $\texttt{employees}(\texttt{emp_id}, \texttt{first_name}, \texttt{last_name}, \texttt{hire_date}, \texttt{dept_id}, \texttt{salary})$
• $\texttt{departments}(\texttt{dept_id}, \texttt{dept_name}, \texttt{manager_id}, \texttt{location_id})$

The task is to retrieve the $\texttt{last_name}$ and $\texttt{hire_date}$ of all employees who are the most recently hired in their respective departments, restricted to departments with $\texttt{location_id} = 1700$.

The given query is:

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

Correctness Analysis

The subquery joins $\texttt{employees}$ and $\texttt{departments}$ on $\texttt{dept_id}$, filters for $\texttt{location_id = 1700}$, groups by $\texttt{dept_id}$, and computes $\texttt{MAX(hire_date)}$ per department. This yields the latest hiring date in each qualifying department.

The outer query selects employees whose $(\texttt{dept_id}, \texttt{hire_date})$ pair matches any $(\texttt{dept_id}, \texttt{MAX(hire_date)})$ pair from the subquery. This correctly identifies all employees hired on the most recent date in their department (among those in location 1700). The tuple comparison syntax is valid in standard SQL.

If multiple employees share the same latest hire date in a department, all are included consistent with the requirement to list “all latest hires”.

Example Instance

Departments:

\[
\begin{array}{|c|c|}
\hline
\texttt{dept_id} & \texttt{location_id} \\
\hline
\texttt{10} & \texttt{1700} \\
\texttt{20} & \texttt{1700} \\
\texttt{30} & \texttt{1800} \\
\hline
\end{array}
\]

Employees:

\[
\begin{array}{|c|c|c|}
\hline
\texttt{last_name} & \texttt{hire_date} & \texttt{dept_id} \\
\hline
\texttt{Chintu}      & \texttt{2020-01-10} & \texttt{10} \\
\texttt{Babu Bhaiya} & \texttt{2022-03-15} & \texttt{10} \\
\texttt{Pappu}       & \texttt{2021-11-01} & \texttt{20} \\
\texttt{Guddu}       & \texttt{2023-06-20} & \texttt{20} \\
\texttt{Chhotu}      & \texttt{2023-06-20} & \texttt{20} \\
\texttt{Ladoo}       & \texttt{2022-12-01} & \texttt{30} \\
\hline
\end{array}
\]

Only departments $\texttt{10}$ and $\texttt{20}$ are in location $\texttt{1700}$.

• In department $\texttt{10}$, the latest hire is $\texttt{Babu Bhaiya}$ on $\texttt{2022-03-15}$.
• In department $\texttt{20}$, the latest hire date is $\texttt{2023-06-20}$, with two employees: $\texttt{Guddu}$ and $\texttt{Chhotu}$.

The subquery returns the pairs:
$(\texttt{10}, \texttt{2022-03-15})$ and $(\texttt{20}, \texttt{2023-06-20})$.

The outer query thus returns:

\[
\begin{array}{|c|c|}
\hline
\texttt{last_name} & \texttt{hire_date} \\
\hline
\texttt{Babu Bhaiya} & \texttt{2022-03-15} \\
\texttt{Guddu}       & \texttt{2023-06-20} \\
\texttt{Chhotu}      & \texttt{2023-06-20} \\
\hline
\end{array}
\]

This is exactly the expected output.

 

The query is syntactically valid, logically correct, and handles ties appropriately.

$\boxed{\text{Answer: B.}}$ It executes and gives the correct result.

Comments

Testing

Answer 3

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

Answer 4

BEST ANSWER☺

Answer 5

(C) It generates an error because of pairwise comparison.

we cannot use where with Group By, we have to use Having  instead

Comments

Seems like oracle allows pairwise comparison and SQL Server does not ...

---

@Arjun Sir,

We can use GROUP BY clause without WHERE clause also,,??

---

Yes you can