Starting
Control Center Console
DB2
Remote Database Behavior
Common
DB2 Commands
Exporting
and Importing Data
Notes
from Steves Advanced SQL Class (CF281)
Starting Control Center Console
Starting DB2 CC From AIX
The current version of WEA sets the JAVA_HOME environment variable, which causes errors when invoking the DB2 Command Console.
1) Allow remote host access by invoking the command "xhost +" from an AIX window.
2) Log on as the DB2 account you wish to invoke the CC from (i.e. "su - wasinst").
3) Dependant upon what terminal emulation access you are using, you must set the JAVA_ HOME environment variable appropriately.
-> When using DSView for your access method to connect to your AIX box, for DB2 7.2.7 you probably WILL NOT have to unset the JAVA_HOME environment variable using the command "unset JAVA_HOME".
-> When using DSView for your access method to connect to your AIX box, for DB2 8.2 you will have to install Java 1.3.1 (using WEA CD1, change to the JAVA 131 directory (i.e. "/nas/WEA_R430/03070443/cdlinks/cd1/aixjava3/jdk") and use the instructions in Installing Java 1.1.8 Manually, but use the Java 131 filesets) . Once this is done you do not have to have a "JAVA_HOME" environment variable set.
(OR)
-> When using VNCView, set the JAVA_HOME environment variable to "/usr/jdk_base" (i.e. "export JAVA_HOME=/usr/jdk_base"), and set your display variable to the display number listed in the label at the top of your VNC window (i.e. if the label on your window states "root's X11 desktop (b80aix104.raleigh.ibm.com:2)", then your export should reflect it accordingly (i.e. "export DISPLAY=rushtest55.raleigh.ibm.com:2.0").
4) Invoke the command to start the DB2 Control Center, (i.e.
"db2cc
&").
From Windows:
[Start][Run] db2cmd
or
[Start][Program][DB2 Control Center]
For a DB2 configuration where you have a remote database, it is necessary to create a connection from the local to the remote database machine. This is done by the use of the "catalog" command (a Remote Database Catalog Example follows). On the local machine, the DB2 client must be installed, and on the remote machine, the full DB2 installation must be performed. Once these steps are complete, additional operations must be performed on both machines. On the remote machine, you must create and populate the appropriate database(s). On the remote machine, dependant upon what db2 instance you used, the operations are different. If you use the same DB2 instance on both machines, you only need to catalog the remote node. If you have different instances on the two machines, you must catalog the remote node as well as catalog a link to each database you wish to connect to on the remote machine.
Also, when connecting to a remote database from a local machine, you
must use the alias you catalogged as the connection value.
Remote Database Catalog Example
Where: wasinst represents the Application Server database instance
<db_hostname> represents the hostname of the machine where the
component
databases were installed
<db_port> represents a port number that is planned to use for
WebSphere
Application Server, such as 55555. Setup Manager will handle the client
configuration for WebSphere Application Server.
su - wasinst
db2 catalog tcpip node LOOPWPS remote <db_hostname> server
<db_port>
db2 catalog db XWPSDB as WPSDB at node LOOPWPS
db2 catalog db WMSDB as WMSDB at node LOOPWPS
db2 catalog db OFLNBRS as OFLNBRS at node LOOPWPS
(OR) if this is a local db
su - db2inst1
db2 catalog tcpip node LOOPBACK remote
127.0.0.1 server 55555
db2 catalog db XWPSDB as WPSDB at node LOOPBACK
db2 catalog db XWMSDB as WMSDB at node LOOPBACK
You can get help on most commands by typing "db2 ?" followed by the command you want help on (i.e. "db2 ? list tables").
Note: When using DB2 "select" command and you get an SQL error, you
might need to get to the "DB2" prompt for these types of queries to
succeed
(i.e. first log in as your instance name (e.g. "su - wasinst"),
and
from the prompt type "db2" and press "Enter". This will put you in a
DB2
command mode and your "select"s might work)
The following commands are typed at the command prompt.
db2 get admin cfg
db2 get dbm cfg
db2 list node
directory
Display connectivity to remote databases
db2 list db
directory
List all databases and tables
db2 get
instance
Displays current instance
db2 connect to
yourdatabasename
Connect to database called "yourdatabasename"
db2 connect
reset
Disconnect from the connected database
Connect to database called "yourdatabasename" using ID
"yourusername"
using password "youruserpassword"
db2 connect to yourdatabasename user yourusername using
youruserpassword
db2 list
tables
List all tables for the database I am connected to
db2 list tablespaces show
details
Show more detail
db2 list tables for
all
Displays all tables in the connected database
(the output of this shows the schema
db2 select * from
wea.dmsfiles
Select all rows from table wea.dmsfiles
(wea=schema and dmsfiles is the table)
db2 select * from wea.dmsfiles where
filename='string'
Select but get specific
db2 delete from wea.dmsfiles where
filename='string'
Delete and be specific
db2 -tvf
webraska.ddl
Execute a file (webraska.ddl) that has a bunch of sql statements
db2icrt inst1
set DB2INSTANCE=inst1
db2 set
-all
Shows db2 env. vars
db2level (ie. WR21254 = fixpack # on CD)
db2 get dbm cfg | find /i "SVC"
set DB2INSTANCE=DB2 (cd
/winnt/system32/drivers/etc/services)
db2 update dbm cfg using SVCENAME 50004 (for current
instance)
db2sampl -k
db2cc
Starts command console
db2 update monitor switches using bufferpool on
db2 get snapshot for bufferpools on sample
db2 -tvf crtblsp (script file for creating lab2 tables)
db2 list history all for musicdb | more (list history and pipe
to "more")
db2 list active databases
db2 list applications show details
db2 force applications (112, 113)
db2 uncatalog db
blahblahblah
Get rid of an DB alias
db2 uncatalog node
blahblahblah
Get rid of a node
db2 activate database musicdb
db2licm -l
Display licensing status for DB2
db2 get monitor switches
lsof -i
:50001"
Is DB2 listening (where 50001 is the port on which db2 normally listens)
Monitor Recording Switches
Switch list for node 0
Buffer Pool Activity Information (BUFFERPOOL) = OFF
Lock
Information
(LOCK) = OFF
Sorting
Information
(SORT) = OFF
SQL Statement
Information
(STATEMENT) = OFF
Table Activity
Information
(TABLE) = OFF
Unit of Work
Information
(UOW) = OFF
db2 update monitor switches using lock on
db2 get snapshot for locks on musicdb
db2 export to file of ixf messages d:\blah\messages.msg select name,
artno,classification from artists
(the above can be any sort of select, including complex joins)
db2 import from file of ixf lobs from d:\cf28\lobs\ commitcount 5
message
import.msg
insert_update to artists
(the commitcount specifies how often to do commit, otherwise the commit
is only done at end)
(the "insert_update" affects (re-)population of data, see notes)
db2 load from file1 of ixf rowcount 100 warningcount 10 messages
d:\cf28.load.msg
insert to artists
(Steve, if it busts, fix problem and)
db2 load from file1 of ixf rowcount 100 warningcount 10 messages
d:\cf28.load.msg
RESTART to artists
Backup Database (users must be off)
db2 backup db musicdb to d:\backup
Steve, the following is the normal sequence
db2icrt inst1 (unix includes "-u fencdid")
db2 attach to inst1 user userid using password
db2 create db musicdb
db2 get dbm cfg
db2 list db directory on d:
db2 connect to musicdb user userid using password
db2 -tvf crtables.mem (to run a script file "crtables.mem")
UNIX > db2set -lr | find -i "PIN"
> db2iauto -on (instancename)
starts....
Steve, the following is the normal sequence for
Keiths commands to do lab3/4
db2 connect to musicdb
db2 -tvf crtables
db2 -tvf grants
db2 -tvf criva
db2 -tvf crricct
When busted, the command processor had died and left a java.exe
process
which had a row/table
locked. Use the next command to determine the process and then kill
it with the "force" command
db2 list applications show detail
Auth
Id
Application Name
Appl.
Application
Id
Seq# Number of Coordinating
Coordinator
Status
Status Change
Time
Node
DB Name DB Path
Handle Agents Node
Number
pid/thread
------------------------------ -------------------- ----------
------------------------------ ---- ---------- ------------
--------------- ------------------------------
-------------------------- -------- -------- --------------------
INST1 javaw.exe 60 *LOCAL.INST1.020319154239 0001 1 0 286 UOW Waiting Not Collected MUSICDB D:\INST1\NODE0000\SQL00002\
INST1 javaw.exe 55 *LOCAL.INST1.020319154749 0001 1 0 328 UOW Waiting Not Collected MUSICDB D:\INST1\NODE0000\SQL00002\
INST1 db2bp.exe 67 *LOCAL.INST1.020319154937 0001 1 0 350 UOW Waiting Not Collected MUSICDB D:\INST1\NODE0000\SQL00002\
INST1 javaw.exe 69 *LOCAL.INST1.020319155002 0001 1 0 271 UOW Waiting Not Collected MUSICDB D:\INST1\NODE0000\SQL00002\
(Steve, then)
db2 force applications (55)
connect to sample user inst1 using inst1
select * from org
select * from department
to change disks on windows, just type "c:\" (or whichever disk you
want
to go to)
To create/execute inline SQL code
Database must be created already,
Contents of "class_function.mem" =
connect to sample@
create function class_function ( in_value integer)
returns char(20)
Language SQL
CONTAINS SQL
return
CASE
WHEN in_value > 15 then '4 weeks vacation'
WHEN in_value > 10 then '3 weeks vacation'
WHEN in_value > 5 then '2 weeks vacation'
ELSE 'Wait another year'
END@
## Steve, then from DB2 command line prompt:
db2 connect to sample
db2 -td@ -vf class_function.mem
db2 select name,years,class_function (years) from staff where dept=20
## and results are:
NAME YEARS 3
--------- ------ --------------------
Sanders 7 2 weeks vacation
Pernal 8 2 weeks
vacation
James - Wait
another year
Sneider 8 2 weeks vacation
4 record(s) selected.
C:\>db2 select name,years,class_function (years) from staff where dept=20
NAME YEARS 3
--------- ------ --------------------
Sanders 7 2 weeks vacation
Pernal 8 2 weeks
vacation
James - Wait
another year
Sneider 8 2 weeks vacation
4 record(s) selected.
Notes
from Steves Advanced SQL Class (CF281)
## LAB 2 PROB 1
CREATE TABLE EMPDEPT
(EMPNO CHAR(6) NOT NULL,
LASTNAME VARCHAR(15) NOT NULL,
SALARY DECIMAL(9,2) CHECK(SALARY <= 50000),
DEPTNO CHAR(3) NOT NULL,
DEP_NAME VARCHAR(36) NOT NULL,
CONSTRAINT DEP_SAL
CHECK (SALARY <= 28000 OR DEPTNO <> 'E11'))
CREATE UNIQUE INDEX EMPIND ON EMPDEPT(EMPNO);
CREATE TABLE HIGH_SALARY_RAISE
(EMPNO CHAR(6) NOT NULL,
PREV_SAL DECIMAL(9,2) NOT NULL,
NEW_SAL DECIMAL(9,2) NOT NULL);
## LAB 2 PROB 2
CREATE UNIQUE INDEX EMPIND ON EMPDEPT(EMPNO)
ALTER TABLE EMPDEPT
ADD PRIMARY KEY (EMPNO);
ALTER TABLE EMPDEPT
ADD FOREIGN KEY (EMPNO) REFERENCES CF132.EMP ON DELETE CASCADE;
ALTER TABLE EMPDEPT
ADD FOREIGN KEY (DEPTNO) REFERENCES CF132.DEPT ON DELETE
RESTRICT;
## LAB 2 PROB 3
(YOU MIGHT NEED TO DROP THIS TRIGGER USING "DROP TRIGGER HIGH_SAL RESTRICT")
CREATE TRIGGER HIGH_SAL
AFTER UPDATE OF SALARY ON EMPDEPT
REFERENCING OLD AS O NEW AS N
FOR EACH ROW
MODE DB2SQL
WHEN (N.SALARY >= O.SALARY * 1.1)
INSERT INTO HIGH_SALARY_RAISE
VALUES (N.EMPNO, O.SALARY, N.SALARY)
## LAB 2 PROB 4
INSERT INTO EMPDEPT
SELECT E.EMPNO, E.LASTNAME, E.SALARY,
D.DEPTNO, D.DEPTNAME
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.WORKDEPT = D.DEPTNO
AND ((E.SALARY <= 50000 AND E.WORKDEPT <> 'E11')
OR (E.SALARY <= 28000 AND E.WORKDEPT = 'E11'));
## LAB 2 PROB 5
UPDATE EMPDEPT SET SALARY = 30000 WHERE EMPNO = '000280';
## LAB 2 PROB 6
UPDATE EMPDEPT SET SALARY = 25000 WHERE EMPNO = '000160';
SELECT * FROM HIGH_SALARY_RAISE;
## LAB 2 PROB 7
CREATE VIEW VEMPPAY (EMPNO, LASTNAME, WORKDEPT, TOTAL_EARNINGS)
AS SELECT EMPNO, LASTNAME, WORKDEPT, SALARY + BONUS + COMM
FROM EMPOYEE;
SELECT WORKDEPT, AVG(TOTAL_EARNINGS) AS AVG_TOTAL_EARNINGS
FROM VEMPPAY
GROUP BY WORKDEPT
## LAB 2 PROB 8
CREATE VIEW VEMP1
AS SELECT EMPNO, LASTNAME, SALARY, WORKDEPT
FROM TESTEMP
WHERE SLARAY < 50000;
SELECT * FROM VEMP1
ORDER BY EMPNO;
UPDATE VEMP1
SET SALARY = 51000
WHERE EMPNO = '000020';
SELECT EMPNO, LASTNAME, SALARY, WORKDEPT
FROM TESTEMP
WHERE EMPNO = '000020';
## LAB 2 PROB 9
UPDATE TESTEMP
SET SALARY = 41250
WHERE EMPNO = '000020';
CREATE VIEW VEMP2
AS SELECT EMPNO, LASTNAME, SALARY, WORKDEPT
FROM TESTEMP
WHERE SALARY < 50000
WITH CHECK OPTION;
SELECT * FROM VEMP2
ORDER BY EMPNO;
UPDATE VEMP2
SET SALARY = 55000
WHERE EMPNO = '000050';
## THE ABOVE STATEMENT IS NOT TO WORK FOR IT VIOLATES THE PREVOIOUS CHECK OPTION
SELECT EMPNO, LASTNAME,SALARY, WORKDEPT
FROM TESTEMP
WHERE EMPNO = '000050';
## EXERCISE 3 PROBLEM 1
SELECT WORKDEPT, EMPNO, LASTNAME
FROM VEMPLOYEE, VDEPARTMENT
WHERE WORKDEPT = DEPTNO
AND DEPTNAME = 'INFORMATION CENTER'
## EXERCISE 3 PROBLEM 2
SELECT WORKDEPT, EMPNO, LASTNAME
FROM VEMPLOYEE INNER JOIN VDEPARTMENT
ON WORKDEPT = DEPTNO
WHERE DEPTNAME = 'INFORMATION CENTER'
## EXERCISE 3 PROBLEM 3
SELECT EMPNO, LASTNAME,WORKDEPT, PROJNAME
FROM VEMPLOYEE INNER JOIN VPROJECT
ON WORKDEPT = DEPTNO
ORDER BY PROJNAME
## EXERCISE 3 PROBLEM 4
SELECT EMPNO, LASTNAME,WORKDEPT, PROJNAME
FROM VEMPLOYEE LEFT OUTER JOIN VPROJECT
ON WORKDEPT = DEPTNO
ORDER BY EMPNO
## EXERCISE 3 PROBLEM 5
SELECT EMPNO, LASTNAME,WORKDEPT, PROJNAME
FROM VEMPLOYEE RIGHT OUTER JOIN VPROJECT
ON WORKDEPT = DEPTNO
ORDER BY PROJNAME
## EXERCISE 3 PROBLEM 6
SELECT EMPNO, LASTNAME,WORKDEPT, PROJNAME
FROM VEMPLOYEE FULL OUTER JOIN VPROJECT
ON WORKDEPT = DEPTNO
## EXERCISE 4 PROBLEM 1
SELECT EMPNO, FIRSTNME, LASTNAME,
CASE
WHEN SALARY < 25000 THEN
'LOW'
WHEN SALARY BETWEEN 25000 AND 30000 THEN
'NOMAL'
WHEN SALARY > 30000 THEN
'HIGH'
ELSE NULL
END AS INCOME
FROM EMPLOYEE
WHERE WORKDEPT = 'D11'
## EXERCISE 4 PROBLEM 2
SELECT LASTNAME, SALARY,
CASE WHEN SALARY >= 30000 THEN SALARY -30000
ELSE 30000 - SALARY
END AS DIFFERENCE
FROM EMPLOYEE
WHERE WORKDEPT = 'D11'
ORDER BY DIFFERENCE
## EXERCISE 4 PROBLEM 3
SELECT WORKDEPT,
CASE WHEN SUM(BONUS) > 0 AND
SUM(SALARY+BONUS+COMM)
> 0
THEN
DECIMAL(DECIMAL(SUM(BONUS), 8,2) *
100.00/
DECIMAL(SUM(SALARY+BONUS+COMM), 8,2),
8,2)
ELSE NULL
END AS PERCENTAGE_BONUS
FROM EMPLOYEE
GROUP BY WORKDEPT
## EXERCISE 4 PROBLEM 4
SELECT LASTNAME,
CAST(100*COMM/SALARY AS DECIMAL(8,3)) AS COMM_PERCENTAGE
FROM EMPLOYEE
WHERE CASE WHEN COMM=0 OR SALARY=0
THEN NULL
ELSE COMM/SALARY
END > 0.08
## EXERCISE 5 PROBLEM 1
SELECT EMPNO, LASTNAME, DEPTNAME
FROM EMPLOYEE, DEPARTMENT
WHERE WORKDEPT = DEPTNO
AND EMPNO NOT IN (SELECT EMPNO FROM EMP_ACT)
## EXERCISE 5 PROBLEM 2
SELECT EMPNO, SALARY, WORKDEPT
FROM EMPLOYEE E
WHERE SALARY > (SELECT AVG(SALARY)
FROM EMPLOYEE
WHERE WORKDEPT = E.WORKDEPT)
ORDER BY WORKDEPT, EMPNO
## EXERCISE 5 PROBLEM 3
SELECT WORKDEPT, COUNT(*) AS EMP_COUNT
FROM EMPLOYEE
WHERE WORKDEPT <> 'A00'
GROUP BY WORKDEPT
HAVING COUNT(*) = (SELECT COUNT(*)
FROM EMPLOYEE
WHERE WORKDEPT = 'A00')
## EXERCISE 5 PROBLEM 4
SELECT EMPNO, LASTNAME, SALARY, WORKDEPT
FROM EMPLOYEE
WHERE WORKDEPT <> 'D11'
AND SALARY > ANY (SELECT SALARY
FROM EMPLOYEE
WHERE WORKDEPT = 'D11')
ORDER BY EMPNO;
(OR STEVE)
SELECT EMPNO, LASTNAME, SALARY, WORKDEPT
FROM EMPLOYEE
WHERE WORKDEPT <> 'D11'
AND SALARY > (SELECT MIN(SALARY)
FROM EMPLOYEE
WHERE WORKDEPT = 'D11')
ORDER BY EMPNO;
## EXERCISE 5 PROBLEM 5
SELECT EMPNO, LASTNAME, SALARY, WORKDEPT
FROM EMPLOYEE
WHERE WORKDEPT <> 'D11'
AND SALARY > ALL (SELECT SALARY
FROM EMPLOYEE
WHERE WORKDEPT = 'D11')
ORDER BY EMPNO;
(OR STEVE)
SELECT EMPNO, LASTNAME, SALARY, WORKDEPT
FROM EMPLOYEE
WHERE WORKDEPT <> 'D11'
AND SALARY > (SELECT MAX(SALARY)
FROM EMPLOYEE
WHERE WORKDEPT = 'D11')
ORDER BY EMPNO;
## EXERCISE 5 PROBLEM 6
SELECT E.EMPNO, LASTNAME, COUNT(*) AS COUNT_ACT
FROM EMPLOYEE E, EMP_ACT EA
WHERE E.EMPNO = EA.EMPNO
GROUP BY E.EMPNO, LASTNAME
HAVING COUNT(*) >= ALL (SELECT COUNT(*)
FROM EMP_ACT
GROUP BY EMPNO)
## EXERCISE 5 PROBLEM 7
SELECT E.EMPNO, LASTNAME, ACTNO
FROM EMPLOYEE E JOIN EMP_ACT EA
ON E.EMPNO = EA.EMPNO
WHERE EXISTS (SELECT * FROM EMP_ACT
WHERE YEAR(EMSTDATE) = 1982)
## EXERCISE 6 PROBLEM 1
SELECT EMPNO, LASTNAME,
SUBSTR(LASTNAME, LENGTH(LASTNAME),1) AS LASTCHAR
FROM EMPLOYEE
WHERE LASTNAME LIKE '%N'
(STEVE, OR)
SELECT EMPNO, LASTNAME,
SUBSTR(LASTNAME, LENGTH(LASTNAME),1) AS LASTCHAR
FROM EMPLOYEE
WHERE SUBSTR(LASTNAME, LENGTH(LASTNAME),1) = 'N'
## EXERCISE 6 PROBLEM 2
SELECT PROJNO, PROJNAME,DEPTNO,
COALESCE(MAJPROJ,'NO MAJOR PROJECT') AS MAJPROJ
FROM PROJECT
WHERE DEPTNO IN ('D01', 'D11')
ORDER BY PROJNO;
## EXERCISE 6 PROBLEM 3
SELECT EMPNO, LASTNAME, WORKDEPT, SALARY,
DECIMAL(SALARY * 0.0375 + 0.5, 5,0) AS AMOUNT
FROM EMPLOYEE
WHERE WORKDEPT = 'E11'
(STEVE, OR)
SELECT EMPNO, LASTNAME, WORKDEPT, SALARY,
DECIMAL(ROUND(SALARY * 0.0375 ,0),9,0) AS AMOUNT
FROM EMPLOYEE
WHERE WORKDEPT = 'E11'
## EXERCISE 6 PROBLEM 4
SELECT EMPNO, LASTNAME, WORKDEPT, SALARY,
INTEGER(SALARY * 0.0375 + 0.50) AS AMOUNT
FROM EMPLOYEE
WHERE WORKDEPT = 'E11'
## EXERCISE 6 PROBLEM 5
SELECT WORKDEPT, CAST(RTRIM(JOB) AS VARCHAR(10))
!! ': '!! LASTNAME AS LISTING
FROM EMPLOYEE
WHERE SEX = 'F'
## EXERCISE 6 PROBLEM 6
SELECT EMPNO, YEAR(HIREDATE - BIRTHDATE) AS YEARS,
MONTH(HIREDATE - BIRTHDATE) AS MONTHS,
DAY(HIREDATE - BIRTHDATE) AS DAYS
FROM EMPLOYEE
WHERE YEAR(HIREDATE - BIRTHDATE) > 30
ORDER BY EMPNO
## EXERCISE 6 PROBLEM 7
SELECT PROJNO, PROJNAME, PRSTDATE, PRENDATE,
DAYS(PRENDATE) - DAYS(PRSTDATE) AS DAYS_DURATION
FROM PROJECT
WHERE PRENDATE - 10 MONTHS < PRSTDATE
## EXERCISE 6 PROBLEM 8
SELECT E.EMPNO, LASTNAME, FIRSTNME, ACTNO, EMSTDATE,
DAYS(EMENDATE) - DAYS(EMSTDATE) AS DAYS_DURATION
FROM EMPLOYEE E JOIN EMP_ACT EA
ON E.EMPNO = EA.EMPNO
WHERE EMSTDATE = (SELECT MAX(EMSTDATE) FROM EMP_ACT
WHERE EMPNO = E.EMPNO)
AND E.WORKDEPT = 'D11'
## EXERCISE 6 PROBLEM 9
SELECT (DAYS('2000-01-01') - DAYS('1969-07-20')) / 7 AS WEEKS
FROM EMPLOYEE
WHERE EMPNO = '000010'
## EXERCISE 6 PROBLEM 7
SELECT HIREDATE, LASTNAME
FROM EMPLOYEE
WHERE DAYOFWEEK(HIREDATE) IN (1,7)
## EXERCISE 8 PROBLEM 1
SELECT TEMP.HIREYEAR, AVG(TEMP.TOTAL_EARNINGS) AS AVG_TOTAL_EARNINGS
FROM
(SELECT YEAR(HIREDATE) AS HIREYEAR,
SALARY + BONUS + COMM AS TOTAL_EARNINGS
FROM EMPLOYEE
WHERE HIREDATE BETWEEN '1970-01-01' AND '1980-12-31') AS TEMP
GROUP BY TEMP.HIREYEAR
## EXERCISE 8 PROBLEM 2
SELECT MAX(TEMP1.AVG_TOTAL_EARNINGS) AS HIGHEST_AVERAGE
FROM
(SELECT TEMP.HIREYEAR AS HIRED,
AVG(TEMP.TOTAL_EARNINGS) AS AVG_TOTAL_EARNINGS
FROM
(SELECT YEAR(HIREDATE) AS HIREYEAR,
SALARY + BONUS + COMM AS TOTAL_EARNINGS
FROM EMPLOYEE
WHERE HIREDATE BETWEEN '1970-01-01' AND '1980-12-31') AS TEMP
GROUP BY TEMP.HIREYEAR) AS TEMP1
## EXERCISE 8 PROBLEM 2.5
SELECT TEMP.HIREYEAR, AVG(TEMP.TOTAL_EARNINGS) AS AVG_TOTAL_EARNINGS
FROM (SELECT YEAR(HIREDATE) AS HIREYEAR,
SALARY + BONUS + COMM AS TOTAL_EARNINGS
FROM EMPLOYEE
WHERE HIREDATE BETWEEN '1970-01-01' AND '1980-12-31') AS TEMP
GROUP BY TEMP.HIREYEAR
HAVING AVG(TEMP.TOTAL_EARNINGS) >= ALL
(SELECT AVG(TEMP1.TOTAL_EARNINGS)
FROM (SELECT YEAR(HIREDATE) AS HIREYEAR,
SALARY + BONUS + COMM AS TOTAL_EARNINGS
FROM EMPLOYEE WHERE HIREDATE BETWEEN '1970-01-01'
AND '1980-12-31' ) AS TEMP1
GROUP BY TEMP1.HIREYEAR)
## EXERCISE 8 PROBLEM 3
SELECT EMPNO, SALARY, AVG_TAB.AVG_SAL, E.WORKDEPT
FROM EMPLOYEE E INNER JOIN (SELECT WORKDEPT, AVG(SALARY) AS
AVG_SAL
FROM EMPLOYEE
GROUP BY WORKDEPT) AS AVG_TAB
ON E.WORKDEPT = AVG_TAB.WORKDEPT
ORDER BY E.WORKDEPT, EMPNO
## EXERCISE 8 PROBLEM 3.B
SELECT EMPNO, SALARY, AVG_TAB.AVG_SAL, E.WORKDEPT
FROM EMPLOYEE E , (SELECT WORKDEPT, AVG(SALARY) AS AVG_SAL
FROM EMPLOYEE
GROUP BY WORKDEPT) AS AVG_TAB
WHERE E.WORKDEPT = AVG_TAB.WORKDEPT
ORDER BY E.WORKDEPT, EMPNO