Lab 7 - Solutions

1. SELECT
       *
   FROM OEHR_EMPLOYEES
   WHERE EMPLOYEE_ID = (
       SELECT MANAGER_ID 
       FROM OEHR_EMPLOYEES 
       WHERE LAST_NAME = 'Greenberg'
   )
   

   SELECT
       *
   FROM OEHR_EMPLOYEES
   WHERE HIRE_DATE > (
       SELECT HIRE_DATE 
       FROM OEHR_EMPLOYEES 
       WHERE LAST_NAME = 'Greenberg'
   )
   

2. SELECT
       *
   FROM OEHR_EMPLOYEES
   WHERE SALARY IN (
       SELECT SALARY
       FROM OEHR_EMPLOYEES
       WHERE FIRST_NAME = 'Frank' OR FIRST_NAME = 'Sandrine'
   )
   ORDER BY SALARY DESC
   

3. select *
   from OEHR_EMPLOYEES
   where SALARY > (
       select SUM(SALARY)
       from OEHR_EMPLOYEES
       where JOB_ID = 'SA_MAN'
   )
   

4. select SUM(SALARY) as "TOTAL_SALARY"
   from OEHR_EMPLOYEES
   where JOB_ID like '%_MGR' or JOB_ID like '%_MAN'
   

5. select *
   from OEHR_EMPLOYEES
   where DEPARTMENT_ID = 90 and SALARY > (
       select AVG(SALARY)
       from OEHR_EMPLOYEES
       where DEPARTMENT_ID = 100
   )
   

6. select *
   from OEHR_DEPARTMENTS
   where DEPARTMENT_ID in (
       select DEPARTMENT_ID
       from (
           select COUNT(*) as "CNT", DEPARTMENT_ID
           from OEHR_EMPLOYEES
           group by DEPARTMENT_ID
           order by CNT desc
       )
       where ROWNUM <= 1
   )
   

   select *
   from OEHR_DEPARTMENTS
   where DEPARTMENT_ID in (
       select DEPARTMENT_ID
       from (
           select COUNT(*) as "CNT", DEPARTMENT_ID
           from OEHR_EMPLOYEES
           group by DEPARTMENT_ID
           order by CNT desc
       )
       where CNT = (
           select MAX(CNT2)
           from (
               select COUNT(*) as "CNT2"
               from OEHR_EMPLOYEES
               group by DEPARTMENT_ID
           )
       )
   )
   

7. select FIRST_NAME, LAST_NAME
   from OEHR_EMPLOYEES, (
       select DEPARTMENT_ID as "DI", MAX(SALARY) as "MS"
       from OEHR_EMPLOYEES
       group by DEPARTMENT_ID
   )
   where DEPARTMENT_ID = DI and SALARY = MS
   

8. select *
   from OEHR_EMPLOYEES e1
   where SALARY < (
       select e2.SALARY
       from OEHR_EMPLOYEES e2
       where e2.EMPLOYEE_ID = e1.MANAGER_ID
   ) and SALARY > (
       select e2.SALARY
       from OEHR_EMPLOYEES e2
       where e2.EMPLOYEE_ID <> e1.MANAGER_ID
       and e2.JOB_ID like '_MGR' or e2.JOB_ID like '_MAN'
   )
   

9. select *
   from OEHR_EMPLOYEES
   where SALARY in (
       select SALARY
       from (
           select SALARY
           from OEHR_EMPLOYEES
           order by SALARY
       )
       where ROWNUM <= 5
   )
   

10. -- No idea...