뒤돌아보기

CREATE TABLE CUST_PARTY
(
SSN INT NOT NULL,
PARTY_NM CHAR(20) NOT NULL,
CUST_ID INT NOT NULL,
TEL_NO CHAR(20) NOT NULL,
MOBILE_NO CHAR(20) NOT NULL,
PRIMARY KEY('SSN')
);

INSERT INTO CUST_PARTY
VALUES
(570, 'AR KIM',  5670, '02-555-6678',   '010-1111-1111'),
(691, 'SH HONG', 2357, '031-4456-9887', '010-2222-2222'),
(831, 'MK KANG', 3977, '051-999-8888',  '010-3333-3333'),
(710, 'JH KIM',  8988, '032-333-1111',  '010-4444-4444'),
(770, 'JH LEE',  7702, '033-111-3355',  '010-5555-5555'),
(650, 'JH RYU',  3574, '02-6666-4444',  '010-6666-6666'),
(820, 'YC JUNG', 5670, '02-2222-1111',  '010-7777-7777'),
(891, 'JH JUN',  6989, '031-224-2222',  '010-8888-8888'),
(901, 'SH LEE',  5570, '033-333-3333',  '010-9999-9999');

UPDATE CUST_PARTY
SET CUST_ID = 1111;

UPDATE CUST_PARTY
SET MOBILE_NO = '010-9988-5555'
WHERE SSN=901;

CREATE TABLE CUST_PARTY
(
SSN INT NOT NULL,
PARTY_NM CHAR(20) NOT NULL,
CUST_ID INT NOT NULL,
TEL_NO CHAR(20) NOT NULL,
MOBILE_NO CHAR(20) NOT NULL,
PRIMARY KEY('SSN')
);

INSERT INTO CUST_PARTY
VALUES
(570, 'AR KIM',  5670, '02-555-6678',   '010-1111-1111'),
(691, 'SH HONG', 2357, '031-4456-9887', '010-2222-2222'),
(831, 'MK KANG', 3977, '051-999-8888',  '010-3333-3333'),
(710, 'JH KIM',  8988, '032-333-1111',  '010-4444-4444'),
(770, 'JH LEE',  7702, '033-111-3355',  '010-5555-5555'),
(650, 'JH RYU',  3574, '02-6666-4444',  '010-6666-6666'),
(820, 'YC JUNG', 5670, '02-2222-1111',  '010-7777-7777'),
(891, 'JH JUN',  6989, '031-224-2222',  '010-8888-8888'),
(901, 'SH LEE',  5570, '033-333-3333',  '010-9999-9999');

DELETE FROM CUST_PARTY
WHERE SSN = 891;

CREATE TABLE CUST_PARTY
(
SSN INT NOT NULL,
PARTY_NM CHAR(20) NOT NULL,
CUST_ID INT NOT NULL,
TEL_NO CHAR(20) NOT NULL,
MOBILE_NO CHAR(20) NOT NULL,
PRIMARY KEY('SSN')
);

INSERT INTO CUST_PARTY
VALUES
(570, 'AR KIM',  5670, '02-555-6678',   '010-1111-1111'),
(691, 'SH HONG', 2357, '031-4456-9887', '010-2222-2222'),
(831, 'MK KANG', 3977, '051-999-8888',  '010-3333-3333'),
(710, 'JH KIM',  8988, '032-333-1111',  '010-4444-4444'),
(770, 'JH LEE',  7702, '033-111-3355',  '010-5555-5555'),
(650, 'JH RYU',  3574, '02-6666-4444',  '010-6666-6666'),
(820, 'YC JUNG', 5670, '02-2222-1111',  '010-7777-7777'),
(891, 'JH JUN',  6989, '031-224-2222',  '010-8888-8888'),
(901, 'SH LEE',  5570, '033-333-3333',  '010-9999-9999');

INSERT INTO CUST_PARTY
VALUES (550, 'MJ YOO', 8828, '02-312-1111', '010-1122-1111');

INSERT INTO CUST_PARTY(SSN, PARTY_NM, CUST_ID, TEL_NO)
VALUES (551, 'MJ YOO', 8828, '02-312-1111');

CREATE TABLE CARD_ACCT 
(
SSN INT NOT NULL,
CC_GRADE INT NOT NULL,
ISSUE_DT CHAR(20) NOT NULL,
CLOSE_DT CHAR(20),
VALID_YYMM INT NOT NULL,
CASH_USE_FLG CHAR(5),
CC_VARIETY_CD INT NOT NULL,
PRIMARY KEY('SSN', 'CC_GRADE')
);

