서브쿼리

: SQL 구문 안에 또 다른 select 구문이 있는 것을 말한다.

= select 문이 2개 이상

from절에 사용하는 경우 view와 같이 독립된 테이블처럼 활용되어 inline view라고 부른다.

참조성 여부에 따라 연관 또는 비연관 서브쿼리로 구분된다.

괄호로 묶어 작성해야 하며 연산자의 오른쪽에 나타나진다. 또한 단일 행 연산자(=, >, < 등)와 복수 행 연산자(in, not in, any, all 등)를 사용한다.

- 서브쿼리 종류

단일 행 서브쿼리 : select문장으로 부터 단 하나의 행만을 검색

다중 행 서브쿼리 : select문장으로 부터 하나 이상의 행을 검색

단일 열 서브쿼리 : select문장으로 부터 단 하나의 열만을 검색

다중 열 서브쿼리 : select문장으로 부터 하나 이상의 컬럼을 검색

from절 상의 서브쿼리 (inline view) : from절 상에 오는 서브쿼리로 view처럼 작동

상관관계 서브 쿼리 : 메인쿼리와 서브쿼리를 연관지어 참조하여 사용

단일 행 서브쿼리

: 오직 한 개의 행(값)만 반환

단일 행 연산자(=, >, >=, <, <=, <>, !=)만 사용할 수 있음

-- 단일 행 서브쿼리
-- 단일 행 연산자(=, >, >=, <, <=, <>, !=)만 사용할 수 있음
select ename, job from emp
    where job = (select job from emp where empno = 7369);

다중 행 서브쿼리

: 하나 이상의 행(값)을 반환.

복수 행 연산자(in, not in, any, all, exists)를 사용할 수 있음

-- 다중 행 서브쿼리
-- in 연산자
select empno, ename, sal, deptno
    from emp
    where sal in (select max(sal) from emp group by deptno);

-- 부서에서 가장 급여가 높은 사람
select max(sal) from emp group by deptno;

-- 다중 행 서브쿼리
-- any 연산자 : 조건 하나라도 허용되면 출력
select ename, sal from emp
    where deptno != 20
    and sal > any(select sal from emp where job = '관리자');

select sal from emp where job = '관리자';

-- all 연산자
-- 350 이상인 사람들
select ename, sal from emp
    where deptno != 20
    and sal > all(select sal from emp where job = '영업사원');

select sal from emp where job = '영업사원';

-- exists 연산자 = self join
-- : "한 건이라도 존재하면" TRUE 없으면 FALSE를 리턴
-- 관리자로 등록되어 있는 사원 조회
select empno, ename, sal from emp e
    where exists (select empno from emp where e.empno = mgr);

다중 열 서브쿼리

: 결과값이 두 개 이상의 컬럼을 반환.

여러 개의 컬럼을 쌍으로 묶어서 비교

기준 컬럼들을 괄호로 묶고 그에 대응되는 컬럼들은 기준 컬럼과 갯수가 동일해야 함. 컬럼들의 이름은 같지 않아도 됨.

-- 다중 열 서브쿼리
-- pairwise(쌍비교) 서브쿼리
select empno, sal, deptno, comm from emp
    where (sal, deptno, comm) in (select sal, deptno, comm from emp where deptno = 30 and comm is not null);

select sal, deptno, comm from emp where deptno = 30 and comm is not null;

from절 상의 서브쿼리 (inline view)

: from절 상에 오는 서브쿼리.

-- from절 상의 서브쿼리 (inline view)
-- 급여가 20부서의 평균 급여보다 크고 사원을 관리하는 사원으로서 40부서에 속하지 않은 사원의 정보를 보여주는 SQL문
select b.empno, b.ename, b.job, b.sal, b.deptno
    from (select empno 
        from emp where sal > (select avg(sal) from emp where deptno = 20)) a,
        emp b
    where a.empno = b.empno
    and b.mgr is not null
    and b.deptno != 40;

-- 20부터에서의 평균 급여는 650만원
select avg(sal) from emp where deptno = 20;

-- 급여가 650보다 큰 empno
select empno 
        from emp where sal > (select avg(sal) from emp where deptno = 20);

상관관계 서브 쿼리

: 바깥쪽 쿼리의 컬럼 중 하나가 안쪽 서브쿼리의 조건에 이용되는 처리 방식

