[데이터베이스] product, saels

drop table product purge;
drop table sales purge;
drop sequence product_seq;
drop sequence sales_seq;

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;

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

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

insert into product (name, price) values ('반팔 티셔츠', 12000);
insert into product (name, price) values ('후드티', 25000);
insert into product (name, price) values ('야구모자', 15000);

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

commit;

select * from product;
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 
    sales.product_idx, 
    product.name,
    sum(sales.cnt) as 합계수량
        from sales
        join product
            on sales.product_idx = product.idx
    group by sales.product_idx, product.name;
    
-- 5-1) 매출 구하기
select sum(total) from sales;

-- 5-2) 날짜 별 매출 구하기
select salesDate, sum(total) from sales
    group by salesDate;
    
-- 5-3) 지정한 월 안에서 날짜별 매출 구하기
select salesDate, sum(total) from sales
    group by salesDate
    having extract(MONTH from salesDate) in (4, 5);

-- 5-4) 상품 idx 에 따른 총 판매가격을 출력하되, 
-- idx가 1001 이하인 품목의 내용만 출력하기
select product_idx, sum(total) from sales
    group by product_idx
    having product_idx <= 1001;

-- 5-5) 상품 name 에 따른 총 판매가격을 출력하되, 
-- 상품name이 '티' 라는 글자를 포함하는 경우만 출력하기 (like '%티%')
select product.name, sum(total)
    from sales
    join product
        on sales.product_idx = product.idx
    group by name
        having name like '%티%';
        
select 
    idx, name, price,
    case
        when name like '%모자%' then '모자'
        when name like '%티%' then '티셔츠'
    end as category
    
    from product;