09-11 SQL

SQL 과제

코드

-- DROP TABLE kwai__product3;
-- DROP TABLE kwai__shop3;
-- DROP TABLE kwai__salelist3;

-- CREATE TABLE kwai__product3 (
--     pcode varchar2(10) not null ,
--     name varchar2(20) ,
--     cost number(10),
--     PRIMARY KEY (pcode)

-- );



-- INSERT INTO kwai__product3(pcode,name,cost) VALUES( 'AA01', '아메리카노'  ,  3000  );
-- INSERT INTO kwai__product3(pcode,name,cost) VALUES( 'AA02', '에스프레소 '  ,   3500 );
-- INSERT INTO kwai__product3(pcode,name,cost) VALUES( 'AA03', '카페라떼 '  ,  4000  );
-- INSERT INTO kwai__product3(pcode,name,cost) VALUES( 'AA04', '카라멜마끼 '  ,  4500  );
-- INSERT INTO kwai__product3(pcode,name,cost) VALUES( 'AA05', '카푸치노'  ,   5000  );
-- INSERT INTO kwai__product3(pcode,name,cost) VALUES( 'AA06', '초코롤케익 '  ,   6000 );
-- INSERT INTO kwai__product3(pcode,name,cost) VALUES( 'AA07', '녹차롤케익 '  ,   6500 );
-- INSERT INTO kwai__product3(pcode,name,cost) VALUES( 'AA08', '망고쥬스 '  ,   7000 );
-- INSERT INTO kwai__product3(pcode,name,cost) VALUES( 'AA09 ', '핫초코 '  ,   2500);








-- CREATE TABLE kwai__shop3(
--     scode varchar2(10) not null,
--     sname varchar2(20),
--     PRIMARY KEY(scode)
    
-- );



-- INSERT INTO kwai__shop3(scode,sname) VALUES( 'S001', '강남점' );
-- INSERT INTO kwai__shop3(scode,sname) VALUES( 'S002', '강서점' );
-- INSERT INTO kwai__shop3(scode,sname) VALUES( 'S003', '강동점' );
-- INSERT INTO kwai__shop3(scode,sname) VALUES( 'S004', '강북점' );
-- INSERT INTO kwai__shop3(scode,sname) VALUES( 'S005', '동대문점' );
-- INSERT INTO kwai__shop3(scode,sname) VALUES( 'S006', '인천점' );



-- CREATE TABLE kwai__salelist3(
--     saleno number(10) not null,
--     pcode varchar2(10) not null,
--     saledate DATE,
--     scode varchar2(10) not null,
--     amount number(10),
--     PRIMARY KEY (saleno)
-- );





-- INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 100001 ,  'AA01' , '20180902' ,'S001',50);

-- INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 100002 ,  'AA03' , '20180902' ,'S002',40);

-- INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 100003 ,  'AA04' , '20180902' ,'S002',20);


-- INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 100004 ,  'AA04' , '20180902' ,'S001',30);


-- INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 100005 ,  'AA05' , '20180902' ,'S004',40);


-- INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 100006 ,  'AA03' , '20180902' ,'S004',30);


-- INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 100007 ,  'AA01' , '20180902' ,'S003',40);


-- INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 100008 ,  'AA04' , '20180902' ,'S004',10);


-- INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 100009 ,  'AA01' , '20180902' ,'S003',20);


-- INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 1000010 ,  'AA05' , '20180902' ,'S003',30);


-- INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 1000011,  'AA01' , '20180902' ,'S001',40);


-- INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 1000012,  'AA03' , '20180902' ,'S002',50);


-- INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 1000013,  'AA04' , '20180902' ,'S002',50);


-- INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 1000014,  'AA05' , '20180902' ,'S004',20);


-- INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 1000015,  'AA01' , '20180902' ,'S003',30);











 


 


 




---------------------------------------------------------------------


-- insert data





