~서브쿼리 실습문제
10. LAST_NAME 의 네번째 글자가 a 인 사원들의 LAST_NAME 을 조회한다.
|
1
2
3
4 |
SELECT LAST_NAME
FROM EMPLOYEES
WHERE LAST_NAME LIKE '___a%'
; |
cs |
* n번째 글자를 조회할 때는 LIKE 를 쓰고 _(언더바)를 사용한다.
11. LAST_NAME 에 a 및 e 글자가 있는 사원들의 LAST_NAME 을 조회한다.
|
1
2
3
4
5 |
SELECT LAST_NAME
FROM EMPLOYEES
WHERE LAST_NAME LIKE '%a%'
OR LAST_NAME LIKE '%e%'
; |
cs |
* A 및 B 라고 할 때, OR 를 쓴다.
* A 와 B 라고 할 때, AND 를 쓴다.
17. LAST_NAME 이 DAVIES 인 사람보다 후에 고용된 사원들의 LAST_NAME 및 HIRE_DATE 을 조회한다.
|
1
2
3
4
5
6
7
8
9 |
SELECT LAST_NAME
, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE > (
SELECT HIRE_DATE
FROM EMPLOYEES
WHERE LAST_NAME = 'Davies'
)
; |
cs |
* Davies 가 1명이기 때문에 동작을 하는 것이다. 여러명일 경우 서브쿼리문 안에 AND ROWNUM = 1 을 써주어야한다.
19. 회사 전체의 최대 연봉, 최소 연봉, 연봉 총 합 및 평균 연봉을 자연수로 포맷하여 조회한다.
|
1
2
3
4
5
6 |
SELECT MAX(SALARY)
, MIN(SALARY)
, SUM(SALARY)
, ROUND(AVG(SALARY))
FROM EMPLOYEES
; |
cs |
* ROUND는 자연수로 포맷하는 함수이고, ROUND( 실수값, 소숫점 n 번째 자리수) 를 쓰면 소수점까지 나온다.
20. 각 JOB_ID 별, 최대 연봉, 최소 연봉, 연봉 총 합 및 평균 연봉을 자연수로 포맷하여 조회한다.
|
1
2
3
4
5
6
7
8
9 |
SELECT JOB_ID
, MAX(SALARY)
, MIN(SALARY)
, SUM(SALARY)
, ROUND(AVG(SALARY))
FROM EMPLOYEES
GROUP BY JOB_ID
;
|
cs |
21. 동일한 직업을 가진 사원들의 총 수를 조회한다.
|
1
2
3
4
5 |
SELECT JOB_ID
, COUNT(EMPLOYEE_ID)
FROM EMPLOYEES
GROUP BY JOB_ID
; |
cs |
22. 매니저로 근무하는 사원들의 총 수를 조회한다.
|
1
2
3 |
SELECT COUNT(DISTINCT MANAGER_ID)
FROM EMPLOYEES
; |
cs |
* 중복을 없애고자할때 DISTINCT를 쓰면 널값은 계산을 하지 않는다.
23. 사내의 최대 연봉 및 최소 연봉의 차이를 조회한다.
|
1
2
3
4 |
SELECT MAX(SALARY)- MIN(SALARY) AS DIFFERENCE
FROM EMPLOYEES
;
|
cs |
26. 총 사원 수 및 2005, 2006, 2007, 2008 년도 별 고용된 사원들의 총 수를 다음과 같이 조회한다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24 |
SELECT DISTINCT ( SELECT COUNT(EMPLOYEE_ID)
FROM EMPLOYEES
) TOTAL
, (
SELECT COUNT(EMPLOYEE_ID)
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'YYYY') = '2005'
) COUNT_2005
, ( SELECT COUNT(EMPLOYEE_ID)
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'YYYY') = '2006'
) COUNT_2006
, (
SELECT COUNT(EMPLOYEE_ID)
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'YYYY') = '2007'
) COUNT_2007
, (
SELECT COUNT(EMPLOYEE_ID)
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'YYYY') = '2008'
) COUNT_2008
FROM EMPLOYEES
; |
cs |
* 날짜를 조회할때는 년도 4자를 모두 입력해주어야한다. TO_CHAR 말고 LIKE를 쓸 수도 있다.
27. 다음과 같은 포맷으로 각 부서 별 각 직업 별 연봉 총 합 및 각 부서별 연봉 총 합을 조회한다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34 |
SELECT JOB_ID JOB
,NVL((
SELECT SUM(SALARY)
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 20
AND JOB_ID = E.JOB_ID
), 0) "DEPT 20"
,NVL((
SELECT SUM(SALARY)
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 50
AND JOB_ID = E.JOB_ID
), 0) "DEPT 50"
,NVL((
SELECT SUM(SALARY)
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80
AND JOB_ID = E.JOB_ID
), 0) "DEPT 80"
,NVL((
SELECT SUM(SALARY)
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90
AND JOB_ID = E.JOB_ID
), 0) "DEPT 90"
,NVL((
SELECT SUM(SALARY)
FROM EMPLOYEES
WHERE JOB_ID = E.JOB_ID
), 0) TOTAL
FROM EMPLOYEES E
GROUP BY JOB_ID
;
|
cs |
28. LAST_NAME 이 Zlotkey 와 동일한 부서에 근무하는 모든 사원들의 사번 및 고용날짜를 조회한다.
|
1
2
3
4
5
6
7
8
9
10
11 |
SELECT EMPLOYEE_ID
, HIRE_DATE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (
SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE LAST_NAME = 'Zlotkey'
)
AND LAST_NAME != 'Zlotkey'
;
|
cs |
29. 회사 전체 평균 연봉보다 더 받는 사원들의 사번 및 LAST_NAME 을 조회한다.
|
1
2
3
4
5
6
7
8
9 |
SELECT EMPLOYEE_ID
, LAST_NAME
FROM EMPLOYEES
WHERE SALARY > (
SELECT AVG(SALARY)
FROM EMPLOYEES
)
;
|
cs |
30. LAST_NAME 에 u 가 포함되는 사원들과 동일 부서에 근무하는 사원들의 사번 및 LAST_NAME 을 조회한다.
|
1
2
3
4
5
6
7
8
9 |
SELECT EMPLOYEE_ID
, LAST_NAME
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (
SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE LAST_NAME LIKE '%u%'
)
; |
cs |
32. King 을 매니저로 두고 있는 모든 사원들의 LAST_NAME 및 연봉을 조회한다.
|
1
2
3
4
5
6
7
8
9
10 |
SELECT LAST_NAME
, SALARY
FROM EMPLOYEES
WHERE MANAGER_ID IN (
SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE LAST_NAME = 'King'
)
;
|
cs |
* 원래는 JOIN으로 푸는 문제이지만 서브쿼리를 사용할 수도 있다.
34. 회사 전체 평균 연봉 보다 더 버는 사원들 중 LAST_NAME 에 u 가 있는 사원들이 근무하는 부서에서 근무하는 사원들의 사번, LAST_NAME 및 연봉을 조회한다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 |
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
)
)
;
|
cs |