-- 1. 연봉이 12000 이상되는 직원들의 LAST_NAME 및 연봉을 조회한다.
SELECT LAST_NAME
, SALARY
FROM EMPLOYEES
WHERE SALARY >= 12000
;
-- 2. 사원번호가 176 인 사람의 LAST_NAME 과 부서 번호를 조회한다.
SELECT LAST_NAME
, DEPARTMENT_ID
FROM EMPLOYEES
WHERE SALARY >= 12000
;
-- 3. 연봉이 5000 에서 12000의 범위 이외인 사람들의 LAST_NAME 및 연봉을 조회힌다.
SELECT LAST_NAME
, SALARY
FROM EMPLOYEES
WHERE SALARY < 5000
OR SALARY > 12000
;
-- 4. 1998/02/20 일부터 1998/05/01 사이에 고용된 사원들의 LAST_NAME 사번, 고용일자를 조회한다.
-- 고용일자 순으로 정렬한다.
SELECT LAST_NAME
, EMPLOYEE_ID
, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE >= '1998/02/20'
AND HIRE_DATE <= '1998/05/01'
;
SELECT LAST_NAME
, EMPLOYEE_ID
, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE
BETWEEN TO_DATE('2002/02/20')
AND TO_DATE('2005/05/01')
ORDER BY HIRE_DATE
;
-- 5. 20 번 및 50 번 부서에서 근무하는 모든 사원들의 LAST_NAME 및 부서 번호를 알파벳순으로 조회한다.
SELECT LAST_NAME
, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN ( 20, 50 )
ORDER BY LAST_NAME ASC
;
-- 6. 20 번 및 50 번 부서에 근무하며, 연봉이 5000 ~ 12,000 사이인 사원들의 LAST_NAME 및 연봉을 조회한다.
SELECT LAST_NAME
, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN ( 20, 50 )
AND SALARY >= 5000
AND SALARY <= 12000
;
-- 7. 1994년도에 고용된 모든 사람들의 LAST_NAME 및 고용일을 조회한다.
SELECT LAST_NAME
, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE LIKE '1994%'
;
-- 8-1. 매니저가 없는 사람들의 LAST_NAME 및 JOB_ID 를 조회한다.
SELECT LAST_NAME
, JOB_ID
, MANAGER_ID
FROM EMPLOYEES
WHERE MANAGER_ID is null
OR MANAGER_ID = ''
;
-- 8-2. 매니저가 있는 사람들의 LAST_NAME 및 JOB_ID 를 조회한다.
SELECT LAST_NAME
, JOB_ID
, MANAGER_ID
FROM EMPLOYEES
WHERE MANAGER_ID is not null
OR MANAGER_ID != ''
;
-- 9. 커미션을 버는 모든 사원들의 LAST_ANME, 연봉 및 커미션을 조회한다.
-- 연봉 역순, 커미션 역순차로 정렬한다.
SELECT LAST_NAME
, SALARY
, COMMISSION_PCT
FROM EMPLOYEES
WHERE NOT (COMMISSION_PCT is null)
ORDER BY SALARY DESC,
COMMISSION_PCT DESC
;
-- 10. LAST_NAME 의 네번째 글자가 a 인 사원들의 LAST_NAME 을 조회한다.
SELECT LAST_NAME
FROM EMPLOYEES
WHERE LAST_NAME LIKE '___a%'
;
-- 11. LAST_NAME 에 a 및(OR) e 글자가 있는 사원들의 LAST_NAME 을 조회한다.
SELECT LAST_NAME
FROM EMPLOYEES
WHERE LAST_NAME LIKE '%a%'
OR LAST_NAME LIKE '%e%'
;
-- 12. 연봉이 2,500, 3,500, 7000 이 아니며 직업이 SA_REP 이나 ST_CLERK 인 사람들을 조회한다.
SELECT LAST_NAME
, JOB_ID
, SALARY
FROM EMPLOYEES
WHERE SALARY NOT IN ( 2500, 3500, 7000 )
AND JOB_ID IN ( 'SA_REP', 'ST_CLERK' )
;
-- 13-1. 직업이 AD_PRESS 인 사람은 A 등급을, ST_MAN 인 사람은 B 등급을, IT_PROG 인 사람은 C 등급을,
-- SA_REP 인 사람은 D 등급을, ST_CLERK 인 사람은 E 등급을 기타는 0 을 부여하여 조회한다.
SELECT EMPLOYEE_ID
, FIRST_NAME
, LAST_NAME
, DECODE(JOB_ID
, 'AD_PRESS', 'A'
, 'ST_MAN', 'B'
, 'IT_PROG', 'C'
, 'SA_REP', 'D'
, 'ST_CLERK', 'E'
, '0') JOB_GRADE
FROM EMPLOYEES
;
-- 13-2
SELECT *
FROM (
SELECT EMPLOYEE_ID
, FIRST_NAME
, LAST_NAME
, DECODE(JOB_ID
, 'AD_PRESS', 'A'
, 'ST_MAN', 'B'
, 'IT_PROG', 'C'
, 'SA_REP', 'D'
, 'ST_CLERK', 'E'
, '0') JOB_GRADE
FROM EMPLOYEES
)
WHERE JOB_GRADE = 'B'
;
-- 13-3
SELECT EMPLOYEE_ID
, FIRST_NAME
, LAST_NAME
, CASE JOB_ID
WHEN 'AD_PRESS' THEN
'A'
WHEN 'ST_MAN' THEN
'B'
WHEN 'IT_PROG' THEN
'C'
WHEN 'SA_REP' THEN
'D'
WHEN 'ST_CLERK' THEN
'E'
ELSE '0'
END GRADE
FROM EMPLOYEES
;
-- 13-4
SELECT EMPLOYEE_ID
, FIRST_NAME
, LAST_NAME
, CASE
WHEN JOB_ID = 'IT_PROG' THEN
'C'
ELSE '0'
END GRADE
FROM EMPLOYEES
;
-- 13-5
SELECT ROWNUM
, EMPLOYEE_ID
, FIRST_NAME
, LAST_NAME
, CASE (SELECT 98 FROM DUAL)
WHEN 100 THEN
(SELECT SYSDATE - 1 FROM DUAL)
ELSE (SELECT SYSDATE - 1 FROM DUAL)
END GRADE
, CASE (SELECT 98 FROM DUAL)
WHEN 100 THEN
SYSDATE - 2
ELSE SYSDATE - 2
END GRADE
, SYSDATE
FROM EMPLOYEES
;
-- 14. 모든 사원들의 LAST_NAME, 부서 이름 및 부서 번호을 조회한다.
SELECT EMPLOYEE_ID
, LAST_NAME
, DEPARTMENT_NAME
, D.DEPARTMENT_ID
FROM EMPLOYEES E
, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
;
-- 15. 부서번호 30 내의 모든 직업들을 유일한 포맷으로 조회한다. 90 부서 또한 포함한다.
SELECT DISTINCT JOB_ID
, D.DEPARTMENT_ID
FROM EMPLOYEES E
, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.DEPARTMENT_ID IN ( 30, 90 )
ORDER BY JOB_ID
;
-- 16-1. 커미션을 버는 모든 사람들의 LAST_NAME, 부서 명, 지역 ID 및 도시 명을 조회한다.
SELECT LAST_NAME
, DEPARTMENT_NAME
, L.LOCATION_ID
, CITY
FROM EMPLOYEES E
, DEPARTMENTS D
, LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID -- 빨간줄
AND D.LOCATION_ID = L.LOCATION_ID -- 보라줄
;
-- 16-2. 시애틀에 사는 사람 중 커미션을 버는 모든 사람들의 LAST_NAME, 부서 명, 지역 ID 및 도시 명을 조회한다.
-- 좋지 못한 방법이다.
SELECT LAST_NAME
, DEPARTMENT_NAME
, L.LOCATION_ID
, CITY
FROM EMPLOYEES E
, DEPARTMENTS D
, LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID -- 빨간줄
AND D.LOCATION_ID = L.LOCATION_ID -- 보라줄
AND L.CITY = "Seattle"
;
-- 16-3. 시애틀에 사는 사람 중 커미션을 버는 모든 사람들의 LAST_NAME, 부서 명, 지역 ID 및 도시 명을 조회한다.
-- 좋은 방법 - PK로 검색
SELECT LAST_NAME
, DEPARTMENT_NAME
, L.LOCATION_ID
, CITY
FROM EMPLOYEES E
, DEPARTMENTS D
, LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID -- 빨간줄
AND D.LOCATION_ID = L.LOCATION_ID -- 보라줄
AND L.LOCATION_ID = (
SELECT LOCATION_ID
FROM LOCATIONS
WHERE CITY = 'Seattle'
)
;
-- 17. LAST_NAME 이 DAVIES 인 사람보다 후에 고용된 사원들의 LAST_NAME 및 HIRE_DATE 을 조회한다.
SELECT LAST_NAME
, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE >= (
SELECT HIRE_DATE
FROM EMPLOYEES
WHERE LAST_NAME = 'Davies'
)
ORDER BY HIRE_DATE
;
-- 18. 자신의 매니저보다 먼저 고용된 사원들의 LAST_NAME 및 고용일을 조회한다.
SELECT EMP.LAST_NAME
, EMP.EMPLOYEE_ID
, EMP.HIRE_DATE
FROM EMPLOYEES EMP
, EMPLOYEES MGR
WHERE EMP.MANAGER_ID = MGR.EMPLOYEE_ID
AND EMP.HIRE_DATE < MGR.HIRE_DATE
ORDER BY EMP.LAST_NAME
;
-- 19. 회사 전체의 최대 연봉, 최소 연봉, 연봉 총 합 및 평균 연봉을 자연수로 포맷하여 조회한다.
SELECT MAX(SALARY)
, MIN(SALARY)
, SUM(SALARY)
, ROUND (AVG(SALARY))
FROM EMPLOYEES
;
-- 20. 각 JOB_ID 별, 최대 연봉, 최소 연봉, 연봉 총 합 및 평균 연봉을 자연수로 포맷하여 조회한다.
SELECT JOB_ID
, MAX(SALARY) MAX
, MIN(SALARY) MIN
, SUM(SALARY) SUM
, ROUND (AVG(SALARY)) AVG
FROM EMPLOYEES
GROUP BY JOB_ID
ORDER BY JOB_ID
;
-- 21. 동일한 직업을 가진 사원들의 총 수를 조회한다.
SELECT JOB_ID
, COUNT(EMPLOYEE_ID)
FROM EMPLOYEES
GROUP BY JOB_ID
ORDER BY JOB_ID
;
-- 22-1. 매니저로 근무하는 사원들의 총 수를 조회한다.
SELECT COUNT(EMPLOYEE_ID)
FROM EMPLOYEES
WHERE EMPLOYEE_ID IN (
SELECT DISTINCT MANAGER_ID
FROM EMPLOYEES
WHERE MANAGER_ID IS NOT NULL
)
;
-- 22-2. 매니저로 근무하는 사원들의 총 수를 조회한다.
SELECT COUNT(DISTINCT MANAGER_ID)
FROM EMPLOYEES
;
-- 23. 사내의 최대 연봉 및 최소 연봉의 차이를 조회한다.
SELECT MAX(SALARY) - MIN(SALARY)
FROM EMPLOYEES
;
-- 24. 매니저의 사번 및 그 매니저 밑 사원들 중 최소 연봉을 받는 사원의 연봉을 조회한다.
-- 매니저가 없는 사람들은 제외한다.
-- 최소 연봉이 6000 미만인 경우는 제외한다.
-- 연봉 기준 역순으로 조회한다.
SELECT MANAGER_ID
, MIN(SALARY)
FROM EMPLOYEES
WHERE MANAGER_ID IS NOT NULL
GROUP BY MANAGER_ID
HAVING MIN(SALARY) >= 6000
ORDER BY MIN(SALARY) DESC
;
-- 25. 부서 명, 위치 ID, 각 부서 별 사원 총 수, 각 부서 별 평균 연봉을 조회한다.
-- 평균 연봉은 소수점 2 자리까지만 표현한다.
SELECT DEPARTMENT_NAME
, LOCATION_ID
, COUNT(EMPLOYEE_ID)
, ROUND(AVG(SALARY), 2) AVG_SALARY
FROM EMPLOYEES E
, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY DEPARTMENT_NAME
, LOCATION_ID
ORDER BY LOCATION_ID
;
-- 26-1. 총 사원 수 및 1995, 1996, 1997, 1998 년도 별 고용된 사원들의 총 수를 다음과 같이 조회한다.
SELECT DISTINCT(
SELECT COUNT(EMPLOYEE_ID)
FROM EMPLOYEES
) AS CountAll
, (
SELECT COUNT(EMPLOYEE_ID)
FROM EMPLOYEES
WHERE HIRE_DATE LIKE '2001%'
) AS Count2001
, (
SELECT COUNT(EMPLOYEE_ID)
FROM EMPLOYEES
WHERE HIRE_DATE LIKE '2003%'
) AS Count2003
, (
SELECT COUNT(EMPLOYEE_ID)
FROM EMPLOYEES
WHERE HIRE_DATE LIKE '2005%'
) AS Count2005
, (
SELECT COUNT(EMPLOYEE_ID)
FROM EMPLOYEES
WHERE HIRE_DATE LIKE '2006%'
) AS Count2006
FROM EMPLOYEES
;
-- 27-1. 각 부서별 각 직업 별 연봉 총 합 및 각 부서별 연봉 총 합을 조회한다.
SELECT DEPARTMENT_ID
, JOB_ID
, SUM(SALARY)
, (
SELECT SUM(SALARY)
FROM EMPLOYEES E1
WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID
) SumAll
FROM EMPLOYEES E2
GROUP BY DEPARTMENT_ID
, JOB_ID
ORDER BY DEPARTMENT_ID
, JOB_ID
;
-- 27-2. 다음과 같은 포멧으로 각 부서별 각 직업 별 연봉 총 합 및
-- 각 부서별 연봉 총 합을 조회한다.
SELECT DISTINCT JOB_ID
, (
SELECT NVL ( SUM(SALARY), 0 )
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 10
AND JOB_ID = E.JOB_ID
) AS "DEPT10"
, (
SELECT NVL ( SUM(SALARY), 0 )
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 20
AND JOB_ID = E.JOB_ID
) AS "DEPT20"
, (
SELECT NVL ( SUM(SALARY), 0 )
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30
AND JOB_ID = E.JOB_ID
) AS "DEPT30"
, (
SELECT NVL ( SUM(SALARY), 0 )
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 40
AND JOB_ID = E.JOB_ID
) AS "DEPT40"
, (
SELECT NVL ( SUM(SALARY), 0 )
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 50
AND JOB_ID = E.JOB_ID
) AS "DEPT50"
, (
SELECT NVL ( SUM(SALARY), 0 )
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60
AND JOB_ID = E.JOB_ID
) AS "DEPT60"
, (
SELECT NVL ( SUM(SALARY), 0 )
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 70
AND JOB_ID = E.JOB_ID
) AS "DEPT70"
, (
SELECT NVL ( SUM(SALARY), 0 )
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80
AND JOB_ID = E.JOB_ID
) AS "DEPT80"
, (
SELECT NVL ( SUM(SALARY), 0 )
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90
AND JOB_ID = E.JOB_ID
) AS "DEPT90"
, (
SELECT NVL ( SUM(SALARY), 0 )
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
AND JOB_ID = E.JOB_ID
) AS "DEPT100"
, (
SELECT NVL ( SUM(SALARY), 0 )
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 110
AND JOB_ID = E.JOB_ID
) AS "DEPT110"
, (
SELECT NVL ( SUM(SALARY), 0 )
FROM EMPLOYEES
WHERE JOB_ID = E.JOB_ID
) AS "TOTAL"
FROM EMPLOYEES E
GROUP BY JOB_ID
;
-- 27-3. 다음과 같은 포멧으로 각 부서별 각 직업 별 연봉 총 합 및
-- 각 부서별 연봉 총 합을 조회한다.
SELECT JOB_ID
, NVL ( SUM(DECODE (DEPARTMENT_ID, 20, SALARY)), 0 ) "DEPT 20"
, NVL ( SUM(DECODE (DEPARTMENT_ID, 50, SALARY)), 0 ) "DEPT 50"
, NVL ( SUM(DECODE (DEPARTMENT_ID, 80, SALARY)), 0 ) "DEPT 80"
, NVL ( SUM(DECODE (DEPARTMENT_ID, 90, SALARY)), 0 ) "DEPT 90"
, NVL ( SUM(SALARY), 0 ) "TOTAL"
FROM EMPLOYEES
GROUP BY JOB_ID;
-- 28. LAST_NAME 이 Zlotkey 와 동일한 부서에 근무하는 모든 사원들의 사번 및 고용날짜를 조회한다.
-- 결과값에서 Zlotkey 는 제외한다.
SELECT EMPLOYEE_ID
, HIRE_DATE
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (
SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE LAST_NAME = 'Zlotkey'
)
AND LAST_NAME != 'Zlotkey'
;
-- 29. 회사 전체 평균 연봉보다 더 받는 사원들의 사번 및 LAST_NAME 을 조회한다.
SELECT EMPLOYEE_ID
, LAST_NAME
FROM EMPLOYEES
WHERE SALARY > (
SELECT AVG(SALARY)
FROM EMPLOYEES
)
;
-- 30-1. LAST_NAME 에 u 가 포함되는 사원들과 동일 부서에 근무하는 사원들의 사번 및 LAST_NAME 을 조회한다.
SELECT EMPLOYEE_ID
, LAST_NAME
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (
SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE LAST_NAME LIKE '%u%'
)
;
-- 30-2. (VIEW 이용) LAST_NAME 에 u 가 포함되는 사원들과 동일 부서에 근무하는 사원들의 사번 및 LAST_NAME 을 조회한다.
SELECT DISTINCT E.EMPLOYEE_ID
, LAST_NAME
FROM (
SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE LAST_NAME LIKE '%u%'
) U_NAME
, EMPLOYEES E
WHERE U_NAME.DEPARTMENT_ID = E.DEPARTMENT_ID
;
-- 31-1. 위치 ID 가 1700 인 부서에서 근무하는 사원들의 LAST_NAME, 부서 번호 및 JOB_ID 를 조회한다.
SELECT LAST_NAME
, D.DEPARTMENT_ID
, JOB_ID
, D.LOCATION_ID
FROM EMPLOYEES E
, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = 1700
ORDER BY D.DEPARTMENT_ID
, JOB_ID
, LAST_NAME
;
-- 31-2. (VIEW 이용)위치 ID 가 1700 인 부서에서 근무하는 사원들의 LAST_NAME, 부서 번호 및 JOB_ID 를 조회한다.
SELECT LAST_NAME
, DEPARTMENT_ID
, JOB_ID
FROM (
SELECT EMPLOYEE_ID
FROM EMPLOYEES E
, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = 1700
) EMP1700
, EMPLOYEES E
WHERE EMP1700.EMPLOYEE_ID = E.EMPLOYEE_ID
;
-- 32. King 을 매니저로 두고 있는 모든 사원들의 LAST_NAME 및 연봉을 조회한다.
SELECT LAST_NAME
, SALARY
, MANAGER_ID
FROM EMPLOYEES
WHERE MANAGER_ID IN (
SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE LAST_NAME = 'King'
)
;
-- 33. EXECUTIVE 부서에 근무하는 모든 사원들의 부서 번호, LAST_NAME, JOB_ID 를 조회한다.
SELECT D.DEPARTMENT_ID
, DEPARTMENT_NAME
, JOB_ID
, LAST_NAME
FROM EMPLOYEES E
, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND DEPARTMENT_NAME = 'Executive'
ORDER BY JOB_ID
, LAST_NAME
;
-- 34-1. 회사 전체 평균 연봉 보다 더 버는 사원들 중 LAST_NAME 에 u 가 있는 사원들이
-- 근무하는 부서에서 근무하는 사원들의 사번, LAST_NAME 및 연봉을 조회한다.
SELECT DISTINCT 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
)
)
;
-- 34-2. (VIEW 이용)회사 전체 평균 연봉 보다 더 버는 사원들 중 LAST_NAME 에 u 가 있는 사원들이
-- 근무하는 부서에서 근무하는 사원들의 사번, LAST_NAME 및 연봉을 조회한다.
SELECT DISTINCT EMPLOYEE_ID
, LAST_NAME
, SALARY
FROM (
/* 그 사람들의 부서와 원래 EMPLOYEES 테이블과 조인 */
SELECT DEPARTMENT_ID
FROM (
/* 평균 연봉보다 많이 버는 사원들 */
SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE SALARY > (
SELECT AVG(SALARY)
FROM EMPLOYEES
)
) EMP2
, EMPLOYEES E
WHERE EMP2.EMPLOYEE_ID = E.EMPLOYEE_ID
AND E.LAST_NAME LIKE '%u%'
) DEPT
, EMPLOYEES E
WHERE DEPT.DEPARTMENT_ID = E.DEPARTMENT_ID
;
-- 35-1. ST_CLERK 인 직업 ID 를 가진 사원이 없는 부서 ID 를 조회한다.
-- NULL 값은 제외한다. (NOT EXISTS 사용)
SELECT DISTINCT DEPARTMENT_ID
FROM EMPLOYEES E
WHERE NOT EXISTS (
/* 부서 하나씩 검사하며 ST_CLERK라는 직업이
있으면 '1' 반환
없으면 NULL 반환*/
SELECT ''
FROM EMPLOYEES E1
WHERE JOB_ID = 'ST_CLERK'
AND E1.DEPARTMENT_ID = E.DEPARTMENT_ID
)
AND DEPARTMENT_ID IS NOT NULL
ORDER BY DEPARTMENT_ID
;
-- 35-2. ST_CLERK 인 직업 ID 를 가진 사원이 있는 부서 ID 를 조회한다.
-- NULL 값은 제외한다. (NOT EXISTS 사용)
SELECT DISTINCT DEPARTMENT_ID
FROM EMPLOYEES E
WHERE NOT EXISTS (
SELECT '1'
FROM EMPLOYEES E2
WHERE E.EMPLOYEE_ID = E2.EMPLOYEE_ID
AND E2.JOB_ID != 'ST_CLERK'
)
ORDER BY DEPARTMENT_ID
;
-- 36-1. 위치한 부서가 없는 국가 ID 및 국가 이름을 조회힌다.(NOT EXISTS 사용)
SELECT COUNTRY_ID
, COUNTRY_NAME
FROM COUNTRIES C
WHERE NOT EXISTS (
/* */
SELECT '1'
FROM LOCATIONS L
, DEPARTMENTS D
WHERE C.COUNTRY_ID = L.COUNTRY_ID
AND L.LOCATION_ID IN (D.LOCATION_ID)
)
ORDER BY COUNTRY_ID
;
-- 36-2. 위치한 부서가 없는 국가 ID 및 국가 이름을 조회힌다.(NOT EXISTS 사용)
SELECT COUNTRY_ID
, COUNTRY_NAME
FROM COUNTRIES C
WHERE NOT EXISTS (
SELECT '1'
FROM LOCATIONS L
, DEPARTMENTS D
WHERE L.COUNTRY_ID = C.COUNTRY_ID
AND L.LOCATION_ID NOT IN (D.LOCATION_ID)
)
ORDER BY COUNTRY_ID
;
-- 36-3. 위치한 부서가 있는 국가 ID 및 국가 이름을 조회힌다.(NOT EXISTS 사용)
SELECT COUNTRY_ID
, COUNTRY_NAME
FROM COUNTRIES C
WHERE EXISTS (
SELECT '1'
FROM LOCATIONS L2
, DEPARTMENTS D2
WHERE C.COUNTRY_ID = L2.COUNTRY_ID
AND L2.LOCATION_ID = D2.LOCATION_ID
)
;
-- 37. 기존의 직업을 여전히 가지고 있는 사원들의 사번 및 JOB_ID 를 조회한다.
SELECT E.EMPLOYEE_ID
, E.JOB_ID
FROM EMPLOYEES E
WHERE E.EMPLOYEE_ID NOT IN (
SELECT EMPLOYEE_ID
FROM JOB_HISTORY J
WHERE E.EMPLOYEE_ID = J.EMPLOYEE_ID
)
;
-- 41. 커미션을 버는 사원들의 부서와 연봉이 동일한 사원들의 LAST_NAME, 부서 번호 및 연봉을 조회한다.
SELECT LAST_NAME
, DEPARTMENT_ID
, SALARY
, COMMISSION_PCT
FROM EMPLOYEES
WHERE (DEPARTMENT_ID, SALARY) IN (
SELECT DEPARTMENT_ID
, SALARY
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL
)
;
-- 42. 위치 ID 가 1700 인 사원들의 연봉과 커미션이 동일한 사원들의 LAST_NAME, 부서 번호 및 연봉을 조회한다.
SELECT LAST_NAME
, DEPARTMENT_NAME
, SALARY
FROM EMPLOYEES E
, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND (SALARY, NVL(COMMISSION_PCT,0)) IN (
SELECT SALARY
, NVL(COMMISSION_PCT, 0)
FROM EMPLOYEES E2
, DEPARTMENTS D2
WHERE E2.DEPARTMENT_ID = D2.DEPARTMENT_ID
AND LOCATION_ID = 1700
AND E2.EMPLOYEE_ID != E.EMPLOYEE_ID
)
;
-- 42-2. (VIEW)위치 ID 가 1700 인 사원들의 연봉과 커미션이 동일한 사원들의 LAST_NAME, 부서 번호 및 연봉을 조회한다.
SELECT LAST_NAME
, E2.DEPARTMENT_ID
, E2.SALARY
FROM (
/* 연봉, COMMISSION */
SELECT SALARY
, NVL(COMMISSION_PCT,0)
/* LOCATION 1700 */
FROM (
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE LOACTION_ID = 1700
) D3
, EMPLOYEES E2
WHERE E2.DEPARTMENT_ID = D3.DEPARTMENT_ID
) SC
, EMPLOYEES E
;
-- 42-2. (VIEW 강사님)위치 ID 가 1700 인 사원들의 연봉과 커미션이 동일한 사원들의 LAST_NAME, 부서 번호 및 연봉을 조회한다.
SELECT DISTINCT LAST_NAME
, DEPARTMENT_ID
, E.SALARY
FROM (
/* 연봉, COMMISSION */
SELECT SALARY
, NVL(COMMISSION_PCT,0) COMMISSION_PCT
/* LOCATION 1700 */
FROM EMPLOYEES E
, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = 1700
) SAL1700
, EMPLOYEES E
WHERE SAL1700.SALARY = E.SALARY
AND SAL1700.COMMISSION_PCT = NVL(E.COMMISSION_PCT, 0)
;
-- 43. LAST_NAME 이 'Kochhar' 인 사원과 동일한 연봉 및 커미션을 버는 사원들의 LAST_NAME, 부서 번호 및 연봉을 조회한다.
SELECT LAST_NAME
, DEPARTMENT_ID
, SALARY
, COMMISSION_PCT
FROM EMPLOYEES
WHERE (SALARY, NVL(COMMISSION_PCT,0)) IN (
SELECT SALARY
, NVL(COMMISSION_PCT, 0)
FROM EMPLOYEES E2
WHERE LAST_NAME = 'Kochhar'
)
AND LAST_NAME != 'Kochhar'
;
-- 44-1. 직업 ID 가 SA_MAN 인 사원들이 받을 수 있는 최대 연봉 보다 높게 받는 사원들의 LAST_NAME, JOB_ID 및 연봉을 조회한다.
SELECT LAST_NAME
, JOB_ID
, SALARY
FROM EMPLOYEES
WHERE SALARY > (
SELECT MAX_SALARY
FROM JOBS
WHERE JOB_ID = 'SA_MAN'
)
;
-- 44-2. 직업 ID 가 SA_MAN 인 사원들의 최대 연봉 보다 높게 받는 사원들의 LAST_NAME, JOB_ID 및 연봉을 조회한다.
SELECT LAST_NAME
, JOB_ID
, SALARY
FROM EMPLOYEES
WHERE SALARY > (
SELECT MAX(SALARY)
FROM EMPLOYEES
WHERE JOB_ID = 'SA_MAN'
)
;
-- 45. 도시 이름이 T 로 시작하는 지역에 사는 사원들의 사번, LAST_NAME 및 부서 번호를 조회한다.
SELECT EMPLOYEE_ID
, LAST_NAME
, D.DEPARTMENT_ID
, CITY
FROM EMPLOYEES E
, DEPARTMENTS D
, LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID
AND CITY LIKE 'T%'
;
-- 46. 각 부서별 평균 연봉보다 더 받는 동일부서 근무사원들의
-- LAST_NAME, 연봉, 부서번호 및 해당 부서의 평균 연봉을 조회한다.
-- 결과는 부서별 연봉을 기준으로 정렬한다.
SELECT E.LAST_NAME
, E.SALARY
, E.DEPARTMENT_ID
, V.DEP_AVG
FROM EMPLOYEES E
,(
SELECT DEPARTMENT_ID
, ROUND( AVG(SALARY), 0 ) DEP_AVG
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
) V
WHERE E.DEPARTMENT_ID = V.DEPARTMENT_ID
AND E.SALARY > V.DEP_AVG
ORDER BY V.DEP_AVG
;
SELECT LAST_NAME
, SALARY
, DEPARTMENT_ID
-- 47. NOT EXISTS 연산자를 사용하여 매니저가 아닌 사원 이름을 조회한다.
SELECT FIRST_NAME
, EMPLOYEE_ID
, MANAGER_ID
FROM EMPLOYEES E
WHERE NOT EXISTS (
SELECT '1'
FROM EMPLOYEES E2
WHERE E.EMPLOYEE_ID = E2.MANAGER_ID
)
ORDER BY EMPLOYEE_ID
;
-- 48. 소속부서의 평균 연봉보다 적게 버는 사원들의 last_name 을 조회한다.
SELECT LAST_NAME
, SALARY
, E.DEPARTMENT_ID
, V.AVG_SALARY
FROM EMPLOYEES E
,(
SELECT DEPARTMENT_ID
, ROUND(AVG(SALARY), 0) AVG_SALARY
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
) V
WHERE E.DEPARTMENT_ID = V.DEPARTMENT_ID
AND E.SALARY < V.AVG_SALARY
ORDER BY V.AVG_SALARY
, E.SALARY
;
-- 49. 각 사원 별 소속부서에서 자신보다 늦게 고용되었으나 보다 많은 연봉을 받는 사원이 존재하는 모든 사원들의 last_name 을 조회한다.
/* LASTNAME 출력 */
SELECT DISTINCT E.FIRST_NAME
|| E.LAST_NAME
, E.DEPARTMENT_ID
FROM EMPLOYEES E
, EMPLOYEES E2
WHERE E.DEPARTMENT_ID = E2.DEPARTMENT_ID
AND E.HIRE_DATE < E2.HIRE_DATE
AND E.SALARY < E2.SALARY
;
-- LPAD, RPAD
SELECT LPAD('111', 6, '0')
, LPAD('1', 6, '0')
, RPAD('111', 6, '0')
, RPAD('1', 6, '0')
FROM DUAL
;
-- || 컬럼들을 붙여서 출력할 때
SELECT LPAD('111', 6, '0')
|| '--++PLUS'
|| LPAD('1', 6, '0')
|| RPAD('111', 6, '0')
|| RPAD('1', 6, '0') CONCATENATION
FROM DUAL
;