연습문제 DB - 마트 운영 SQL 작성해보기

연습문제

 

마트 운영
새로운 DB 공간을 만들어서 사용해 주세요.(shopdb)

회원 : 아이디(기본키), 패스워드, 이름, 주소, 연락처, 충전금액(기본값 0)
상품 : 상품번호(자동증가, 기본키), 상품명, 가격, 수량(기본값 0)
구입 : 회원과 상품 다대 다 관계. 구입번호(자동증가, 기본키), 구입일자(기본값 입력날짜), 
구입시간(기본값 입력시간), 구입수량, 구입가격, 구분(기본값 정상/취소).

회원제로 운영하는 마트임.
회원은 충전금액으로만 상품을 구입할 수 있음.
모든 컬럼은 필수 입력 요소로 가정한다.
충전금액 또는 수량 차감 시 음수가 되는 상황은 상정하지 않는다.
회원 등록 쿼리.
각 회원별 충전 쿼리.
상품 등록 쿼리.

-- 구매 시나리오 
-- 1. buytbl에 상품 구매 내역을 입력
-- 2. membertbl에 금액에 따라 충전금액 차감
-- 3. producttbl에 상품 수량을 차감

-- 반품 시나리오
-- 1. buytbl에 구분를 '취소'로 변경
-- 2. membertbl에 충전금액 증가
-- 3. producttbl에 상품 수량 증가

위 제시된 내용을 처리하기 위한 SQL 쿼리문을 작성하세요~
CREATE TABLE, INSERT, UPDATE 쿼리문 작성.

 

1. 데이터베이스 만들기

 

2. 테이블 만들기

use shopdb;

drop table if exists buytbl;
drop table if exists producttbl;
drop table if exists membertbl;

-- 회원 : 아이디(기본키), 패스워드, 이름, 주소, 연락처, 
-- 		 충전금액(기본값 0)
create table if not exists membertbl (
	memid VARCHAR(20) PRIMARY KEY,
    mempwd VARCHAR(45) NOT NULL,
    memname VARCHAR(10) NOT NULL,
    memaddr VARCHAR(45) NOT NULL,
    memphone VARCHAR(15) NOT NULL,
    memmoney INT DEFAULT 0
);

-- 상품 : 상품번호(자동증가, 기본키), 상품명, 가격, 
-- 		 수량(기본값 0)
create table if not exists producttbl (
	prdnum INT AUTO_INCREMENT PRIMARY KEY,
    prdname VARCHAR(45) NOT NULL,
    prdprice INT NOT NULL,
    prdamount INT NOT NULL
);

-- 구입 : 회원과 상품 다대 다 관계. 
-- 		구입번호(자동증가, 기본키), 구입일자(기본값 입력날짜), 
-- 		구입시간(기본값 입력시간), 구입수량, 
-- 		구입가격, 구분(기본값 정상/취소).
create table if not exists buytbl (
	bnum INT AUTO_INCREMENT PRIMARY KEY,
    bmemid VARCHAR(20) NOT NULL,
    bprdnum INT NOT NULL,
    bdate DATE DEFAULT (CURRENT_DATE),
    btime TIME DEFAULT (CURRENT_TIME),
    bamount INT NOT NULL,
    bprice INT NOT NULL,
    bstatus VARCHAR(5) DEFAULT '정상',
    CONSTRAINT m_b_fk FOREIGN KEY (bmemid)
    REFERENCES membertbl (memid),
    CONSTRAINT m_p_fk FOREIGN KEY (bprdnum)
    REFERENCES producttbl (prdnum)
);

-- 회원 가입 처리용 쿼리
insert into membertbl
values ('hong01', '1234', '홍길동', 
		'인천시 남구', '010-1234-5678', default);
insert into membertbl
values ('jun01', '1111', '전우치', 
		'인천시 동구', '010-8888-9999', default);
insert into membertbl
values ('park01', '1234', '박종일', 
		'인천시 동구', '010-4444-1111', default);

-- 상품 등록용 쿼리
insert into producttbl
values (NULL, '새우깡', 1500, 10);
insert into producttbl
values (NULL, '감자깡', 1500, 20);
insert into producttbl
values (NULL, '흰우유', 1000, 15);
insert into producttbl
values (NULL, '아이스크림', 2000, 6);
insert into producttbl
values (NULL, '커피', 2400, 10);
insert into producttbl
values (NULL, '소주', 2700, 5);

commit;

-- 운영
-- 1. 충전
update membertbl
set memmoney = 100000
where memid = 'park01';

update membertbl
set memmoney = 50000
where memid = 'hong01';

update membertbl
set memmoney = 30000
where memid = 'jun01';

commit;

-- 구매 시나리오 
-- 1. buytbl에 상품 구매 내역을 입력
-- 전우치가 아이스크림 2개 구매
insert into buytbl
values (null, 'jun01', 4, now(), now(),
		2, 4000, DEFAULT);
-- 2. membertbl에 금액에 따라 충전금액 차감
update membertbl
set memmoney = memmoney - 4000
where memid = 'jun01';
-- 3. producttbl에 상품 수량을 차감
update producttbl
set prdamount = prdamount - 2
where prdnum = 4;

commit;

-- 반품 시나리오
-- 1. buytbl에 구분를 '취소'로 변경
update buytbl
set bstatus = '취소'
where bnum = 1;
-- 2. membertbl에 충전금액 증가
update membertbl
set memmoney = memmoney + 4000
where memid = 'jun01';
-- 3. producttbl에 상품 수량 증가
update producttbl
set prdamount = prdamount + 2
where prdnum = 4;

commit;