INSERT INTO CARD_ACCT 
VALUES
(570, 8, '2012-03-26', NULL,         201503, 'Y', 11111),
(770, 2, '2011-12-22', '2013-12-01', 201512, NULL, 44444),
(650, 8, '2013-06-08', NULL,         201806, 'Y', 11111),
(820, 8, '2013-09-28', NULL,         201809, 'Y', 22222),
(570, 1, '2009-11-17', NULL,         201411, NULL, 33333),
(770, 8, '2010-07-27', NULL,         201407, NULL, 22222),
(820, 2, '2010-09-11', NULL,         201509, 'Y', 44444),
(820, 1, '2013-11-26', NULL,         201811, NULL, 33333);

CREATE TABLE CUST_PARTY
(
SSN INT NOT NULL,
PARTY_NM CHAR(20) NOT NULL,
CUST_ID INT NOT NULL,
TEL_NO CHAR(20) NOT NULL,
MOBILE_NO CHAR(20) NOT NULL,
PRIMARY KEY('SSN')
);

INSERT INTO CUST_PARTY
VALUES
(570, 'AR KIM',  5670, '02-555-6678',   '010-1111-1111'),
(691, 'SH HONG', 2357, '031-4456-9887', '010-2222-2222'),
(831, 'MK KANG', 3977, '051-999-8888',  '010-3333-3333'),
(710, 'JH KIM',  8988, '032-333-1111',  '010-4444-4444'),
(770, 'JH LEE',  7702, '033-111-3355',  '010-5555-5555'),
(650, 'JH RYU',  3574, '02-6666-4444',  '010-6666-6666'),
(820, 'YC JUNG', 5670, '02-2222-1111',  '010-7777-7777'),
(891, 'JH JUN',  6989, '031-224-2222',  '010-8888-8888'),
(901, 'SH LEE',  5570, '033-333-3333',  '010-9999-9999');

CREATE TABLE CUSTOMERS
(
CUST_ID INT NOT NULL,
CUST_NM CHAR(20) NOT NULL,
HOME_TEL CHAR(20) NOT NULL,
PRIMARY KEY('CUST_ID')
);

INSERT INTO CUSTOMERS
VALUES
(5464, 'JH KIM', '02-333-1111'),
(6570, 'LY KIM', '031-111-1133'),
(8780, 'AR KIM', '032-998-5555'),
(5632, 'KK LEE', '02-6677-8888'),
(2445, 'HJ WANG', '055-4444-5666'),
(3210, 'HH AN', '031-888-0111'),
(2596, 'DK SUNG', '02-113-3331');

CREATE TABLE ORDERS (
	ORDER_ID	INTEGER NOT NULL,
	CUST_ID	INTEGER NOT NULL,
	EMP_ID	INTEGER NOT NULL,
	ORDER_DT	TEXT NOT NULL,
	PRIMARY KEY("ORDER_ID")
);

INSERT INTO ORDERS
VALUES
(10000, 2596, 23480, '2013-12-06'),
(10001, 5464, 16780, '2013-11-01'),
(10002, 3210, 63278, '2014-03-02'),
(10003, 2445, 15501, '2014-02-23'),
(10004, 5632, 15687, '2013-11-13');

CREATE TABLE EMPLOYEE
(
EMP_ID INT NOT NULL,
GRADE INT NOT NULL,
NM CHAR(20) NOT NULL,
TELNO INT NOT NULL,
PRIMARY KEY('EMP_ID'));

INSERT INTO EMPLOYEE
VALUES
(15501, 8, 'YK MO', 5740),
(15687, 8, 'SS CHANG', 5547),
(16780, 5, 'HY YOO', 2327),
(63278, 7, 'JW PARK', 2304),
(87871, 7, 'SW HONG', 2367),
(23578, 6, 'JI CHOI', 4654),
(32480, 6, 'JM CHA', 1270),
(23480, 5, 'KE LEE', 5466);

SELECT CUSTOMERS.CUST_NM AS NM FROM CUSTOMERS
UNION
SELECT EMPLOYEE.NM AS NM FROM EMPLOYEE
ORDER BY 1;

