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.

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

@Pratik Gawali

The employees work for dept-id = 1  (both Bangalore and Mysore branch) and they have the same hire_date.

They joined 2 departments in Bangalore and Mysore on the same hire-date.

What is the expected result according to you?

---

@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.

Answer 2

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 3

i can't comment so i am writing here. Isn't lastname a non aggregate column and GROUPBY clause doesn't contain it so won't there be a syntax error? 

similar to this question: https://gateoverflow.in/3388/gate2008-it-74

can someone clearify this.

Comments

Group By is used in inner query

---

Group By is used in the inner query in both the questions.

---

But, in that query syntax error is for outer query. In GATE-2008Q, 

GROUP By school-id, clause is present for outer query.

Answer 4

(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