-- 상관관계 서브쿼리
-- 사원을 관리할 수 있는 사원의 평균급여보다 급여를 많이 받는 사원의 정보를 출력
select empno, ename, sal
    from emp e
    where sal > (select avg(sal) sal from emp where e.empno = mgr);

집합 쿼리 (union, intersect, minus)

- union : 합집합

-- 집합 쿼리 (union, intersect, minus)
-- union : 순수한 합집합
select deptno from emp
union
select deptno from dept;

- union all : 중복되는 것까지 전부 포함한 합집합

-- 집합 쿼리 (union, intersect, minus)
-- union all : 중복되는 것까지 전부 포함
select deptno from emp
union all
select deptno from dept;

- intersect : 교집합

-- 집합 쿼리 (union, intersect, minus)
-- intersect : 교집합
select deptno from emp
intersect
select deptno from dept;

- minus : 차집합

-- minus : 차집합
select deptno from dept
minus
select deptno from emp;

전에 실습하여 만든 학사관리시스템을 이용하여 문제를 풀이한다.

하단의 테이블 생성 후 데이터 삽입을 참고하여 세팅한다.

https://forest-of-coding.tistory.com/35

추가 생성 테이블 & 데이터

-- 학사관리시스템에 아래 학점관리 테이블을 생성.
create table hakjum
    (grade char(2 byte) not null enable,
    min_point number(3) not null enable,
    max_point number(3) not null enable,
    constraint xpk_hakjum primary key (grade));

-- 학점관리 테이블에 데이터 입력
insert into hakjum(grade, min_point, max_point) values('A+', 96, 100);
insert into hakjum(grade, min_point, max_point) values('A0', 90, 95);
insert into hakjum(grade, min_point, max_point) values('B+', 85, 89);
insert into hakjum(grade, min_point, max_point) values('B0', 80, 84);
insert into hakjum(grade, min_point, max_point) values('C+', 75, 79);
insert into hakjum(grade, min_point, max_point) values('C0', 70, 74);
insert into hakjum(grade, min_point, max_point) values('D+', 65, 69);
insert into hakjum(grade, min_point, max_point) values('D0', 60, 64);
insert into hakjum(grade, min_point, max_point) values('F', 0, 59);

문제

1. 학사관리시스템에서 수강학생들의 성적을 학점으로 나타내시오.
학번, 학생명, 과목코드, 과목명, 성적, 학점

2. 학사관리시스템에서　과목별　평균성적을 구하시오.
과목코드, 과목명, 평균성적

1번 문제

-- 1. 학사관리시스템에서 수강학생들의 성적을 학점으로 나타내시오.
-- 학번, 학생명, 과목코드, 과목명, 성적, 학점
select * from student;
select * from class;
select * from subject;

select student.std_no 학번, student.std_name 학생명, class.sub_no 과목코드, subject.sub_name 과목명, class.cls_score 성적,   
    case when class.cls_score >= 95 then 'A+'
        when class.cls_score >= 90 then 'A0'
        when class.cls_score >= 85 then 'B+'
        when class.cls_score >= 80 then 'B0'
        when class.cls_score >= 75 then 'C+'
        when class.cls_score >= 70 then 'C0'
        when class.cls_score >= 65 then 'D+'
        when class.cls_score >= 60 then 'D0'
        else 'F'
    end 학점
    from student, class, subject
    where student.std_no = class.std_no
    and subject.sub_no = class.sub_no
    order by 6;

select student.std_no 학번, student.std_name 학생명, class.sub_no 과목코드, subject.sub_name 과목명, class.cls_score 성적, hakjum.grade 학점
    from student, class, subject, hakjum
    where student.std_no = class.std_no
    and subject.sub_no = class.sub_no
    and (class.cls_score between hakjum.min_point and hakjum.max_point)
    order by 6;

2번 문제

-- 2. 학사관리시스템에서　과목별　평균성적을 구하시오.
-- 과목코드, 과목명, 평균성적
select * from class;
select * from subject;
select * from student;

select class.sub_no 과목코드, subject.sub_name 과목명, round(avg(class.cls_score),0) 평균성적
    from class, subject, student, hakjum
    where class.sub_no = subject.sub_no
    and student.std_no = class.std_no
    group by subject.sub_no, subject.sub_name, class.sub_no;

