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

Consider the set of relations

  • EMP (Employee-no. Dept-no, Employee-name, Salary)
  • DEPT (Dept-no. Dept-name, Location)

Write an SQL query to:

a)Find all employees names who work in departments located at ‘Calcutta’ and whose salary is greater than Rs.50,000.

b)Calculate, for each department number, the number of employees with a salary greater than Rs. 1,00,000.

asked in Databases by Veteran (59.7k points)
edited by | 1.1k views

2 Answers

+18 votes
Best answer

(a) 

select Employee-name
from EMP, DEPT
where Salary>50000 and EMP.Dept-no=DEPT.Dept-no and Location="Calcutta"


(b)

select Dept-no, count(*)
from EMP where salary > 100000
group by Dept-no
answered by Active (3.3k points)
selected by
0
select Dept-no, count(*)
from EMP where salary > 100000
group by Dept-no

instead of where clause, I think,  will come having some(salary)>100000

because above will give the count value same for each department.

0
I don't think so. Because "Having" clause applies only on grouped tuples whereas "Select" works on individual tuples. Here, we want to group by only those tuples whose salary is greater than 1,00,000.
0
For (b), a department with no employee having salary > 100000 should also be a part of result.

Example, suppose Dept-no 123 does not have a single employee with salary > 100000 then result should also  contain the tuple (123,0).

The query for (b) will only display the (Dept-no, count) tuples in result where count > 0.
0

@Pratik Gawali we cannot expect any records to be outputted when using a GROUP BY clause, when no records exist in your source.The reason is that when you have no records, the GROUP BY clause have nothing to group by, and then is not able to give you any output.

0

@meghna I do agree with you that GROUP BY will not be able to output a record for a dept-no containing no record with salary > 100000 and this is what concerns me as the question is Calculate, for each department number, the number of employees with a salary greater than Rs. 1,00,000.

If the result of the query does not give info about certain departments then how has the query calculated for each department number?

0

 @Pratik Gawali I think in sql with a SELECT-FROM-WHERE block, if I say "retrieve the result for each <some attribute>" it means to retrieve the result for those tuples only which satisfy certain conditions present in WHERE clause or other constructs like HAVING(in conjunction to group by) , unlike the mathematical logic.

I found some examples from Navathe, it may help to understand that "each" doesn't only correspond to "each tuple" but "each tuple which satisfies the condition"

 

 

+4 votes
a.
SELECT EMPLOYEE-NAME FROM EMP WHERE SALARY > 50000
AND DEPT-NO IN (SELECT DEPT-NO FROM DEPT WHERE LOCATION='Calcutta');

b.
SELECT COUNT(EMPLOYEE-NO),DEPT-NO FROM EMP 
GROUP BY (DEPT-NO) HAVING SALARY > 100000;
answered by Loyal (8.5k points)
0
does second query works?

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,456 questions
49,911 answers
165,378 comments
65,897 users