데이터베이스

[Database] 13장 함수 모음2

반응형

 

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

 

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

 

결과 화면1

 

 

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

 

결과 화면2

 

 

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

 

결과 화면3

 

 

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

 

결과 화면4

 

 

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

 

결과 화면5

 

 

 

테이블 조인 (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;

 

결과 화면6

 

 

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;

 

결과 화면7

 

 

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

 

결과 화면8

 

 

-- 장바구니테이블의 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;

 

결과 화면9

 

 

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);

 

결과 화면10

 

 

- 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);

 

결과 화면11

 

 

- 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;

 

결과 화면12

 

 

- > 응용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;

 

결과 화면13

 

-- 전체 분류의 상품자료 수를 검색 조회
-- 분류코드, 분류명, 상품자료수
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;

 

결과 화면14

 

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

 

 

- > 응용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;

 

결과 화면15

 

 

-- 전체상품의 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;

 

결과 화면16

 

 

-- 전체상품의 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;

 

결과 화면17

 

 

-- 전체 회원의 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;

 

결과 화면18

 

 

- > 응용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;

 

결과 화면19

 

 

-- 전체 상품의 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;

 

결과 화면20

 

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

-- 전체 상품의 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;

 

결과 화면21

 

 

정리

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;

 

결과 화면22

 

 

 

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);

 

 

반응형

'데이터베이스' 카테고리의 다른 글

[Database] 15장 서브쿼리  (0) 2023.12.28
[Database] 14장 SQL 연습문제4  (2) 2023.12.27
[Database] 12장 함수 모음1  (0) 2023.12.22
[Database] 11장 날짜 타입  (0) 2023.12.19
[Database] 10장 SQL 연습문제3  (0) 2023.12.19

티스토리툴바