edited by
14,357 views
49 votes
49 votes

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?

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

4 Answers

Best answer
51 votes
51 votes
    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.

edited by
1 votes
1 votes
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);
1 votes
1 votes

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.

1 flag:
✌ Low quality (PreyumKr)
–4 votes
–4 votes

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

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

Answer:

Related questions

43 votes
43 votes
4 answers
2