[Oracle DB SQL] SQL 연습하기

product, sales 테이블 만들어서 SQL 연습하기

1. 생성하려는 테이블이 존재 할수도 있으니 먼저 제거 해준다

drop table product purge;
drop table sales purge;

2. 시퀀스도 존재 할수도 있으니 먼저 제거해준다

drop sequence product_seq;
drop sequence sales_seq;

3. 시퀀스 생성하기

create sequence product_seq
	start with 1000
    maxvalue 9999
    increment by 1
    nocycle
    nocache;

create sequence sales_seq
	start with 1000
    maxvalue 9999
    increment by 1
    nocycle
    nocache;

시퀀스는 데이터베이스에서 유일한 값을 생성하기 위한 객체로, 일련번호, 고유한 ID 등을 자동으로 생성할 때 사용된다.

위 코드에서는 product_seq와 sales_seq라는 시퀀스를 생성하고, 시작값(start with)을 1000으로 설정하며, 최대값(maxvalue)은 9999로 설정한다. 또한, 1씩 증가(increment by)하며, 사이클(nocycle)과 캐시(nocache)를 사용하지 않도록 설정한다.

사이클을 사용하지 않으면 시퀀스가 최대값에 도달하면 자동으로 멈춘다. 캐시를 사용하지 않으면 시퀀스 값이 로드될 때마다 디스크에서 값을 읽어오므로, 일반적으로 캐시를 사용하지 않는다.

4. product 테이블 생성

create table product (
	idx		number			default product_seq.nextval,	-- 상품번호
    name	varchar(100)	not null,						-- 상품이름
    price	number			not null						-- 상품가격 (단일가격)
);

5. sales 테이블 생성

create table sales (
	idx			number		default sales_seq.nextnal,	-- 매출번호
    product_idx number		not null,					-- 상품번호 (참조)
    saleDate	date		default sysdate,			-- 매출발생 날짜 및 시간
    cnt			number		not null,					-- 구매 개수
    total		number		not null					-- 총 매출 금액
);    

6. product 테이블에 값 넣기

insert into product (name, price) values ('반팔 티셔츠', 12000);
insert into product (name, price) values ('후드티', 25000);
insert into product (name, price) values ('야구모자', 15000);
-- idx와 date는 알아서 값이 들어가진다

7. sales 테이블에 값 넣기

insert into sales (product_idx, cnt, total) values (1000, 1, 12000);
insert into sales (product_idx, cnt, total) values (1001, 3, 75000);
insert into sales (product_idx, cnt, total) values (1002, 2, 30000);
insert into sales (product_idx, cnt, total) values (1000, 5, 60000);
insert into sales (product_idx, cnt, total, salesDate) 
    values (1001, 2, 50000, '23/04/14');
insert into sales (product_idx, cnt, total, salesDate) 
    values (1002, 1, 15000, '23/03/14');

8. product 테이블에 값이 잘 들어갔는지 확인하기

select * from product;

9. sales 테이블에 값이 잘 들어갔는지 확인하기

select * from sales;

---

문제 풀어보기

1) sales 테이블에 등록된 매출금액의 총 합계를 구하세요

select sum(total) from sales;

2) sales 테이블에서 날짜에 따라 매출금액의 합계를 구하세요

select salesDate, sum(total) from sales
	group by salesDate;

3) sales 테이블에서 상품의 idx와 상품의 판매수량을 각각 구하세요

select product_idx, sum(cnt) from sales
	group by product_idx;

4) 3번 내용과 동일하게 출력하면서, 상품의 이름도 함께 출력하세요

select
	S.product_idx, P.name, sum(S.cnt) 
    from sales S
    join product P
    	on S.product_idx = P.idx
    group by S.product_idx, P.name
    order by S.product_idx;

5-1) 매출 구하기

select sum(total) from sales;

5-2) 날짜별 매출 구하기

select salesDate, sum(total) from sales
	group by salesDate
    droder by salesDate;

5-3) 지정한 월 안에서 날짜별 매출 구하기

select salesDate, sum(total) from sales
	group by salesDate
    having extract(MONTH from salesDate) = 3; -- 4를 입력하면 4월달에 대한 매출이 출력된다
--  having extract(MONTH from salesDate) in (3, 4); 3, 4월 포함된 정보가 출력

5-4) 상품 idx에 따른 총 판매가격을 출력하되, idx가 1001 이하인 품목의 내용만 출력하기

select product_idx, sum(total) from sales
	group by product_idx
    having product_idx <= 1001;

5-5) 상품 idx에 따른 총 판매가격을 출력하되, 상품이름이 '티'라는 글자를 포함하는 경우만 출력하기 (like '%티%')

select P.name, sum(S.total) 
	from sales S
	join product P
    	on S.product_idx = P.idx
    group by S.product_idx, P.name
    having name like '%티%';

5-6) 상품별 총 판매가격, 총 판매수량 구하기

select P.name, sum(S.total), sum(S.cnt) 
	from sales S
	join product P
    	on S.product_idx = P.idx
    group by name;

5-7) '티'가 포함되는 상품별 총 판매가격

select P.name, sum(S.total)
	from sales S
    join product P
    	on S.product_idx = P.idx
    group by P.name
    having P.name like '%티%';

5-8) 상품명이 '모자'를 포함하거나, '티셔츠'를 포함하면 카테고리라는 가상의 컬럼을 만든다

select 
	idx, name, price, 
	case
    	when name like '&모자%' then '모자'
        when name like '%티셔츠%' then '티셔츠'
    end as category    
from product;