DML(Data Manipulate Language)과 트랜잭션 작업 (*캠페인 결과 분석)

DML(Data Manipulate Language)

테이블 내에서 검색, 등록, 수정, 삭제

데이터 조작어: *SELECT(조회만 하는거라 커밋과는 관계X), UPDATE, DELETE, INSERT

 

트랜잭션

DML 작업을 완수하기 위한 작업 단위

확정(Commit)이나 되돌리기(Rollback)로 마무리 된다

commit;
rollback;

commit; 또는 f1 저장이 필요

rollback; 또는 f2 되돌리기 필요

 

-- INSERT
INSERT INTO DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES
(1, '001 TEST', 200, 1700);

INSERT INTO - VALUE() : 데이터 타입, 자릿 수, 크기가 일치해야 INSERT 된다

 

-- UPDATE
UPDATE departments
SET       department_name = '002 TEST'
WHERE  department_id = 1;

WHERE - SET

 

-- DELETE
DELETE departments
WHERE department_id = 1;

WHERE 조건식 없으면 테이블 데이터 전체 삭제

 

 

캠페인 실행하기

-- campaign 준비 테이블 삽입 (없을시 아래 DDL문으로 생성해야함)
@c|sql_practice|6_1.create_campaign_table.sql

실습 스크립트 실행

 

DDL문 생성하기

-- campaign_master 테이블 생성
CREATE TABLE campaign_master ( 
	campaign_no	    VARCHAR2(30),
	start_date	    VARCHAR2(8),
	end_date	    VARCHAR2(8),
	target_cnt	    NUMBER(3,0),
	title		    VARCHAR2(200),
        send_ch             VARCHAR2(8),
	send_yn		    VARCHAR2(1)
);

ALTER TABLE campaign_master ADD CONSTRAINT pk_campaign_master PRIMARY KEY (campaign_no);

campaign_no : 캠페인 번호

start_date : 시작 날짜

end_date : 종료 날짜

target_cnt : 타깃 수

title : 제목

send_ch : 실행 채널

send_yn : 실행 여부

 

CONSTRAINT - PRIMARY KEY () : 제약 조건. PRIMARY KEY → Unique 값 O, Null 값 X

 

-- campaign_result 테이블 생성
CREATE TABLE campaign_result ( 
	campaign_no	    VARCHAR2(30),
        reserv_no	    VARCHAR2(30),
	reserv_date	    VARCHAR2(8),
	reserv_time	    VARCHAR2(4),
	customer_id	    VARCHAR2(10) CONSTRAINT nn_campaign_customer_id NOT NULL enable,
	branch		    VARCHAR2(20),
	visitor_cnt	    NUMBER(3,0),
	order_no	    VARCHAR2(30),
	item_id		    VARCHAR2(10),
	quantity	    NUMBER(3,0),
        sales               NUMBER(10,0), 
	coupon_no	    VARCHAR2(20)	
);

campaign_no : 캠페인 번호

reser_no : 예약 번호

reser_date : 예약 날짜

reser_time : 예약 시간

customer : 고객 번호

branch : 부서

visitor_cnt : 방문 고객 수

order_no : 주문 번호

item_id : 아이템

id quantity : 수량

sales : 금액

coupon_no : 쿠폰 번호

 

NUMBER (정수자리수,소수점자리수)

 

 

 

데이터 삽입

Insert into campaign_master (CAMPAIGN_NO,START_DATE,END_DATE,TARGET_CNT,TITLE,SEND_CH,SEND_YN) values ('CPN001','20170801','20170930',200,'갈릭 스테이크 상품 탄생 기념 모든 스테이크 주문시 음료 인당 1잔 추가제공','TOK','Y');
Insert into campaign_master (CAMPAIGN_NO,START_DATE,END_DATE,TARGET_CNT,TITLE,SEND_CH,SEND_YN) values ('CPN002','20170801','20170930',200,'갈릭 스테이크 상품 탄생 기념 모든 스테이크 주문시 음료 인당 1잔 추가제공','SMS','Y');
Insert into campaign_master (CAMPAIGN_NO,START_DATE,END_DATE,TARGET_CNT,TITLE,SEND_CH,SEND_YN) values ('CPN003','20171101','20171231',120,'연말 이벤트 스테이크 주문시 와인 추가 주문하면 15% 할인','TOK','Y');
Insert into campaign_master (CAMPAIGN_NO,START_DATE,END_DATE,TARGET_CNT,TITLE,SEND_CH,SEND_YN) values ('CPN004','20171001','20171031',400,'TOK플러스 친구맺으면 와인 한잔 공짜쿠폰','TOK','N');

commit;

캠페인 내용 삽입

 

