|
CREATE TABLE PERF
(
CUST_ID CHAR(5) PRIMARY KEY NOT NULL,
CUST_NM CHAR(10) NOT NULL,
CUST_BIRTH DATETIME,
VISIT_CNT DECIMAL(15,0),
SALES_AMT DECIMAL(15,0),
SALES_CNT DECIMAL(15,0)
);
INSERT INTO PERF VALUES ('56456', '모형건', '1982-01-24', 123, 3700000, 24);
INSERT INTO PERF VALUES ('65412', '이상훈', '1984-05-10', 23, 467200, 14);
INSERT INTO PERF VALUES ('23472', '이상희', '1978-02-27', 117, 2237065, 23);
INSERT INTO PERF VALUES ('27896', '모영길', '1982-05-11', 37, 123721, 2);
INSERT INTO PERF VALUES ('35478', '강주혁', '1983-09-10', 86, 830000, 30);
INSERT INTO PERF VALUES ('78693', '이선우', '1977-07-07', 103, 1789023, 7);
CREATE TABLE EMP
(
ID CHAR(3) PRIMARY KEY NOT NULL,
“POSITION” CHAR(10) NOT NULL,
PARTY_NM CHAR(10) NOT NULL,
MANAGER_ID CHAR(10) NOT NULL,
TEAM_NM CHAR(10) NOT NULL,
GRADE CHAR(10) NOT NULL
);
INSERT INTO EMP VALUES ('650', '대리', '이재훈', '1270', '마케팅부', '1');
INSERT INTO EMP VALUES ('540', '과장', '장성수', '3221', '리스크부', '2');
INSERT INTO EMP VALUES ('210', '차장', '문보미', '3914', '인사팀', '3');
INSERT INTO EMP VALUES ('347', '차장', '정호천', '3942', '기획팀', '3');
INSERT INTO EMP VALUES ('970', '부장', '김영성', '3221', '리스크부', '2');
INSERT INTO EMP VALUES ('345', '대리', '오윤경', '1270', '마케팅부', '2');
INSERT INTO EMP VALUES ('711', '과장', '이재중', '3914', '인사팀', '2');
CREATE TABLE CARD_TRAN_201311
(
CMF CHAR(4) PRIMARY KEY NOT NULL,
PARTY_NM CHAR(10) NOT NULL,
SEG CHAR(10) NOT NULL,
PIF_AMT DECIMAL(15,0),
INST_AMT DECIMAL(15,0),
OVRS_AMT DECIMAL(15,0),
CASH_AMT DECIMAL(15,0)
);
INSERT INTO CARD_TRAN_201311 VALUES ('2356', '김아름', 'PB', 1234041, null, 1301710, null);
INSERT INTO CARD_TRAN_201311 VALUES ('4570', '이선우', 'MASS', null, null, 524560, null);
INSERT INTO CARD_TRAN_201311 VALUES ('4563', '홍지은', 'MASS', 213570, null, null, 3700000);
INSERT INTO CARD_TRAN_201311 VALUES ('3266', '윤일상', 'MASS', 89641, null, null, null);
INSERT INTO CARD_TRAN_201311 VALUES ('8904', '이동건', 'PB', 1278960, 500000, null,null );
INSERT INTO CARD_TRAN_201311 VALUES ('4678', '최혜연', 'MASS', 4567780, null, null, null);
INSERT INTO CARD_TRAN_201311 VALUES ('1746', '임하영', 'PB', 7836100, 3213400,null , null);
INSERT INTO CARD_TRAN_201311 VALUES ('3120', '김지철', 'PB',null , null, null, null);
INSERT INTO CARD_TRAN_201311 VALUES ('8974', '강성범', 'MASS', 655456, null, null, null);
INSERT INTO CARD_TRAN_201311 VALUES ('3255', '김지연', 'MASS', 213, null, null, null);
INSERT INTO CARD_TRAN_201311 VALUES ('8977', '김지현', 'PB', 1300, null, 54000, 100000);
CREATE TABLE PERF_MAST_201312
(
CMF CHAR(4) PRIMARY KEY NOT NULL,
PARTY_NM CHAR(10) NOT NULL,
SEG CHAR(10) NOT NULL,
TOT_AMT_1 DECIMAL(15,0),
TOT_AMT_2 DECIMAL(15,0),
TOT_AMT_3 DECIMAL(15,0)
);
INSERT INTO PERF_MAST_201312 VALUES ('2356', '김아름', 'PB', 790, 1770, 4780);
INSERT INTO PERF_MAST_201312 VALUES ('4570', '이선우', 'MASS', 90700, 5789, 87986);
INSERT INTO PERF_MAST_201312 VALUES ('4563', '홍지은', 'MASS', null, null, null);
INSERT INTO PERF_MAST_201312 VALUES ('3268', '윤일상', 'MASS', 88805, 659860, 5130);
INSERT INTO PERF_MAST_201312 VALUES ('8904', '이동건', 'PB', 9846000, 5708900, 7600000);
INSERT INTO PERF_MAST_201312 VALUES ('4678', '최혜연', 'MASS', null, 6000, null);
INSERT INTO PERF_MAST_201312 VALUES ('1748', '임하영', 'PB', 1000400, 788000, 2378696);
INSERT INTO PERF_MAST_201312 VALUES ('3120', '김지철', 'PB',null ,null , null);
INSERT INTO PERF_MAST_201312 VALUES ('8974', '강성범', 'MASS', 540,null , 7700);
INSERT INTO PERF_MAST_201312 VALUES ('3255', '김지연', 'MASS', 254860, 578321, 432004);
INSERT INTO PERF_MAST_201312 VALUES ('8977', '김지현', 'PB', 687063, 870000, 545400);
CREATE TABLE CUST_PERF
(
ID CHAR(4) PRIMARY KEY NOT NULL,
NAME CHAR(10) NOT NULL,
SEG CHAR(10) NOT NULL,
TOT_AMT_1 DECIMAL(15,0),
TOT_AMT_2 DECIMAL(15,0),
TOT_AMT_3 DECIMAL(15,0),
CITY CHAR(10),
COUNTRY CHAR(10)
);
INSERT INTO CUST_PERF VALUES ('1', 'SUE', 'PRIORITY', 790, 1770, 4780, 'BERLIN', 'GERMANY');
INSERT INTO CUST_PERF VALUES ('2', 'DAVID', 'MASS', 90700, 5789, 87986, 'BERN', 'SWITZERLAND');
INSERT INTO CUST_PERF VALUES ('3', 'SAM', 'MASS', null , null, null, 'NANTES', 'FRANCE');
INSERT INTO CUST_PERF VALUES ('4', 'KIM', 'MASS', 88805, 659860, 5130, 'BERGAMO', 'BRAZIL');
INSERT INTO CUST_PERF VALUES ('5', 'LEE', 'PRIORITY', 9846000, 5708900, 7600000, 'VERSAILLES', 'FRANCE');
INSERT INTO CUST_PERF VALUES ('6', 'BERNEY', 'MASS', null, 6000, null, 'BERGAMO', 'ITALY');
INSERT INTO CUST_PERF VALUES ('7', 'SANDY', 'PRIORITY', 1000400, 788000, 2378696, 'BERLIN', 'GERMANY');
INSERT INTO CUST_PERF VALUES ('8', 'YOUNG', 'MASS',null ,null ,null , 'SEOUL', 'KOREA');
INSERT INTO CUST_PERF VALUES ('9', 'SALLY', 'MASS', 540,null , 7700, 'TOKYO', 'JAPAN');
INSERT INTO CUST_PERF VALUES ('10', 'BRIAN', 'MASS', 254860, 578321, 432004, 'PUSAN', 'KOREA');
INSERT INTO CUST_PERF VALUES ('11', 'CHRISTINA', 'PRIORITY', 687063, 870000, 545400, 'OSAKA', 'JAPAN');
CREATE TABLE CUST_INFO
(
RESIDENCE_ID CHAR(13) PRIMARY KEY NOT NULL,
FIRST_NM CHAR(10) NOT NULL,
LAST_NM CHAR(10) NOT NULL,
ANNL_PERF DECIMAL(15,2)
);
INSERT INTO CUST_INFO VALUES ('8301111999999', 'JIHUN', 'KIM', 330.08);
INSERT INTO CUST_INFO VALUES ('7012012888888', 'JINYOUNG', 'LEE', 857.61);
INSERT INTO CUST_INFO VALUES ('6705302777666', 'MIJA', 'HAN', -76.77);
INSERT INTO CUST_INFO VALUES ('8411011555666', 'YOUNGJUN', 'HA', 468.54);
INSERT INTO CUST_INFO VALUES ('7710092666777', 'DAYOUNG', 'SUNG', -890);
INSERT INTO CUST_INFO VALUES ('7911022444555', 'HYEJIN', 'SEO', 47.44);
CREATE TABLE CASA_201312
(
CUST_ID CHAR(13) PRIMARY KEY NOT NULL,
CUST_SEG CHAR(10) NOT NULL,
BALANCE_201311 DECIMAL(15,0),
BALANCE_201312 DECIMAL(15,0)
);
INSERT INTO CASA_201312 VALUES ('54560', 'SILVER', 1000000, 2000000);
INSERT INTO CASA_201312 VALUES ('68477', 'GOLD', 112000, 3500);
INSERT INTO CASA_201312 VALUES ('96147', 'SILVER', 300000, 1000010);
INSERT INTO CASA_201312 VALUES ('32134', 'GOLD', 2354000, 3200000);
INSERT INTO CASA_201312 VALUES ('12478', 'DIAMOND', 6015000, 5800000);
INSERT INTO CASA_201312 VALUES ('54789', 'SILVER', 200000, 300000);
INSERT INTO CASA_201312 VALUES ('34181', 'GOLD', 4200000, 4100000);
INSERT INTO CASA_201312 VALUES ('23458', 'DIAMOND', 5000000, 6200000);
INSERT INTO CASA_201312 VALUES ('12344', 'SILVER', 210000, 200000);
CREATE TABLE PPC_MAST_201312
(
SSN CHAR(13) NOT NULL, -- PRIMARY KEY ,
ACCT_NO CHAR(10) NOT NULL,
ACCT_CD DECIMAL(10) NOT NULL,
PRFT DECIMAL(15,0),
BALANCE_AMT DECIMAL(15,0)
);
CREATE INDEX PPC_MAST_201312_i ON PPC_MAST_201312 (SSN, ACCT_NO);
INSERT INTO PPC_MAST_201312 VALUES ('7802221111111', '22033', 130, 504, 56746);
INSERT INTO PPC_MAST_201312 VALUES ('8307153333444', '54412', 110, 585, 23540);
INSERT INTO PPC_MAST_201312 VALUES ('5605099999222', '65433', 340, 213, 987800);
INSERT INTO PPC_MAST_201312 VALUES ('8012301111333', '58721', 320, 780, 310000);
INSERT INTO PPC_MAST_201312 VALUES ('6711032222111', '23422', 120, 5679, 3);
INSERT INTO PPC_MAST_201312 VALUES ('8910103333222', '89811', 310, 240, 40011);
INSERT INTO PPC_MAST_201312 VALUES ('7802221111111', '78022', 100, 899, 4565000);
INSERT INTO PPC_MAST_201312 VALUES ('6711032222111', '35714', 300, 3780, 2545640);
INSERT INTO PPC_MAST_201312 VALUES ('8910103333222', '68740', 310, 233, 522312);
INSERT INTO PPC_MAST_201312 VALUES ('5605099999222', '96870', 330, 7000, 2158);
INSERT INTO PPC_MAST_201312 VALUES ('7802221111111', '89770', 140, 1000, 566600);
INSERT INTO PPC_MAST_201312 VALUES ('6711032222111', '33270', 130, 5600, 68770);
INSERT INTO PPC_MAST_201312 VALUES ('7802221111111', '87890', 340, 1270, 5500000);
CREATE TABLE CUST_PARTY
(
SSN CHAR(13) PRIMARY KEY NOT NULL,
PARTY_NM CHAR(10) NOT NULL,
CUST_ID CHAR(10) NOT NULL,
TEL_NO CHAR(20) NOT NULL,
MOBILE_NO CHAR(20) NOT NULL
);
INSERT INTO CUST_PARTY VALUES ('5707121111000', 'AR KIM', '5670', '02-555-6678', '010-1111-1111');
INSERT INTO CUST_PARTY VALUES ('6912081111222', 'SH HONG', '2357', '031-4456-9887', '010-2222-2222');
INSERT INTO CUST_PARTY VALUES ('8311221111333', 'MK KANG', '3977', '051-999-8888', '010-3333-3333');
INSERT INTO CUST_PARTY VALUES ('7105252222000', 'JH KIM', '8988', '032-333-1111', '010-4444-4444');
INSERT INTO CUST_PARTY VALUES ('7706302222111', 'JH LEE', '7702', '033-111-3355', '010-5555-5555');
INSERT INTO CUST_PARTY VALUES ('6508112222333', 'JH RYU', '3574', '02-6666-4444', '010-6666-6666');
INSERT INTO CUST_PARTY VALUES ('8204073333111', 'YC JUNG', '5670', '02-2222-1111', '010-7777-7777');
INSERT INTO CUST_PARTY VALUES ('8911293333222', 'JH JUN', '6989', '031-224-2222', '010-8888-8888');
INSERT INTO CUST_PARTY VALUES ('9011034444111', 'SH LEE', '5570', '033-333-3333', '010-9999-9999');
CREATE TABLE RCPT_ACCT
(
SSN CHAR(13) NOT NULL,
ACCT_NO CHAR(10) PRIMARY KEY NOT NULL,
NEW_DT DATE,
CNCL_DT DATE,
RCPT_AMT DECIMAL(15,0)
);
INSERT INTO RCPT_ACCT VALUES ('5707121111000', '578221', '2012-03-26', null, 500000);
INSERT INTO RCPT_ACCT VALUES ('7706302222111', '687322', '2011-12-22', '2013-12-01', 0);
INSERT INTO RCPT_ACCT VALUES ('6508112222333', '658720', '2013-06-08', null, 41324);
INSERT INTO RCPT_ACCT VALUES ('8204073333111', '554520', '2013-09-28', null, 5678740);
INSERT INTO RCPT_ACCT VALUES ('5707121111000', '656421', '2009-11-17', null, 354210);
INSERT INTO RCPT_ACCT VALUES ('7706302222111', '668721', '2010-07-27', null, 547700);
INSERT INTO RCPT_ACCT VALUES ('8204073333111', '223620', '2010-09-11', null, 1000357);
INSERT INTO RCPT_ACCT VALUES ('8204073333111', '275123', '2013-11-26', null, 123000);
CREATE TABLE DLQ_PARTY
(
SSN CHAR(13) NOT NULL,
ACCT_NO CHAR(10) NOT NULL,
DLQ_ST DATE,
DLQ_END DATE,
DLQ_DURATION DECIMAL(15,0),
CURR_DLQ CHAR(10)
);
CREATE INDEX DLQ_PARTY_I ON DLQ_PARTY (SSN, ACCT_NO);
INSERT INTO DLQ_PARTY VALUES ('6912081111222', '32110', '2012-07-30', '2012-08-15', 16, 'N');
INSERT INTO DLQ_PARTY VALUES ('8204073333111', '88930', '2012-09-21', '2012-10-01', 10, 'N');
INSERT INTO DLQ_PARTY VALUES ('8204073333111', '35780', '2013-01-26', '2013-01-29', 3, 'N');
INSERT INTO DLQ_PARTY VALUES ('7706302222111', '78320', '2013-11-01', NULL, 31, 'Y');
INSERT INTO DLQ_PARTY VALUES ('6912081111222', '87120', '2013-10-01', NULL, 62, 'Y');
INSERT INTO DLQ_PARTY VALUES ('8204073333111', '56830', '2013-11-18', '2013-11-28', 10, 'N');
INSERT INTO DLQ_PARTY VALUES ('8311221111333', '78720', '2013-11-14', NULL, 18, 'Y');
INSERT INTO DLQ_PARTY VALUES ('8311221111333', '98730', '2013-11-16', NULL, 16, 'Y');
INSERT INTO DLQ_PARTY VALUES ('6508112222333', '57830', '2012-12-01', '2012-12-02', 1, 'N');
INSERT INTO DLQ_PARTY VALUES ('6508112222333', '78770', '2013-09-19', NULL, 74, 'Y');
CREATE TABLE EMPLOYEE
(
EMP_ID CHAR(5) PRIMARY KEY NOT NULL,
GRADE CHAR(10) NOT NULL,
NM CHAR(10),
TEL_NO CHAR(20)
);
INSERT INTO EMPLOYEE VALUES ('15501', '8', 'YK MO', '5740');
INSERT INTO EMPLOYEE VALUES ('15687', '8', 'SS CHANG', '5547');
INSERT INTO EMPLOYEE VALUES ('16780', '5', 'HY YOO', '2327');
INSERT INTO EMPLOYEE VALUES ('63278', '7', 'JW PARK', '2304');
INSERT INTO EMPLOYEE VALUES ('87871', '7', 'SW HONG', '2367');
INSERT INTO EMPLOYEE VALUES ('23578', '6', 'JI CHOI', '4654');
INSERT INTO EMPLOYEE VALUES ('32480', '6', 'JM CHA', '1270');
INSERT INTO EMPLOYEE VALUES ('23480', '5', 'KE LEE', '5466');
|