CREATE TABLE ADDR1 (
	"CUST_ID"	INT NOT NULL,
	"HOME_ADDR1"	CHAR(50) NOT NULL,
	"HOME_ADDR2"	CHAR(50) NOT NULL,
	PRIMARY KEY('CUST_ID')
);

INSERT INTO ADDR1
VALUES
(5465, '서울시 강남구 역삼동', '111-11'),
(2354, '서울시 종로구 공평동', '222-22'),
(5410, '서울시 중구 서소문동', '333-33'),
(6511, '서울시 송파구 문정동', '444-44');


CREATE TABLE MOBILE1 (
	"CUST_ID"	INT NOT NULL,
	"MOBILE_NO"	CHAR(50) NOT NULL,
	PRIMARY KEY('MOBILE_NO')
);

INSERT INTO MOBILE1
VALUES
(5465, '010-1111-1111'),
(2354, '010-2222-2222'),
(5410, '010-3333-3333'),
(5410, '010-7777-7777'),
(7979, '010-7979-7979');

CREATE TABLE ADDR1 (
	"CUST_ID"	INT NOT NULL,
	"HOME_ADDR1"	CHAR(50) NOT NULL,
	"HOME_ADDR2"	CHAR(50) NOT NULL,
	PRIMARY KEY('CUST_ID')
);

INSERT INTO ADDR1
VALUES
(5465, '서울시 강남구 역삼동', '111-11'),
(2354, '서울시 종로구 공평동', '222-22'),
(5410, '서울시 중구 서소문동', '333-33'),
(6511, '서울시 송파구 문정동', '444-44');


CREATE TABLE MOBILE1 (
	"CUST_ID"	INT NOT NULL,
	"MOBILE_NO"	CHAR(50) NOT NULL,
	PRIMARY KEY('MOBILE_NO')
);

INSERT INTO MOBILE1
VALUES
(5465, '010-1111-1111'),
(2354, '010-2222-2222'),
(5410, '010-3333-3333'),
(5410, '010-7777-7777'),
(7979, '010-7979-7979');

CREATE TABLE ADDR1 (
	"CUST_ID"	INT NOT NULL,
	"HOME_ADDR1"	CHAR(50) NOT NULL,
	"HOME_ADDR2"	CHAR(50) NOT NULL,
	PRIMARY KEY('CUST_ID')
);

INSERT INTO ADDR1
VALUES
(5465, '서울시 강남구 역삼동', '111-11'),
(2354, '서울시 종로구 공평동', '222-22'),
(5410, '서울시 중구 서소문동', '333-33'),
(6511, '서울시 송파구 문정동', '444-44');


CREATE TABLE MOBILE1 (
	"CUST_ID"	INT NOT NULL,
	"MOBILE_NO"	CHAR(50) NOT NULL,
	PRIMARY KEY('MOBILE_NO')
);

INSERT INTO MOBILE1
VALUES
(5465, '010-1111-1111'),
(2354, '010-2222-2222'),
(5410, '010-3333-3333'),
(5410, '010-7777-7777'),
(7979, '010-7979-7979');

CREATE TABLE ADDR
(
CUST_ID INT NOT NULL,
HOME_ADDR1 CHAR(50) NOT NULL,
HOME_ADDR2 CHAR(50) NOT NULL,
PRIMARY KEY('CUST_ID')
);

INSERT INTO ADDR
VALUES
(5465, '서울시 강남구 역삼동', '111-11'),
(2354, '서울시 종로구 공평', '222-22'),
(5410, '서울시 중구 서소문동', '333-33');

CREATE TABLE MOBILE
(
CUST_ID INT NOT NULL,
MOBILE_NO CHAR(50) NOT NULL,
PRIMARY KEY('CUST_ID')
);

INSERT INTO MOBILE
VALUES
(5465, '010-1111-1111'),
(2354, '010-2222-2222');

CREATE TABLE CUSTOMERS
(
CUST_ID INT NOT NULL,
CUST_NM CHAR(20) NOT NULL,
HOME_TEL CHAR(20) NOT NULL,
PRIMARY KEY('CUST_ID')
);

INSERT INTO CUSTOMERS
VALUES
(5464, 'JH KIM', '02-333-1111'),
(6570, 'LY KIM', '031-111-1133'),
(8780, 'AR KIM', '032-998-5555'),
(5632, 'KK LEE', '02-6677-8888'),
(2445, 'HJ WANG', '055-4444-5666'),
(3210, 'HH AN', '031-888-0111'),
(2596, 'DK SUNG', '02-113-3331');