-- Campaign Result 데이터 Insert
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN001','2018080203','20180802','1800','W1334366','중구',2,'180802137834910000','M0005',2,70000,'CPN001001');
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN001','2018080501','20180805','1800','W1341061','동작',1,'180805517276510000','M0005',1,35000,'CPN001002');
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN001','2018082601','20180826','1800','W1328993','강북',4,'180826137192910000','M0005',4,140000,'CPN001003');
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN001','2018082801','20180828','1200','W1331124','서초',2,'180828137203910000','M0005',2,70000,'CPN001004');
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN001','2018091201','20180912','1800','W1342347','동대문',2,'180912372833100000','M0005',2,70000,'CPN001005');
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN001','2018091501','20180915','1800','W1338910','강남',2,'180915107838310000','M0005',2,70000,'CPN001006');
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN001','2018091506','20180915','1800','W1327803','서초',2,'180915137187310000','M0005',2,70000,'CPN001007');
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN002','2018081602','20180816','1800','W1338910','강남',7,'180816578383100000','M0005',4,140000,'CPN002001');
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN002','2018090302','20180903','1200','W1341063','은평',2,'180903578398100000','M0005',2,70000,'CPN002002');
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN003','2018112101','20181121','1200','W1356560','노원',16,'181121137367610000','M0005',8,280000,'CPN003001');
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN003','2018112101','20181121','1200','W1356560','노원',16,'181121137367610000','M0009',16,128000,'CPN003002');
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN003','2018112102','20181121','1800','W1356173','동작',8,'181121537853210000','M0005',8,280000,'CPN003003');
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN003','2018112102','20181121','1800','W1356173','동작',8,'181121537853210000','M0009',8,64000,'CPN003004');
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN003','2018120202','20181202','1800','W363739','종로',22,'181202578574100000','M0005',12,420000,'CPN003005');
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN003','2018120202','20181202','1800','W363739','종로',22,'181202578574100000','M0001',10,240000,'CPN003006');
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN003','2018120202','20181202','1800','W363739','종로',22,'181202578574100000','M0009',22,176000,'CPN003007');
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN003','2018122702','20181227','1800','W1340242','영등포',2,'181227137839010000','M0005',2,70000,'CPN003008');
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN003','2018122702','20181227','1800','W1340242','영등포',2,'181227137839010000','M0009',2,16000,'CPN003009');
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN003','2018122703','20181227','1800','W329920','동대문',2,'181227137853710000','M0005',2,70000,'CPN003010');
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN003','2018122703','20181227','1800','W329920','동대문',2,'181227137853710000','M0009',2,16000,'CPN003011');
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN003','2018122801','20181228','1200','W1355375','강남',5,'181228107357710000','M0005',5,175000,'CPN003012');
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN003','2018122801','20181228','1200','W1355375','강남',5,'181228107357710000','M0009',5,40000,'CPN003013');
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN003','2018122804','20181228','1200','W375192','광진',2,'181228107864610000','M0005',2,70000,'CPN003014');
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN003','2018122804','20181228','1200','W375192','광진',2,'181228107864610000','M0009',2,16000,'CPN003015');
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN003','2018122810','20181228','1200','W1369282','강동',2,'181228137860010000','M0009',2,16000,'CPN003016');
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN003','2018122810','20181228','1200','W1369282','강동',2,'181228137860010000','M0005',2,70000,'CPN003017');
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN003','2018123006','20181230','1200','W350187','양천',1,'181230187379710000','M0005',1,35000,'CPN003018');
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN003','2018123006','20181230','1200','W350187','양천',1,'181230187379710000','M0009',1,8000,'CPN003019');
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN003','2018123107','20181231','1800','W375192','강남',4,'181231607864610000','M0005',4,140000,'CPN003020');
Insert into campaign_result (CAMPAIGN_NO,RESERV_NO,RESERV_DATE,RESERV_TIME,CUSTOMER_ID,BRANCH,VISITOR_CNT,ORDER_NO,ITEM_ID,QUANTITY,SALES,COUPON_NO) values ('CPN003','2018123107','20181231','1800','W375192','강남',4,'181231607864610000','M0009',4,32000,'CPN003021');

commit;

캠페인 반응 결과 삽입

 

 

캠페인 성과분석

-- 마케팅 캠페인 분석
SELECT A.campaign_no,  A.target_cnt, COUNT(DISTINCT B.reserv_no) AS 예약적중
FROM   campaign_master A, campaign_result B
WHERE A.campaign_no = B.campaign_no
GROUP BY A.campaign_no, A.target_cnt
ORDER BY A.campaign_no;

COUNT(DISTINCT B.reserv_no) AS 예약 적중 : 중복 없이 예약 번호 카운트

 

 

 

 

 

(출처: 핵심만 쉽게, 모두의 SQL 데이터 분석)