--PROB 1

SELECT EMPNO, LASTNAME, BIRTHDATE, SALARY FROM db2inst1.EMPLOYEE
WHERE SALARY > 30000
ORDER BY SALARY DESC;

--PROB 2

SELECT LASTNAME, FIRSTNME, WORKDEPT FROM db2inst1.EMPLOYEE
ORDER BY WORKDEPT DESC, LASTNAME DESC;

--PROB 3

SELECT DISTINCT(EDLEVEL) FROM db2inst1.EMPLOYEE
ORDER BY EDLEVEL DESC;

--PROB 4

SELECT DISTINCT EMPNO, PROJNO FROM db2inst1.EMP_ACT
WHERE EMPNO <= 100
ORDER BY EMPNO;

--PROB 5

SELECT FIRSTNME, LASTNAME, SALARY, BONUS FROM db2inst1.EMPLOYEE
WHERE SEX = 'M';

--PROB 6

SELECT LASTNAME, SALARY, COMM FROM db2inst1.EMPLOYEE
WHERE SALARY > 20000 AND HIREDATE >='1980-01-01';

--PROB 7

SELECT LASTNAME, SALARY, BONUS, COMM FROM db2inst1.EMPLOYEE
WHERE (SALARY > 22000 AND BONUS = 400) OR (BONUS = 500 AND COMM < 1900)
ORDER BY LASTNAME;

--PROB 8

SELECT LASTNAME, SALARY, BONUS, COMM FROM db2inst1.EMPLOYEE
WHERE SALARY > 22000 AND (BONUS = 400 OR BONUS = 500) AND COMM < 1900
ORDER BY LASTNAME;

--PROB 9

SELECT PROJNO, ACTNO, EMSTDATE, EMENDATE FROM db2inst1.EMP_ACT
WHERE PROJNO LIKE 'AD%' AND ACTNO IN (10,80,180)
ORDER BY PROJNO, ACTNO;

--PROB 10

SELECT MGRNO, DEPTNO FROM db2inst1.DEPARTMENT
WHERE MGRNO is not null
ORDER BY MGRNO;

--PROB 11

SELECT EMPNO, LASTNAME, SALARY, BONUS FROM db2inst1.EMPLOYEE
WHERE BONUS BETWEEN 800 and 1000
ORDER BY BONUS, EMPNO;

--PROB 12

SELECT EMPNO, LASTNAME, SALARY, WORKDEPT FROM db2inst1.EMPLOYEE
WHERE WORKDEPT BETWEEN 'A00' and 'C01'
ORDER BY LASTNAME, EMPNO;

--PROB 13

SELECT PROJNO, PROJNAME FROM db2inst1.PROJECT
WHERE PROJNAME like '%SUPPORT%'
ORDER BY PROJNO;

--PROB 14

SELECT DEPTNO FROM db2inst1.DEPARTMENT
WHERE DEPTNO LIKE '_I_'
ORDER BY DEPTNO;

--PROB 15

SELECT LASTNAME, FIRSTNME, MIDINIT, SALARY FROM db2inst1.EMPLOYEE
WHERE JOB NOT IN ('PRESIDENT', 'MANAGER')
ORDER BY SALARY DESC
FETCH FIRST 5 ROWS ONLY;

--JOIN Exmaple

SELECT DEPTNAME, MGRNO, E.EMPNO, E.FIRSTNME, E.LASTNAME
FROM DB2INST1.EMPLOYEE AS E, DB2INST1.DEPARTMENT AS D
WHERE D.MGRNO = E.EMPNO;

SELECT DEPTNAME, E.EMPNO, E.FIRSTNME, E.LASTNAME
FROM DB2INST1.EMPLOYEE AS E, DB2INST1.DEPARTMENT AS D
WHERE D.DEPTNO = E.WORKDEPT;

SELECT DEPTNAME, E.EMPNO, E.FIRSTNME, E.LASTNAME
FROM DB2INST1.EMPLOYEE AS E, DB2INST1.DEPARTMENT AS D
WHERE D.DEPTNO = E.WORKDEPT AND E.LASTNAME LIKE 'S%';

SELECT PROJ.PROJNAME, E.EMPNO, E.FIRSTNME, E.LASTNAME
FROM DB2INST1.EMPLOYEE AS E, DB2INST1.PROJECT AS PROJ, DB2INST1.EMPACT AS A
WHERE E.EMPNO = A.PROJNO AND A.PROJNO = PROJ.PROJNO AND FIRSTNME = 'DANIEL';

SELECT E.LASTNAME, E.FIRSTNME, D.DEPTNAME
FROM DB2INST1.DEPARTMENT AS D, DB2INST1.EMPLOYEE AS E
WHERE E.WORKDEPT = D.DEPTNO
ORDER BY D.DEPTNAME, E.LASTNAME, E.FIRSTNME;

SELECT A.EMPNO, E.FIRSTNME, E.LASTNAME, PROJ.PROJNO, PROJ.PROJNAME, A.ACTNO, A.EMSTDATE
FROM DB2INST1.EMPACT AS A, DB2INST1.PROJECT AS PROJ, DB2INST1.EMPLOYEE AS E
WHERE PROJ.PROJNO = A.PROJNO AND A.EMSTDATE = '2002-10-15' AND A.EMPNO = E.EMPNO
ORDER BY PROJ.PROJNO, A.EMPNO,E.LASTNAME,E.FIRSTNME, A.ACTNO;

SELECT DISTINCT E.WORKDEPT, E.LASTNAME, PROJ.PROJNAME, A.ACTNO
FROM DB2INST1.EMPACT AS A, DB2INST1.PROJECT AS PROJ, DB2INST1.EMPLOYEE AS E
WHERE E.EMPNO = A.EMPNO AND A.PROJNO = PROJ.PROJNO AND E.WORKDEPT BETWEEN 'A00' AND 'C01'
ORDER BY E.WORKDEPT, E.LASTNAME, A.ACTNO;

SELECT D.DEPTNO, EM.LASTNAME AS EM_LASTNAME, E.LASTNAME AS E_LASTNAME, EM.HIREDATE AS EM_LASTNAME, E.HIREDATE AS E_LASTNAME
FROM DB2INST1.DEPARTMENT AS D, DB2INST1.EMPLOYEE AS E, DB2INST1.EMPLOYEE AS EM
WHERE E.WORKDEPT = D.DEPTNO AND D.DEPTNO = 'A00' AND EM.EMPNO = D.MGRNO AND E.HIREDATE < EM.HIREDATE
ORDER BY E.LASTNAME;