oracle을 공부하며 쇼핑몰 데이터를 만들어 쇼핑몰DB를 구축해보았습니다.
테이블은 총 5개 회원, 제품, 주문, 장바구니, 할인율로 구성되어있습니다.
생성한 테이블을 이용해 쿼리작성시 조인을 활용하기위해 not null 외에 다른 옵션은 집어넣지 않았습니다.
create table member_5(
id varchar(20) not null,
name varchar(100) not null,
pw varchar(15) not null,
ssn varchar(30) not null,
email varchar(50) not null,
hp varchar(15) not null,
m_add varchar(40) not null,
grade varchar(10) not null
);
select * from member_5;
create table product_5(
p_number varchar(20) not null,
p_group varchar(20) not null,
name varchar(100) not null,
brand varchar(40) not null,
quantity int not null,
price int not null
);
drop table product_5;
select * from product_5;
drop table product_5;
create table orders_5(
o_number varchar(10) not null,
o_day varchar(100) not null,
o_address varchar(30) not null,
quantity int not null,
payment varchar(10) not null,
id varchar(20) not null,
p_number varchar(100) not null
);
drop table orders_5;
create table basket_5(
b_number varchar(10) not null,
quantity int not null,
b_day varchar(100) not null,
id varchar(20) not null,
p_number varchar(100) not null,
grade varchar(100) not null,
price int not null
);
drop table basket_5;
create table discount_5(
grade varchar(20) not null,
grade_discount number not null
);
drop table discount_5;
----------------------------------
-- 상품
----------------------------------
insert into product_5 values('100C1804SL20WH','반팔','SURFER MAN TEE WHITE','COVERNAT',120,39000);
insert into product_5 values('100C1902ST02BK','반팔','LAYOUT LOGO TEE BLACK','COVERNAT',150,39000);
insert into product_5 values('100C1904ST05BK','반팔','POCKET TEE BLACK','COVERNAT',130,12900);
insert into product_5 values('100C1901ST29BL','반팔','WHALE GRAPHIC TEE BLUE','COVERNAT',10,37000);
insert into product_5 values('100C1903ST02BK','반팔','JERSEY LOGO TEE BLACK','COVERNAT',60,41000);
insert into product_5 values('100C1804ST03BR','반팔','C LOGO EMBROIDERY POCKET TEE BROWN','COVERNAT',220,38000);
insert into product_5 values('200C1901SO01BK','반바지','PACKABLE SHORE SHORTS BLACK','COVERNAT',150,49000);
insert into product_5 values('200C1803SO05BK','반바지','COTTON BANDANA SHORTS BLACK','COVERNAT',160,59000);
insert into product_5 values('200C1901SO01NA','반바지','PACKABLE SHORE SHORTS NAVY','COVERNAT',260,49000);
insert into product_5 values('200C1803SO03BE','반바지','FATIGUE SHORTS BEIGE','COVERNAT',200,59000);
insert into product_5 values('200C1704SO02NA','반바지','GURKHA SHORTS NAVY','COVERNAT',16,69000);
insert into product_5 values('200C1901SO05BE','반바지','PIN STRIPE WIDE SHORTS BEIGE','COVERNAT',120,69000);
insert into product_5 values('10019SUMMER_1','반팔','SIGNATURE T-SHIRTS_white','5252BYOIOI',200,39000);
insert into product_5 values('10019SUMMER_10','반팔','LOGO TAPING T-SHIRTS_black','5252BYOIOI',70,41000);
insert into product_5 values('10019SUMMER_21','반팔','SERIF LOGO CROP T-SHIRTS_white','5252BYOIOI',110,39000);
insert into product_5 values('10019SUMMER_11','반팔','LOGO TAPING T-SHIRTS_blue','5252BYOIOI',30,41000);
insert into product_5 values('10019SUMMER_16','반팔','BACK LOGO BOXY T-SHIRTS_white','5252BYOIOI',90,45000);
insert into product_5 values('10018HS01','반팔','5.B.O SIGNATURE T-SHIRTS_white','5252BYOIOI',280,39000);
insert into product_5 values('20019SUMMER_41','반바지','CARGO SHORTS_navy','5252BYOIOI',140,72000);
insert into product_5 values('20019SUMMER_39','반바지',' LOGO TAPING SHORTS_green','5252BYOIOI',188,45000);
insert into product_5 values('20018HS22','반바지','5.B.O TRUNK SHORTS_green','5252BYOIOI',158,41000);
insert into product_5 values('20018SS525253','반바지','LANYARD POINT DENIM SHORTS_blue','5252BYOIOI',70,59000);
insert into product_5 values('20019SUMMER_43','반바지','SIDE LOGO TRACK SKIRTS_black','5252BYOIOI',7,49000);
insert into product_5 values('2005252_19SPRING_38','반바지','PIERCING MINI SKIRTS_green','5252BYOIOI',30,79000);
insert into product_5 values('100FE2RSB5101XINA','반팔','리니어 반팔티셔츠','FILA','200','29000');
insert into product_5 values('100FE2RSB5103XDGR','반팔','스포르티바 로고 반팔티','FILA','160','35000');
insert into product_5 values('100FE2RSA5101XINA','반팔','STACKED LOGO TEE','FILA','30','35000');
insert into product_5 values('100FS2RSB2161FINA','반팔','3단 블록 크롭 반팔티셔츠','FILA','300','39000');
insert into product_5 values('100FS2RSB2152FDRD','반팔','스몰 리니어 반팔티셔츠','FILA','80','35000');
insert into product_5 values('100FS2RSB2201MBGE','반팔','레귤러핏 스피디 스몰 로고 반팔 티셔츠','FILA','150','29000');
insert into product_5 values('200FS2TRB2102XOWH','반바지','레귤러핏 BIELLA ITALIA 로고 쇼츠','FILA','200','49000');
insert into product_5 values('200FE2TRA5102XBLK','반바지','STRIPE TAPE HALF PANTS','FILA','100','49000');
insert into product_5 values('200FS2TRB2103XDNA','반바지','레귤러핏 FILA 리니어 로고 배색 쇼츠','FILA','175','49000');
insert into product_5 values('200FS2TRB2252FLBU','반바지','레귤러핏 FILA 컬러 블럭 우븐 쇼츠','FILA','130','39000');
insert into product_5 values('200FS2TRB2204MINA','반바지','레귤러핏 스피디 로고 테잎 쇼츠','FILA','280','49000');
insert into product_5 values('200FS2TRB2151FINA','반바지','컬러 배색 여성 반바지','FILA','50','49000');
insert into product_5 values('100TN2360', '반팔', 'Fishing Club Tee White', 'THISISNEVERTHAT', 251, 42000);
insert into product_5 values('100TN2373', '반팔', 'Court Hawaiian Shirt Black/White', 'THISISNEVERTHAT', 121, 104000);
insert into product_5 values('100TN2336', '반팔', 'TN INTL. Panel Tee Blue', 'THISISNEVERTHAT', 400, 39000);
insert into product_5 values('100TN2394', '반팔', 'TN Polo White', 'THISISNEVERTHAT', 175, 69000);
insert into product_5 values('100TN2379', '반팔', 'HSP Oxford S/SL Shirt SkyBlue/Green', 'THISISNEVERTHAT', 57, 89000);
insert into product_5 values('100TN2348', '반팔', 'HSP Jersey Polo Lemon', 'THISISNEVERTHAT', 300, 59000);
insert into product_5 values('200TN2481', '반바지', 'Denim Skate Short Mid Blue', 'THISISNEVERTHAT', 72, 72000);
insert into product_5 values('200TN2464', '반바지', 'Jogging Short Black', 'THISISNEVERTHAT', 148, 62000);
insert into product_5 values('200TN2422', '반바지', 'T-Logo Cargo Short Beige', 'THISISNEVERTHAT', 225, 79000);
insert into product_5 values('200TN2404', '반바지', 'T-Logo PT Short Grey', 'THISISNEVERTHAT', 501, 89000);
insert into product_5 values('200TN2465', '반바지', 'Velour Short Apricot', 'THISISNEVERTHAT', 240, 69000);
insert into product_5 values('200TN2444', '반바지', 'MV-SP Sweatshort Grey', 'THISISNEVERTHAT', 351, 72000);
----------------------------------
-- 고객
----------------------------------
insert into member_5 values('wooya2702','허우','1234','7809232116784','wooya2702@gmail.com','010-6122-2345','서울 강서구','실버');
insert into member_5 values('onejimae','일지매','1234','7710291186223','onejimae@gmail.com','010-6788-4861','서울 강동구','골드');
insert into member_5 values('samjang7','김진욱','1234','7705021358674','samjang7@gmail.com','010-4888-2998','서울 강북구','플레티넘');
insert into member_5 values('nonnon1','안광훈','1234','7702131276431','nonnon1@gmail.com','010-7136-4981','서울 강남구','다이아몬드');
insert into member_5 values('munho','김문호','1234','7703261196365','munho@gmail.com','010-6175-3945','경기도 시흥시','다이아몬드');
insert into member_5 values('star123','노정호','1234','7711141254963','star123@gmail.com','010-7785-6984','경기도 안양시','실버');
insert into member_5 values('prettygirl','이윤나','1234','7810192157498','prettygirl@gmail.com','010-7278-3649','경기도 부천시','골드');
insert into member_5 values('silverwt','안은수','1234','7805051776346','silverwt@gmail.com','010-7381-5440','경기도 고양시','플레티넘');
insert into member_5 values('youngmin','인영민','1234','7806091786954','youngmin@gmail.com','010-7345-5677','경기도 안산시','다이아몬드');
insert into member_5 values('kimjh','김주현','1234','7805241981987','kimjh@gmail.com','010-7423-9870','경기도 부평시','브론즈');
insert into member_5 values('a1234','서유딩','1234','7810241981987','a1234@gmail.com','010-2662-3545','경기도 군포시','실버');
insert into member_5 values('b1234','김초딩','1234','7508222345678','b1234@gmail.com','010-2125-6452','경기도 이천시','골드');
insert into member_5 values('c1234','서새알','1234','7410212364678','c1234@gmail.com','010-6227-6652','경기도 여주시','플레티넘');
insert into member_5 values('d1234','서공룡알','1234','7706222343128','d1234@gmail.com','010-1227-9104','경기도 산본시','다이아몬드');
insert into member_5 values('e1234','서바둑알','1234','8701222343128','e1234@gmail.com','010-9227-5204','경기도 의왕시','브론즈');
insert into member_5 values('captain','조인형','1234','8006231343128','captain@gmail.com','010-9612-5424','경기도 의정부시','실버');
insert into member_5 values('sweety','박승곤','1234','8701301345128','sweety@gmail.com','010-1692-6524','경기도 의정부시','골드');
insert into member_5 values('powerman','송도권','1234','9803221153128','powerman@gmail.com','010-3232-4521','경기도 파주시','플레티넘');
insert into member_5 values('lamb1','양선희','1234','0109011135728','lamb1@gmail.com','010-5745-6242','경기도 시흥시','다이아몬드');
insert into member_5 values('number1','김영조','1234','8511301752728','number1@gmail.com','010-3745-3229','경기도 수원시','브론즈');
insert into member_5 values('taebaek2','이태백','1234', '8501251081118','taebaek2@gmail.com','010-4873-3984','서울 강서구','실버');
insert into member_5 values('snowak83','김설악','1234', '8303222057505','snowak83@naver.com','010-3310-3756','강원도 인제군','다이아몬드');
insert into member_5 values('fivebig82','최오대','1234', '8204151078618','fivebig82@daum.net','010-2687-4689','전북 전주시','브론즈');
insert into member_5 values('geography','박지리','1234', '8105251072721','geography@naver.com','010-1367-5987','경남 함양군','다이아몬드');
insert into member_5 values('northak0615','정북악','1234','8006152081118','northak0615@gmail.com','010-4687-9515','서울 성북구','골드');
insert into member_5 values('judobong','유도봉','1234', '8007052057024','judobong@daum.net','010-7658-3455','서울 송파구','플레티넘');
insert into member_5 values('kingjw80','윤주왕','1234', '8008181237615','kingjw80@naver.com','010-4468-6987','서울 노원구','플레티넘');
insert into member_5 values('strong0925','강월악','1234', '8009251345808','strong0925@gmail.com','010-2125-6578','경기도 하남시','브론즈');
insert into member_5 values('goldriver','장금강','1234', '8011052209717','goldriver@naver.com','010-8795-1326','서울 마포구','실버');
insert into member_5 values('hanraisme','나한라','1234', '8012151917311','hanraisme@daum.net','010-6548-7846','서울 광진구','골드');
insert into member_5 values('75true','서진수','1234','7510231901813','75true@naver.com','010-381-2158','경기도 파주시','플레티넘');
insert into member_5 values('pooh94','서재수','1234','7502241128467','pooh94@gmail.com','010-426-1700','경기도 의왕시','실버');
insert into member_5 values('angel000','이미경','1234','7506152123648','angel000@nate.com','010-266-8947','경기도 시흥시','다이아몬드');
insert into member_5 values('gunmandu','김재수','1234','7512251063421','gunmandu@gmail.com','010-6255-9875','경기도 군포시','실버');
insert into member_5 values('pincle1','박동호','1234','7503031639826','pincle1@naver.com','010-740-6388','경기도 이천시','골드');
insert into member_5 values('bingo','김신영','1234','7601232186327','bingo@hanmail.net','010-333-6328','경기도 인천시','실버');
insert into member_5 values('jjang1','신은경','1234','7604122298371','jjang1@dreamwiz.com','010-418-9627','경기도 안양시','골드');
insert into member_5 values('nara5','오나라','1234','7609112118379','nara5@empal.com','010-724-9618','경기도 고양시','플레티넘');
insert into member_5 values('guyume','구유미','1234','7601202378641','guyume@gmail.com','010-296-3784','경기도 여주시','실버');
insert into member_5 values('shyun1','임세현','1234','7610122196482','shyun1@nate.com','010-312-9838','경기도 하남시','다이아몬드');
INSERT INTO member_5 VALUES ('iamceo', '나사장', '1234', '9510231369824', 'iamceo@naver.com', '010-4223-0001', '경기도 안양시', '다이아몬드');
INSERT INTO member_5 VALUES ('boojang', '전부장', '1234', '9502241128467','boojang@naver.com','010-6255-8000','경기도 수원시', '브론즈');
INSERT INTO member_5 VALUES ('bestone', '최일도', '1234','9506152123648','bestone@naver.com','010-6255-8005','서울 마포구','브론즈');
INSERT INTO member_5 VALUES ('ichika', '김경묵', '1234','9512251063421','ichika@hanmail.net','010-6255-8010','경기도 의왕시','실버');
INSERT INTO member_5 VALUES ('1000man', '천만득', '1234','9503031639826','1000man@hanmail.net','010-6255-8020','경기도 안산시','플레티넘');
INSERT INTO member_5 VALUES ('applejjang', '이재용', '1234','9601232186327','applejjang@hanmail.net','010-2223-4000','경기도 군포시','플레티넘');
INSERT INTO member_5 VALUES ('bestkorean', '유관순', '1234','9609112118379','bestkorean@gmail.com','010-2998-7005','서울 강남구','골드');
INSERT INTO member_5 VALUES ('meekoo', '이승주', '1234','9704021358674','meekoo@gmail.com','010-5773-2098','서울 용산구','브론즈');
INSERT INTO member_5 VALUES ('mrjjoo', '김주호', '1234', '9808192157498', 'mrjjoo@gmail.com', '010-4223-4500', '경기도 시흥시', '실버');
INSERT INTO member_5 VALUES ('bestman', '김현중', '1234', '9803242114563', 'bestman@gmail.com', '010-4223-4600', '경기도 안양시', '다이아몬드');
------
--discount
------
insert into discount_5 values('브론즈',0.02);
insert into discount_5 values('실버',0.04);
insert into discount_5 values('골드',0.06);
insert into discount_5 values('플레티넘',0.08);
insert into discount_5 values('다이아몬드',0.1);
---------
--orders 결제방식 (무통장,카드,계좌이체) 3가지로만
---------
insert into orders_5 values ('1','2019-08-24','서울 강서구',1,'카드','wooya2702','100C1804SL20WH');
insert into orders_5 values ('2','2019-12-02','서울 강동구',2,'계좌이체','onejimae','100C1902ST02BK');
insert into orders_5 values ('3','2019-01-05','서울 강북구',3,'무통장','samjang7','100C1904ST05BK');
insert into orders_5 values ('4','2019-09-23','서울 강남구',4,'카드','nonnon1','100C1901ST29BL');
insert into orders_5 values ('5','2019-02-21','경기도 시흥시',5,'계좌이체','munho','100C1903ST02BK');
insert into orders_5 values ('6','2019-03-08','경기도 안양시',1,'무통장','star123','100C1804ST03BR');
insert into orders_5 values ('7','2019-11-19','경기도 부천시',2,'카드','prettygirl','200C1901SO01BK');
insert into orders_5 values ('8','2019-05-11','경기도 고양시',3,'계좌이체','silverwt','200C1803SO05BK');
insert into orders_5 values ('9','2019-07-01','경기도 안산시',4,'무통장','youngmin','200C1901SO01NA');
insert into orders_5 values ('10','2019-04-15','경기도 부평시',5,'카드','kimjh','200C1803SO03BE');
insert into orders_5 values ('11','2019-01-29','경기도 군포시',1,'카드','a1234','100C1902ST02BK');
insert into orders_5 values ('12','2019-04-04','경기도 이천시',4,'계좌이체','b1234','10019SUMMER_11');
insert into orders_5 values ('13','2019-10-15','경기도 여주시',2,'무통장','c1234','10018HS01');
insert into orders_5 values ('14','2019-05-13','경기도 산본시',1,'카드','d1234','100C1902ST02BK');
insert into orders_5 values ('15','2019-11-06','경기도 의왕시',4,'무통장','e1234','20019SUMMER_41');
insert into orders_5 values ('16','2019-12-25','경기도 의정부시',3,'계좌이체','captain','100FS2RSB2152FDRD');
insert into orders_5 values ('17','2019-07-21','경기도 의정부시',1,'무통장','sweety','100TN2336');
insert into orders_5 values ('18','2019-02-03','경기도 파주시',2,'무통장','powerman','200TN2444');
insert into orders_5 values ('19','2019-03-01','경기도 시흥시',1,'카드','lamb1','2005252_19SPRING_38');
insert into orders_5 values ('20','2019-06-25','경기도 수원시',5,'계좌이체','number1','200FS2TRB2252FLBU');
insert into orders_5 values ('21','2019-02-10','경기도 의왕시',1,'카드','ichika','100FS2RSB2161FINA');
insert into orders_5 values ('22','2019-03-13','경기도 군포시',1,'계좌이체','applejjang','200FS2TRB2252FLBU');
insert into orders_5 values ('23','2019-03-23','경기도 시흥시',2,'카드','mrjjoo','100FS2RSB2201MBGE');
insert into orders_5 values ('24','2019-03-25','경기도 부평시',5,'계좌이체','kimjh','200C1803SO05BK');
insert into orders_5 values ('25','2019-04-10','경기도 하남시',2,'무통장','strong0925','200C1704SO02NA');
insert into orders_5 values ('26','2019-05-10','경기도 파주시',1,'카드','75true','100C1901ST29BL');
insert into orders_5 values ('27','2019-07-15','경기도 의왕시',2,'계좌이체','pooh94','10019SUMMER_21');
insert into orders_5 values ('28','2019-08-07','경기도 여주시',4,'카드','c1234','100C1904ST05BK');
insert into orders_5 values ('29','2019-09-05','경기도 고양시',2,'카드','silverwt','200TN2422');
insert into orders_5 values ('30','2019-09-18','강원도 인제군',3,'무통장','snowak83','100TN2336');
---------
--basket 수량과 금액은 상품가격참고해서 작성
---------
insert into basket_5 values (1, 2, '2019-01-24', 'bestman', '100C1804SL20WH', '다이아몬드', 78000);
insert into basket_5 values (2, 2, '2019-01-12', 'mrjjoo', '100TN2360', '실버', 84000);
insert into basket_5 values (3, 4, '2019-02-08', 'bestkorean', '200C1901SO01BK', '골드', 196000);
insert into basket_5 values (4, 1, '2019-02-04', 'applejjang', '100FS2RSB2201MBGE', '플레티넘', 29000);
insert into basket_5 values (5, 1, '2019-03-01', 'boojang', '100FE2RSB5101XINA', '브론즈', 29000);
insert into basket_5 values (6, 5, '2019-03-07', 'meekoo', '10019SUMMER_10', '브론즈', 205000);
insert into basket_5 values (7, 1, '2019-03-26', 'ichika', '100C1903ST02BK', '실버', 41000);
insert into basket_5 values (8, 3, '2019-04-11', 'guyume', '200C1704SO02NA', '실버', 207000);
insert into basket_5 values (9, 2, '2019-04-13', 'iamceo', '20018SS525253', '다이아몬드', 118000);
insert into basket_5 values (10, 4, '2019-05-05', 'kingjw80', '200FE2TRA5102XBLK', '플레티넘', 196000);
insert into basket_5 values('11',2,'2019-10-24','bestkorean','20019SUMMER_39','골드',90000);
insert into basket_5 values('12',3,'2019-1-1','1000man','100C1804SL20WH','플레티넘',117000);
insert into basket_5 values('13',4,'2019-2-27','wooya2702','200C1901SO05BE','실버',276000);
insert into basket_5 values('14',5,'2019-3-15','onejimae','10019SUMMER_1','골드',195000);
insert into basket_5 values('15',1,'2019-4-12','samjang7','20019SUMMER_41','플레티넘',72000);
insert into basket_5 values('16',2,'2019-5-19','nonnon1','100FE2RSB5101XINA','다이아몬드',58000);
insert into basket_5 values('17',3,'2019-6-24','munho','200FS2TRB2102XOWH','다이아몬드',147000);
insert into basket_5 values('18',4,'2019-7-30','star123','100TN2360','골드',168000);
insert into basket_5 values('19',5,'2019-8-11','prettygirl','100TN2348','골드',295000);
insert into basket_5 values('20',1,'2019-12-15','b1234','200TN2404','골드',89000);
select * from member_5;
select * from orders_5;
select * from basket_5;
select * from product_5;
--지역화폐 개통 기념으로 할인 이벤트를 진행하려고 한다. 단, 회원등급이 골드이상인 회원에게만 할인을 적용해주려고 하며
--골드이상인 회원들이 주문한 상품에 할인가격을 적용하여 회원이름과 상품에 대한 정보 그리고 할인된 가격을 함께 출력하여라.
-- 이름 상품번호 분류 상품명 브랜드 재고 가격 주문금액에서 할인된 가격
--예) star123 , '100C1804ST03BR','반팔','C LOGO EMBROIDERY POCKET TEE BROWN','COVERNAT',220,38000, 37240
--
select m1.name, o.p_number, p.p_group,p.name, p.brand, p.quantity
from member_5 m1, orders_5 o, product_5 p,discount d
where p.p_number = o.p_number and m1.id = o.id;
장바구니 테이블에 자료가 너무 쌓여 10월이전에 등록된 데이터는 다 지우려고 한다.
지워진 장바구니 목록을 회원등급기준으로 묶어 출력하여라.
select d.grade 등급,b.quantity 수량,b.b_day 등록일,m.id 아이디,b.p_number 상품번호,b.price 총가격
from basket_5 b, member_5 m, discount_5 d
where b.ID=m.id and m.grade=d.GRADE and
(b.b_day between '2019-01-01' and '2019-10-01');