오라클 sql Practice8 [퀴즈 해답]

SQL 처음 공부했을 때 썼던 코드 복습하는 느낌으로 기록합니다.

연습 Table은 오라클에서 처음 제공하는 emp, dept 를 이용하였습니다.

DESC EMPLOYEES;

SELECT * FROM EMPLOYEES;
SELECT * FROM DEPARTMENTS;

SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMPLOYEES' OR TABLE_NAME = 'DEPARTMENT';
--------------------------------------------------1번
SELECT DEPARTMENT_NAME FROM EMPLOYEES E JOIN DEPARTMENTS D ON(E.DEPARTMENT_ID = D.DEPARTMENT_ID)
WHERE FIRST_NAME = 'Steven' AND LAST_NAME = 'King';
--------------------------------------------------2번
SELECT FIRST_NAME FROM EMPLOYEES E JOIN DEPARTMENTS D ON(E.DEPARTMENT_ID = D.DEPARTMENT_ID)
WHERE JOB_ID LIKE 'IT%';
--------------------------------------------------3번
SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, E.DEPARTMENT_ID, D.DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID
FROM EMPLOYEES E JOIN DEPARTMENTS D ON(E.DEPARTMENT_ID = D.DEPARTMENT_ID);
--------------------------------------------------4번
SELECT E2.EMPLOYEE_ID,E1.SALARY FROM EMPLOYEES E1 LEFT OUTER JOIN EMPLOYEES E2 ON(E1.MANAGER_ID = E2.EMPLOYEE_ID)
WHERE E1.MANAGER_ID IS NOT NULL ORDER BY E1.SALARY DESC;
--------------------------------------------------5번
SELECT DEPARTMENT_ID, COUNT(*) FROM EMPLOYEES GROUP BY DEPARTMENT_ID
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM EMPLOYEES GROUP BY DEPARTMENT_ID);
--------------------------------------------------6번
SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID,  HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMPLOYEES
WHERE SALARY > (SELECT MAX(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID HAVING DEPARTMENT_ID = 50);
--------------------------------------------------7번
SELECT DEPARTMENT_ID FROM EMPLOYEES GROUP BY DEPARTMENT_ID
HAVING MIN(SALARY) > (SELECT MIN(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID HAVING DEPARTMENT_ID = 100);
--------------------------------------------------8번
SELECT DEPARTMENT_ID, COUNT(*) FROM EMPLOYEES GROUP BY DEPARTMENT_ID
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM EMPLOYEES GROUP BY DEPARTMENT_ID);