GATE CSE 1999 | Question: 22-b

Question

Consider the set of relations

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

Write an SQL query to:

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

Answer 1

SELECT Dept-no, count(Employee-no) as total_employees
FROM EMP
WHERE Salary > 100000
GROUP BY Dept-no

Comments

i am getting something like this! 

SELECT COUNT(EMPLOYEE-NO),DEPT-NO FROM EMP e,DEPT d WHERE 
e.dept-no=d.dept-no HAVING SALARY > 100000  GROUP BY (DEPT-NO);

 

pls check it once!

---

@Pranavpurkar 

Having Clause can not come before Group By clause.

Answer 2

select Dept-no, count(*)

from EMP group by Dept-no having salary > 100000

Comments

I am also thinking the same.

I think we Can also use having in group by

Answer 3

select Dept-no, count(Employee-no)

from EMP where Salary>100000 group by Dept-no