CREATE TABLE ORDERS (
	ORDER_ID	INTEGER NOT NULL,
	CUST_ID	INTEGER NOT NULL,
	EMP_ID	INTEGER NOT NULL,
	ORDER_DT	TEXT NOT NULL,
	PRIMARY KEY("ORDER_ID")
);

INSERT INTO ORDERS
VALUES
(10000, 2596, 23480, '2013-12-06'),
(10001, 5464, 16780, '2013-11-01'),
(10002, 3210, 63278, '2014-03-02'),
(10003, 2445, 15501, '2014-02-23'),
(10004, 5632, 15687, '2013-11-13');

CREATE TABLE EMPLOYEE
(
EMP_ID INT NOT NULL,
GRADE INT NOT NULL,
NM CHAR(20) NOT NULL,
TELNO INT NOT NULL,
PRIMARY KEY('EMP_ID'));

INSERT INTO EMPLOYEE
VALUES
(15501, 8, 'YK MO', 5740),
(15687, 8, 'SS CHANG', 5547),
(16780, 5, 'HY YOO', 2327),
(63278, 7, 'JW PARK', 2304),
(87871, 7, 'SW HONG', 2367),
(23578, 6, 'JI CHOI', 4654),
(32480, 6, 'JM CHA', 1270),
(23480, 5, 'KE LEE', 5466);

SELECT CUSTOMERS.CUST_ID, CUST_NM, ORDER_ID 
FROM CUSTOMERS INNER JOIN ORDERS
ON CUSTOMERS.CUST_ID = ORDERS.CUST_ID;

SELECT TMP1.CUST_ID, TMP1.CUST_NM, TMP2.ORDER_ID, TMP3.EMP_ID, TMP3.NM
FROM CUSTOMERS TMP1
	 INNER JOIN ORDERS TMP2 ON TMP1.CUST_ID = TMP2.CUST_ID
	 INNER JOIN EMPLOYEE TMP3 ON TMP2.EMP_ID = TMP3.EMP_ID;

- 각 테이블에 TMP1, TMP2, TMP3 라는 별칭을 붙여서 쿼리문 작성

CREATE TABLE PROD_SALES
(
	CUST_NM CHAR(20) NOT NULL,
	PRD_ID INT NOT NULL,
	SALES_AMT INT NOT NULL,
	PRIMARY KEY('PRD_ID')
);

INSERT INTO PROD_SALES
VALUES
('LEE', 546,  3000),
('KIM', 324,  4780),
('KANG', 564, 87900),
('KWON', 556, 45478),
('KIM', 254,  3000),
('YOO', 567,  78900),
('PARK', 877, 89787),
('LEE', 890,  10000),
('KIM', 787,  2341),
('PARK', 566, 50000);

A1. 

1단계. 먼저 COUNT 함수와 GROUP BY 절을 이용해 고객별 구매 횟수를 추출

SELECT CUST_NM, COUNT(*)
FROM PROD_SALES
GROUP BY CUST_NM;

2단계. HAVING 절을 이용해 구매 횟수가 2번 이상인 대상자를 추출

SELECT CUST_NM, COUNT(*)
FROM PROD_SALES
GROUP BY CUST_NM
HAVING COUNT(*)>=2;

A1. 

1단계. 먼저 SUM 함수와 GROUP BY 절을 이용해 고객별 구매금액을 추출

SELECT CUST_NM, SUM(SALES_AMT)
FROM PROD_SALES
GROUP BY CUST_NM;

2단계. HAVING 절을 이용해 구매금액의 합이 7만원 이상인 대상자를 추출

SELECT CUST_NM, SUM(SALES_AMT)
FROM PROD_SALES
GROUP BY CUST_NM
HAVING SUM(SALES_AMT)>=70000;

A1. 

1단계. 먼저 AVG 함수와 GROUP BY 절을 이용해 고객별 구매금액을 추출

SELECT CUST_NM, AVG(SALES_AMT)
FROM PROD_SALES
GROUP BY CUST_NM;

2단계. HAVING 절을 이용해 구매금액의 평균이 7만원 이상인 대상자를 추출

SELECT CUST_NM, AVG(SALES_AMT)
FROM PROD_SALES
GROUP BY CUST_NM
HAVING AVG(SALES_AMT)>=70000;