List all the employees who have atleast one person reporting to themList the employee details if and only if more than 10 employees are presently in department 10

Showing Answers 1 - 21 of 21 Answers

Sangeetha Sivakumar

  • May 23rd, 2007
 

select empid,empname
from employees a
where EXISTS (select manager_id from employees where empid=a.empid)

Enrique

  • Sep 23rd, 2007
 

/*
 * b) List the employee details if and only if more than 10 employees are presently in department 10
*/
/*This Query Is More Generic instead of Department 10, it searches for any Department*/
SELECT *
FROM   Eml
WHERE  Dpt IN (SELECT   CASE
                          WHEN COUNT(Dpt) > 10 THEN Dpt
                          ELSE NULL
                        END
               FROM     Eml
               GROUP BY Dpt);

This query lists all the employees who have atleast one person reporting

select ename, cnt
from (select ename, count(*) over (partition by mgr) cnt from emp order by cnt)
where cnt >1;

  Was this answer useful?  Yes

Use the following query. we are just using the same table twice to find the employess who are having some employee under them or they are mgr

SELECT emp_tab.ename,

emp_tab.job,

emp_tab.deptno

FROM scott.emp emp_tab,

scott.emp mgr_tab

WHERE mgr_tab.mgr = emp_tab.empno

GROUP BY emp_tab.ename,

emp_tab.job,

emp_tab.deptno

HAVING COUNT (mgr_tab.empno) > 0

  Was this answer useful?  Yes

debjit20

  • Apr 14th, 2010
 

SELECT employee_id, last_name, job_id, department_id
FROM employees outer
WHERE EXISTS (SELECT ’X’ FROM employees
WHERE manager_id =outer.employee_id);

SELECT employee_id, employee_name, ........
FROM employee a
WHERE a.dept_id IN (SELECT dept_id FROM employee GROUP BY dept_id having count(*) > 10)

  Was this answer useful?  Yes

  1  select manager_id,last_name,count(employee_id) over (partition by manager_id ) cnt from employe
  2* order by cnt
SQL> /

MANAGER_ID LAST_NAME                        CNT
---------- ------------------------- ----------
       103 Lorentz                           1
       205 Gietz                               1
       201 Fay                                 1
           King                                   1
       101 Whalen                          2
           Higgins                              2
       102 Ernst                              2
           Hunold                               2
       149 Grant                              3
           Taylor                                 3
           Abel                                   3
       124 Rajs                                4
           Vergas                               4
           Davies                               4
           Matos                                 4
       100 Mourgos                         5
           Zlotkey                                5
           De Haan                             5
           Kochhar                              5
           Hartstein                             5



This is for employees > 2 not for 10 as I donot have that many employees in my table :)

  1  select employee_id,last_name,department_id from employees where department_id in
  2*  (select department_id from employees group by department_id having count(employee_id) > 2)
SQL> /

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID
----------- ------------------------- -------------
        100 King                                 90
        101 Kochhar                              90
        102 De Haan                              90
        103 Hunold                               60
        104 Ernst                                60
        107 Lorentz                              60
        124 Mourgos                              50
        141 Rajs                                 50
        142 Davies                               50
        143 Matos                                50
        144 Vergas                               50
        149 Zlotkey                              80
        174 Abel                                 80
        176 Taylor                               80

  Was this answer useful?  Yes

krishsidd

  • Nov 26th, 2010
 

CREATE TABLE EMP ( EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
);

INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

COMMIT;

select * from emp e where exists (select null from emp e1 where e1.mgr = e.empno);

select * from emp e where 10 < (select count(1) from emp e1 where e1.deptno = e.deptno);

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions