Warmup Queries with DB2:
Examples tables include:
EMPLOYEE (EMPNO, FIRSTNME, LASTNAME, WORKDEPT, SALARY, ...)
DEPT (DEPARTMENT, DEPTNAME, MGRNO, ...)
PROJECT (PROJNO, PROJNAME, DEPTNO, RESPEMP, ...)
...
Query 1: Based on above tables in the sample database, write a query to find if there exists a project which has a responsible employee (PROJECT.RESPEMP) not belonging to the project's owning department (PROJECT.DEPTNO).
Query 2: Check if employee "00001" works on same project(s) as employee "200340".
Query 3: Find employees who earn 2nd highest in the department.
Query 4: Find 2nd highest salary.
Test Referential Integrity Constraints:
I.
a. Create two tables:
MY.STUDENT (sid, sname, deptid), and makes stdid a primary key.
MY.DEPT (deptid, dname) has deptid as primary key.
b. Add constraint to MY.STUDENT, to make deptid a foreign key to MY.DEPT, set integrity constraint as "ON DELETE CASCADE".
(Follow example to add constraint to existing table.)
c. Insert example records for a dept, and add multiple students.
d. Delete the dept. Query student table.
II. Drop tables and repeat the above, except that set integrity constraint as "ON DELETE RESTRICT".
Check the example
Test NULL related queries