데이터베이스 4일차_서브쿼리(oracle, sql)
## 서브쿼리
=> SQL 내부에 있는 또다른 쿼리
=> 소괄호 ( 서브쿼리 ) 감싼다
=> 서브쿼리안에 또 서브쿼리가 올 수 있다
=> 메인쿼리안에 여러개의 서버쿼리가 올 수 있다
=> 수행순서 : 서브쿼리(내부쿼리) 실행 -> 메인쿼리(외부쿼리) 실행
=> 서브쿼리의 리턴값이 NULL 이면 결과값도 NULL 이다
=> 사용위치 :
- SELECT 절 -> 함수로 구현하여 제공하는 추세
- WHERE 절 -> 조건식의 우항(오른쪽)
- FROM 절 -> IN-LINE 뷰
-- Chen 보다 많은 급여를 받는 사원의 이름과 급여
SELECT LAST_NAME, SALARY
FROM EMP
WHERE SALARY > ( SELECT SALARY
FROM EMP
WHERE LAST_NAME ='Chen' ) ;
-- 부서가 101번 사원과 같고, 급여이 141번 사원보다 많은 사원의 이름과 급여
SELECT LAST_NAME, SALARY
FROM EMP
WHERE SALARY > ( SELECT SALARY
FROM EMP
WHERE EMPLOYEE_ID =141 )
and DEPARTMENT_ID = ( SELECT DEPARTMENT_ID
FROM EMP
WHERE EMPLOYEE_ID = 101 ) ;
-- 우리회사에서 가장 월급을 많이 받는 사원의 이름과 급여
SELECT LAST_NAME, SALARY
FROM EMP
WHERE SALARY = ( SELECT max(SALARY ) FROM EMP ) ;
-- 부서별 최저급여를 검색, 단, 60번 부서의 최저급여보다 큰값만 검색
select department_id, min(salary) as 최저급여
from emp
group by department_id
having min(salary) > ( select min(salary)
from emp
where department_id = 60 ) ;
==============================================
-- 부서별 최저급여를 검색, 단, 60번 부서의 급여보다 큰값만 검색
select department_id, min(salary) as 최저급여
from emp
group by department_id
having min(salary) > ( select salary
from emp
where department_id = 60 ) ;
==> 오류발생 : single-row subquery returns more than one row
## 단일행 비교연산자
-- >, <,>=, <=, !=, <>
## 다중행 비교연산자
-- in, any, all
-- in , 단일행비교연산자 any, 단일행비교연산자 all
-- >= any , > any , <= all, < all , in
## any
-- 서브쿼리의 리턴값중 아무것 하나하고 만족
## all
-- 서브쿼리의 리턴값 모두하고 만족
-- 부서별 최고급여와 같은 급여를 받는 사원의 이름과 급여
select last_name, salary
from emp
where salary in ( select max(salary)
from emp
group by department_id )
-- IT_PROG 직군의 어떤보다도 급여를 많이 받는 사원
select last_name, salary
from emp
where salary > all ( select salary
from emp
where job_id = 'IT_PROG' )
===============================================
## TOP-N 알고리즘
-- FROM 절에 사용되는 서브쿼리
-- INLINE VIEW
-- SQL 실행시 잠깐 생성되었다가 사라지는 임시테이블
-- ROWNUM : 의사컬럼, 없지만 늘 사용가능한 컬럼 , 줄번호
SELECT LAST_NAME, SALARY
FROM EMP
WHERE SALARY >= 10000 ;
-- 급여를 많이 받는 10명의 명단
SELECT LAST_NAME, SALARY
FROM ( SELECT LAST_NAME, SALARY
FROM EMP
ORDER BY SALARY DESC )
WHERE ROWNUM <= 10 ;
-- 급여를 적게 받는 10명의 명단
SELECT LAST_NAME, SALARY
FROM ( SELECT LAST_NAME, SALARY
FROM EMP
ORDER BY SALARY )
WHERE ROWNUM <= 10 ;
===============================================
상품(상품번호, 상품명, 상품가격..... )
구매(구매번호, 구매자, 상품번호, 구매갯수, 구매금액, 구매날짜 )
1. 2020년 하반기 1000개 이상 팔린 상품번호과 총판매갯수
SELECT 상품번호, SUM(구매갯수) AS 총판매갯수
FROM 구매
WHERE 구매날짜 BETWEEN '2020-07-01' AND '2020-12-31'
GROUP BY 상품번호
HAVING SUM(구매갯수) >= 1000
ORDER BY 2 DESC ;
-- 베스트 상품 10개 검색
SELECT 상품번호, 총판매갯수
FROM ( SELECT 상품번호, SUM(구매갯수) AS 총판매갯수
FROM 구매
WHERE 구매날짜 BETWEEN '2020-07-01' AND '2020-12-31'
GROUP BY 상품번호
ORDER BY 2 DESC )
WHERE ROWNUM <= 10 ;
-- 베스트 상품 10 검색
-- 상품명, 총판매갯수
SELECT 상품명, 총판매갯수
FROM 상품 JOIN ( SELECT 상품번호, 총판매갯수
FROM ( SELECT 상품번호, SUM(구매갯수) AS 총판매갯수
FROM 구매
WHERE 구매날짜 BETWEEN '2020-07-01' AND '2020-12-31'
GROUP BY 상품번호
ORDER BY 2 DESC )
WHERE ROWNUM <= 10 )
USING ( 상품번호 ) ;
2. 2020년 베스트상품 10개 검색 ( 상품번호, 총판매갯수 )
SELECT ROWNUM AS 순위, 상품명, 총판매갯수
FROM 상품 JOIN ( SELECT 상품번호, 총판매갯수
FROM ( SELECT 상품번호, SUM(구매갯수) AS 총판매갯수
FROM 구매
WHERE TO_CHAR(구매날짜, 'YYYY') = '2020'
GROUP BY 상품번호
ORDER BY 2 DESC )
WHERE ROWNUM <= 10 )
USING ( 상품번호 ) ;