Database Oracle SQL - 실습 6

#1
SELECT LAST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 
(SELECT DEPARTMENT_ID  FROM  EMPLOYEES
WHERE  LAST_NAME = 'Zlotkey') 
AND LAST_NAME <> 'Zlotkey';

#2
SELECT EMPLOYEE_ID, LAST_NAME
FROM EMPLOYEES
WHERE SALARY > (SELECT AVG(SALARY)
FROM EMPLOYEES)
ORDER BY SALARY;

#3
SELECT EMPLOYEE_ID, LAST_NAME
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE LAST_NAME LIKE '%u%');

#4
SELECT LAST_NAME, DEPARTMENT_ID, JOB_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE LOCATION_ID = 1700);

#5
SELECT LAST_NAME, SALARY
FROM EMPLOYEES
WHERE MANAGER_ID = (SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE LAST_NAME = 'King' AND EMPLOYEE_ID = 100);

#6
SELECT DEPARTMENT_ID, LAST_NAME, JOB_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'Executive');

#7
SELECT EMPLOYEE_ID, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE LAST_NAME LIKE '%u%')
AND SALARY > (SELECT AVG(SALARY)
FROM EMPLOYEES);

#8
SELECT EMPLOYEE_ID, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY >= (SELECT AVG(E.SALARY)
FROM EMPLOYEES E, DEPARTMENTS D
WHERE D.LOCATION_ID IN 
(SELECT DISTINCT LOCATION_ID
FROM LOCATIONS
WHERE COUNTRY_ID = 'US'));

#9
SELECT EMPLOYEE_ID, LAST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE (DEPARTMENT_ID, SALARY) IN (SELECT DEPARTMENT_ID, MAX(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID)
ORDER BY DEPARTMENT_ID;

#10
SELECT E.EMPLOYEE_ID, E.LAST_NAME, E.SALARY, E.DEPARTMENT_ID
FROM EMPLOYEES E INNER JOIN (SELECT DEPARTMENT_ID, MAX(SALARY) MAXSALARY
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID) E2
ON E.SALARY = E2.MAXSALARY AND E.DEPARTMENT_ID = E2.DEPARTMENT_ID;  

#11
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM DEPARTMENTS D
WHERE EXISTS(SELECT DEPARTMENT_ID 
FROM EMPLOYEES E
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID);

#12
SELECT ROWNUM, EMPLOYEE_ID, LAST_NAME, SALARY
FROM (SELECT EMPLOYEE_ID, LAST_NAME, SALARY
FROM EMPLOYEES
ORDER BY SALARY ASC)
WHERE ROWNUM <= 5;