6일차 DB.sql (쇼핑몰 SQL문 문제풀기 : 모든문제 완료, 서브쿼리＆조인)

 

create database mall;

use mall;

create table member
(    
     id varchar(20) primary key, -- 회원 id
     name varchar(20), -- 회원명
     gender varchar(20), -- 회원성별
     joindate varchar(20) -- 가입일자
);

insert into member values('hkd', '홍길동', 'm', '2001-01-01');
insert into member values('lss', '이순신', 'm', '2003-02-01');
insert into member values('hj', '황진이', 'f', '2002-11-13');
insert into member values('wg', '왕건', 'm', '2005-12-21');
insert into member values('pms ','박문수', 'm', '2006-11-09');

create table goods
(     goodsNo int primary key, -- 상품의 인덱스번호
     goodName varchar(20), -- 상품명
     price int -- 상품가격
);

insert into goods values(1, 'mp3', 10000);
insert into goods values(2, 'camera', 50000);
insert into goods values(3, 'pc', 700000);


create table orders
(     no int primary key, -- 주문 인덱스번호
     id varchar(20), -- 회원 id
     orderDate varchar(20) -- 주문일자
);


insert into orders values(1, 'hkd', '2001-03-27');
insert into orders values(2, 'wg', '2005-04-17');
insert into orders values(3, 'hkd', '2006-02-07');


create table ordersDetail -- 주문내역
(    
     num int,
     orderNo int,  -- 주문번호
     goodsNo int, -- 상품번호
     qty int -- 상품수량
);


insert into ordersDetail values(1,1,1,1); -- mp3 1대
insert into ordersDetail values(2,1,3,1); -- pc 1대
insert into ordersDetail values(3,2,2,2); -- camera 2대
insert into ordersDetail values(4,3,2,1); -- camera 1대

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

-- 1.전체 회원수를 구하시오.
select count(m.id)
as 전체회원수
from member m;

-- 2.성별 회원수를 구하시오. (남, 여)
select count(m.gender)
as 남성회원
from member m
where m.gender='m';

select count(m.gender)
as 여성회원
from member m
where m.gender='f';

select m.gender, count(m.id)
as 회원수
from member m
group by m.gender
order by m.gender desc;

-- 3.가장 많이(개수기준) 판매된 상품부터 출력하시오.
select * from orders;
select * from ordersDetail;

-- 서브쿼리와 조인을 사용
select goods.goodName, temp.sumQty
from (select od.goodsNo, sum(od.qty)
          as sumQty
          from ordersdetail od
          group by od.goodsNo) as temp, goods
where temp.goodsNo = goods.goodsNo
order by temp.sumQty desc;

select g.goodName, d.qty
as 판매량_내림차순
from goods g, ordersDetail d
where g.goodsNo=d.goodsNo order by d.qty desc;

-- 4.주문내역을 출력하시오. 회원명, 상품명, 주문갯수
select m.name, g.goodName, od.qty
from goods g, member m, orders o, ordersDetail od
where g.goodsNo=od.goodsNo
and m.id=o.id
and o.no=od.orderNo;

-- 5.성이 '홍'인 회원을 검색하시오.
select * from member where name like '홍%';

-- 6.년도, 상품별 판매수량을 구하시오. 년도, 상품명, 판매갯수
select temp.years, goods.goodName, temp.sumQty
from(select year(o.orderDate) as years, od.goodsNo, sum(od.qty) as sumQty
          from orders o, ordersdetail od
          where o.no=od.orderNo
          group by year(o.orderDate), od.goodsNo) as temp, goods
where temp.goodsNo=goods.goodsNo
order by temp.years asc;

-- 7.년도별 판매 총액(금액)을 구하시오. 년도, 판매총액
-- 판매금액 = 가격 * 판매개수
select temp.년도 as 연도, sum(temp.판매총액) as 판매총금액
from(select year(o.orderDate) as 년도, g.price * od.qty as 판매총액
          from goods g, orders o, ordersdetail od
          where g.goodsNo=od.goodsNo
          and o.no=od.orderNo) as temp
group by temp.년도;