/*------------------------
/*------------------------
This page shows the contents of a file that
was created by running a query
in SQL Server 2005 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.
To recreate this entire file, by running it as a new query,
1.) remove the top line (but not the identical second line,
which is needed to comment out these instructions).
2.) remove everything below the line further down that says:
"END OF QUERY - OUTPUT FOLLOWS".
3.) Copy and paste what remains into the query window.
4.) Execute the query as instructed above.
------------------------*/
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)
Date created: January 16, 2009.