특정 개수의 행만 조회 (rownum)

-- rownum : 조회된 행 순서대로 행 번호를 매김.
-- 무조건 1번부터 시작하며 중간에 끊어서 확인할 수 없음.
select rownum, emp.* from emp;

-- select 하는 순간에 rownum은 부여되지만 가장 마지막에 나열되는 order by로 인해 뒤직박죽으로 출력됨.
select rownum, emp.* from emp order by job;

-- 행 번호 유지시키기 위해서는 아래와 같은 방식으로 해야함.
-- rownum을 사용할 때 주로 서브쿼리를 사용한다.
-- 서브쿼리 : select 안에 select문이 있는 쿼리
select rownum, x.*
    from (select a.* from emp a order by a.ename) x;

-- 영향을 미치지 않고 가장 처음에 있는 것만 찾거나 어느 등위 안에 들때 주로 사용.
select rownum, x.*
    from (select a.* from emp a order by a.ename) x
    where rownum <= 5;

-- cart table에서 주문 수량이 큰 것부터 순서를 매겨 주문번호, 주문수량을 조회하시오.
select rownum, x.cart_no 주문번호, x.cart_qty 주문수량 
    from (select * from cart order by cart_qty desc) x;

테이블 조인 (join)

1. Non-Equi Join

select prod_id 상품코드, prod_name 상품명, prod_sale 판매가
    from prod
    where prod_sale between 200000 and 400000
    order by prod_sale desc;

2. Cartesian Product / Cross Join

: 모든 경우의 수를 전부 조회하는 것.

-- Cartesian Product
select * from lprod;
select * from prod;
-- 9 x 74 = 666
select count(*) from lprod, prod;

-- 8658
select count(*)
    from lprod, prod, buyer;

위의 코드와 아래 코드의 결과는 같음.

아래를 더 권장사항으로 쓰길 권유한다.

-- Cross Join
-- 권장사항
select * from lprod cross join prod;

-- 8658
select count(*)
    from lprod cross join prod cross join buyer;

3. Equi join / Inner join

: 내부 조인

-- Equi-join
select prod.prod_id 상품코드, prod.prod_name 상품명, lprod.lprod_nm 분류명
    from prod, lprod
    where prod.prod_lgu = lprod.lprod_gu;

-- ansi 형식
select prod.prod_id 상품코드, prod.prod_name 상품명, lprod.lprod_nm 분류명
    from prod
    inner join lprod on (prod.prod_lgu = lprod.lprod_gu);

-- 장바구니테이블의 2020년도 5월의 회원별 구매금액을 검색 하시오 ?
-- ( 구매금액 = 구매수량 * 판매가 )
-- ( Alias는 회원ID, 회원명, 구매금액 )
select * from member;
select * from cart;
select * from prod;

-- 일반 join 방법
select m.mem_id 회원ID, m.mem_name 회원명,sum(p.prod_sale * c.cart_qty) 구매금액
    from cart c, member m, prod p
    where c.cart_member = m.mem_id
    and c.cart_prod = p.prod_id
    and substr(c.cart_no, 0, 6) = '202005'
    group by m.mem_id, m.mem_name;

-- inner join
select mem_id 회원ID, mem_name 회원명,sum(prod_sale * cart_qty) 구매금액
    from cart
    inner join member on (cart_member = mem_id)
    inner join prod on (cart_prod = prod_id)
    where substr(cart_no, 0, 6) = '202005'
    group by mem_id, mem_name;

4. Outer join

: 외부 조인을 말하며 종류가 여러개 존재한다.

하단은 테이블과 데이터를 추가적으로 삽입하여 테스트한다.

-- 테이블 생성 및 데이터 삽입
create table a
    (no     number(3) not null,
    name    varchar2(20) not null,
    constraint pk_a primary key(no));

create table b
    (no     number(3) not null,
    score   number(3) not null,
    constraint pk_b primary key(no));

insert into a values(1, '김철수');
insert into a values(2, '박미미');
insert into a values(3, '정다비');
insert into a values(4, '멘토스');
insert into a values(5, '박버거');

insert into b values(1, 60);
insert into b values(2, 71);
insert into b values(5, 99);
insert into b values(7, 100);

- left outer join