INSERT INTO kwai__product3(pcode,name,cost) VALUES( 'AA01', '아메리카노'  ,  3000  )
INSERT INTO kwai__product3(pcode,name,cost) VALUES( 'AA02', '에스프레소 '  ,   3500 )
INSERT INTO kwai__product3(pcode,name,cost) VALUES( 'AA03', '카페라떼 '  ,  4000  )
INSERT INTO kwai__product3(pcode,name,cost) VALUES( 'AA04', '카라멜마끼 '  ,  4500  )
INSERT INTO kwai__product3(pcode,name,cost) VALUES( 'AA05', '카푸치노'  ,   5000  )
INSERT INTO kwai__product3(pcode,name,cost) VALUES( 'AA06', '초코롤케익 '  ,   6000 )
INSERT INTO kwai__product3(pcode,name,cost) VALUES( 'AA07', '녹차롤케익 '  ,   6500 )
INSERT INTO kwai__product3(pcode,name,cost) VALUES( 'AA08', '망고쥬스 '  ,   7000 )
INSERT INTO kwai__product3(pcode,name,cost) VALUES( 'AA09 ', '핫초코 '  ,   2500)













INSERT INTO kwai__shop3(scode,sname) VALUES( 'S001', '강남점' )
INSERT INTO kwai__shop3(scode,sname) VALUES( 'S002', '강서점' )
INSERT INTO kwai__shop3(scode,sname) VALUES( 'S003', '강동점' )
INSERT INTO kwai__shop3(scode,sname) VALUES( 'S004', '강북점' )
INSERT INTO kwai__shop3(scode,sname) VALUES( 'S005', '동대문점' )
INSERT INTO kwai__shop3(scode,sname) VALUES( 'S006', '인천점' )




INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 100001 ,  'AA01' , '20180902' ,'S001',50)

INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 100002 ,  'AA03' , '20180902' ,'S002',40)

INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 100003 ,  'AA04' , '20180902' ,'S002',20)


INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 100004 ,  'AA04' , '20180902' ,'S001',30)


INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 100005 ,  'AA05' , '20180902' ,'S004',40)


INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 100006 ,  'AA03' , '20180902' ,'S004',30)


INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 100007 ,  'AA01' , '20180902' ,'S003',40)


INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 100008 ,  'AA04' , '20180902' ,'S004',10)


INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 100009 ,  'AA01' , '20180902' ,'S003',20)


INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 1000010 ,  'AA05' , '20180902' ,'S003',30)


INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 1000011,  'AA01' , '20180902' ,'S001',40)


INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 1000012,  'AA03' , '20180902' ,'S002',50)


INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 1000013,  'AA04' , '20180902' ,'S002',50)


INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 1000014,  'AA05' , '20180902' ,'S004',20)


INSERT INTO kwai__salelist3(saleno ,pcode, saledate , scode, amount) VALUES( 1000015,  'AA01' , '20180902' ,'S003',30)





 

-------------- join 조회

-- 과제3. 
SELECT
    sa.saleno AS 판매번호,
    sa.pcode AS 상품코드,
    p.name AS 상품명,
    sa.saledate AS 판매날짜,
    sa.scode AS 매장코드,
    sp.sname AS 매장명,
    sa.amount AS 판매수량,
    sa.amount * p.cost AS 총판매액
FROM
    kwai__salelist3 AS sa
JOIN
    kwai__product3 AS p ON sa.pcode = p.pcode
JOIN
    kwai__shop3 AS sp ON sa.scode = sp.scode
ORDER BY
    sa.saleno;


-- 과제4.


SELECT
    sp.scode AS 매장코드,
    sp.sname AS 매장명, -- p.name 대신 sp.sname을 사용
    SUM(sa.amount * p.cost) AS 매장별_판매액
FROM
    kwai__salelist3 sa
JOIN
    kwai__product3 p ON sa.pcode = p.pcode
JOIN
    kwai__shop3 sp ON sa.scode = sp.scode
GROUP BY
    sp.scode, sp.sname -- GROUP BY 절에도 sp.sname을 사용
ORDER BY
    sp.scode;
    


SELECT
    p.pcode AS 상품코드,
    p.name AS 상품명,
    SUM(sa.amount * p.cost) AS 상품별_판매액
FROM
    kwai__salelist3 AS sa
JOIN
    kwai__product3 AS p ON sa.pcode = p.pcode
GROUP BY
    p.pcode, p.name
ORDER BY
    p.pcode;
    
    


 










 

마지막 과제 4 부분 출력 결과가 문제지의 출력값과 달랐음, 이유는

문제지에서는 AA05 데이터 값을 한행에서 A005로 넣었음, 없는값이 됨. 100010 에서 A005로넣어서 30 amount는 계산 안됨.
60 * 5000 = 300000

데이터 정확하게 삽입시 계산결과는

90*5000 = 450000