Create table #employees
(empid int Primary Key, dept char(5), salary float(8), mgrid int);
GO
insert into #employees values(100, 'SALES', 95000, 200);
insert into #employees values(200, 'SALES', 100000, 990);
insert into #employees values(300, 'SALES', 105000, 200);
insert into #employees values(400, 'ACCTG', 110000, 990);
insert into #employees values(500, 'ACCTG', 115000, 400);
insert into #employees values(600, 'ACCTG', 120000, 400);
insert into #employees values(700, 'ADMIN', 125000, 900);
insert into #employees values(800, 'ADMIN', 130000, 900);
insert into #employees values(900, 'ADMIN', 127000, 990);
insert into #employees values(990, 'DAMAN', 98000, NULL);
GO
---------------------------------------------------------- Query #0:
select * from #employees;
GO
---------------------------------------------------------- Query #1:
--Count how many employees in each department are
-- paid more than their managers.
-- Only count non-managerial employees.
select E.dept
, count(M.mgrid) As NumOverPaidEmps -- ignore mgr-CEO comparisons
--, count(*) As NumOverPaidEmps -- includes mgr-CEO comparisons
from #employees E, #employees M
where E.salary > M.salary and E.mgrid = M.empid
and M.mgrid IS NOT NULL -- ignore cases of mgr paid more than CEO
group by E.dept
;
GO
---------------------------------------------------------- Query #2:
--Count how many employees in each department are
-- paid more than their managers.
--Same as above, but include managers this time.
select E.dept
--, count(M.mgrid) As NumOverPaidEmps -- ignore mgr-CEO comparisons
, count(*) As NumOverPaidEmps -- includes mgr-CEO comparisons
from #employees E, #employees M
where E.salary > M.salary and E.mgrid = M.empid
--and M.mgrid IS NOT NULL -- ignore cases of mgr paid more than CEO
group by E.dept
;
GO
---------------------------------------------------------- Query #3:
--Identify the non-managerial employees we counted in query 1
select E.dept, E.empid
from #employees E, #employees M
where E.salary > M.salary and
E.mgrid = M.empid
and M.mgrid IS NOT NULL -- ignore cases of mgr paid more than CEO
--group by E.dept
;
GO
---------------------------------------------------------- Query #4:
--Identify the employees and managers we counted in query 2
select E.dept, E.empid
from #employees E, #employees M
where E.salary > M.salary and
E.mgrid = M.empid
--and M.mgrid IS NOT NULL -- ignore cases of mgr paid more than CEO
--group by E.dept
;
GO
------------------------------------------------- Done, cleaning up:
drop table #employees;
GO
--------------------------------------------------------------------
------------------ END OF QUERY - OUTPUT FOLLOWS -------------------
--------------------------------------------------------------------
------------------------*/