-- outer join
-- left outer join : 아무것도 없는 값은 null을 뿌려줌
-- where r.c = s.c(+);
select * from a, b where a.no = b.no(+);

select * from a left outer join b on (a.no = b.no);

- right outer join

-- right outer join
select * from a, b where a.no(+) = b.no;

select * from a right outer join b on (a.no = b.no);

- full outer join

-- full outer join : a와 b의 합집합
select * from a full outer join b on (a.no = b.no);

select * from a, b where a.no = b.no(+)
union
select * from a, b where a.no(+) = b.no;

- > 응용1

-- 전체 분류의 상품자료 수를 검색 조회
-- 분류코드, 분류명, 상품자료수
select * from lprod;

-- 일반 join과 inner join의 경우 상품 자료수가 있는 것만 보여짐.
-- 일반 join
select lprod_gu 분류코드, lprod_nm 분류명, count(prod_lgu) 상품자료수
    from lprod, prod
    where lprod_gu = prod_lgu
    group by lprod_gu, lprod_nm;
    
-- inner join
select lprod_gu 분류코드, lprod_nm 분류명, count(prod_lgu) 상품자료수
    from lprod inner join prod on (lprod_gu = prod_lgu)
    group by lprod_gu, lprod_nm;

-- 전체 분류의 상품자료 수를 검색 조회
-- 분류코드, 분류명, 상품자료수
select * from lprod;

-- (left) outer join과 ansi (left) outer joi의 경우 상품 자료수가 없어도 다 보여짐.
-- (left) outer join 사용
select lprod_gu 분류코드, lprod_nm 분류명, count(prod_lgu) 상품자료수
    from lprod, prod
    where lprod_gu = prod_lgu(+)
    group by lprod_gu, lprod_nm
    order by lprod_gu;

-- ansi (left) outer join 사용 확인
select lprod_gu 분류코드, lprod_nm 분류명, count(prod_lgu) 상품자료수
    from lprod left outer join prod on (lprod_gu = prod_lgu)
    group by lprod_gu, lprod_nm
    order by lprod_gu;

=> 상품 자료수에 상관 없이 모든 상품이 화면에 보이기 위해 사용됨.

- > 응용2

-- 전체상품의 2020년 1월 입고수량을 검색 조회
-- 상품코드, 상품명, 입고수량

-- 일반 join
select prod.prod_id 상품코드, prod.prod_name 상품명, sum(buyprod.buy_qty) 입고수량
    from prod, buyprod
    where prod.prod_id = buyprod.buy_prod
    and buy_date between '2020-01-01' and '2020-01-31'
    group by prod.prod_id, prod.prod_name;


-- outer join
-- 아래와 같은 식으로 진행 시 오류가 발생함. => 서브쿼리나 ansi 조인을 사용해야 함.
select prod.prod_id 상품코드, prod.prod_name 상품명, sum(buyprod.buy_qty) 입고수량
    from prod left outer join buyprod on(prod.prod_id = buyprod.buy_prod)
    group by prod.prod_id, prod.prod_name
    having buyprod.buy_date between '2020-01-01' and '2020-01-31'
    group by prod.prod_id, prod.prod_name;

-- 전체상품의 2020년 1월 입고수량을 검색 조회
-- 상품코드, 상품명, 입고수량

-- ansi outer join
-- 입고되지 않은 현황도 확인하기 위해 아래와 같이 사용함.
select * from prod; -- 74개
select * from buyprod; -- 148개
select prod.prod_id "상품코드", prod.prod_name 상품명, sum(buyprod.buy_qty) 입고수량
    from prod left outer join buyprod on (prod.prod_id = buyprod.buy_prod
        and buyprod.buy_date between '2020-01-01' and '2020-01-31')
    group by prod.prod_id, prod.prod_name
    order by prod.prod_id, prod.prod_name;

-- 전체상품의 2020년 1월 입고수량을 검색 조회
-- 상품코드, 상품명, 입고수량

-- null로 나오는 값을 0으로 변경해줌.
select prod.prod_id "상품코드", prod.prod_name 상품명, sum(nvl(buyprod.buy_qty, 0)) 입고수량
    from prod left outer join buyprod on (prod.prod_id = buyprod.buy_prod
        and buyprod.buy_date between '2020-01-01' and '2020-01-31')
    group by prod.prod_id, prod.prod_name
    order by prod.prod_id, prod.prod_name;

