-- 테이블 조인 연습문제 마켓컬리
-- 연습문제 마켓컬리 샘플 DB 만들기
-- 1. user 테이블 생성
create table users(
id int primary key,
email varchar(100),
nickname varchar(100)
) ;
alter table users alter column id type SERIAL ;
-- 1-1 테이블 생성 확인
select * from users ;
-- 2. orders 테이블 생성
create table orders(
id int primary key,
status varchar(50),
created_at timestamp,
user_id int references users(id)
) ;
-- 2-1. orders 생성 확인
select * from orders ;
--3. prducts 테이블 생성
create table products (
id int primary key,
name varchar(100),
price int,
ptyep varchar(100)
) ;
-- 3-1. product 테이블 확인
select * from products ;
--4. order_details 테이블 생성
create table order_details(
id int primary key,
order_id int references orders(id),
product_id int references products(id),
count int
) ;
-- 4-1 order_details 테이블 확인
select * from order_details ;
-- 5. payments 테이블 생성
create table payments(
id int primary key,
amount int,
ptype varchar(50),
order_id int references orders(id)
) ;
-- 5.1 payments 확인
select * from payments ;
-- 6 레코드 등록
/* 레코드 등록 */
-- 사용자
INSERT INTO
users(email, nickname)
VALUES
('sehongpark@cloudstudying.kr', '홍팍'),
('kuma@cloudstudying.kr', '쿠마'),
('hawk@cloudstudying.kr', '호크')
;
-- 주문
INSERT INTO
orders(status, created_at, user_id)
VALUES
('DELIVERED', '2021-11-12 5:07:12', 1),
('DELIVERED', '2021-11-17 22:14:54', 1),
('DELIVERED', '2021-11-24 19:13:46', 2),
('DELIVERED', '2021-11-29 23:57:29', 3),
('DELIVERED', '2021-12-06 22:25:13', 3),
('DELIVERED', '2022-01-02 13:04:25', 2),
('DELIVERED', '2022-01-06 15:45:51', 2),
('DELIVERED', '2022-02-13 17:55:35', 1),
('DELIVERED', '2022-02-28 17:50:14', 2),
('IN_CART', '2022-03-06 14:54:23', 1)
;
-- 결제
INSERT INTO
payments(amount, ptype, order_id)
VALUES
(33640, 'SAMSUNG CARD', 1),
(33110, 'SAMSUNG CARD', 2),
(31200, 'LOTTE CARD', 3),
(69870, 'KAKAO PAY', 4),
(32800, 'KAKAO PAY', 5),
(42210, 'LOTTE CARD', 6),
(46060, 'LOTTE CARD', 7),
(42520, 'SAMSUNG CARD', 8),
(23070, 'LOTTE CARD', 9)
;
-- 상품
INSERT INTO
products(name, price, ptype)
VALUES
('돈까스 8입 1kg', 12900, '냉장 식품'),
('우유 900mL', 1970, '냉장 식품'),
('채소 믹스 500g', 4990, '냉장 식품'),
('참치마요 120g',4400,'냉장 식품'),
('돼지 프랑크 360g',3900,'냉장 식품'),
('달걀감자 샐러드 500g',6900,'냉장 식품'),
('달걈듬뿍 샐러드 500g',6900,'냉장 식품'),
('크림치즈',2180,'냉장 식품'),
('김자반 볶음 50g + 50g',3000,'상온 식품'),
('신라면 멀티 5봉',3680,'상온 식품'),
('우유식빵',2900, '상온 식품'),
('야채참치 100g',1590, '상온 식품'),
('고추참치 85g 8캔',10800,'상온 식품'),
('간편 양배추 280g',2200, '냉장 식품'),
('샐러드 키트 6봉',8900,'냉장 식품'),
('직화구이 햄',2990,'냉장 식품'),
('비앤나 소시지 800g',6900,'냉장 식품'),
('무항생제 특란 20구',7200,'냉장 식품'),
('나가사키짬뽕 5입',4480,'상온 식품'),
('수제 크림치즈 200g',9000,'냉장 식품'),
('한돈 떡갈비',3100,'냉장 식품'),
('플레인 베이글',1300,'냉장 식품'),
('노브랜드 리얼 햄 1kg',7380,'냉장 식품'),
('짜파게티 멀티 5봉',3680,'상온 식품'),
('짜왕 멀티 4봉',5300,'상온 식품'),
('노브랜드 짜장라면 멀티 4봉',2280,'상온 식품')
;
-- 주문내역
INSERT INTO
order_details(order_id, product_id, count)
VALUES
(1, 22, 6),
(1, 20, 1),
(1, 2, 2),
(1, 1, 1),
(2, 2, 3),
(2, 20, 1),
(2, 11, 2),
(2, 21, 4),
(3, 18, 1),
(3, 19, 1),
(3, 10, 1),
(3, 2, 2),
(3, 20, 1),
(3, 11, 1),
(4, 15, 1),
(4, 7, 1),
(4, 1, 1),
(4, 9, 4),
(4, 12, 6),
(4, 16, 1),
(4, 17, 1),
(4, 2, 2),
(4, 11, 2),
(5, 11, 2),
(5, 20, 1),
(5, 14, 1),
(5, 15, 1),
(5, 7, 1),
(6, 10, 1),
(6, 3, 1),
(6, 1, 1),
(6, 2, 2),
(6, 6, 1),
(6, 7, 1),
(6, 11, 1),
(7, 4, 1),
(7, 12, 10),
(7, 13, 1),
(7, 14, 1),
(7, 2, 1),
(7, 3, 1),
(7, 11, 2),
(8, 8, 1),
(8, 5, 1),
(8, 2, 2),
(8, 11, 2),
(8, 1, 1),
(8, 6, 1),
(8, 7, 1),
(9, 11, 1),
(9, 4, 1),
(9, 2, 1),
(9, 6, 1),
(9, 7, 1),
(10, 1, 1),
(10, 2, 2),
(10, 5, 1),
(10, 8, 1)
;
댓글
댓글 쓰기