[sql] 20200902 sql 접속/테이블/저장/select/문자열 결합

Notice

Recent Posts

Recent Comments

Link

« 2024/11 »
일	월	화	수	목	금	토
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30

Tags more

Archives

Today

Total

관리 메뉴

Dev study and notes

[sql] 20200902 sql 접속/테이블/저장/select/문자열 결합 본문

studyLog

[sql] 20200902 sql 접속/테이블/저장/select/문자열 결합

devlunch4 2020. 9. 13. 22:54

항시준비
sql과 sourcetree 실행

*복습
*DB장점 : 동시성(공유성), 접근성(보편성)
*트랜잭션 : 여러단계의 과정을 하나의 작업 행위로 묶는 단위 (원자성,일관성,격리성(독립성),지속성)
*sql 배우는 이유 DBMS(DATA BASE MANAGEMENT SYSTEM)과 통신하기 위하여

*이전 글 사용자생성, 저장공간 생성
*사용자생성

접속이름.사용자이름.비밀번호(java).저장체크.호스트.포트(1521).SID(버전xe).확인 후
테스트>저장>접속.

5*내용복사
“

-- NAME

-- demobld.sql

-- DESCRIPTION

-- This script creates the SQL*Plus demonstration tables in the

-- current schema. It should be STARTed by each user wishing to

-- access the tables. To remove the tables use the demodrop.sql

-- script.

-- USAGE

-- SQL> START demobld.sql

SET TERMOUT ON

PROMPT Building demonstration tables. Please wait.

SET TERMOUT OFF

CREATE TABLE BONUS

(ENAME VARCHAR2(10),

JOB VARCHAR2(9),

SAL NUMBER,

COMM NUMBER);

CREATE TABLE EMP

(EMPNO NUMBER(4) NOT NULL,

ENAME VARCHAR2(10),

JOB VARCHAR2(9),

MGR NUMBER(4),

HIREDATE DATE,

SAL NUMBER(7, 2),

COMM NUMBER(7, 2),

DEPTNO NUMBER(2));

INSERT INTO EMP VALUES

