하나의 SQL문장에 포함되어 있는 다른 SQL문장
SELECT, FROM, WHERE에 들어갈 수 있음
괄호() 로 감싸서 사용하며 서브쿼리 연산을 먼저 진행
서브쿼리의 반환 행이 1줄 이하이면 단일 행이고 2줄 이상이면 다중 행 서브쿼리
서브쿼리 안에서는 정렬을 할 수 없음
--Nancy의 급여보다 많은 사람 검색
SELECT
e.first_name,
e.salary
FROM
employees e
WHERE
e.salary > (
SELECT
salary
FROM
employees
WHERE
first_name = 'Nancy'
);
--employee_id가 103번인 사람과 job_id가 동일한 사람을 검색
SELECT
*
FROM
employees
WHERE
job_id = (
SELECT
job_id
FROM
employees
WHERE
employee_id = 103
);
IN : 목록의 어떤 값과 같은지 확인
SELECT
*
FROM
employees
WHERE
job_id IN (
SELECT
job_id
FROM
employees
WHERE
job_id = 'IT_PROG'
);
--IT_PROG의 값과 같은 값이 있는지 확인하여 조회
ANY : 서브쿼리의 결과가 앞의 부등호를 하나라도 만족하면 조회
--first_name이 David인 사람 중 가장 작은 값보다 급여가 큰 사람을 조회
SELECT
*
FROM
employees
WHERE
salary > ANY (
SELECT
salary
FROM
employees
WHERE
first_name = 'David'
);
--ANY : 서브쿼리의 결과가 앞의 부등호를 하나라도 만족하면 모두 조회
--이러면 최소값을 찾을 수 있겠네
ALL : 서브쿼리의 결과가 앞의 부등호를 모두 만족해야 조회
SELECT
salary
FROM
employees
WHERE
salary > ALL (
SELECT
salary
FROM
employees
WHERE
first_name = 'David'
);
SELECT 구문에 Sub Query 작성하며 LEFT OUTER JOIN과 유사한 결과
스칼라 서브쿼리가 조인보다 좋은 경우는 함수처럼 한 레코드당 정확히 하나의 값만을 반환할 때
조인이 스칼라 서브쿼리보다 좋은 경우는 조회할 데이터가 대용량인 경우, 해당 데이터가 수정, 삭제 등이 빈번한 경우
--LEFT OUTER JOIN
SELECT
e.first_name,
d.department_name
FROM
employees e
LEFT OUTER JOIN departments d ON e.department_id = d.department_id
ORDER BY
first_name ASC;
--scalar sub query
SELECT
e.first_name,
(
SELECT
d.department_name
FROM
departments d
WHERE
d.department_id = e.department_id
) AS department_name
FROM
employees e
ORDER BY
first_name ASC;
--각 부서의 매니저 이름
--LEFT OUTER JOIN
SELECT
d.*,
e.first_name AS 매니저아이디
FROM
departments d
LEFT OUTER JOIN employees e ON d.manager_id = e.employee_id
ORDER BY
d.manager_id ASC;
--scalar sub query
SELECT
d.*,
(
SELECT
e.first_name
FROM
employees e
WHERE
d.manager_id = e.employee_id
) AS 매니저이름
FROM
departments d
ORDER BY
d.manager_id ASC;
--각 부서별 사원수 뽑기
SELECT
d.*,
(
SELECT
COUNT(e.department_id)
FROM
employees e
WHERE
d.department_id = e.department_id
GROUP BY
e.department_id
) AS 사원수
FROM
departments d;
SELECT
d.*,
(
SELECT
COUNT(e.department_id)
FROM
employees e
GROUP BY
e.department_id
HAVING
d.department_id = e.department_id
) AS 사원수
FROM
departments d;
FROM에 서브쿼리가 오는 것을 말함
SELECT
*
FROM
(
SELECT
ROWNUM AS rn,
tbl.*
FROM
(
SELECT
employee_id,
first_name,
salary
FROM
employees
ORDER BY
salary DESC
) tbl
)tbl2
WHERE
rn <= 20
AND rn > 10;
--가장 안쪽 SELECT절에서 필요한 테이블 형식을 생성
--바깥쪽 SELECT 절에서 ROWNUM을 붙여서 다시 조회
--가장 바깥쪽 SELECT 절에서는 이미 붙어있는 ROWNUM의 범위를 지정해서 조회
--실행순서 : FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
SELECT tt.name, tt.* FROM
(SELECT TO_CHAR(TO_DATE(test, 'YY/MM/DD'), 'MMDD') as mm, name FROM
(SELECT
'홍길동' AS name,
'20211126' AS test
FROM
dual
UNION ALL
SELECT
'김철수',
'20210301'
FROM
dual
UNION ALL
SELECT
'박영희',
'20210401'
FROM
dual
UNION ALL
SELECT
'김뽀삐',
'20210501'
FROM
dual
UNION ALL
SELECT
'박뚜띠',
'20210601'
FROM
dual
UNION ALL
SELECT
'김테스트',
'20210701'
FROM
dual)) tt
WHERE mm = '0401';
Sub Query
SELECT
*
FROM
employees e
WHERE
e.salary > (
SELECT
AVG(e2.salary)
FROM
employees e2
);
SELECT
COUNT(*)
FROM
employees e
WHERE
e.salary > (
SELECT
AVG(e2.salary)
FROM
employees e2
);
SELECT
*
FROM
employees e
WHERE
e.salary > (
SELECT
AVG(e2.salary)
FROM
employees e2
WHERE
e2.job_id = 'IT_PROG'
);
SELECT
*
FROM
employees e
WHERE
e.department_id = (
SELECT
d.department_id
FROM
departments d
WHERE
manager_id = 100
);
SELECT
*
FROM
employees
WHERE
manager_id > (
SELECT
e.manager_id
FROM
employees e
WHERE
e.first_name = 'Pat'
);
SELECT
*
FROM
employees
WHERE
manager_id IN (
SELECT
e.manager_id
FROM
employees e
WHERE
e.first_name = 'James'
);
SELECT
*
FROM
(
SELECT
ROWNUM AS num,
tr.first_name
FROM
(
SELECT
*
FROM
employees e
ORDER BY
e.first_name DESC
) tr
)
WHERE
num >= 41
AND num <= 50;
SELECT
*
FROM
(
SELECT
ROWNUM AS rn,
tbl.*
FROM
(
SELECT
e.employee_id,
e.first_name,
e.phone_number,
e.hire_date
FROM
employees e
ORDER BY
e.hire_date ASC
) tbl
)
WHERE
rn > 30
AND rn <= 40;
/*
문제 6
직원 테이블 부서 테이블을 left 조인하세요
-- 직원 아이디, 이름(성, 이름), 부서아이디, 부서명만 출력
-- 직원 아이디 기준 오름차순 정렬
*/
SELECT
e.employee_id,
e.first_name || e.last_name,
d.department_id,
d.department_name
FROM
employees e
LEFT OUTER JOIN departments d ON e.department_id = d.department_id
ORDER BY
e.employee_id DESC;
/*
문제 7
문제 6의 결과를 스칼라 쿼리로 동일하게 조회
*/
SELECT
e.employee_id,
e.first_name || e.last_name,
e.department_id,
(
SELECT
d.department_name
FROM
departments d
WHERE
e.department_id = d.department_id
) AS 부서이름
FROM
employees e
ORDER BY
e.employee_id DESC;
/*
문제 8
departments 테이블 locations 테이블을 left 조인하세요
-- 부서아아디, 부서이름, 매니저아아디, 로케이션 아이디, 스트릿 어드레스 ,포스트 코드, 시티만 출력
-- 부서아이디 기준 오름차순 정렬
*/
SELECT
d.department_id,
d.department_name,
d.manager_id,
d.location_id,
l.street_address,
l.postal_code,
l.city
FROM
departments d
LEFT OUTER JOIN locations l ON d.location_id = l.location_id
ORDER BY
d.department_id ASC;
/*
문제 9
문제 8의 결과를 스칼라 쿼리로 동일하게 조회
*/
SELECT
d.department_id,
d.department_name,
d.manager_id,
d.location_id,
(
SELECT
l.street_address
FROM
locations l
WHERE
d.location_id = l.location_id
) AS 지역주소,
(
SELECT
l.postal_code
FROM
locations l
WHERE
d.location_id = l.location_id
) AS postal_code,
(
SELECT
l.city
FROM
locations l
WHERE
d.location_id = l.location_id
)AS 도시
FROM
departments d
ORDER BY
d.department_id ASC;
--조인이 훨씬 좋음
/*
문제 10
locations 테이블 countries 테이블을 left 조인하세요
-- 로케이션 아이디, 주소, 시티, 나라 아이디, 나라 이름만 출력
-- 나라 이름 기준 오름차순 정렬
*/
SELECT
l.location_id,
l.street_address,
l.postal_code,
l.city,
c.country_id,
c.country_name
FROM
countries c
LEFT OUTER JOIN locations l ON l.country_id = c.country_id
ORDER BY c.country_name;
/*
문제 10의 결과를 스칼라 쿼리로 동일하게 조회
*/
SELECT
l.location_id,
l.street_address,
l.postal_code,
l.city,
(
SELECT
c.country_id
FROM
countries c
WHERE
l.country_id = c.country_id
) AS 나라아이디,
(
SELECT
c.country_name
FROM
countries c
WHERE
l.country_id = c.country_id
) AS 나라이름
FROM
locations l
ORDER BY 나라이름;
Join & Sub Query
SELECT
*
FROM
employees e
WHERE
e.salary > (
SELECT
AVG(e2.salary)
FROM
employees e2
);
SELECT
COUNT(*)
FROM
employees e
WHERE
e.salary > (
SELECT
AVG(e2.salary)
FROM
employees e2
);
SELECT
*
FROM
employees e
WHERE
e.salary > (
SELECT
AVG(e2.salary)
FROM
employees e2
WHERE
e2.job_id = 'IT_PROG'
);
SELECT
*
FROM
employees e
WHERE
e.department_id = (
SELECT
d.department_id
FROM
departments d
WHERE
manager_id = 100
);
SELECT
*
FROM
employees
WHERE
manager_id > (
SELECT
e.manager_id
FROM
employees e
WHERE
e.first_name = 'Pat'
);
SELECT
*
FROM
employees
WHERE
manager_id IN (
SELECT
e.manager_id
FROM
employees e
WHERE
e.first_name = 'James'
);
SELECT
*
FROM
(
SELECT
ROWNUM AS num,
tr.first_name
FROM
(
SELECT
*
FROM
employees e
ORDER BY
e.first_name DESC
) tr
)
WHERE
num >= 41
AND num <= 50;
SELECT
*
FROM
(
SELECT
ROWNUM AS rn,
tbl.*
FROM
(
SELECT
e.employee_id,
e.first_name,
e.phone_number,
e.hire_date
FROM
employees e
ORDER BY
e.hire_date ASC
) tbl
)
WHERE
rn > 30
AND rn <= 40;
/*
문제 6
직원 테이블 부서 테이블을 left 조인하세요
-- 직원 아이디, 이름(성, 이름), 부서아이디, 부서명만 출력
-- 직원 아이디 기준 오름차순 정렬
*/
SELECT
e.employee_id,
e.first_name || e.last_name,
d.department_id,
d.department_name
FROM
employees e
LEFT OUTER JOIN departments d ON e.department_id = d.department_id
ORDER BY
e.employee_id DESC;
/*
문제 7
문제 6의 결과를 스칼라 쿼리로 동일하게 조회
*/
SELECT
e.employee_id,
e.first_name || e.last_name,
e.department_id,
(
SELECT
d.department_name
FROM
departments d
WHERE
e.department_id = d.department_id
) AS 부서이름
FROM
employees e
ORDER BY
e.employee_id DESC;
/*
문제 8
departments 테이블 locations 테이블을 left 조인하세요
-- 부서아아디, 부서이름, 매니저아아디, 로케이션 아이디, 스트릿 어드레스 ,포스트 코드, 시티만 출력
-- 부서아이디 기준 오름차순 정렬
*/
SELECT
d.department_id,
d.department_name,
d.manager_id,
d.location_id,
l.street_address,
l.postal_code,
l.city
FROM
departments d
LEFT OUTER JOIN locations l ON d.location_id = l.location_id
ORDER BY
d.department_id ASC;
/*
문제 9
문제 8의 결과를 스칼라 쿼리로 동일하게 조회
*/
SELECT
d.department_id,
d.department_name,
d.manager_id,
d.location_id,
(
SELECT
l.street_address
FROM
locations l
WHERE
d.location_id = l.location_id
) AS 지역주소,
(
SELECT
l.postal_code
FROM
locations l
WHERE
d.location_id = l.location_id
) AS postal_code,
(
SELECT
l.city
FROM
locations l
WHERE
d.location_id = l.location_id
) AS 도시
FROM
departments d
ORDER BY
d.department_id ASC;
--조인이 훨씬 좋음
/*
문제 10
locations 테이블 countries 테이블을 left 조인하세요
-- 로케이션 아이디, 주소, 시티, 나라 아이디, 나라 이름만 출력
-- 나라 이름 기준 오름차순 정렬
*/
SELECT
l.location_id,
l.street_address,
l.postal_code,
l.city,
c.country_id,
c.country_name
FROM
countries c
LEFT OUTER JOIN locations l ON l.country_id = c.country_id
ORDER BY
c.country_name;
/*
문제 11
문제 10의 결과를 스칼라 쿼리로 동일하게 조회
*/
SELECT
l.location_id,
l.street_address,
l.postal_code,
l.city,
(
SELECT
c.country_id
FROM
countries c
WHERE
l.country_id = c.country_id
) AS 나라아이디,
(
SELECT
c.country_name
FROM
countries c
WHERE
l.country_id = c.country_id
) AS 나라이름
FROM
locations l
ORDER BY
나라이름;
--문제 12
SELECT
*
FROM
(
SELECT
ROWNUM AS num,
tt.*
FROM
(
SELECT
e.employee_id,
e.first_name,
e.phone_number,
e.hire_date,
d.department_id,
d.department_name
FROM
employees e
LEFT OUTER JOIN departments d ON d.department_id = e.department_id
ORDER BY
e.hire_date ASC
) tt
)
WHERE
num > 0
AND num <= 10;
--문제 13
SELECT
e.last_name,
e.job_id,
e.department_id,
d.department_name
FROM
employees e
LEFT OUTER JOIN departments d ON e.department_id = d.department_id
WHERE
e.job_id = 'SA_MAN';
SELECT
d.department_name,
tbl.*
FROM
(
SELECT
last_name,
job_id,
department_id
FROM
employees
WHERE
job_id = 'SA_MAN'
) tbl
JOIN departments d ON tbl.department_id = d.department_id;
--문제 14
SELECT
*
FROM
departments d
JOIN (
SELECT
department_id,
COUNT(*) AS total
FROM
employees
GROUP BY
department_id
) groupa ON groupa.department_id = d.department_id
ORDER BY
groupa.total DESC;
--GROUPBY한 것을 먼저 작성하고 이후 조인을 통해 원하는 쿼리 적으면 groupby 열 말고도 다른 열도 조회가능
--문제 15
SELECT
d.*,
loc.street_address,
loc.postal_code,
nvl(groupemp.result, 0)
FROM
departments d
INNER JOIN locations loc ON d.location_id = loc.location_id
LEFT JOIN (
SELECT
department_id,
trunc(AVG(salary)) AS result
FROM
employees
GROUP BY
department_id
) groupemp ON d.department_id = groupemp.department_id;
--우선 안에 들어가는 서브쿼리부터 먼저 작성(여기서 그룹화)하고 조인하여 나머지 정보 출력