2015년 Naver Blog 기록
데이터베이스 구축 실습 과제
CREATE TABLE TB_MEM(
MEM_ID VARCHAR2(20)
CONSTRAINT TB_MEM_MEM_ID_PK PRIMARY KEY,
MEM_NM VARCHAR2(20) NOT NULL,
MEM_CD VARCHAR2(20)
CONSTRAINT TB_MEM_MEM_CD_CK CHECK(MEM_CD IN('Silver','Gold','VIP','VVIP','Guest')),
PWD VARCHAR2(20) NOT NULL,
PWD_HINT VARCHAR2(20) NOT NULL,
PWD_HINT_ANS VARCHAR2(20) NOT NULL,
SSN VARCHAR2(20) NOT NULL
CONSTRAINT TB_MEM_SSN_U UNIQUE,
BIRTH_DT VARCHAR2(20),
MALE_FLG VARCHAR2(20) NOT NULL,
TEL_NO VARCHAR2(20),
CELL_NO VARCHAR2(20),
ADDR VARCHAR2(100) NOT NULL,
EMAIL VARCHAR2(20),
EMAIL_RCV_FLG VARCHAR2(20),
MEM_REG_DTM VARCHAR2(20)
);
-------------------------------------------------------------------------------------------------------
CREATE TABLE TB_PRODUCT(
PRD_NO VARCHAR2(20)
CONSTRAINT TB_PRODUCT_PRD_NO_PK PRIMARY KEY,
PRD_NM VARCHAR2(20),
CATEGORY_NO VARCHAR2(20),
PRD_DES VARCHAR2(20),
SELL_PRC_UNIT VARCHAR2(20),
SELL_START_DT VARCHAR2(20),
SELL_END_DT VARCHAR2(20),
DLV_CONSTRAINT VARCHAR2(20),
AS_FLG VARCHAR2(20),
RETURN_FLG VARCHAR2(20),
IMG_ORG_FILE_NM VARCHAR2(20),
IMG_GEN_FILE_NM VARCHAR2(20),
REG_ID VARCHAR2(20),
REG_DTM VARCHAR2(20));
-------------------------------------------------------------------------------------------------------
CREATE TABLE TB_ORDER(
ORD_NO VARCHAR2(20)
CONSTRAINT TB_ORDER_ORD_NO_PK PRIMARY KEY,
MEM_ID VARCHAR2(20)
CONSTRAINT TB_ORDER_MEM_ID_FK REFERENCES TB_MEM(MEM_ID),
ORD_AMT VARCHAR2(20) NOT NULL,
RCVR_NM VARCHAR2(20) NOT NULL,
RCVR_TEL_NO VARCHAR2(20) NOT NULL,
RCVR_ADDR VARCHAR2(20) NOT NULL,
DLV_CORP_NO VARCHAR2(20)
CONSTRAINT TB_ORDER_DLV_CORP_NO_FK REFERENCES TB_DLV_CORP(DLV_CORP_NO),
ORD_STAT_CD VARCHAR2(20) NOT NULL,
ORD_DTM VARCHAR2(20) NOT NULL
);
ALTER TABLE TB_PRODUCT MODIFY(PRD_DES VARCHAR2(200));
ALTER TABLE TB_PRODUCT MODIFY(DLV_CONSTRAINT VARCHAR2(100));
ALTER TABLE TB_PRODUCT MODIFY(PRD_NM VARCHAR2(100));
-------------------------------------------------------------------------------------------------------
CREATE TABLE TB_DLV_CORP(
DLV_CORP_NO VARCHAR2(20)
CONSTRAINT TB_DLV_CORP_DLV_CORP_NO_PK PRIMARY KEY,
DLV_CORP_NM VARCHAR2(20),
BIZ_NO VARCHAR2(20),
CEO_NM VARCHAR2(20),
CEO_TEL_NO VARCHAR2(20),
ADDR VARCHAR2(20),
REG_ID VARCHAR2(20),
REG_DTM VARCHAR2(20)
);
-------------------------------------------------------------------------------------------------------
CREATE TABLE TB_ORDER_PRODUCT(
ORD_NO VARCHAR2(20),
PRD_NO VARCHAR2(20),
QTY VARCHAR2(20),
FOREIGN KEY(ORD_NO) REFERENCES TB_ORDER(ORD_NO),
FOREIGN KEY(PRD_NO) REFERENCES TB_PRODUCT(PRD_NO)
);
ALTER TABLE TB_ORDER_PRODUCT DROP COLUMN QTV;
ALTER TABLE TB_ORDER_PRODUCT ADD (QTY VARCHAR2(20));
ALTER TABLE TB_ORDER DROP COLUMN DLV_REQ;
-------------------------------------------------------------------------------------------------------
INSERT INTO TB_DLV_CORP(DLV_CORP_NO,DLV_CORP_NM,BIZ_NO,CEO_NM,CEO_TEL_NO,ADDR,REG_ID,REG_DTM)
VALUES('1004','123','345','SS','D','원주다','F','G');
<<회원테이블>>
INSERT INTO TB_MEM(MEM_ID, MEM_NM, MEM_CD, PWD, PWD_HINT, PWD_HINT_ANS, SSN, BIRTH_DT, MALE_FLG, TEL_NO, CELL_NO, ADDR, EMAIL, EMAIL_RCV_FLG, MEM_REG_DTM)
VALUES('user01', '홍길동', 'Silver', 'user01', '초등학교는?', '서울초등학교', '8010101234567', '19801010', 'F', '02-555-4567', '010-234-4567', '서울시 서초구 서초동 서초아파트 101동 1001호', 'abcd@email.com', 'Y', to_char(sysdate, 'YYYYMMDDHH24MISS')) ;
INSERT INTO TB_MEM(MEM_ID, MEM_NM, MEM_CD, PWD, PWD_HINT, PWD_HINT_ANS, SSN, BIRTH_DT, MALE_FLG, TEL_NO, CELL_NO, ADDR, EMAIL, EMAIL_RCV_FLG, MEM_REG_DTM)
VALUES('user02', '김용철', 'Gold', 'user02', '초등학교는?', '서울초등학교', '7910101234567', '19801010', 'M', '02-555-4567', '010-234-4567', '서울시 서초구 서초동 서초아파트 101동 1001호', 'abcd@email.com', 'Y', to_char(sysdate, 'YYYYMMDDHH24MISS')) ;
INSERT INTO TB_MEM(MEM_ID, MEM_NM, MEM_CD, PWD, PWD_HINT, PWD_HINT_ANS, SSN, BIRTH_DT, MALE_FLG, TEL_NO, CELL_NO, ADDR, EMAIL, EMAIL_RCV_FLG, MEM_REG_DTM)
VALUES('admin01', '관리자', 'VVIP', 'admin01', '고등학교는?', '삼성고등학교', '7010101234567', '19701010', 'F', '02-555-1000', '010-100-2000', '서울시 서초구 서초동 삼성아파트 201동 1201호', 'admin@email.com', 'Y', to_char(sysdate, 'YYYYMMDDHH24MISS')) ;
<<상품테이블>>
INSERT INTO TB_PRODUCT(PRD_NO, PRD_NM, CATEGORY_NO, PRD_DES, SELL_PRC_UNIT, SELL_START_DT, SELL_END_DT, DLV_CONSTRAINT, AS_FLG, RETURN_FLG, IMG_ORG_FILE_NM, IMG_GEN_FILE_NM, REG_ID, REG_DTM)
VALUES('PRD000000001', '위니네크리스', 'CAT000000005', '착용하셨을때 떨어지는 라인이 정말 예뻐요', 39000, '20140201', '20141201', '국내 배송만 됩니다.', 'Y', 'Y', 'acc_image1.jpg', 'acc_image1.jpg', 'admin', to_char(sysdate, 'YYYYMMDDHH24MISS')) ;
INSERT INTO TB_PRODUCT(PRD_NO, PRD_NM, CATEGORY_NO, PRD_DES, SELL_PRC_UNIT, SELL_START_DT, SELL_END_DT, DLV_CONSTRAINT, AS_FLG, RETURN_FLG, IMG_ORG_FILE_NM, IMG_GEN_FILE_NM, REG_ID, REG_DTM)
VALUES('PRD000000002', '올인원크리스', 'CAT000000005', '진주의방울방울이 끝내줍니다.', 39000, '20140201', '20141201', '국내 배송만 됩니다.', 'Y', 'Y', 'acc_image2.jpg', 'acc_image2.jpg', 'admin', to_char(sysdate, 'YYYYMMDDHH24MISS')) ;
INSERT INTO TB_PRODUCT(PRD_NO, PRD_NM, CATEGORY_NO, PRD_DES, SELL_PRC_UNIT, SELL_START_DT, SELL_END_DT, DLV_CONSTRAINT, AS_FLG, RETURN_FLG, IMG_ORG_FILE_NM, IMG_GEN_FILE_NM, REG_ID, REG_DTM)
VALUES('PRD000000003', '블론디네크리스', 'CAT000000005', '조금 있으면 품절이예요 빨리 서두르세요', 18000, '20140201', '20141201', '국내 배송만 됩니다.', 'Y', 'Y', 'acc_image3.jpg', 'acc_image3.jpg', 'admin', to_char(sysdate, 'YYYYMMDDHH24MISS')) ;
INSERT INTO TB_PRODUCT(PRD_NO, PRD_NM, CATEGORY_NO, PRD_DES, SELL_PRC_UNIT, SELL_START_DT, SELL_END_DT, DLV_CONSTRAINT, AS_FLG, RETURN_FLG, IMG_ORG_FILE_NM, IMG_GEN_FILE_NM, REG_ID, REG_DTM)
VALUES('PRD000000004', '로렐라이네크리스', 'CAT000000005', 'Best of Best', 15000, '20140201', '20141201', '국내 배송만 됩니다.', 'Y', 'Y', 'acc_image4.jpg', 'acc_image4.jpg', 'admin', to_char(sysdate, 'YYYYMMDDHH24MISS')) ;
INSERT INTO TB_PRODUCT(PRD_NO, PRD_NM, CATEGORY_NO, PRD_DES, SELL_PRC_UNIT, SELL_START_DT, SELL_END_DT, DLV_CONSTRAINT, AS_FLG, RETURN_FLG, IMG_ORG_FILE_NM, IMG_GEN_FILE_NM, REG_ID, REG_DTM)
VALUES('PRD000000005', '스트로우네크리스', 'CAT000000005', '악세사리 멋져요', 15000, '20140201', '20141201', '국내 배송만 됩니다.', 'Y', 'Y', 'acc_image5.jpg', 'acc_image5.jpg', 'admin', to_char(sysdate, 'YYYYMMDDHH24MISS')) ;
INSERT INTO TB_PRODUCT(PRD_NO, PRD_NM, CATEGORY_NO, PRD_DES, SELL_PRC_UNIT, SELL_START_DT, SELL_END_DT, DLV_CONSTRAINT, AS_FLG, RETURN_FLG, IMG_ORG_FILE_NM, IMG_GEN_FILE_NM, REG_ID, REG_DTM)
VALUES('PRD000000006', '글로아셔링ops', 'CAT000000001', '사랑스런 셔링의 루즈핏', 22000, '20140201', '20141201', '국내 배송만 됩니다.', 'Y', 'Y', 'dress_images1.jpg', 'dress_images1.jpg', 'admin', to_char(sysdate, 'YYYYMMDDHH24MISS')) ;
INSERT INTO TB_PRODUCT(PRD_NO, PRD_NM, CATEGORY_NO, PRD_DES, SELL_PRC_UNIT, SELL_START_DT, SELL_END_DT, DLV_CONSTRAINT, AS_FLG, RETURN_FLG, IMG_ORG_FILE_NM, IMG_GEN_FILE_NM, REG_ID, REG_DTM)
VALUES('PRD000000007', '바이블ops', 'CAT000000001', '큐트한 라인감이 여성스러워요', 23900, '20140201', '20141201', '국내 배송만 됩니다.', 'Y', 'Y', 'dress_images2.jpg', 'dress_images2.jpg', 'admin', to_char(sysdate, 'YYYYMMDDHH24MISS')) ;
INSERT INTO TB_PRODUCT(PRD_NO, PRD_NM, CATEGORY_NO, PRD_DES, SELL_PRC_UNIT, SELL_START_DT, SELL_END_DT, DLV_CONSTRAINT, AS_FLG, RETURN_FLG, IMG_ORG_FILE_NM, IMG_GEN_FILE_NM, REG_ID, REG_DTM)
VALUES('PRD000000008', '텐디박스ops', 'CAT000000001', '편안해요 홈웨어로도 좋아요', 24000, '20140201', '20141201', '국내 배송만 됩니다.', 'Y', 'Y', 'dress_images3.jpg', 'dress_images3.jpg', 'admin', to_char(sysdate, 'YYYYMMDDHH24MISS')) ;
INSERT INTO TB_PRODUCT(PRD_NO, PRD_NM, CATEGORY_NO, PRD_DES, SELL_PRC_UNIT, SELL_START_DT, SELL_END_DT, DLV_CONSTRAINT, AS_FLG, RETURN_FLG, IMG_ORG_FILE_NM, IMG_GEN_FILE_NM, REG_ID, REG_DTM)
VALUES('PRD000000009', '마리안 ops', 'CAT000000001', '살랑거리는 핏이 가녀린 여성미를 연출해줄 봄 원피스', 36000, '20140201', '20141201', '국내 배송만 됩니다.', 'Y', 'Y', 'dress_images4.jpg', 'dress_images4.jpg', 'admin', to_char(sysdate, 'YYYYMMDDHH24MISS')) ;
INSERT INTO TB_PRODUCT(PRD_NO, PRD_NM, CATEGORY_NO, PRD_DES, SELL_PRC_UNIT, SELL_START_DT, SELL_END_DT, DLV_CONSTRAINT, AS_FLG, RETURN_FLG, IMG_ORG_FILE_NM, IMG_GEN_FILE_NM, REG_ID, REG_DTM)
VALUES('PRD000000010', '디아망 ops', 'CAT000000001', '시스루 디테일로 여성스러움에 은은한 섹시함까지', 59800, '20140201', '20141201', '국내 배송만 됩니다.', 'Y', 'Y', 'dress_images5.jpg', 'dress_images5.jpg', 'admin', to_char(sysdate, 'YYYYMMDDHH24MISS')) ;
INSERT INTO TB_PRODUCT(PRD_NO, PRD_NM, CATEGORY_NO, PRD_DES, SELL_PRC_UNIT, SELL_START_DT, SELL_END_DT, DLV_CONSTRAINT, AS_FLG, RETURN_FLG, IMG_ORG_FILE_NM, IMG_GEN_FILE_NM, REG_ID, REG_DTM)
VALUES('PRD000000011', 'no.3932', 'CAT000000002', '썸머 나시 남다른 비치웨어로', 9800, '20140201', '20141201', '국내 배송만 됩니다.', 'Y', 'Y', 'top_image1.gif', 'top_image1.jpg', 'admin', to_char(sysdate, 'YYYYMMDDHH24MISS')) ;
INSERT INTO TB_PRODUCT(PRD_NO, PRD_NM, CATEGORY_NO, PRD_DES, SELL_PRC_UNIT, SELL_START_DT, SELL_END_DT, DLV_CONSTRAINT, AS_FLG, RETURN_FLG, IMG_ORG_FILE_NM, IMG_GEN_FILE_NM, REG_ID, REG_DTM)
VALUES('PRD000000012', '숄더홀믹스', 'CAT000000002', '네추럴한 가오리핏으로 순수하고 께끗한 이미지를 up', 38000, '20140201', '20141201', '국내 배송만 됩니다.', 'Y', 'Y', 'top_image2.jpg', 'top_image2.jpg', 'admin', to_char(sysdate, 'YYYYMMDDHH24MISS')) ;
INSERT INTO TB_PRODUCT(PRD_NO, PRD_NM, CATEGORY_NO, PRD_DES, SELL_PRC_UNIT, SELL_START_DT, SELL_END_DT, DLV_CONSTRAINT, AS_FLG, RETURN_FLG, IMG_ORG_FILE_NM, IMG_GEN_FILE_NM, REG_ID, REG_DTM)
VALUES('PRD000000013', '클로라모티브', 'CAT000000002', '넉넉한 핏과 레이스패턴으로 여리하고 여성스러워요', 22000, '20140201', '20141201', '국내 배송만 됩니다.', 'Y', 'Y', 'top_image3.jpg', 'top_image3.jpg', 'admin', to_char(sysdate, 'YYYYMMDDHH24MISS')) ;
INSERT INTO TB_PRODUCT(PRD_NO, PRD_NM, CATEGORY_NO, PRD_DES, SELL_PRC_UNIT, SELL_START_DT, SELL_END_DT, DLV_CONSTRAINT, AS_FLG, RETURN_FLG, IMG_ORG_FILE_NM, IMG_GEN_FILE_NM, REG_ID, REG_DTM)
VALUES('PRD000000014', '타임포켓', 'CAT000000002', '부드러운 소재로 착용감 정말 편해요', 26000, '20140201', '20141201', '국내 배송만 됩니다.', 'Y', 'Y', 'top_image4.jpg', 'top_image4.jpg', 'admin', to_char(sysdate, 'YYYYMMDDHH24MISS')) ;
INSERT INTO TB_PRODUCT(PRD_NO, PRD_NM, CATEGORY_NO, PRD_DES, SELL_PRC_UNIT, SELL_START_DT, SELL_END_DT, DLV_CONSTRAINT, AS_FLG, RETURN_FLG, IMG_ORG_FILE_NM, IMG_GEN_FILE_NM, REG_ID, REG_DTM)
VALUES('PRD000000015', '글로니린넨', 'CAT000000002', '체형커버에도 좋은 장점', 27400, '20140201', '20141201', '국내 배송만 됩니다.', 'Y', 'Y', 'top_image5.jpg', 'top_image5.jpg', 'admin', to_char(sysdate, 'YYYYMMDDHH24MISS')) ;
INSERT INTO TB_PRODUCT(PRD_NO, PRD_NM, CATEGORY_NO, PRD_DES, SELL_PRC_UNIT, SELL_START_DT, SELL_END_DT, DLV_CONSTRAINT, AS_FLG, RETURN_FLG, IMG_ORG_FILE_NM, IMG_GEN_FILE_NM, REG_ID, REG_DTM)
VALUES('PRD000000016', '7부쿨팬츠', 'CAT000000003', '여름까지 쭉 입기 좋은 소재와 베이직한 디자인으로 소장가치 up',14500, '20140201', '20141201', '국내 배송만 됩니다.', 'Y', 'Y', 'bottom_image1.gif', 'bottom_image1.jpg', 'admin', to_char(sysdate, 'YYYYMMDDHH24MISS')) ;
INSERT INTO TB_PRODUCT(PRD_NO, PRD_NM, CATEGORY_NO, PRD_DES, SELL_PRC_UNIT, SELL_START_DT, SELL_END_DT, DLV_CONSTRAINT, AS_FLG, RETURN_FLG, IMG_ORG_FILE_NM, IMG_GEN_FILE_NM, REG_ID, REG_DTM)
VALUES('PRD000000017', '미카베이직펜츠', 'CAT000000003', '세련된 느낌이 참좋아요', 24500, '20140201', '20141201', '국내 배송만 됩니다.', 'Y', 'Y', 'bottom_image2.jpg', 'bottom_image2.jpg', 'admin', to_char(sysdate, 'YYYYMMDDHH24MISS')) ;
INSERT INTO TB_PRODUCT(PRD_NO, PRD_NM, CATEGORY_NO, PRD_DES, SELL_PRC_UNIT, SELL_START_DT, SELL_END_DT, DLV_CONSTRAINT, AS_FLG, RETURN_FLG, IMG_ORG_FILE_NM, IMG_GEN_FILE_NM, REG_ID, REG_DTM)
VALUES('PRD000000018', '빈스큐롯반펜츠', 'CAT000000003', '가볍고 시원하게 즐겨주세요', 24000, '20140201', '20141201', '국내 배송만 됩니다.', 'Y', 'Y', 'bottom_image3.jpg', 'bottom_image3.jpg', 'admin', to_char(sysdate, 'YYYYMMDDHH24MISS')) ;
INSERT INTO TB_PRODUCT(PRD_NO, PRD_NM, CATEGORY_NO, PRD_DES, SELL_PRC_UNIT, SELL_START_DT, SELL_END_DT, DLV_CONSTRAINT, AS_FLG, RETURN_FLG, IMG_ORG_FILE_NM, IMG_GEN_FILE_NM, REG_ID, REG_DTM)
VALUES('PRD000000019', '브루노배기팬츠', 'CAT000000003', '화사한 플라워 패턴으로 스타일UP ', 26000, '20140201', '20141201', '국내 배송만 됩니다.', 'Y', 'Y', 'bottom_image4.jpg', 'bottom_image4.jpg', 'admin', to_char(sysdate, 'YYYYMMDDHH24MISS')) ;
INSERT INTO TB_PRODUCT(PRD_NO, PRD_NM, CATEGORY_NO, PRD_DES, SELL_PRC_UNIT, SELL_START_DT, SELL_END_DT, DLV_CONSTRAINT, AS_FLG, RETURN_FLG, IMG_ORG_FILE_NM, IMG_GEN_FILE_NM, REG_ID, REG_DTM)
VALUES('PRD000000020', '히스토리팬츠', 'CAT000000003', '신축성좋은 슬림핏데님', 27000, '20140201', '20141201', '국내 배송만 됩니다.', 'Y', 'Y', 'bottom_image5.jpg', 'bottom_image5.jpg', 'admin', to_char(sysdate, 'YYYYMMDDHH24MISS')) ;
INSERT INTO TB_PRODUCT(PRD_NO, PRD_NM, CATEGORY_NO, PRD_DES, SELL_PRC_UNIT, SELL_START_DT, SELL_END_DT, DLV_CONSTRAINT, AS_FLG, RETURN_FLG, IMG_ORG_FILE_NM, IMG_GEN_FILE_NM, REG_ID, REG_DTM)
VALUES('PRD000000021', '필립차이나 사파리', 'CAT000000004', '네추럴한 핏과 가벼운 소재로 편하게 걸치기 좋아요',61800, '20140201', '20141201', '국내 배송만 됩니다.', 'Y', 'Y', 'outer_image1.gif', 'outer_image1.jpg', 'admin', to_char(sysdate, 'YYYYMMDDHH24MISS')) ;
INSERT INTO TB_PRODUCT(PRD_NO, PRD_NM, CATEGORY_NO, PRD_DES, SELL_PRC_UNIT, SELL_START_DT, SELL_END_DT, DLV_CONSTRAINT, AS_FLG, RETURN_FLG, IMG_ORG_FILE_NM, IMG_GEN_FILE_NM, REG_ID, REG_DTM)
VALUES('PRD000000022', '띠어리nb', 'CAT000000004', '아우터 셔츠로 연출하기 좋은 실용적인 아이템', 59000, '20140201', '20141201', '국내 배송만 됩니다.', 'Y', 'Y', 'outer_image2.jpg', 'outer_image2.jpg', 'admin', to_char(sysdate, 'YYYYMMDDHH24MISS')) ;
INSERT INTO TB_PRODUCT(PRD_NO, PRD_NM, CATEGORY_NO, PRD_DES, SELL_PRC_UNIT, SELL_START_DT, SELL_END_DT, DLV_CONSTRAINT, AS_FLG, RETURN_FLG, IMG_ORG_FILE_NM, IMG_GEN_FILE_NM, REG_ID, REG_DTM)
VALUES('PRD000000023', '메종더블', 'CAT000000004', '날씬하게 떨어지는 핏이 정말 매력적인', 73000, '20140201', '20141201', '국내 배송만 됩니다.', 'Y', 'Y', 'outer_image3.jpg', 'outer_image3.jpg', 'admin', to_char(sysdate, 'YYYYMMDDHH24MISS')) ;
INSERT INTO TB_PRODUCT(PRD_NO, PRD_NM, CATEGORY_NO, PRD_DES, SELL_PRC_UNIT, SELL_START_DT, SELL_END_DT, DLV_CONSTRAINT, AS_FLG, RETURN_FLG, IMG_ORG_FILE_NM, IMG_GEN_FILE_NM, REG_ID, REG_DTM)
VALUES('PRD000000024', '세인트린넨jk', 'CAT000000004', '린넨 100% 고급스러운 자켓!소재,디자인,핏,마감 정말 좋은 퀄리티메이드 아이템!', 68000, '20140201', '20141201', '국내 배송만 됩니다.', 'Y', 'Y', 'outer_image4.jpg', 'outer_image4.jpg', 'admin', to_char(sysdate, 'YYYYMMDDHH24MISS')) ;
INSERT INTO TB_PRODUCT(PRD_NO, PRD_NM, CATEGORY_NO, PRD_DES, SELL_PRC_UNIT, SELL_START_DT, SELL_END_DT, DLV_CONSTRAINT, AS_FLG, RETURN_FLG, IMG_ORG_FILE_NM, IMG_GEN_FILE_NM, REG_ID, REG_DTM)
VALUES('PRD000000025', '메이퀸 진주 조끼', 'CAT000000004', '가볍게 걸치기만해도 멋스러운 아이템!', 49300, '20140201', '20141201', '국내 배송만 됩니다.', 'Y', 'Y', 'outer_image5.jpg', 'outer_image5.jpg', 'admin', to_char(sysdate, 'YYYYMMDDHH24MISS')) ;
-------------------------------------------------------------------------------------------------------
INSERT INTO TB_ORDER(ORD_NO,MEM_ID,ORD_AMT,RCVR_NM,RCVR_TEL_NO,RCVR_ADDR,DLV_CORP_NO,ORD_STAT_CD,ORD_DTM)
VALUES('001','user01','3000','엄마','000-000-0000','강원도 원주시','1004','GOOD','15-07-14');
INSERT INTO TB_ORDER_PRODUCT(ORD_NO,PRD_NO,QTY) VALUES('001','PRD000000001','1');
INSERT INTO TB_ORDER_PRODUCT(ORD_NO,PRD_NO,QTY) VALUES('002','PRD000000020','2');
INSERT INTO TB_ORDER_PRODUCT(ORD_NO,PRD_NO,QTY) VALUES('003','PRD000000023','1');
INSERT INTO TB_ORDER(ORD_NO,MEM_ID,ORD_AMT,RCVR_NM,RCVR_TEL_NO,RCVR_ADDR,DLV_CORP_NO,ORD_STAT_CD,ORD_DTM)
VALUES('002','user02','2000','아빠','001-000-0000','강원도 원주시','1004','GOOD','15-07-14');
INSERT INTO TB_ORDER(ORD_NO,MEM_ID,ORD_AMT,RCVR_NM,RCVR_TEL_NO,RCVR_ADDR,DLV_CORP_NO,ORD_STAT_CD,ORD_DTM)
VALUES('003','user02','3000','아빠','001-000-0000','서울특별시','1004','GOOD','15-07-15');
고객별 판매현황을 조회합니다.(고객명, 판매금액)
SELECT M.MEM_NM, SUM(P.SELL_PRC_UNIT*OP.QTY)
FROM TB_MEM M, TB_ORDER O, TB_PRODUCT P, TB_ORDER_PRODUCT OP
WHERE M.MEM_ID = O.MEM_ID AND P.PRD_NO = OP.PRD_NO AND O.ORD_NO = OP.ORD_NO
GROUP BY M.MEM_NM;
상품별 판매현황을 조회합니다.(상품명, 판매금액)
SELECT P.PRD_NM, NVL(SUM(P.SELL_PRC_UNIT*OP.QTY),0)
FROM TB_PRODUCT P, TB_ORDER_PRODUCT OP
WHERE P.PRD_NO = OP.PRD_NO(+)
GROUP BY P.PRD_NM;
'DB > Oracle' 카테고리의 다른 글
| [Oracle] 과제 문제 모음 -3- (0) | 2021.05.07 |
|---|---|
| [Oracle] 과제 문제 모음 -2- (0) | 2021.05.07 |
| [Oracle] 과제 문제 모음 -1- (0) | 2021.05.07 |
| [Oracle] ORACLE 컴퓨터 이름이 한글일 때 트러블슈팅 (0) | 2021.05.07 |
| [Oracle] 뷰와 시퀀스 -1- (0) | 2021.05.07 |
