LAB6.SQL

1. SELECT LAST_NAME, JOB_ID, DEPARTMENT_ID FROM EMPLOYEES
   WHERE LOWER(LAST_NAME) != 'ZLOTKEY' AND LOWER(FIRST_NAME) != 'ZLOTKEY' AND (DEPARTMENT_ID =
   (SELECT DEPARTMENT_ID FROM EMPLOYEES
   WHERE LOWER(FIRST_NAME) = 'ZLOTKEY' OR LOWER(LAST_NAME) = 'ZLOTKEY'));

2. SELECT LAST_NAME, SALARY, HIRE_DATE FROM EMPLOYEES
   WHERE HIRE_DATE >
   (SELECT HIRE_DATE FROM EMPLOYEES
   WHERE LOWER(FIRST_NAME) = 'DAVIES' OR LOWER(LAST_NAME) = 'DAVIES') OR SALARY > ( SELECT SALARY FROM EMPLOYEES 
   WHERE LOWER(FIRST_NAME) = 'GRANT' OR LOWER(LAST_NAME) = 'GRANT')
   ORDER BY 3;

3. SELECT CITY, NVL(STATE_PROVINCE, 'UNKNOWN') AS "PROVINCE", POSTAL_CODE FROM LOCATIONS WHERE COUNTRY_ID IN 
   (SELECT COUNTRY_ID FROM COUNTRIES WHERE COUNTRY_NAME LIKE '%C%' OR COUNTRY_NAME LIKE '%C%')
   ORDER BY CITY ASC;

4. SELECT LAST_NAME, JOB_ID, SALARY FROM EMPLOYEES
   WHERE DEPARTMENT_ID =  (SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE UPPER(DEPARTMENT_NAME) = 'ACCOUNTING')
   ORDER BY SALARY DESC, JOB_ID;

5. SELECT LAST_NAME, JOB_ID, SALARY FROM EMPLOYEES
   WHERE SALARY = (SELECT SALARY FROM DEPARTMENTS
   WHERE LOWER(DEPARTMENT_NAME) = 'SALES')
   ORDER BY SALARY ASC, LAST_NAME;

6. SELECT LAST_NAME, SALARY FROM EMPLOYEES WHERE (DEPARTMENT_ID, SALARY) IN 
   (SELECT DEPARTMENT_ID, AVG(SALARY) FROM EMPLOYEES
   GROUP BY DEPARTMENT_ID)
   ORDER BY SALARY DESC, LAST_NAME;