The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
x
+18 votes
2.5k views

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 of the GROUP BY clause cannot be used with table joins in a sub-query.
asked in Databases by Veteran (111k points) | 2.5k views

3 Answers

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

answered by Boss (41.5k points)
edited by
+11
group by and where can be used together. But, where should be done first.
+1

What is difference between USING and ON in sql ?

+7

The USING clause

The USING clause is used if several columns share the same name but you don’t want to join using all of these common columns. The columns listed in the USING clause can’t have any qualifiers in the statement, including the WHERE clause.

The ON clause

The ON clause is used to join tables where the column names don’t match in both tables. The join conditions are removed from the filter conditions in the WHERE clause.

0

" hire dates of all latest hires in their respective departments "

but when we writing query then we writing max(hire-date)

What that exactly mean?

+4
@srestha it means we want to have the latest hire date for ex 16082017>15082017 we expect year would be there !
0
@saxena yes, thank u:)
+1
One small doubt, this query is a co-related subquery or  a non co-related subquery?? As employee table is used both in outer and inner queries but no aliasing is done here...pls can anyone confirm on this part..
+1

@ aspirant_18  Not co-related query. It's purely subquery based question.

Co-related : if the reference of outer query is used within inner query then we call it co-related. Co-related queries will execute from top to bottom, and subqueries execute from bottom to top.

+2

exmple

employees(emp-id, first-name, last-name, hire-date, dept-id, salary)

emp-id first-name last-name hire-date dept-id sal
1 Raj  Malhotra 2 July 2018 1 50000
2 Robert sinha 3 July 2018 1 100000
3 Yogesh kapoor 3 July 2018 2 70000
4 Nirav Modi 6 July 2018 2 400000

departments(dept-id, dept-name, manager-id, location-id)

dept-id dept-name manager-id location-id
1 Executive 1 1700
2 Finance 2 1700
SELECT dept-id, MAX(hire-date) 
    FROM employees JOIN departments USING(dept-id) 
    WHERE location-id =1700 
    GROUP BY dept-id

will return (1,3 July 2018) &( 2,6 July 2018) 

SELECT last-name, hire-date  
    FROM employees 
    WHERE (dept-id, hire-date) IN (1,3 July 2018) &( 2,6 July 2018) 

will return ( sinha ,3 July 2018 ) & (Modi , 6 July 2018)

0

plz explain the meaning of the line highlighted

The USING clause

The USING clause is used if several columns share the same name but you don’t want to join using all of these common columns. The columns listed in the USING clause can’t have any qualifiers in the statement, including the WHERE clause.

 

0

"The columns listed in the USING clause can’t have any qualifiers in the statement" means you can not use Aliasing in SELECT clause(ex- Select  A.dept_id) & WHERE Clause(ex-  Where A.dept_id=B.dept_id) If you apply join condition by "USING" Clause. But you may use aliasing with other column names if it is not in "USING" clause.

Select dept_id, A.dept_name       / / can not use A.dept_id

from Employee  A JOIN  Department B USING( dept_id)

where A.ssn=B.essn  ;            / / can not use A.dept_id >100

0 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);
answered by Loyal (8.7k points)
–4 votes

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

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

answered by Active (3.3k points)
+13
That is right. But in the given query where is not used with Group By. Where is used to restrict the tuples given to Group By. After group by, we cannot use "where" and must use "having" to restrict the groups being grouped.
0
we can use where clause before group by clause, but not after group by.
0
Seems like oracle allows pairwise comparison and SQL Server does not ...
0
@Arjun Sir,

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

Related questions



Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true

44,149 questions
49,639 answers
163,311 comments
65,807 users