---------------------연습문제----------------------
--1. 연봉이 12000 이상되는 직원들의 LAST_NAME 및 연봉을 조회한다.
SELECT LAST_NAME
, SALARY
FROM EMPLOYEES E
WHERE SALARY >=12000
;
--2. 사원번호가 176 인 사람의 LAST_NAME 과 부서 번호를 조회한다.
SELECT LAST_NAME
,DEPARTMENT_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 176
;
--3. 연봉이 5000 에서 12000의 범위 이외인 사람들의 LAST_NAME 및 연봉을 조회힌다.
SELECT LAST_NAME
,SALARY
FROM EMPLOYEES
WHERE NOT (SALARY > 5000
AND SALARY <12000)
ORDER BY SALARY
;
--4. 2005/02/20 일부터 2005/05/01 사이에 고용된 사원들의
-- LAST_NAME 사번, 고용일자를 조회한다.
-- 고용일자 순으로 정렬한다.
SELECT LAST_NAME
,EMPLOYEE_ID
,HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE >= TO_DATE('2005-02-20' , 'YYYY-MM-DD')
AND HIRE_DATE <= TO_DATE('2005-05-01' , 'YYYY-MM-DD')
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
;
--6. 20 번 및 50 번 부서에 근무하며, 연봉이 5000 ~ 12,000 사이인 사원들의
-- LAST_NAME 및 연봉을 조회한다.
SELECT LAST_NAME
,SALARY
FROM EMPLOYEES
WHERE (DEPARTMENT_ID =20
OR DEPARTMENT_ID =50)
AND SALARY > 5000
AND SALARY < 12000
;
--7. 2004년도에 고용된 모든 사람들의 LAST_NAME 및 고용일을 조회한다.
SELECT LAST_NAME
,HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE >= TO_DATE('2004-01-01' , 'YYYY-MM-DD')
AND HIRE_DATE <= TO_DATE('2004-12-31' , 'YYYY-MM-DD')
;
--8. 매니저가 없는 사람들의 LAST_NAME 및 JOB_ID 를 조회한다.
SELECT LAST_NAME
,JOB_ID
FROM EMPLOYEES
WHERE MANAGER_ID IS NULL
;
--9. 커미션을 버는 모든 사원들의 LAST_ANME, 연봉 및 커미션을 조회한다.
-- 연봉 역순, 커미션 역순차로 정렬한다.
SELECT LAST_NAME
,SALARY
,COMMISSION_PCT
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT 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 및 e 글자가 있는 사원들의 LAST_NAME 을 조회힌다.
SELECT LAST_NAME
FROM EMPLOYEES
WHERE LAST_NAME LIKE '%a%'
AND 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. 직업이 AD_PRESS 인 사람은 A 등급을,
--ST_MAN 인 사람은 B 등급을,
--IT_PROG 인 사람은 C 등급을,
--SA_REP 인 사람은 D 등급을,
--ST_CLEAK 인 사람은 E 등급을
--기타는 0 을 부여하여 조회한다.
SELECT JOB_ID
,DECODE(
JOB_ID
,'AD_PRESS','A'
,'ST_MAN','B'
,'IT_PROG','C'
,'SA_REP','D'
,'ST_CLERK','E'
,'0'
) G
FROM EMPLOYEES
;
--14. 모든 사원들의 LAST_NAME, 부서 번호 및 부서 이름을 조회한다.
SELECT LAST_NAME
,E.DEPARTMENT_ID
,DEPARTMENT_NAME
FROM EMPLOYEES E
,DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
;
--15. 부서번호 30 내의 모든 직업들을 유일한 포맷으로 조회한다.
-- 90 부서 또한 포함한다.
SELECT DISTINCT JOB_ID
,LOCATION_ID
FROM EMPLOYEES E
,DEPARTMENTS D
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
AND (D.DEPARTMENT_ID <= 30
OR D.DEPARTMENT_ID = 90)
;
--16. 커미션을 버는 모든 사람들의
-- LAST_NAME, 부서 명, 지역 ID 및 도시 명을 조회한다.
SELECT LAST_NAME
,DEPARTMENT_NAME
,L.LOCATION_ID
,CITY
FROM EMPLOYEES E,
DEPARTMENTS D,
LOCATIONS L
WHERE (D.DEPARTMENT_ID = E.DEPARTMENT_ID
AND L.LOCATION_ID = D.LOCATION_ID)
AND COMMISSION_PCT IS NOT NULL
;
--17. LAST_NAME 이 Davies 인 사람보다 후에 고용된 사원들의
-- LAST_NAME 및 HIRE_DATE 을 조회한다.
SELECT E.LAST_NAME
,E.HIRE_DATE
FROM EMPLOYEES E
,(
SELECT *
FROM EMPLOYEES
WHERE LAST_NAME = 'Davies'
)E1
WHERE E.EMPLOYEE_ID > E1.EMPLOYEE_ID
;
SELECT LAST_NAME
,HIRE_DATE
FROM EMPLOYEES E
WHERE HIRE_DATE >= (
SELECT HIRE_DATE
FROM EMPLOYEES
WHERE LAST_NAME = 'Davies'
)
;
--18. 자신의 매니저보다 먼저 고용된 사원들의 LAST_NAME 및 고용일을 조회한다
SELECT E.LAST_NAME
,E.HIRE_DATE
,M.LAST_NAME MANAGER_LAST_NAME
,M.HIRE_DATE MANAGER_HIRE_DATE
FROM EMPLOYEES E ----부하직원
,EMPLOYEES M ----상사
WHERE E.MANAGER_ID = M.EMPLOYEE_ID
AND E.HIRE_DATE < M.HIRE_DATE
;
--19. 회사 전체의 최대 연봉, 최소 연봉, 연봉 총 합 및 평균 연봉을 자연수로 포맷하여 조회한다.
SELECT MAX(SALARY)
,MIN(SALARY)
,SUM(SALARY)
,ROUND(AVG(SALARY),0)
FROM EMPLOYEES
;
--20. 각 JOB_ID 별, 최대 연봉, 최소 연봉, 연봉 총 합 및 평균 연봉을 자연수로 포맷하여 조회한다.
SELECT JOB_ID
,MAX(SALARY)
,MIN(SALARY)
,SUM(SALARY)
,ROUND(AVG(SALARY),0)
FROM EMPLOYEES
GROUP BY JOB_ID
;
--21. 동일한 직업을 가진 사원들의 총 수를 조회한다
SELECT JOB_ID
,COUNT(1)
FROM EMPLOYEES
GROUP BY JOB_ID
;
--22. 매니저로 근무하는 사원들의 총 수를 조회한다
SELECT COUNT(DISTINCT MANAGER_ID) MANAGER#
,MANAGER_ID
,MIN(SALARY)
FROM EMPLOYEES
GROUP BY MANAGER_ID
ORDER BY MIN(SALARY) DESC
;
SELECT DISTINCT MANAGER_ID MANAGER#
FROM EMPLOYEES
;
--23. 사내의 최대 연봉 및 최소 연봉의 차이를 조회한다.
SELECT MAX(SALARY)-MIN(SALARY) DIFF
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)
FROM DEPARTMENTS D
,EMPLOYEES E
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY DEPARTMENT_NAME
,LOCATION_ID
;
--26. 총 사원 수 및 2001, 2002, 2003, 2004 년도 별 고용된 사원들의 총 수를 다음과 같이 조회한다
-- DUMMY TABLE 이라고함 (DUAL)
SELECT (
SELECT COUNT(1)
FROM EMPLOYEES
)TOTAL
,(
SELECT COUNT(1)
FROM EMPLOYEES
WHERE HIRE_DATE >= TO_DATE('2001-01-01','YYYY-MM-DD')
AND HIRE_DATE <= TO_DATE('2001-12-31','YYYY-MM-DD')
) "2001"
,(
SELECT COUNT(1)
FROM EMPLOYEES
WHERE HIRE_DATE >= TO_DATE('2002-01-01','YYYY-MM-DD')
AND HIRE_DATE <= TO_DATE('2002-12-31','YYYY-MM-DD')
) "2002"
,(
SELECT COUNT(1)
FROM EMPLOYEES
WHERE HIRE_DATE >= TO_DATE('2003-01-01','YYYY-MM-DD')
AND HIRE_DATE <= TO_DATE('2003-12-31','YYYY-MM-DD')
) "2003"
,(
SELECT COUNT(1)
FROM EMPLOYEES
WHERE HIRE_DATE >= TO_DATE('2004-01-01','YYYY-MM-DD')
AND HIRE_DATE <= TO_DATE('2004-12-31','YYYY-MM-DD')
) "2004"
FROM DUAL
;
SELECT JOB_ID JOB
,(
SELECT SUM(SALARY)
FROM EMPLOYEES E1
WHERE E1.DEPARTMENT_ID = 20
AND E1.JOB_ID = E.JOB_ID
)"DEPT 20"
,SUM(SALARY)TOTAL
FROM EMPLOYEES E
GROUP BY E.JOB_ID
;
--27. 다음과 같은 포맷으로 각 부서 별 각 직업 별 연봉 총 합 및 각 부서별 연봉 총 합을 조회한다.
--28. LAST_NAME 이 Zlotkey 와 동일한 부서에 근무하는 모든 사원들의 사번 및 고용날짜를 조회한다.
-- 결과값에서 Zlotkey 는 제외한다.
SELECT LAST_NAME
,HIRE_DATE
FROM EMPLOYEES E
WHERE DEPARTMENT_ID =(
SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE LAST_NAME = 'Zlotkey'
)
AND NOT LAST_NAME='Zlotkey'
;
--29. 회사 전체 평균 연봉보다 더 받는 사원들의 사번 및 LAST_NAME 을 조회한다.
SELECT EMPLOYEE_ID
,LAST_NAME
FROM EMPLOYEES
WHERE SALARY > (
SELECT AVG(SALARY)
FROM EMPLOYEES
)
;
--30. ** 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%')
)
;
--31. 위치 ID 가 1700 인 부서에서 근무하는 사원들의 LAST_NAME, 부서 번호 및 JOB_ID 를 조회한다.
SELECT LAST_NAME
,D.DEPARTMENT_ID
,JOB_ID
FROM EMPLOYEES E
,DEPARTMENTS D
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
AND D.LOCATION_ID = 1700
;
--32. ** King 을 매니저로 두고 있는 모든 사원들의 LAST_NAME 및 연봉을 조회한다.
SELECT LAST_NAME
,SALARY
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
,LAST_NAME
,JOB_ID
FROM EMPLOYEES E
,DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND DEPARTMENT_NAME = 'Executive'
;
--34. 회사 전체 평균 연봉 보다 더 버는 사원들 중 LAST_NAME 에 u 가 있는 사원들이
-- 근무하는 부서에서 근무하는 사원들의 사번, LAST_NAME 및 연봉을 조회한다.
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
)
)
;
-- 35. ST_CLERK 인 직업 ID 를 가진 사원이 없는 부서 ID 를 조회한다.
-- NULL 값은 제외한다. (NOT EXISTS 사용)
SELECT DISTINCT DEPARTMENT_ID
FROM EMPLOYEES E
WHERE NOT EXISTS (
SELECT *
FROM EMPLOYEES E1
WHERE E.DEPARTMENT_ID = E1.DEPARTMENT_ID
AND JOB_ID = 'ST_CLERK'
)
AND DEPARTMENT_ID IS NOT NULL
;
SELECT DISTINCT DEPARTMENT_ID
FROM EMPLOYEES E
WHERE DEPARTMENT_ID NOT IN (
SELECT DEPARTMENT_ID
FROM EMPLOYEES E1
WHERE E1.DEPARTMENT_ID = E.DEPARTMENT_ID
AND E1.JOB_ID = 'ST_CLERK'
)
AND DEPARTMENT_ID IS NOT NULL
;
-- 36. 위치한 부서가 없는 국가 ID 및 국가 이름을 조회힌다.(NOT EXISTS 사용)
SELECT COUNTRY_ID CO
, COUNTRY_NAME
FROM COUNTRIES C
WHERE C.COUNTRY_ID IN (
SELECT COUNTRY_ID
FROM LOCATIONS L
WHERE NOT EXISTS(
SELECT *
FROM DEPARTMENTS D
WHERE L.LOCATION_ID = D.LOCATION_ID
)
)
;
SELECT C.COUNTRY_ID CO
, COUNTRY_NAME
FROM COUNTRIES C
, (
SELECT DISTINCT COUNTRY_ID
FROM LOCATIONS L
WHERE NOT EXISTS(
SELECT *
FROM DEPARTMENTS D
WHERE L.LOCATION_ID = D.LOCATION_ID
)
) L -- 뷰 형식!!!!!
WHERE C.COUNTRY_ID = L.COUNTRY_ID
;
SELECT *
FROM EMP_DETAILS_VIEW
;
-- 37. 기존의 직업을 여전히 가지고 있는 사원들의 사번 및 JOB_ID 를 조회한다
SELECT E.EMPLOYEE_ID
, JOB_ID
FROM EMPLOYEES E
, (
SELECT EMPLOYEE_ID
FROM EMPLOYEES
MINUS
SELECT EMPLOYEE_ID
FROM JOB_HISTORY
) E1
WHERE E.EMPLOYEE_ID = E1.EMPLOYEE_ID
;
-- 41. 커미션을 버는 사원들의 부서 및 연봉이 동일한 사원들의 LAST_NAME, 부서 번호 및 연봉을 조회한다.
SELECT LAST_NAME --MULTI_IN 을 쓰는 것
,DEPARTMENT_ID
,SALARY
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_ID
,SALARY
FROM EMPLOYEES
WHERE (SALARY, COMMISSION_PCT) IN (
SELECT SALARY,
COMMISSION_PCT
FROM EMPLOYEES E
,DEPARTMENTS D
WHERE D. DEPARTMENT_ID = E. DEPARTMENT_ID
AND D. LOCATION_ID = 2500
)
;
-- 43. LAST_NAME 이 'Kochhar' 인 사원과 동일한 연봉 및 커미션을 버는 사원들의
-- LAST_NAME, 부서 번호 및 연봉을 조회한다.
SELECT LAST_NAME
,DEPARTMENT_ID
,E. SALARY
FROM EMPLOYEES E
,(
SELECT SALARY
,NVL(COMMISSION_PCT,0) COMMISSION_PCT
FROM EMPLOYEES
WHERE LAST_NAME = 'Kochhar'
)K
WHERE E.SALARY = K.SALARY
AND NVL(E.COMMISSION_PCT,0) = K.COMMISSION_PCT
AND E. LAST_NAME != 'Kochhar'
;
-- 44. 직업 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
FROM EMPLOYEES E
,DEPARTMENTS D
,LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND L.LOCATION_ID = D.LOCATION_ID
AND CITY LIKE '%T%'
;
--46. 각 부서별 평균 연봉보다 더 받는 동일부서 근무사원들의
-- LAST_NAME, 연봉, 부서번호 및 해당 부서의 평균 연봉을 조회한다.
-- 결과는 부서별 연봉을 기준으로 정렬한다.
SELECT E.LAST_NAME
, E.SALARY
, E.DEPARTMENT_ID DEPTNO
, AVG.SALARY DEPT_AVG
FROM EMPLOYEES E
, (
SELECT DEPARTMENT_ID
, ROUND(AVG(SALARY)) SALARY
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
) AVG
WHERE E.DEPARTMENT_ID = AVG.DEPARTMENT_ID
AND E.SALARY > AVG.SALARY
ORDER BY AVG.SALARY
;
-- 47. NOT EXISTS 연산자를 사용하여 매니저가 아닌 사원 이름을 조회한다.
SELECT LAST_NAME
,EMPLOYEE_ID
FROM EMPLOYEES E
WHERE NOT EXISTS(
SELECT 1
FROM EMPLOYEES E1
WHERE E1.MANAGER_ID = E.EMPLOYEE_ID
)
;
-- 48. 소속부서의 평균 연봉보다 적게 버는 사원들의 last_name 을 조회한다.
SELECT LAST_NAME
FROM EMPLOYEES E
,(
SELECT AVG(SALARY) SALARY
,DEPARTMENT_ID
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
)E1
WHERE E.SALARY > E1.SALARY
AND E.DEPARTMENT_ID = E1.DEPARTMENT_ID
;