(7369, 'SMITH', 'CLERK', 7902,

TO_DATE('17-DEC-1980', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN'), 800, NULL, 20);

INSERT INTO EMP VALUES

(7499, 'ALLEN', 'SALESMAN', 7698,

TO_DATE('20-FEB-1981', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN'), 1600, 300, 30);

INSERT INTO EMP VALUES

(7521, 'WARD', 'SALESMAN', 7698,

TO_DATE('22-FEB-1981', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN'), 1250, 500, 30);

INSERT INTO EMP VALUES

(7566, 'JONES', 'MANAGER', 7839,

TO_DATE('2-APR-1981', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN'), 2975, NULL, 20);

INSERT INTO EMP VALUES

(7654, 'MARTIN', 'SALESMAN', 7698,

TO_DATE('28-SEP-1981', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN'), 1250, 1400, 30);

INSERT INTO EMP VALUES

(7698, 'BLAKE', 'MANAGER', 7839,

TO_DATE('1-MAY-1981', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN'), 2850, NULL, 30);

INSERT INTO EMP VALUES

(7782, 'CLARK', 'MANAGER', 7839,

TO_DATE('9-JUN-1981', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN'), 2450, NULL, 10);

INSERT INTO EMP VALUES

(7788, 'SCOTT', 'ANALYST', 7566,

TO_DATE('09-DEC-1982', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN'), 3000, NULL, 20);

INSERT INTO EMP VALUES

(7839, 'KING', 'PRESIDENT', NULL,

TO_DATE('17-NOV-1981', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN'), 5000, NULL, 10);

INSERT INTO EMP VALUES

(7844, 'TURNER', 'SALESMAN', 7698,

TO_DATE('8-SEP-1981', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN'), 1500, 0, 30);

INSERT INTO EMP VALUES

(7876, 'ADAMS', 'CLERK', 7788,

TO_DATE('12-JAN-1983', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN'), 1100, NULL, 20);

INSERT INTO EMP VALUES

(7900, 'JAMES', 'CLERK', 7698,

TO_DATE('3-DEC-1981', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN'), 950, NULL, 30);

INSERT INTO EMP VALUES

(7902, 'FORD', 'ANALYST', 7566,

TO_DATE('3-DEC-1981', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN'), 3000, NULL, 20);

INSERT INTO EMP VALUES

(7934, 'MILLER', 'CLERK', 7782,

TO_DATE('23-JAN-1982', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN'), 1300, NULL, 10);

CREATE TABLE DEPT

(DEPTNO NUMBER(2),

DNAME VARCHAR2(14),

LOC VARCHAR2(13) );

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');

INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');

INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');

INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE SALGRADE

(GRADE NUMBER,

LOSAL NUMBER,

HISAL NUMBER);

INSERT INTO SALGRADE VALUES (1, 700, 1200);

INSERT INTO SALGRADE VALUES (2, 1201, 1400);

INSERT INTO SALGRADE VALUES (3, 1401, 2000);

INSERT INTO SALGRADE VALUES (4, 2001, 3000);

INSERT INTO SALGRADE VALUES (5, 3001, 9999);

COMMIT;

SET TERMOUT ON

PROMPT Demonstration table build is complete.

*전체 드래그후 컨트롤+엔터 (테이블 생성)

*사용자(users) 복사 붙여넣기
“

DROP TABLE USERS;

CREATE TABLE USERS

( USERID VARCHAR2(20 BYTE),

USERNM VARCHAR2(20 BYTE),

PASS VARCHAR2(100 BYTE),

REG_DT DATE,

ALIAS VARCHAR2(50 BYTE),

ADDR1 VARCHAR2(50 BYTE),

ADDR2 VARCHAR2(50 BYTE),

ZIPCODE VARCHAR2(5 BYTE),

FILENAME VARCHAR2(50 BYTE),

REALFILENAME VARCHAR2(200 BYTE),

CONSTRAINT PK_USER PRIMARY KEY (USERID)

);

COMMENT ON COLUMN USERS.USERID IS '사용자 아이디';

COMMENT ON COLUMN USERS.USERNM IS '사용자 이름';

COMMENT ON COLUMN USERS.PASS IS '비밀번호';

COMMENT ON COLUMN USERS.REG_DT IS '사용자 등록일';

COMMENT ON COLUMN USERS.ALIAS IS '별명';

COMMENT ON COLUMN USERS.ADDR1 IS '주소';

COMMENT ON COLUMN USERS.ADDR2 IS '상세주소';

COMMENT ON COLUMN USERS.ZIPCODE IS '우편번호';

COMMENT ON COLUMN USERS.FILENAME IS '업로드파일명';

COMMENT ON COLUMN USERS.REALFILENAME IS '실제파일경로';

COMMENT ON TABLE USERS IS '사용자';

insert into users values('brown', '브라운', 'c6347b73d5b1f7c77f8be828ee3e871c819578f23779c7d5e082ae2b36a44', '19/01/28', '곰', '', '', '', '', '');

insert into users values('cony', '코니', '55525cd5e74f52ef9e686b812f616b5891bda924f26450113b3f6ebd6adb5c', '19/01/28', '토끼', '', '', '', '', '');

insert into users values('sally', '샐리', 'c96de0c53a6194aceefbf83121e5b7dfb9d675dee1821d6da438dc3ae3e1ef55', '19/01/28', '병아리', '', '', '', '', '');

insert into users values('james', '제임스', 'b097f022d7879a164ea13dd63719e7a2af70c372d9139fc1d6443c6884e2f851', '19/01/28', '사람', '', '', '', '', '');

insert into users values('moon', '문', '7deff5c6fea2a1683b983bc5969afa759d13c5dc0d6dbd02c6f5bd091e06c', '', '달', '', '', '', '', '');

commit;

* BASETABLE 추가 (공개글 코드 생략 ) 접은글
접은글

“

--SET TERMOUT OFF

SET ECHO OFF

--DROP TABLE cart;

--DROP TABLE member;

--DROP TABLE buyprod;

--DROP TABLE prod;

--DROP TABLE buyer;

--DROP TABLE lprod;

-- 객체이름 30자 이내, 무조건 알파벳시작, 알파벳, 숫자, _,$

-- 객체이름은 무조건 대문자로 저장됨.

CREATE TABLE lprod

(

lprod_id NUMBER(7) NOT NULL,

lprod_gu CHAR(4) NOT NULL,

lprod_nm VARCHAR2(40) NOT NULL,

CONSTRAINT pk_lprod PRIMARY KEY (lprod_gu)

);

INSERT INTO lprod(lprod_id,lprod_gu,lprod_nm) VALUES(1,'P101','컴퓨터제품');

INSERT INTO lprod(lprod_id,lprod_gu,lprod_nm) VALUES(2,'P102','전자제품');

INSERT INTO lprod(lprod_id,lprod_gu,lprod_nm) VALUES(3,'P201','여성캐주얼');

INSERT INTO lprod(lprod_id,lprod_gu,lprod_nm) VALUES(4,'P202','남성캐주얼');

INSERT INTO lprod(lprod_id,lprod_gu,lprod_nm) VALUES(5,'P301','피혁잡화');

INSERT INTO lprod(lprod_id,lprod_gu,lprod_nm) VALUES(6,'P302','화장품');

INSERT INTO lprod(lprod_id,lprod_gu,lprod_nm) VALUES(7,'P401','음반/CD');

INSERT INTO lprod(lprod_id,lprod_gu,lprod_nm) VALUES(8,'P402','도서');

INSERT INTO lprod(lprod_id,lprod_gu,lprod_nm) VALUES(9,'P403','문구류');

-- DROP TABLE buyer

CREATE TABLE buyer

( buyer_id CHAR(6) NOT NULL, --거래처코드

buyer_name VARCHAR2(50) NOT NULL, --거래처명

buyer_lgu CHAR(4) NOT NULL, --취급상품대분류

buyer_bank VARCHAR2(40), --은행

buyer_bankno VARCHAR2(40), --계좌번호

buyer_bankname VARCHAR2(15), --예금주

buyer_zip CHAR(7), --우편번호

buyer_add1 VARCHAR2(100), --주소1

buyer_add2 VARCHAR2(80), --주소2

buyer_comtel VARCHAR2(14) NOT NULL, --전화번호

buyer_fax VARCHAR2(20) NOT NULL --fax번호

);

ALTER TABLE buyer add ( buyer_mail VARCHAR2(40) NOT NULL,

buyer_charger VARCHAR2(10),

buyer_telext VARCHAR2(2));

ALTER TABLE buyer

modify( buyer_name VARCHAR2(40));

ALTER TABLE buyer

add ( CONSTRAINT pk_buyer PRIMARY KEY(buyer_id),

CONSTRAINT fr_buyer_lgu foreign key(buyer_lgu)

references lprod(lprod_gu) );

--INSERT INTO buyer (buyer_id, buyer_name, buyer_lgu, buyer_bank,

-- buyer_bankno, buyer_bankname, buyer_zip,

-- buyer_add1, buyer_add2, buyer_comtel, buyer_fax,

-- buyer_mail, buyer_charger)

INSERT INTO buyer (buyer_id,buyer_name,buyer_lgu,buyer_bank,buyer_bankno,buyer_bankname,buyer_zip,buyer_add1,buyer_add2,buyer_comtel,buyer_fax,buyer_mail,buyer_charger)

VALUES ('P10101','삼성컴퓨터','P101','주택은행','123-456-7890','이건상','135-972','서울 강남구 도곡2동현대비젼21','1125호','02-522-7890','02-522-7891','samcom@samsung.co.kr','송동구');

INSERT INTO buyer (buyer_id,buyer_name,buyer_lgu,buyer_bank,buyer_bankno,buyer_bankname,buyer_zip,buyer_add1,buyer_add2,buyer_comtel,buyer_fax,buyer_mail,buyer_charger)

VALUES ('P10102','삼보컴퓨터','P101','제일은행','732-702-195670','김현우','142-726','서울 강북구 미아6동 행전빌딩','2712호','02-632-5690','02-632-5699','sambo@sambo.co.kr','김서구');

INSERT INTO buyer (buyer_id,buyer_name,buyer_lgu,buyer_bank,buyer_bankno,buyer_bankname,buyer_zip,buyer_add1,buyer_add2,buyer_comtel,buyer_fax,buyer_mail,buyer_charger)

VALUES ('P10103','현주컴퓨터','P101','국민은행','112-650-397811','심현주','404-260','인천 서구 마전동','157-899번지','032-233-7832','032-233-7833','hyunju@hyunju.com','강남구') ;

INSERT INTO buyer (buyer_id,buyer_name,buyer_lgu,buyer_bank,buyer_bankno,buyer_bankname,buyer_zip,buyer_add1,buyer_add2,buyer_comtel,buyer_fax,buyer_mail,buyer_charger)

VALUES ('P10201','대우전자','P102','농협','222-333-567890','강대우','702-864','대구 북구 태전동','232번지','053-780-2356','053-780-2357','daewoo@daewoo.co.kr','성대우') ;

INSERT INTO buyer (buyer_id,buyer_name,buyer_lgu,buyer_bank,buyer_bankno,buyer_bankname,buyer_zip,buyer_add1,buyer_add2,buyer_comtel,buyer_fax,buyer_mail,buyer_charger)

VALUES ('P10202','삼성전자','P102','외환은행','989-323-567898','박삼성','614-728','부산 부산진구 부전1동 동아빌딩','1708호','051-567-5312','051-567-5313','samsung@samsung.com','김인우');

INSERT INTO buyer (buyer_id,buyer_name,buyer_lgu,buyer_bank,buyer_bankno,buyer_bankname,buyer_zip,buyer_add1,buyer_add2,buyer_comtel,buyer_fax,buyer_mail,buyer_charger)

VALUES ('P20101','대현','P201','국민은행','688-323-567898','신대현','306-785','대전 대덕구 오정동 운암빌딩','508호','042-332-5123','042-332-5125','daehyun@daehyun.com','진대영');

INSERT INTO buyer (buyer_id,buyer_name,buyer_lgu,buyer_bank,buyer_bankno,buyer_bankname,buyer_zip,buyer_add1,buyer_add2,buyer_comtel,buyer_fax,buyer_mail,buyer_charger)

VALUES ('P20102','마르죠','P201','주택은행','123-777-7890','이마루','135-972','서울 강남구 도곡2동 현대비젼21','1211호','02-533-7890','02-533-5699','mar@marjo.co.kr','조현상') ;

INSERT INTO buyer (buyer_id,buyer_name,buyer_lgu,buyer_bank,buyer_bankno,buyer_bankname,buyer_zip,buyer_add1,buyer_add2,buyer_comtel,buyer_fax,buyer_mail,buyer_charger)

VALUES ('P20201','LG패션','P202','제일은행','732-702-556677','김애지','142-726','서울 강북구 미아6동 행전빌딩','5011호','02-332-5690','02-332-5699','lgfashion.co.kr','남지수');

INSERT INTO buyer (buyer_id,buyer_name,buyer_lgu,buyer_bank,buyer_bankno,buyer_bankname,buyer_zip,buyer_add1,buyer_add2,buyer_comtel,buyer_fax,buyer_mail,buyer_charger)

VALUES ('P20202','캠브리지','P202','국민은행','112-888-397811','안불이주','404-260','인천 서구 마전동','535-899번지','032-255-7832','032-255-7833','cambrige@cambrige.com','신일수');

INSERT INTO buyer (buyer_id,buyer_name,buyer_lgu,buyer_bank,buyer_bankno,buyer_bankname,buyer_zip,buyer_add1,buyer_add2,buyer_comtel,buyer_fax,buyer_mail,buyer_charger)

VALUES ('P30101','가파치','P301','농협','211-333-511890','김선아','702-864','대구 북구 태전동','555-66호','053-535-2356','053-535-2357','gapachi@gapachi.co.kr','이수나') ;

INSERT INTO buyer (buyer_id,buyer_name,buyer_lgu,buyer_bank,buyer_bankno,buyer_bankname,buyer_zip,buyer_add1,buyer_add2,buyer_comtel,buyer_fax,buyer_mail,buyer_charger)

VALUES ('P30201','한국화장품','P302','외환은행','333-355-568898','박한국','614-728','부산 부산진구 부전1동 동아빌딩','309호','051-212-5312','051-212-5313','hangook@hangook.com','김사우');

INSERT INTO buyer (buyer_id,buyer_name,buyer_lgu,buyer_bank,buyer_bankno,buyer_bankname,buyer_zip,buyer_add1,buyer_add2,buyer_comtel,buyer_fax,buyer_mail,buyer_charger)

VALUES ('P30202','피리어스','P302','국민은행','677-888-569998','신상우','306-785','대전대덕구 오정동 운암빌딩','612호','042-222-5123','042-222-5125','pieoris@pieoris.com','이진영');

INSERT INTO buyer (buyer_id,buyer_name,buyer_lgu,buyer_bank,buyer_bankno,buyer_bankname,buyer_zip,buyer_add1,buyer_add2,buyer_comtel,buyer_fax,buyer_mail,buyer_charger)

VALUES ('P30203','참존','P302','주택은행','555-777-567778','오참존','306-785','대전대덕구 오정동 운암빌딩','1007호','042-622-5123','042-622-5125','chamjon@chamjon.com','성애란');

CREATE TABLE prod

( prod_id VARCHAR2(10) NOT NULL, -- 상품코드

prod_name VARCHAR2(40) NOT NULL, -- 상품명

prod_lgu CHAR(4 ) NOT NULL, -- 상품분류

prod_buyer CHAR(6) NOT NULL, -- 공급업체(코드)

prod_cost NUMBER(10) NOT NULL, -- 매입가

prod_price NUMBER(10) NOT NULL, -- 소비자가

prod_sale NUMBER(10) NOT NULL, -- 판매가

prod_outline VARCHAR2(100) NOT NULL, -- 상품개략설명

prod_detail CLOB, -- 상품상세설명

prod_img VARCHAR2(40) NOT NULL, -- 이미지(소)

prod_totalstock NUMBER(10) NOT NULL, -- 재고수량

prod_insdate DATE, -- 신규일자(등록일)

prod_properstock NUMBER(10) NOT NULL, -- 안전재고수량

prod_size VARCHAR2(20), -- 크기

prod_color VARCHAR2(20), -- 색상

prod_delivery VARCHAR2(255), -- 배달특기사항

prod_unit VARCHAR2(6), -- 단위(수량)

prod_qtyin NUMBER(10), -- 총입고수량

prod_qtysale NUMBER(10), -- 총판매수량

prod_mileage NUMBER(10), -- 개당 마일리지 점수

CONSTRAINT pk_prod_id PRIMARY KEY (prod_id),

CONSTRAINT fr_prod_lgu FOREIGN KEY (prod_lgu) REFERENCES lprod(lprod_gu),

CONSTRAINT fr_prod_buyer FOREIGN KEY (prod_buyer) REFERENCES buyer(buyer_id)

);

INSERT INTO prod (prod_id,prod_name,prod_lgu,prod_buyer,prod_cost,prod_price,prod_sale,prod_outline,prod_detail,prod_img,prod_totalstock,prod_insdate,prod_properstock,prod_size,prod_color,prod_delivery,prod_unit,prod_qtyin,prod_qtysale)

VALUES ('P101000001','모니터 삼성전자15인치칼라','P101','P10101',210000,290000,230000,'평면모니터의 기적','우리기술의 개가','P101000001.gif',0,'2005-01-10',33,'15인치','','파손 주의','EA',0,0) ;

VALUES ('P101000002','모니터 삼성전자17인치칼라','P101','P10101',310000,390000,330000,'평면모니터의 기적','우리기술의 개가','P101000002.gif',0,'2005-01-10',23,'17인치','','파손 주의','EA',0,0) ;

VALUES ('P101000003','모니터 삼성전자19인치칼라','P101','P10101',410000,490000,430000,'평면모니터의 기적','우리기술의 개가','P101000003.gif',0,'2005-01-10',15,'19인치','','파손 주의','EA',0,0) ;

VALUES ('P101000004','삼보컴퓨터 P-III 600Mhz','P101','P10102',1150000,1780000,1330000,'쉬운 인터넷을.....','새로운 차원의 컴퓨터를.....','P101000004.gif',0,'2005-02-08',22,'','','파손 주의','EA',0,0) ;

VALUES ('P101000005','삼보컴퓨터 P-III 700Mhz','P101','P10102',2150000,2780000,2330000,'쉬운 인터넷을.....','새로운 차원의 컴퓨터를.....','P101000005.gif',0,'2005-02-08',31,'','','파손 주의','EA',0,0) ;

VALUES ('P101000006','삼보컴퓨터 P-III 800Mhz','P101','P10102',3150000,3780000,3330000,'쉬운 인터넷을.....','새로운 차원의 컴퓨터를.....','P101000006.gif',0,'2005-02-08',17,'','','파손 주의','EA',0,0) ;

VALUES ('P102000001','대우 칼라 TV 25인치','P102','P10201',690000,820000,720000,'집안에 영화관을.....','평면 브라운관의 새장.....','P102000001.gif',0,'2005-02-22',53,'25인치','흑색','파손 주의','EA',0,0) ;

VALUES ('P102000002','대우 칼라 TV 29인치','P102','P10201',890000,1020000,920000,'집안에 영화관을.....','평면 브라운관의 새장.....','P102000002.gif',0,'2005-02-22',21,'29인치','흑색','파손 주의','EA',0,0) ;

VALUES ('P102000003','삼성 칼라 TV 21인치','P102','P10202',590000,720000,620000,'집안에 영화관을.....','평면 브라운관의 새장.....','P102000003.gif',0,'2005-01-22',11,'21인치','은색','파손 주의','EA',0,0) ;

VALUES ('P102000004','삼성 칼라 TV 29인치','P102','P10202',990000,1120000,1020000,'집안에 영화관을.....','평면 브라운관의 새장.....','P102000004.gif',0,'2005-01-22',19,'29인치','은색','파손 주의','EA',0,0) ;

VALUES ('P102000005','삼성 칼라 TV 53인치','P102','P10202',1990000,2120000,2020000,'집안에 영화관을.....','평면 브라운관의 새장.....','P102000005.gif',0,'2005-01-22',8,'53인치','은색','파손 주의','EA',0,0) ;

VALUES ('P102000006','삼성 캠코더','P102','P10202',660000,880000,770000,'가족과 영화촬영을.....','레저와 함께.....','P102000006.gif',0,'2005-02-23',17,'','','파손 주의','EA',0,0) ;

VALUES ('P102000007','대우 VTR 6헤드','P102','P10201',550000,760000,610000,'선명한 화질','감동의 명화를.....','P102000007.gif',0,'2005-01-23',36,'','','파손 주의','EA',0,0) ;

VALUES ('P201000001','여성 봄 셔츠 1','P201','P20101',21000,42000,27000,'파릇한 봄을 위한','아름다운.....','P201000001.gif',0,'2005-01-09',9,'s','청색','세탁 주의','EA',0,0) ;

VALUES ('P201000002','여성 봄 셔츠 2','P201','P20101',22000,43000,28000,'파릇한 봄을 위한','아름다운.....','P201000002.gif',0,'2005-01-09',9,'M','흰색','세탁 주의','EA',0,0) ;

VALUES ('P201000003','여성 봄 셔츠 3','P201','P20101',23000,44000,29000,'파릇한 봄을 위한','아름다운.....','P201000003.gif',0,'2005-01-09',9,'L','감색','세탁 주의','EA',0,0) ;

VALUES ('P201000004','여성 여름 셔츠 1','P201','P20101',12000,21000,25000,'시원한 여름을 위한','아름다운.....','P201000004.gif',0,'2005-01-11',9,'s','청색','세탁 주의','EA',0,0) ;

VALUES ('P201000005','여성 여름 셔츠 2','P201','P20101',13000,22000,26000,'시원한 여름을 위한','아름다운.....','P201000005.gif',0,'2005-01-11',9,'M','흰색','세탁 주의','EA',0,0) ;

VALUES ('P201000006','여성 여름 셔츠 3','P201','P20101',14000,23000,27000,'시원한 여름을 위한','아름다운.....','P201000006.gif',0,'2005-01-11',9,'L','감색','세탁 주의','EA',0,0) ;

VALUES ('P201000007','여성 겨울 라운드 셔츠 1','P201','P20101',31000,45000,33000,'따뜻한 겨울을 위한','아름다운.....','P201000007.gif',0,'2005-01-25',9,'s','청색','세탁 주의','EA',0,0) ;

VALUES ('P201000008','여성 겨울 라운드 셔츠 2','P201','P20101',32000,46000,34000,'따뜻한 겨울을 위한','아름다운.....','P201000008.gif',0,'2005-01-25',9,'M','흰색','세탁 주의','EA',0,0) ;

VALUES ('P201000009','여성 겨울 라운드 셔츠 3','P201','P20101',33000,47000,35000,'따뜻한 겨울을 위한','아름다운.....','P201000009.gif',0,'2005-01-25',9,'L','감색','세탁 주의','EA',0,0) ;

VALUES ('P201000010','여성 청바지 1','P201','P20102',55000,66000,57000,'편리한 활동파를 위한','편리한.....','P201000010.gif',0,'2005-01-31',38,'30','','세탁 주의','EA',0,0) ;

VALUES ('P201000011','여성 청바지 2','P201','P20102',56000,67000,58000,'편리한 활동파를 위한','편리한.....','P201000011.gif',0,'2005-01-31',35,'32','','세탁 주의','EA',0,0) ;

VALUES ('P201000012','여성 청바지 3','P201','P20102',57000,68000,59000,'편리한 활동파를 위한','편리한.....','P201000012.gif',0,'2005-01-31',33,'34','','세탁 주의','EA',0,0) ;

VALUES ('P201000013','여성 봄 자켓 1','P201','P20101',110000,210000,170000,'편리한 활동파의 봄을 위한','아름다운.....','P201000013.gif',0,'2005-02-18',16,'66','청색','세탁 주의','EA',0,0) ;

VALUES ('P201000014','여성 봄 자켓 2','P201','P20101',120000,220000,180000,'편리한 활동파의 봄을 위한','아름다운.....','P201000014.gif',0,'2005-02-18',18,'77','흰색','세탁 주의','EA',0,0) ;

VALUES ('P201000015','여성 봄 자켓 3','P201','P20101',130000,230000,190000,'편리한 활동파의 봄을 위한','아름다운.....','P201000015.gif',0,'2005-02-18',17,'88','감색','세탁 주의','EA',0,0) ;

VALUES ('P201000016','여성 여름 자켓 1','P201','P20102',100000,160000,130000,'편리한 활동파의 여름을 위한','아름다운.....','P201000016.gif',0,'2005-02-21',12,'66','청색','세탁 주의','EA',0,0) ;

VALUES ('P201000017','여성 여름 자켓 2','P201','P20102',110000,170000,140000,'편리한 활동파의 여름을 위한','아름다운.....','P201000017.gif',0,'2005-02-21',21,'77','흰색','세탁 주의','EA',0,0) ;

VALUES ('P201000018','여성 여름 자켓 3','P201','P20102',120000,180000,150000,'편리한 활동파의 여름을 위한','아름다운.....','P201000018.gif',0,'2005-02-21',11,'77','감색','세탁 주의','EA',0,0) ;

VALUES ('P201000019','여성 겨울 자켓 1','P201','P20102',210000,270000,240000,'편리한 활동파의 따뜻한 겨울을 위한','아름다운.....','P201000019.gif',0,'2005-02-28',22,'66','청색','세탁 주의','EA',0,0) ;

VALUES ('P201000020','여성 겨울 자켓 2','P201','P20102',220000,280000,250000,'편리한 활동파의 따뜻한 겨울을 위한','아름다운.....','P201000020.gif',0,'2005-02-28',29,'77','흰색','세탁 주의','EA',0,0) ;

VALUES ('P201000021','여성 겨울 자켓 3','P201','P20102',230000,290000,260000,'편리한 활동파의 따뜻한 겨울을 위한','아름다운.....','P201000021.gif',0,'2005-02-28',19,'88','감색','세탁 주의','EA',0,0) ;

VALUES ('P202000001','남성 봄 셔츠 1','P202','P20201',10000,19000,15000,'파릇한 봄을 위한','아름다운.....','P202000001.gif',0,'2005-01-05',9,'M','청색','세탁 주의','EA',0,0) ;

VALUES ('P202000002','남성 봄 셔츠 2','P202','P20201',13000,22000,18000,'파릇한 봄을 위한','아름다운.....','P202000002.gif',0,'2005-01-05',9,'L','흰색','세탁 주의','EA',0,0) ;

VALUES ('P202000003','남성 봄 셔츠 3','P202','P20201',15000,24000,20000,'파릇한 봄을 위한','아름다운.....','P202000003.gif',0,'2005-01-05',9,'XL','감색','세탁 주의','EA',0,0) ;

VALUES ('P202000004','남성 여름 셔츠 1','P202','P20201',18000,28000,23000,'시원한 여름을 위한','아름다운.....','P202000004.gif',0,'2005-02-05',9,'M','청색','세탁 주의','EA',0,0) ;

VALUES ('P202000005','남성 여름 셔츠 2','P202','P20201',23000,33000,28000,'시원한 여름을 위한','아름다운.....','P202000005.gif',0,'2005-02-05',9,'L','흰색','세탁 주의','EA',0,0) ;

VALUES ('P202000006','남성 여름 셔츠 3','P202','P20201',28000,38000,33000,'시원한 여름을 위한','아름다운.....','P202000006.gif',0,'2005-02-05',9,'XL','감색','세탁 주의','EA',0,0) ;

VALUES ('P202000007','남성 겨울 라운드 셔츠 1','P202','P20201',25000,42000,31000,'따뜻한 겨울을 위한','아름다운.....','P202000007.gif',0,'2005-01-13',9,'M','청색','세탁 주의','EA',0,0) ;

VALUES ('P202000008','남성 겨울 라운드 셔츠 2','P202','P20201',27000,43000,33000,'따뜻한 겨울을 위한','아름다운.....','P202000008.gif',0,'2005-01-13',9,'L','흰색','세탁 주의','EA',0,0) ;

VALUES ('P202000009','남성 겨울 라운드 셔츠 3','P202','P20201',28500,44000,35000,'따뜻한 겨울을 위한','아름다운.....','P202000009.gif',0,'2005-01-13',9,'XL','감색','세탁 주의','EA',0,0) ;

VALUES ('P202000010','남성 청바지 1','P202','P20202',55000,66000,58000,'편리한 활동파를 위한','편리한.....','P202000010.gif',0,'2005-01-16',38,'30','','세탁 주의','EA',0,0) ;

VALUES ('P202000011','남성 청바지 2','P202','P20202',55000,66000,58000,'편리한 활동파를 위한','편리한.....','P202000011.gif',0,'2005-01-16',35,'32','','세탁 주의','EA',0,0) ;

VALUES ('P202000012','남성 청바지 3','P202','P20202',55000,66000,58000,'편리한 활동파를 위한','편리한.....','P202000012.gif',0,'2005-01-16',33,'34','','세탁 주의','EA',0,0) ;

VALUES ('P202000013','남성 봄 자켓 1','P202','P20201',110000,230000,150000,'편리한 활동파의 봄을 위한','아름다운.....','P202000013.gif',0,'2005-02-17',16,'M','청색','세탁 주의','EA',0,0) ;

VALUES ('P202000014','남성 봄 자켓 2','P202','P20201',120000,230000,160000,'편리한 활동파의 봄을 위한','아름다운.....','P202000014.gif',0,'2005-02-17',18,'L','흰색','세탁 주의','EA',0,0) ;

VALUES ('P202000015','남성 봄 자켓 3','P202','P20201',130000,230000,170000,'편리한 활동파의 봄을 위한','아름다운.....','P202000015.gif',0,'2005-02-17',17,'XL','감색','세탁 주의','EA',0,0) ;

VALUES ('P202000016','남성 여름 자켓 1','P202','P20202',99000,160000,130000,'편리한 활동파의 여름을 위한','아름다운.....','P202000016.gif',0,'2005-02-06',12,'M','청색','세탁 주의','EA',0,0);

VALUES ('P202000017','남성 여름 자켓 2','P202','P20202',109000,170000,150000,'편리한 활동파의 여름을 위한','아름다운.....','P202000017.gif',0,'2005-02-06',21,'L','흰색','세탁 주의','EA',0,0) ;

VALUES ('P202000018','남성 여름 자켓 3','P202','P20202',159000,190000,170000,'편리한 활동파의 여름을 위한','아름다운.....','P202000018.gif',0,'2005-02-06',11,'XL','감색','세탁 주의','EA',0,0) ;

VALUES ('P202000019','남성 겨울 자켓 1','P202','P20202',210000,370000,280000,'편리한 활동파의 따뜻한 겨울을 위한','아름다운.....','P202000019.gif',0,'2005-02-20',22,'M','청색','세탁 주의','EA',0,0) ;

VALUES ('P202000020','남성 겨울 자켓 2','P202','P20202',220000,370000,290000,'편리한 활동파의 따뜻한 겨울을 위한','아름다운.....','P202000020.gif',0,'2005-02-20',29,'L','흰색','세탁 주의','EA',0,0);

VALUES ('P202000021','남성 겨울 자켓 3','P202','P20202',230000,370000,300000,'편리한 활동파의 따뜻한 겨울을 위한','아름다운.....','P202000021.gif',0,'2005-02-20',19,'XL','감색','세탁 주의','EA',0,0) ;

VALUES ('P301000001','악어 가죽 혁대','P301','P30101',21000,41000,33000,'멋진 혁대를 선물로.....','진귀한 가죽으로 제작된.....','P301000001.gif',0,'2005-01-15',32,'','','','EA',0,0) ;

VALUES ('P301000002','물소 가죽 장지갑','P301','P30101',17000,37000,29000,'멋진 지갑을 선물로.....','진귀한 가죽으로 제작된.....','P301000002.gif',0,'2005-01-15',52,'','','','EA',0,0) ;

VALUES ('P301000003','여성용 손지갑','P301','P30101',22000,33000,26000,'멋진 지갑을 선물로.....','진귀한 가죽으로 제작된.....','P301000003.gif',0,'2005-02-15',22,'','','','EA',0,0) ;

VALUES ('P301000004','여성용 캐쥬얼 벨트','P301','P30101',27000,37000,29000,'멋진 벨트를 선물로.....','진귀한 가죽으로 제작된.....','P301000004.gif',0,'2005-02-15',21,'','','','EA',0,0) ;

VALUES ('P302000001','향수 NO 5','P302','P30201',89000,110000,93000,'향기를 동반한.....','다정함을 향기와 함께.....','P302000001.gif',0,'2005-01-24',11,'','','','EA',0,0) ;

VALUES ('P302000002','샤넬 NO 7','P302','P30201',99000,120000,103000,'향기를 동반한.....','다정함을 향기와 함께.....','P302000002.gif',0,'2005-01-24',17,'','','','EA',0,0);

VALUES ('P302000003','남성용 스킨','P302','P30201',19000,32000,21000,'세안후 바르는.....','은은한 향기와 함께.....','P302000003.gif',0,'2005-01-24',21,'','','','EA',0,0) ;

VALUES ('P302000004','남성용 로숀','P302','P30201',21000,33000,23000,'세안후 바르는.....','은은한 향기와 함께.....','P302000004.gif',0,'2005-02-12',19,'','','','EA',0,0) ;

VALUES ('P302000005','여성용 스킨','P302','P30201',18000,31000,20000,'세안후 바르는.....','은은한 향기와 함께.....','P302000005.gif',0,'2005-02-12',21,'','','','EA',0,0) ;

VALUES ('P302000006','여성용 로숀','P302','P30201',20000,32000,22000,'세안후 바르는.....','은은한 향기와 함께.....','P302000006.gif',0,'2005-02-12',19,'','','','EA',0,0) ;

VALUES ('P302000011','남성 향수','P302','P30202',59000,70000,63000,'좋은 향기를 동반한.....','다정함을 향기와 함께.....','P302000011.gif',0,'2005-01-13',21,'','','','EA',0,0) ;

VALUES ('P302000012','여성 향수','P302','P30202',89000,110000,93000,'좋은향기를 동반한.....','다정함을 향기와 함께.....','P302000012.gif',0,'2005-01-13',27,'','','','EA',0,0) ;

VALUES ('P302000013','립스틱','P302','P30202',17000,27000,23000,'세안후 바르는 좋은.....','은은한 향기와 함께.....','P302000013.gif',0,'2005-01-13',11,'','','','EA',0,0) ;

VALUES ('P302000014','면도크림','P302','P30202',25000,32000,26000,'세안후 바르는 좋은.....','은은한 향기와 함께.....','P302000014.gif',0,'2005-01-14',29,'','','','EA',0,0) ;

VALUES ('P302000015','화운데이션','P302','P30202',22000,32000,23000,'세안후 바르는 좋은.....','은은한 향기와 함께.....','P302000015.gif',0,'2005-01-14',15,'','','','EA',0,0) ;

VALUES ('P302000016','머드팩','P302','P30202',120000,220000,172000,'세안후 바르는 좋은.....','은은한 향기와 함께.....','P302000016.gif',0,'2005-01-14',32,'','','','EA',0,0) ;

VALUES ('P302000021','참존 기초화장품','P302','P30203',23500,37500,26000,'피부를 산뜻하게.....','다정함을 향기와 함께.....','P302000021.gif',0,'2005-01-28',25,'','','','EA',0,0) ;

VALUES ('P302000022','참존 여성 향수','P302','P30203',78500,98500,83000,'좋은향기와 피부를 산뜻하게.....','다정함을 향기와 함께.....','P302000022.gif',0,'2005-01-28',53,'','','','EA',0,0) ;

VALUES ('P302000023','참존 립스틱','P302','P30203',21500,26500,22500,'좋은 피부를 산뜻하게.....','은은한 향기와 함께.....','P302000023.gif',0,'2005-01-28',17,'','','','EA',0,0) ;

CREATE TABLE buyprod

( buy_date DATE NOT NULL, -- 입고일자

buy_prod VARCHAR2(10) NOT NULL, -- 상품코드

buy_qty NUMBER(10) NOT NULL, -- 매입수량

buy_cost NUMBER(10) NOT NULL, -- 매입단가

CONSTRAINT pk_buyprod PRIMARY KEY (buy_date,buy_prod),

CONSTRAINT fr_buy_prod FOREIGN KEY (buy_prod) REFERENCES prod(prod_id)

);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-08','P202000001',18,10000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-08','P202000002',19,13000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-08','P202000003',11,15000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-12','P201000001',21,21000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-12','P201000002',13,22000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-12','P201000003',15,23000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-13','P101000001',22,210000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-13','P101000002',23,310000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-13','P101000003',21,410000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-14','P201000004',15,12000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-14','P201000005',32,13000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-14','P201000006',11,14000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-16','P202000007',22,25000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-16','P202000008',33,27000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-16','P202000009',14,28500);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-16','P302000011',125,59000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-16','P302000012',16,89000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-16','P302000013',13,17000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-17','P302000014',21,25000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-17','P302000015',33,22000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-17','P302000016',17,120000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-18','P301000001',15,21000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-18','P301000002',19,17000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-19','P202000010',21,55000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-19','P202000011',91,55000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-19','P202000012',15,55000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-25','P102000003',11,590000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-25','P102000004',13,990000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-25','P102000005',22,1990000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-26','P102000007',52,550000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-27','P302000001',253,89000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-27','P302000002',31,99000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-27','P302000003',197,19000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-28','P201000007',19,31000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-28','P201000008',22,32000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-28','P201000009',26,33000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-31','P302000021',23,23500);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-31','P302000022',17,78500);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-01-31','P302000023',15,21500);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-02-03','P201000010',23,55000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-02-03','P201000011',21,56000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-02-03','P201000012',55,57000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-02-08','P202000004',12,18000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-02-08','P202000005',19,23000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-02-08','P202000006',28,28000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-02-09','P202000016',22,99000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-02-09','P202000017',41,109000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-02-09','P202000018',21,159000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-02-11','P101000004',11,1150000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-02-11','P101000005',10,2150000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-02-11','P101000006',9,3150000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-02-15','P302000004',33,21000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-02-15','P302000005',191,18000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-02-15','P302000006',39,20000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-02-18','P301000003',46,22000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-02-18','P301000004',41,27000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-02-20','P202000013',16,110000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-02-20','P202000014',18,120000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-02-20','P202000015',13,130000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-02-21','P201000013',16,110000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-02-21','P201000014',28,120000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-02-21','P201000015',25,130000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-02-23','P202000019',22,210000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-02-23','P202000020',19,220000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-02-23','P202000021',13,230000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-02-25','P102000001',15,690000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-02-25','P102000002',12,890000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-02-26','P102000006',13,660000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-03-02','P201000016',725,100000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-03-02','P201000017',341,110000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-03-02','P201000018',111,120000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-03-03','P201000019',16,210000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-03-03','P201000020',39,220000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-03-03','P201000021',32,230000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-04-16','P202000001',12,10000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-04-16','P202000002',13,13000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-04-16','P202000003',5,15000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-04-20','P201000001',15,21000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-04-20','P201000002',7,22000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-04-20','P201000003',9,23000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-04-21','P101000001',16,210000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-04-21','P101000002',17,310000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-04-21','P101000003',15,410000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-04-22','P201000004',9,12000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-04-22','P201000005',26,13000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-04-22','P201000006',5,14000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-04-24','P202000007',16,25000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-04-24','P202000008',27,27000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-04-24','P202000009',8,28500);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-04-24','P302000011',19,59000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-04-24','P302000012',10,89000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-04-24','P302000013',7,17000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-04-25','P302000014',15,25000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-04-25','P302000015',27,22000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-04-25','P302000016',11,120000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-04-26','P301000001',9,21000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-04-26','P301000002',13,17000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-04-27','P202000010',15,55000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-04-27','P202000011',25,55000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-04-27','P202000012',9,55000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-03','P102000003',5,590000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-03','P102000004',7,990000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-03','P102000005',16,1990000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-04','P102000007',46,550000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-05','P302000001',17,89000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-05','P302000002',25,99000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-05','P302000003',11,19000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-06','P201000007',13,31000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-06','P201000008',16,32000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-06','P201000009',20,33000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-09','P302000021',17,23500);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-09','P302000022',11,78500);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-09','P302000023',9,21500);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-12','P201000010',17,55000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-12','P201000011',15,56000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-12','P201000012',49,57000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-17','P202000004',6,18000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-17','P202000005',13,23000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-17','P202000006',22,28000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-18','P202000016',16,99000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-18','P202000017',35,109000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-18','P202000018',15,159000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-20','P101000004',5,1150000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-20','P101000005',4,2150000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-20','P101000006',3,3150000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-24','P302000004',27,21000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-24','P302000005',25,18000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-24','P302000006',33,20000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-27','P301000003',40,22000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-27','P301000004',35,27000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-29','P202000013',10,110000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-29','P202000014',12,120000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-29','P202000015',7,130000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-30','P201000013',10,110000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-30','P201000014',22,120000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-05-30','P201000015',19,130000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-06-01','P202000019',16,210000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-06-01','P202000020',13,220000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-06-01','P202000021',7,230000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-06-03','P102000001',9,690000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-06-03','P102000002',6,890000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-06-04','P102000006',7,660000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-06-09','P201000016',19,100000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-06-09','P201000017',35,110000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-06-09','P201000018',25,120000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-06-10','P201000019',10,210000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-06-10','P201000020',33,220000);

INSERT INTO buyprod(buy_date,buy_prod,buy_qty,buy_cost) VALUES('2005-06-10','P201000021',26,230000);

CREATE TABLE member

( mem_id VARCHAR2(15) NOT NULL, -- 회원ID

mem_pass VARCHAR2(15) NOT NULL, -- 비밀번호

mem_name VARCHAR2(20) NOT NULL, -- 성명

mem_regno1 CHAR(6) NOT NULL, -- 주민등록번호앞6자리

mem_regno2 CHAR(7) NOT NULL, -- 주민등록번호뒤7자리

mem_bir DATE, -- 생일

mem_zip CHAR(7) NOT NULL, -- 우편번호

mem_add1 VARCHAR2(100) NOT NULL, -- 주소1

mem_add2 VARCHAR2(80) NOT NULL, -- 주소2

mem_hometel VARCHAR2(14) NOT NULL, -- 집전화번호

mem_comtel VARCHAR2(14) NOT NULL, -- 회사전화번호

mem_hp VARCHAR2(15), -- 이동전화

mem_mail VARCHAR2(40) NOT NULL, -- E-mail주소

mem_job VARCHAR2(40), -- 직업

mem_like VARCHAR2(40), -- 취미

mem_memorial VARCHAR2(40), -- 기념일명

mem_memorialday DATE, -- 기념일날짜

mem_mileage NUMBER(10), -- 마일리지

mem_delete VARCHAR2(1), -- 삭제여부

CONSTRAINT pk_mem_id PRIMARY KEY (mem_id)

);

INSERT INTO member (mem_id,mem_pass,mem_name,mem_regno1,mem_regno2,mem_bir,mem_zip,mem_add1,mem_add2,mem_hometel,