-- 전체 회원의 2020년도 4월의 구매현황 조회
-- 회원id, 성명, 구매수량의 합

-- outer join 사용
select member.mem_id 회원ID, member.mem_name 성명, sum(cart.cart_qty) 구매수량
    from member right outer join cart on(member.mem_id = cart.cart_member)
    and substr(cart.cart_no, 1, 6) = '202004'
    group by member.mem_id, member.mem_name
    order by member.mem_id, member.mem_name;

- > 응용3

-- 전체 상품의 2020년도 5월 5일의 입고, 출고현황 조회
-- 상품코드, 상품명, 입고수량의 합, 판매수량의 합

-- 입고 확인 (Equi Join)
select prod.prod_id 상품코드, prod.prod_name 상품, sum(buyprod.buy_qty) 입고수량
    from prod, buyprod
    where prod.prod_id = buyprod.buy_prod
    and buyprod.buy_date = '20200505'
    group by prod.prod_id, prod.prod_name;

-- 전체 상품의 2020년도 5월 5일의 입고, 출고현황 조회
-- 상품코드, 상품명, 입고수량의 합, 판매수량의 합

-- 출고 확인 (Inner Join)
select * from cart;
-- 위의 cart_no가 년도와 날짜 + 다른 코드 이기에 like를 사용함.
select prod.prod_id 상품코드, prod.prod_name 상품, sum(cart.cart_qty) 판매수량
    from prod inner join cart on (prod.prod_id = cart.cart_prod)
    where cart.cart_no like '20200505%'
    group by prod.prod_id, prod.prod_name;

- > 입고 출고를 한 번에 보기 위해 아래와 같은 방식을 사용함.

-- 전체 상품의 2020년도 5월 5일의 입고, 출고현황 조회
-- 상품코드, 상품명, 입고수량의 합, 판매수량의 합

-- outer join 사용 확인
-- null 값 제거
select prod.prod_id 상품코드, prod.prod_name 상품, sum(nvl(buyprod.buy_qty, 0)) 입고수량, sum(nvl(cart.cart_qty, 0)) 판매수량
    from prod
    left outer join buyprod on(prod.prod_id = buyprod.buy_prod and buyprod.buy_date = '20200505')
    left outer join cart on (prod.prod_id = cart.cart_prod and substr(cart.cart_no, 1, 8) = '20200505')
    having sum(nvl(buyprod.buy_qty, 0)) > 0 or sum(nvl(cart.cart_qty, 0)) > 0
    group by prod.prod_id, prod.prod_name;

정리

select * from a;
select * from b;

-- 1. inner join
select * from a inner join b on a.no = b.no;

select * from a, b where a.no = b.no;

-- 2. left outer join
select * from a left outer join b on(a.no = b.no);

select * from a, b where a.no = b.no(+);

-- 3. right outer join
select * from a right outer join b on(a.no = b.no);

select * from a, b where a.no(+) = b.no;

-- 4. full outer join
select * from a full outer join b on(a.no = b.no);

select * from a, b where a.no(+) = b.no
union
select * from a, b where a.no = b.no(+);

4. Self join

-- self join
select b.mem_id 회원ID, b.mem_name 성명, b.mem_mileage 마일리지
    from member a, member b
    where a.mem_id = 'h001'
    and a.mem_mileage <= b.mem_mileage;

group by의 조건절 (having)

-- having 절 정리
-- 2020년도 판매일자, 판매총액 (5,000,000초과의 경우만),
-- 판매수량 (50초과의 경우만), 판매횟수를 조회하시오.
-- 단, 판매회수가 8개 이상인 판매일자만 조회

select * from prod;
select * from cart;

select substr(cart.cart_no, 1, 8) 판매일, sum(cart.cart_qty * prod.prod_sale) 판매금액, sum(cart.cart_qty) 판매수량, count(*) 판매횟수
    from prod inner join cart on(prod.prod_id = cart.cart_prod)
    where cart.cart_no like '2020%'
    group by substr(cart.cart_no, 1, 8)
    having sum(cart.cart_qty * prod.prod_sale) > 5000000
    and sum(cart.cart_qty) > 50
    and count(*) >= 8
    order by substr(cart.cart_no, 1, 8);