Ritam Das Ritam Das - 7 months ago 8
SQL Question

SQL Query to Retrieve the details of those employees who works in a department having head count more than 5

I have the following Table.

STAFF


STAFFNO STAFFNAME DESIGNATI SALARY DEPTNO
---------- ---------- --------- ---------- ----------
1000 Rajesh Manager 35000 1
1001 Manoj Caretaker 7420.35 1
1002 Swati HR 22500 3
1003 Suresh HR 23400 3
1004 Najim Mangager 17200 2
1006 Ritesh Prgrmr 23500 2
1005 Nisha Prgrmr 24852 1
1007 Rajib Security 6547 3
1008 Neeraj Prgrmr 17300 1
1009 Dushant Prgrmr 16500 1
1010 Pradyut Manager 26300 2
1011 Manisha Prgrmr 21500 2
1012 Janak Security 8500 2


Now I want to run a query on oracle (SQL*Plus) in which I can retrieve the details of those employees who works in a department having 5 or more head count.(e.g. deptno. 1 and deptno. 2 have 5 employees working in them)

Can you help me with the Oracle query to retrieve that? Thanks in advance.

Answer

You need create a sub query or perform a JOIN.

With a JOIN first you need to know what department has more that 5 employees.

 SELECT DEPTNO
 FROM STAFF
 GROUP BY DEPTNO
 HAVING  COUNT(*) >= 5

Now you join both result

SELECT S.*
FROM STAFF S
JOIN  ( SELECT DEPTNO
        FROM STAFF
        GROUP BY DEPTNO
        HAVING  COUNT(*) >= 5 ) F
ON S.DEPTNO = F.DEPTNO

Subquery version:

SELECT S.*
FROM STAFF S
WHERE S.DEPTNO IN ( SELECT DEPTNO
                    FROM STAFF
                    GROUP BY DEPTNO
                    HAVING  COUNT(*) >= 5 )