LAB5.SQL

1. SELECT D.DEPARTMENT_NAME, L.CITY, L.STATE_PROVINCE, L.POSTAL_CODE FROM DEPARTMENTS D JOIN LOCATIONS L USING(LOCATION_ID)
   WHERE D.DEPARTMENT_NAME NOT LIKE 'S%'
   ORDER BY 2, 1;

2. SELECT E.LAST_NAME ||', '|| E.FIRST_NAME AS "LAST, FIRST", E.JOB_ID, D.DEPARTMENT_NAME FROM EMPLOYEES E JOIN DEPARTMENTS D ON(E.DEPARTMENT_ID = D.DEPARTMENT_ID)
   WHERE DEPARTMENT_NAME LIKE '%C%' OR DEPARTMENT_NAME LIKE '%C%'
   ORDER BY D.DEPARTMENT_NAME, E.LAST_NAME;

3. SELECT E.LAST_NAME ||', '|| E.FIRST_NAME AS "LAST, FIRST", E.JOB_ID, D.DEPARTMENT_NAME FROM EMPLOYEES E, DEPARTMENTS D
   WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND (DEPARTMENT_NAME LIKE '%C%' OR DEPARTMENT_NAME LIKE '%C%')
   ORDER BY D.DEPARTMENT_NAME, E.LAST_NAME;

4. SELECT E.FIRST_NAME ||':'|| E.LAST_NAME ||'.' AS "FULL NAME", D.DEPARTMENT_NAME, L.CITY, L.POSTAL_CODE, L.STATE_PROVINCE, E.MANAGER_ID FROM EMPLOYEES 
   E JOIN DEPARTMENTS D ON(E.DEPARTMENT_ID = D.DEPARTMENT_ID) JOIN LOCATIONS L ON(L.LOCATION_ID = D.LOCATION_ID)
   WHERE L.CITY IN( 'OXFORD', 'SOUTHLAKE'); 

5. SELECT E.FIRST_NAME ||':'|| E.LAST_NAME ||'.' AS "FULL NAME", D.DEPARTMENT_NAME, L.CITY, L.POSTAL_CODE, L.STATE_PROVINCE, E.MANAGER_ID 
   FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
   AND (L.LOCATION_ID = D.LOCATION_ID)
   AND L.CITY IN( 'OXFORD', 'SOUTHLAKE', 'TORONTO')
   ORDER BY D.DEPARTMENT_NAME;

6. SELECT D.DEPARTMENT_NAME, MAX(E.SALARY) AS "HIGHEST", MIN(E.SALARY) AS "LOWEST", ROUND(AVG(E.SALARY),0) AS "AVERAGE" FROM EMPLOYEES E JOIN DEPARTMENTS D
   USING (DEPARTMENT_ID)
   GROUP BY DEPARTMENT_NAME 
   HAVING MAX(E.SALARY) < 10000 ORDER BY 2, 4;

7. SELECT EMP.LAST_NAME AS "EMPLOYEE LAST NAME", EMP.SALARY AS "EMPLOYEE SALARY", SUPV.LAST_NAME AS "SUPERVISOR LAST NAME", SUPV.SALARY AS "SUPERVISOR SALARY" 
   FROM EMPLOYEES EMP FULL OUTER JOIN EMPLOYEES SUPV ON(EMP.EMPLOYEE_ID = SUPV.MANAGER_ID)
   WHERE EMP.SALARY > 9000;