Self-join Query.

This page shows the contents of a file that
 was created by running a query
 in SQL Server Management Studio (Express) and
 sending the output to a file. 

The query creates a temporary table,
 runs 5 select statements against it, and
 ends after deleting the temporary table.

The configuration needed to have
 Management Studio (Express) send the output
 to a file is as follows:

### STEP #1:

1.) Open Management Studio (Express).
2.) On the top menu bar, select item 'Query'.
    It is located between 'View' and 'Tools'.
    If you don't see it,
      hit 'New Query' on the Standard Toolbar below.
      It should now be visible on the top menu bar.
3.) Under 'Query' go to the bottom and select
     'Query Options...'
4.) In the left pane, select 'Results->Text',
      check 'Include the query in the result set',
      check 'Right align numeric values',
      under 'Maximum number of characters displayed in each column' 
        change 256 to 35. Click OK. 

These settings remain in effect until you log out.

### STEP #2:

Choose 'Query->Results To->Result to File (Ctrl+Shift+F)'. 

The query output will now be sent to a file
 the next time you hit the red exclamation mark
 to 'Execute' the query.

This step must be repeated before EVERY query execution
 if you want the output to continue to be sent to a file.

### STEP #3:

When you hit the red exclamation mark to execute the query, 
  a popup dialog box prompts you for the file name before 
  executing the query.

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 -------------------
--------------------------------------------------------------------
------------------------*/



(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
      empid dept         salary       mgrid
----------- ----- ------------- -----------
        100 SALES         95000         200
        200 SALES        100000         990
        300 SALES        105000         200
        400 ACCTG        110000         990
        500 ACCTG        115000         400
        600 ACCTG        120000         400
        700 ADMIN        125000         900
        800 ADMIN        130000         900
        900 ADMIN        127000         990
        990 DAMAN         98000        NULL

(10 row(s) affected)

dept  NumOverPaidEmps
----- ---------------
ACCTG               2
ADMIN               1
SALES               1
(3 row(s) affected)

dept  NumOverPaidEmps
----- ---------------
ACCTG               3
ADMIN               2
SALES               2

(3 row(s) affected)

dept        empid
----- -----------
SALES         300
ACCTG         500
ACCTG         600
ADMIN         800

(4 row(s) affected)

dept        empid
----- -----------
SALES         200
SALES         300
ACCTG         400
ACCTG         500
ACCTG         600
ADMIN         800
ADMIN         900

(7 row(s) affected)

Leif Ohman

Self-join Query.