DB PROJECT

Himoonhee 2020. 11. 13. 10:44

2020. 11. 13. 10:44

728x90

****DB 프로젝트

주제선정
벤치마킹
요구분석(유스케이스)-액터별 &SQL쿼리문
논리적설계(ER다이어그램&정규화)
물리적설계
DB구축(CREATE, 제약조건생성)
쿼리문 생성
결과값이 요구사항과 일치하는가

레스토랑 경영관리(ERP) 어플

-"직원"

직원번호, 직원이름, 직원직급, 전화번호, 성별, 파트, 주소, 연봉, 입사날짜
홀직원은 여러명의 고객을 응대해야 한다.
주방직원은 유통기한을 확인하고 재고를 확인해야 한다.
관리자는 직원을 관리한다.
관리자는 예약을 관리한다.
관리자는 제공업체를 관리한다.
홀직원이 계산을 해준다.
홀직원이 주차권을 발급한다.
직원은 출근 날짜가 정해져있다.
주방직원은 여러 요리를 담당할 수 있다.
직원직급에 따라 손님을 등급별로 담당한다.

-고객(번호표)

고객 번호, 이름, 전화번호, 성별, 생년월일, 등급, 주차유무, 컴플레인 횟수, 대기번호
고객은 다양한 음식을 주문할 수 있다.
등급별 혜택을 받을 수 있다.
고객은 날짜를 정해 예약할 수 있다.
고객 테이블 당 한명의 담당 직원이 배정되어야 한다.
고객은 컴플레인을 걸 수 있다.

-음식

음식 번호, 이름, 가격, 수량, 유통기한
고객이 다양한 음식을 주문할 수 있다.
요일별로 할인되는 음식이 있다.
음식은 수량이 떨어지면 판매할 수 없다.
유통기한 지난 음식은 판매할 수 없다.

-제공업체

제공업체 번호, 이름, 전화번호,계약날짜, 수량
제공업체는 여러 음식을 제공할 수 있다.

-주차

주차번호, 차량번호, 차종, 주차구역, 차주번호
고객한팀당 한대만 주차할 수 있다.

Q. 이달의 가장 컴플레인을 많이 받은 사원은?

Q. 가장 바쁜 시간대(방문시간)

Q. 회전율

Q. 월별로 가장 많이 팔린 메뉴

Q. 8월에 컴플레인을 받은 직원 이름과 내용

Q. 생일인 고객 챙겨주기

Q. (트리거사용)제공수량 음식수량 비교해서 발주넣기

Q. 업체계약일 가장 오래된 것 3개 ->재계약

Q. 컴플레인이 가장 많은 고객의 정보

Q. 주차구역으로 차주전화번호 찾기

Q. 마일리지 트리거 사용해서 적립 및 사용

직원번호, 직원이름, 직원직급, 직원전화번호, 직원성별, 파트, 직원주소, 직원연봉, 직원입사날짜, 고객 번호, 고객이름, 고객전화번호, 고객성별, 고객생년월일, 고객등급, 주차유무, 컴플레인 횟수, 대기번호, 음식 번호, 음식이름, 음식가격, 음식수량, 음식유통기한, 제공업체 번호, 제공업체이름, 업체전화번호, 업체계약날짜, 제공수량, 주차번호, 차량번호, 차종, 주차구역, 차주번호

직원번호, 직원이름, 직원직급, 직원전화번호, 직원성별, 파트, 직원주소, 직원연봉, 직원입사날짜,

고객 번호, 고객이름, 고객전화번호, 고객성별, 고객생년월일, 고객등급, 주차유무, 컴플레인 횟수, 대기번호,

음식 번호, 음식이름, 음식가격, 음식수량, 음식유통기한,

제공업체 번호, 제공업체이름, 업체전화번호, 업체계약날짜, 제공수량,

주차번호, 차량번호, 차종, 주차구역, 차주번호

주문(날짜, 시간, 주문번호, 고객번호, 음식번호, 수량, 고객등급, 직원번호)

고객(고객 번호, 고객이름, 고객전화번호, 고객성별, 고객생년월일, 고객등급, 주차유무, 컴플레인 횟수, 대기번호, 마일리지,컴플레인횟수,예약번호)

직원(직원번호, 직원이름, 직원직급, 직원전화번호, 직원성별, 파트, 직원주소, 직원연봉, 직원입사날짜)

음식 (음식 번호, 음식이름, 음식가격, 음식수량, 음식유통기한)

제공업체 (제공업체 번호, 제공업체이름, 업체전화번호, 업체계약날짜, 제공수량,)

주차 (주차번호, 차량번호, 차종, 주차구역, 차주번호)

결제(결제수단,마일리지 사용여부)

**1차 정규화

고객(고객 번호, 고객이름, 고객전화번호, 고객성별, 고객생년월일, 고객등급, 주차유무, 컴플레인 횟수, 대기번호, 마일리지,컴플레인횟수,예약번호, 결제수단, 마일리지 사용여부)

직원(직원번호, 직원이름, 직원직급, 직원파트,직원전화번호, 직원성별, 직원주소, 직원연봉, 직원입사날짜, 컴플레인횟수 )

음식 (음식 번호, 음식이름, 음식가격, 음식수량, 음식유통기한)

제공업체 (제공업체 번호, 제공업체이름, 업체전화번호, 업체계약날짜, 제공수량,)

주문(날짜, 주문번호, 고객번호, 음식번호, 수량, 고객등급, 직원번호)

주차 (주차번호, 차종, 주차구역, 차주번호)

**2차 정규화

고객(고객번호, 고객이름, 고객전화번호, 고객성별, 고객생년월일, 고객등급,마일리지)

예약(예약번호, 고객번호,직원번호)

고객관리(고객번호, 컴플레인번호, 고객등급,직원번호)

컴플레인(컴플레인번호, 내용)

직원컴플레인관리(직원번호, 컴플레인번호)

직원(직원번호, 직원이름, 직원전화번호, 직원성별, 직원주소,직원입사날짜)

직원관리(직원번호, 직원직급, 직원파트, 직원연봉)

음식(음식번호, 음식이름, 음식가격, 음식수량, 음식제조일자, 음식유통기한),

제공업체(제공업체번호, 제공업체이름, 업체전화번호, 업체계약날짜, 제공수량,직원번호)

주문(날짜, 주문번호, 음식번호 ,직원번호,고객번호)

결제(고객번호,주문번호, 음식번호, 음식수량,마일리지),

주차(주차번호, 차종, 주차구역, 고객번호) ,

**3차 정규화

고객(고객번호, 고객이름, 고객전화번호, 고객성별, 고객생년월일, 고객등급)

예약(예약번호, 직원번호, 고객번호)

고객관리(고객번호, 직원번호,컴플레인번호, 고객등급)

컴플레인(컴플레인번호, 내용)

마일리지관리(직원번호, 고객번호, 마일리지)

직원(직원번호, 직원이름, 직원전화번호, 직원성별, 직원주소, 직원입사날짜)

직원관리(직원번호, 직원직급, 직원연봉, 제공업체번호)

직원컴플레인관리(직원번호, 컴플레인번호)

음식(음식번호, 음식이름, 음식가격, 음식수량)

음식관리(음식번호, 음식제조일자, 음식유통기한)

제공업체(제공업체번호, 제공업체명, 업체전화번호, 업체계약일, 제공수량, 직원번호)

주문(주문날짜,주문번호, 고객번호, 음식번호, 주문수량, 직원번호)

결제(고객번호, 주문번호, 음식번호, 결제수단, 마일리지 사용여부)

주차(주차번호, 차량번호, 차종, 주차구역, 고객번호)

INSERT INTO customer VALUES('C101','김민재','010-5475-8765','남','1996-11-15','우수');

INSERT INTO customer VALUES('C102','이청용','010-4572-1245','남','1988-07-02','일반');

INSERT INTO customer VALUES('C103','조현우','010-7865-5674','남','1988-07-02','신규');

INSERT INTO customer VALUES('C104','지소연','010-5483-1678','여','1991-02-21','신규');

INSERT INTO customer VALUES('C105','이민아','010-4785-9654','여','1991-11-08','우수');

INSERT INTO customer VALUES('C106','강수진','010-5785-1485','여','1997-03-01','일반');

INSERT INTO customer VALUES('C107','박은선','010-7821-3547','여','1986-12-25','일반');

INSERT INTO customer VALUES('C108','여민지','010-9754-4872','여','1993-07-21','신규');

INSERT INTO customer VALUES('C109','구자철','010-8256-7319','남','1989-02-27','우수');

INSERT INTO customer VALUES('C110','박지성','010-7359-4312','남','1981-02-25','우수');

INSERT INTO parking VALUES('p100', 'C101', 1000, '소형','A-1');

INSERT INTO parking VALUES('p101', 'C102', 1111,'중형', 'A-2');

INSERT INTO parking VALUES('p102', 'C103', 2222, '중형','A-3');

INSERT INTO parking VALUES('p103', 'C104', 3333,'대형', 'B-1');

INSERT INTO parking VALUES('p104', 'C105', 4444, '소형','B-2');

INSERT INTO parking VALUES('p105', 'C106', 5555,'대형', 'B-3');

INSERT INTO parking VALUES('p106', 'C107', 6666, '중형','C-1');

INSERT INTO parking VALUES('p107', 'C108', 7777,'중형', 'C-2');

INSERT INTO parking VALUES('p108', 'C109', 8888, '소형','C-3');

INSERT INTO parking VALUES('p109', 'C110', 9999,'대형', 'C-4');

INSERT INTO food VALUES('F100',15, 65000, '채끝등심구이');

INSERT INTO food VALUES('F101',20, 20000, '육회비빔밥');

INSERT INTO food VALUES('F102',10,18000,'연어덮밥');

INSERT INTO food VALUES('F103',16,19000, '장어양념구이');

INSERT INTO food VALUES('F104',20, 25000, '전복찜');

INSERT INTO food VALUES('F105',16,24000, '떡갈비반상');

INSERT INTO food VALUES('F106',20,110000, '랍스터');

INSERT INTO food VALUES('F107', 30,18000, '에그인헬');

INSERT INTO food VALUES('F108',35,18900, '크림파스타');

INSERT INTO food VALUES('F200',6,95000, '레드와인');

INSERT INTO food VALUES('F201',6, 89000,'화이트와인');

INSERT INTO food VALUES('F202',20, 5000, '진로이즈백');

INSERT INTO food VALUES('F203', 20,5000, '참이슬');

INSERT INTO food VALUES('F204',20,5000, '테라');

INSERT INTO food VALUES('F205',50,5000, '아메리카노');

INSERT INTO food VALUES('F300',8,5500, '치즈케이크');

INSERT INTO food VALUES('F301',8,6000, '생딸기타르트');

INSERT INTO food VALUES('F302',20,5000, '아포카토');

INSERT INTO food VALUES('F303',20,8000, '무화과빵');

ALTER TABLE emp MODIFY (emp_gender VARCHAR2(3));

INSERT INTO emp VALUES('E100','정영훈','010-1111-2222','남','서울 강남구',TO_DATE('2010-01-18','YYYY-MM-DD'));

INSERT INTO emp VALUES('E101','위진학','010-2222-1234','남','서울 동작구',TO_DATE('2012-03-24','YYYY-MM-DD'));

INSERT INTO emp VALUES('E102','황은지','010-2323-1231','여','서울 노원구',TO_DATE('2013-05-28','YYYY-MM-DD'));

INSERT INTO emp VALUES('E103','오민지','010-2023-2030','여','인천 부평구',TO_DATE('2014-07-11','YYYY-MM-DD'));

INSERT INTO emp VALUES('E104','서민용','010-2929-1010','남','인천 계양구',TO_DATE('2014-12-30','YYYY-MM-DD'));

INSERT INTO emp VALUES('E105','서유리','010-5050-1010','여','경기 수원시',TO_DATE('2015-10-22','YYYY-MM-DD'));

INSERT INTO emp VALUES('E106','유민호','010-2023-3929','남','경기 파주시',TO_DATE('2016-08-30','YYYY-MM-DD'));

INSERT INTO emp VALUES('E107','강희주','010-2222-3333','여','서울 관악구',TO_DATE('2018-07-16','YYYY-MM-DD'));

INSERT INTO emp VALUES('E108','김기범','010-3131-2323','남','서울 강서구',TO_DATE('2018-12-22','YYYY-MM-DD'));

INSERT INTO emp VALUES('E109','김가람','010-3232-1231','여','인천 서구',TO_DATE('2019-09-15','YYYY-MM-DD'));

INSERT INTO emp_complain VALUES('E109', 'VOC02');

INSERT INTO emp_complain VALUES('E100', 'VOC03');

INSERT INTO emp_complain VALUES('E100', 'VOC01');

INSERT INTO emp_complain VALUES('E102', 'VOC10');

INSERT INTO emp_complain VALUES('E101', 'VOC07');

DELETE FROM complain WHERE COMP_NO = 'E101';

INSERT INTO complain VALUES ('VOC01','불친절');

INSERT INTO complain VALUES ('VOC02','맛 없음');

INSERT INTO complain VALUES ('VOC03','양이 적음');

INSERT INTO complain VALUES ('VOC04','비위생적임');

INSERT INTO complain VALUES ('VOC05','가격이 비쌈');

INSERT INTO complain VALUES ('VOC06','늦게 나옴');

INSERT INTO complain VALUES ('VOC07','매장이 시끄러움');

INSERT INTO complain VALUES ('VOC08','음식이 짬');

INSERT INTO complain VALUES ('VOC09','음식이 싱거움');

INSERT INTO complain VALUES ('VOC10','음식이 식음');

INSERT INTO customer_management VALUES('E100','C101','우수','VOC03');

INSERT INTO customer_management VALUES('E109','C102','일반','VOC02');

INSERT INTO customer_management VALUES('E101','C105','우수','VOC07');

INSERT INTO customer_management VALUES('E102','C109','우수','VOC10');

INSERT INTO customer_management VALUES('E100','C110','우수','VOC01');

INSERT INTO food_management VALUES('F100',TO_DATE('2020-08-02','YYYY-MM-DD'),TO_DATE('2020-09-02','YYYY-MM-DD'));

INSERT INTO food_management VALUES('F101',TO_DATE('2020-08-03','YYYY-MM-DD'),TO_DATE('2020-09-03','YYYY-MM-DD'));

INSERT INTO food_management VALUES('F102',TO_DATE('2020-08-04','YYYY-MM-DD'),TO_DATE('2020-09-04','YYYY-MM-DD'));

INSERT INTO food_management VALUES('F103',TO_DATE('2020-08-05','YYYY-MM-DD'),TO_DATE('2020-09-05','YYYY-MM-DD'));

INSERT INTO food_management VALUES('F104',TO_DATE('2020-08-06','YYYY-MM-DD'),TO_DATE('2020-09-06','YYYY-MM-DD'));

INSERT INTO food_management VALUES('F105',TO_DATE('2020-08-07','YYYY-MM-DD'),TO_DATE('2020-09-07','YYYY-MM-DD'));

INSERT INTO food_management VALUES('F106',TO_DATE('2020-08-08','YYYY-MM-DD'),TO_DATE('2020-09-08','YYYY-MM-DD'));

INSERT INTO food_management VALUES('F107',TO_DATE('2020-08-09','YYYY-MM-DD'),TO_DATE('2020-09-09','YYYY-MM-DD'));

INSERT INTO food_management VALUES('F108',TO_DATE('2020-08-10','YYYY-MM-DD'),TO_DATE('2020-09-10','YYYY-MM-DD'));

INSERT INTO food_management VALUES('F200',TO_DATE('2020-06-30','YYYY-MM-DD'),TO_DATE('2020-08-31','YYYY-MM-DD'));

INSERT INTO food_management VALUES('F201',TO_DATE('2020-09-11','YYYY-MM-DD'),TO_DATE('2022-12-05','YYYY-MM-DD'));

INSERT INTO food_management VALUES('F202',TO_DATE('2020-07-31','YYYY-MM-DD'),TO_DATE('2021-07-31','YYYY-MM-DD'));

INSERT INTO food_management VALUES('F203',TO_DATE('2020-08-01','YYYY-MM-DD'),TO_DATE('2021-08-01','YYYY-MM-DD'));

INSERT INTO food_management VALUES('F204',TO_DATE('2020-08-02','YYYY-MM-DD'),TO_DATE('2021-08-02','YYYY-MM-DD'));

INSERT INTO food_management VALUES('F205',TO_DATE('2020-08-03','YYYY-MM-DD'),TO_DATE('2020-09-03','YYYY-MM-DD'));

INSERT INTO food_management VALUES('F300',TO_DATE('2020-08-04','YYYY-MM-DD'),TO_DATE('2020-09-04','YYYY-MM-DD'));

INSERT INTO food_management VALUES('F301',TO_DATE('2020-08-05','YYYY-MM-DD'),TO_DATE('2020-09-05','YYYY-MM-DD'));

INSERT INTO food_management VALUES('F302',TO_DATE('2020-08-06','YYYY-MM-DD'),TO_DATE('2020-09-06','YYYY-MM-DD'));

INSERT INTO food_management VALUES('F303',TO_DATE('2020-08-07','YYYY-MM-DD'),TO_DATE('2020-09-07','YYYY-MM-DD'));

INSERT INTO emp_management VALUES('E100',6300,'총괄매니저','홀');

INSERT INTO emp_management VALUES('E101',5700,'매니저','주방');

INSERT INTO emp_management VALUES('E102',5200,'매니저','홀');

INSERT INTO emp_management VALUES('E103',4800,'직원','홀');

INSERT INTO emp_management VALUES('E104',4500,'직원','주방');

INSERT INTO emp_management VALUES('E105',4500,'직원','주방');

INSERT INTO emp_management VALUES('E106',4250,'직원','홀');

INSERT INTO emp_management VALUES('E107',3800,'직원','주방');

INSERT INTO emp_management VALUES('E108',3700,'직원','주방');

INSERT INTO emp_management VALUES('E109',3550,'직원','홀');

INSERT INTO provider VALUES('S100','E100','2010-07-25',20,'02-111-1111','동원');

INSERT INTO provider VALUES('S200','E100','2015-03-05',25,'02-222-2222','아워홈');

INSERT INTO provider VALUES('S300','E101','2019-11-25',25,'02-333-3333','웰스토리');

INSERT INTO provider VALUES('S400','E101','2018-07-11',20,'02-444-4444','프레시웨이');

INSERT INTO provider VALUES('S500','E102','2020-01-22',40,'02-555-5555','그린푸드');

INSERT INTO reservation VALUES('V100', 'E101', 'C101');

INSERT INTO reservation VALUES('V101', 'E101', 'C102');

INSERT INTO reservation VALUES('V102', 'E102', 'C103');

INSERT INTO reservation VALUES('V103', 'E102', 'C104');

INSERT INTO reservation VALUES('V104', 'E102', 'C105');

INSERT INTO reservation VALUES('V105', 'E102', 'C106');

INSERT INTO reservation VALUES('V106', 'E100', 'C107');

INSERT INTO reservation VALUES('V107', 'E100', 'C108');

INSERT INTO reservation VALUES('V108', 'E100', 'C109');

INSERT INTO reservation VALUES('V109', 'E100', 'C110');

insert INTO order2 values('R100','F101','E100',2,TO_DATE('2020-06-19','YYYY-MM-DD'),'C101');

insert INTO order2 values('R101','F103','E100',1,TO_DATE('2020-06-19','YYYY-MM-DD'),'C101');

insert INTO order2 values('R102','F104','E100',1,TO_DATE('2020-06-19','YYYY-MM-DD'),'C101');

insert INTO order2 values('R103','F106','E103',3,TO_DATE('2020-06-20','YYYY-MM-DD'),'C102');

insert INTO order2 values('R104','F200','E103',2,TO_DATE('2020-06-20','YYYY-MM-DD'),'C102');

insert INTO order2 values('R105','F104','E101',1,TO_DATE('2020-06-21','YYYY-MM-DD'),'C103');

insert INTO order2 values('R106','F202','E101',1,TO_DATE('2020-06-21','YYYY-MM-DD'),'C103');

insert INTO order2 values('R107','F100','E102',1,TO_DATE('2020-06-22','YYYY-MM-DD'),'C104');

insert INTO order2 values('R108','F106','E102',1,TO_DATE('2020-06-22','YYYY-MM-DD'),'C104');

insert INTO order2 values('R109','F200','E102',2,TO_DATE('2020-06-22','YYYY-MM-DD'),'C104');

insert INTO order2 values('R110','F103','E100',1,TO_DATE('2020-06-23','YYYY-MM-DD'),'C105');

insert INTO order2 values('R111','F108','E100',1,TO_DATE('2020-06-23','YYYY-MM-DD'),'C105');

insert INTO order2 values('R112','F100','E102',1,TO_DATE('2020-07-19','YYYY-MM-DD'),'C106');

insert INTO order2 values('R113','F104','E101',1,TO_DATE('2020-07-20','YYYY-MM-DD'),'C107');

insert INTO order2 values('R114','F105','E101',2,TO_DATE('2020-07-20','YYYY-MM-DD'),'C107');

insert INTO order2 values('R115','F106','E104',1,TO_DATE('2020-07-21','YYYY-MM-DD'),'C108');

insert INTO order2 values('R116','F107','E104',1,TO_DATE('2020-07-21','YYYY-MM-DD'),'C108');

insert INTO order2 values('R117','F100','E100',1,TO_DATE('2020-07-22','YYYY-MM-DD'),'C109');

insert INTO order2 values('R118','F301','E100',1,TO_DATE('2020-07-22','YYYY-MM-DD'),'C109');

insert INTO order2 values('R119','F100','E100',1,TO_DATE('2020-07-23','YYYY-MM-DD'),'C110');

insert INTO order2 values('R120','F106','E100',2,TO_DATE('2020-07-23','YYYY-MM-DD'),'C110');

insert INTO order2 values('R121','F200','E100',1,TO_DATE('2020-07-23','YYYY-MM-DD'),'C110');

insert INTO order2 values('R122','F301','E100',3,TO_DATE('2020-07-23','YYYY-MM-DD'),'C110');

INSERT INTO mileage_management VALUES('C101', 1000);

INSERT INTO mileage_management VALUES('C102', 500);

INSERT INTO mileage_management VALUES('C103', 0);

INSERT INTO mileage_management VALUES('C104', 0);

INSERT INTO mileage_management VALUES('C105', 2000);

INSERT INTO mileage_management VALUES('C106', 1500);

INSERT INTO mileage_management VALUES('C107', 1200);

INSERT INTO mileage_management VALUES('C108', 0);

INSERT INTO mileage_management VALUES('C109', 2000);

INSERT INTO mileage_management VALUES('C110', 10000);

INSERT INTO payment VALUES('R100','C101','F101','카드','y');

INSERT INTO payment VALUES('R101','C101','F103','카드','y');

INSERT INTO payment VALUES('R102','C101','F104','카드','y');

INSERT INTO payment VALUES('R103','C102','F106','카드','n');

INSERT INTO payment VALUES('R104','C102','F200','카드','n');

INSERT INTO payment VALUES('R105','C103','F104','카드','y');

INSERT INTO payment VALUES('R106','C103','F202','카드','y');

INSERT INTO payment VALUES('R107','C104','F100','카드','y');

INSERT INTO payment VALUES('R108','C104','F106','카드','y');

INSERT INTO payment VALUES('R109','C104','F200','카드','y');

INSERT INTO payment VALUES('R110','C105','F103','카드','y');

INSERT INTO payment VALUES('R111','C105','F108','카드','y');

INSERT INTO payment VALUES('R112','C106','F100','카드','y');

INSERT INTO payment VALUES('R113','C107','F104','카드','y');

INSERT INTO payment VALUES('R114','C107','F105','카드','y');

INSERT INTO payment VALUES('R115','C108','F106','카드','n');

INSERT INTO payment VALUES('R116','C108','F107','카드','n');

INSERT INTO payment VALUES('R117','C109','F100','카드','y');

INSERT INTO payment VALUES('R118','C109','F301','카드','y');

INSERT INTO payment VALUES('R119','C110','F100','현금','y');

INSERT INTO payment VALUES('R120','C110','F106','현금','y');

INSERT INTO payment VALUES('R121','C110','F200','현금','y');

INSERT INTO payment VALUES('R1','C110','F301','현금','y');

INSERT INTO payment VALUES('C101','카드','y');

INSERT INTO payment VALUES('C102','카드','y');

INSERT INTO payment VALUES('C103','카드','y');

INSERT INTO payment VALUES('C104','카드','n');

INSERT INTO payment VALUES('C105','카드','n');

INSERT INTO payment VALUES('C106','카드','y');

INSERT INTO payment VALUES('C107','카드','y');

INSERT INTO payment VALUES('C108','카드','y');

INSERT INTO payment VALUES('C109','카드','y');

INSERT INTO payment VALUES('C110','카드','y');

Q. 월별로 가장 많이 팔린 메뉴

Q. 8월에 컴플레인을 받은 직원 이름과 내용

Q. 생일인 고객 챙겨주기

Q. (트리거사용)제공수량 음식수량 비교해서 발주넣기

Q. 업체계약일 가장 오래된 것 3개 ->재계약

Q. 컴플레인이 가장 많은 고객의 정보

Q. 주차구역으로 차주전화번호 찾기

Q. 마일리지 트리거 사용해서 적립 및 사용

index, sequence, view, trigger

PL/SQL

-SEQUENCE

(직원추가)

CREATE SEQUENCE empSeq

START WITH 110

INCREMENT BY 1;

INSERT INTO emp VALUES('E'||trim(to_char(empSeq.nextval)),'김호진','010-11-2222','남','서울 동작구',TO_DATE('2010-01-02','YYYY-MM-DD'));

INSERT INTO emp VALUES('E'||trim(to_char(empSeq.nextval)),'김지훈','010-11-2222','남','서울 강남구',TO_DATE('2010-01-18','YYYY-MM-DD'));

(고객추가)

CREATE SEQUENCE customerSeq

START WITH 110

INCREMENT BY 1;

INSERT INTO customer VALUES('C'||trim(to_char(customerSeq.nextval)),'호날두','010-5775-8765','남','1985-11-15','일반');

(음식추가)

CREATE OR REPLACE PROCEDURE new_food(p_f_no IN FOOD.F_NO%TYPE,

p_quantity IN food.f_quantity%TYPE, p_price IN food.f_price%TYPE,

p_name IN food.f_name%TYPE,p_f_date IN FOOD_MANAGEMENT.F_DATE%TYPE,

p_f_shelfdate IN FOOD_MANAGEMENT.F_SHELFDATE%TYPE)

BEGIN

INSERT INTO food VALUES (p_f_no,p_quantity,p_price,p_name);

INSERT INTO food_management VALUES (p_f_no,p_f_date,p_f_shelfdate);

END;

EXECUTE new_food('F304',10,3000,'마카롱',TO_DATE('20-08-20','YY/MM/DD'),TO_DATE('20-10-20','YY/MM/DD'));

CREATE OR REPLACE TRIGGER new_food_trg

AFTER INSERT ON food FOR EACH ROW

BEGIN

DBMS_OUTPUT.PUT_LINE('음식이 추가되었습니다.');

END;

--월별로 가장 많이 팔린 메뉴

SELECT TO_CHAR(o_date,'MM'),f.f_name ,o.o_quantity

FROM food f, order2 o

WHERE f.f_no = o.f_no

AND TO_CHAR(o_date,'MM') =6

AND o.o_quantity IN (SELECT max(sum(o_quantity))

FROM order2

WHERE TO_CHAR(o_date,'MM')=6

GROUP BY o_quantity, f_no);

--월별로 매출순위

SELECT TO_CHAR(o_date,'MM') 월 ,f.f_name 음식명 ,sum(o.o_quantity) 판매수량,

DENSE_RANK() OVER (PARTITION BY TO_CHAR(o_date,'MM') ORDER BY sum(o.o_quantity) desc) as 순위

FROM food f, order2 o

WHERE f.f_no = o.f_no

AND TO_CHAR(o_date,'MM') IN (6,7)

GROUP BY TO_CHAR(o_date,'MM'),f.f_name

ORDER BY TO_CHAR(o_date,'MM') , sum(o_quantity) DESC;

--7월에 컴플레인을 받은 직원 이름과 내용

CREATE VIEW finding_e AS

SELECT DISTINCT e.emp_no, e.emp_name, m.emp_rank, m.emp_part, c.comp_no, t.comp_content

FROM emp e, emp_management m, emp_complain c, complain t, order2 o

WHERE e.emp_no = m.emp_no

AND c.emp_no = e.emp_no

AND t.comp_no = c.comp_no

AND o.emp_no = e.emp_no

AND TO_CHAR(o_date,'MM')=7;

SELECT * FROM finding_e;

- 컴플레인을 한 사람의 정보와 컴플레인의 내용이 무엇인지 출력

SELECT cm.comp_no, cm.c_no,cu.c_name, cu.c_tel, cm.c_grade, cm.emp_no "담당 직원번호",

e.emp_name "담당 직원이름",c.comp_content

FROM customer_management cm, complain c, customer cu, emp e

WHERE cm.comp_no = c.comp_no

AND cm.c_no = cu.c_no

AND e.emp_no = cm.emp_no

ORDER BY c_no;

--마일리지

ALTER TABLE mileage_management ADD discount NUMBER;

CREATE OR REPLACE TRIGGER mileage_trg

AFTER UPDATE ON payment FOR EACH ROW

WHEN (new.mileage_use ='y')

BEGIN

UPDATE mileage_management SET discount=mileage

WHERE c_no= :OLD.c_no;

END;

--UPDATE payment SET MILEAGE_USE='y' WHERE c_no='C101';

--UPDATE payment SET MILEAGE_USE=null WHERE c_no='C103';

--UPDATE MILEAGE_MANAGEMENT SET discount=null WHERE c_no='C101';

--c_no 받아서 총합구하기

CREATE OR REPLACE FUNCTION getTotal(

p_c_no payment.c_no%TYPE)

RETURN NUMBER IS

v_total NUMBER;

BEGIN

SELECT sum(o.o_quantity*f.f_price)-NVL(avg(mm.discount),0) INTO v_total

FROM order2 o JOIN payment p

ON o.o_no=p.o_no

JOIN food f

ON f.F_NO=p.F_NO

JOIN mileage_management mm

ON p.C_NO=mm.c_no

WHERE p.c_no=p_c_no;

RETURN v_total;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RETURN '해당손님없음';

END;

--SELECT getTotal('C101') total_price FROM dual;

set SERVEROUTPUT ON;

-----------------주차구역 받아서 차주명,차주번호 출력-----------------------------

DECLARE

v_c_name customer.c_name%TYPE;

v_c_tel customer.c_tel%TYPE;

v_parking_area parking.parking_area%TYPE;

BEGIN

SELECT c_name,c_tel,parking_area INTO v_c_name,v_c_tel,v_parking_area

FROM customer c,parking p

WHERE c.c_no=p.c_no AND p.parking_area='B-1';

DBMS_OUTPUT.PUT_LINE(v_parking_area||' 구역에 주차된 차의 차주명은 '||v_c_name||', 차주번호는 '||v_c_tel||'입니다.');

END;

- 최근에 계약한 업체를 출력

SELECT ROWNUM, alias.*

FROM(SELECT * FROM provider

ORDER BY p_date DESC)alias

WHERE ROWNUM <=3;

--직원리스트뽑기

CREATE OR REPLACE PROCEDURE listByempno(p_emp_no emp.emp_no%TYPE)

CURSOR empno_cursor is

SELECT * FROM emp

WHERE emp_no = p_emp_no;

empno_record emp%ROWTYPE;

BEGIN

DBMS_OUTPUT.PUT_LINE('==========직원 리스트==========');

FOR empno_record IN empno_cursor LOOP

DBMS_OUTPUT.PUT_LINE('입력한 사원 번호는 ' || p_emp_no || '이며 기본 정보를 출력합니다.');

DBMS_OUTPUT.PUT_LINE('직원번호 : ' || p_emp_no);

DBMS_OUTPUT.PUT_LINE('직원이름 : ' ||empno_record.emp_name);

DBMS_OUTPUT.PUT_LINE('직원연락처 : ' || empno_record.emp_tel);

END LOOP;

END;

EXECUTE listByempno('E100');

--생일자 쿠폰지급

create or replace PROCEDURE birthList002(birth IN VARCHAR2)

CURSOR customer_cursors IS

SELECT * FROM customer

WHERE substr(TO_CHAR(customer.c_birth),4,2) = birth;

customer_record customer_cursors%ROWTYPE;

BEGIN

dbms_output.put_line('===========생일리스트============');

FOR customer_record IN customer_cursors LOOP

dbms_output.put_line(birth ||' '||customer_record.c_no||' '|| customer_record.c_name||' '||customer_record.c_birth);

END LOOP;

dbms_output.put_line('생일자 쿠폰을 지급합니다.');

END;

execute birthlist002('07');

--우수회원

CREATE OR REPLACE TRIGGER trg_cust01

AFTER UPDATE

ON CUSTOMER

FOR EACH ROW

WHEN(NEW.c_grade='우수')

BEGIN

DBMS_OUTPUT.PUT_LINE('=================================');

DBMS_OUTPUT.PUT_LINE('축하드립니다. 우수회원이 되었습니다!');

DBMS_OUTPUT.PUT_LINE('마일리지 5000이 지급되었습니다.');

DBMS_OUTPUT.PUT_LINE('=================================');

END;

update customer set c_grade='우수' where c_no ='C102';

CREATE OR REPLACE TRIGGER trg_cust02

AFTER UPDATE

ON CUSTOMER

FOR EACH ROW

WHEN(NEW.c_grade='일반')

BEGIN

DBMS_OUTPUT.PUT_LINE('=================================');

DBMS_OUTPUT.PUT_LINE('축하드립니다. 일반회원이 되었습니다!');

DBMS_OUTPUT.PUT_LINE('마일리지 3000이 지급되었습니다.');

DBMS_OUTPUT.PUT_LINE('=================================');

UPDATE mileage_management SET MILEAGE = MILEAGE+3000;

END;

UPDATE CUSTOMER SET c_grade = '일반' where c_no='C108';

INSERT INTO order2 VALUES('R123','F100','E101',5,sysdate,'C101');

뷰를 생성하여 연봉이 높은 순으로 출력

--뷰 생성

CREATE OR REPLACE VIEW emsal_vw AS

SELECT emp_no, emp_name,emp_salary

FROM emp e

JOIN emp_management em

ON e.emp_no = em.emp_no;

-- 연봉순 정렬

SELECT emp_no, emp_name, emp_salary

FROM emsal_vw

ORDER BY emp_salary DESC;

SELECT * FROM emp_Info;

--뷰

CREATE OR REPLACE VIEW emp_info

SELECT e.emp_no, e.emp_name, m.emp_rank, m.emp_part

FROM emp e, emp_management m

WHERE e.emp_no = m.emp_no;

--음식 주문하면 음식수량 감소하는 트리거

CREATE OR REPLACE TRIGGER food_quantity_trg

AFTER INSERT ON order2 FOR EACH ROW

--DECLARE v_quantity ORDER2.O_QUANTITY;

BEGIN

--v_quantity:=NEW.o_quantity;

UPDATE food SET F_QUANTITY=f_quantity-:NEW.o_quantity

WHERE f_no= :NEW.f_no;

END;

--발주-----------------------------

CREATE OR REPLACE PROCEDURE getFood(

p_f_no IN food.f_no%TYPE)

v_quantity FOOD.F_QUANTITY%TYPE;

BEGIN

SELECT F_QUANTITY INTO v_quantity FROM food WHERE f_no=p_f_no;

IF v_quantity<=5 THEN

UPDATE food SET f_quantity = f_quantity + (

SELECT DISTINCT p_quantity FROM provider p, food f WHERE p.p_no=f.p_no AND f.f_no=p_f_no)

WHERE f_no=p_f_no;

DBMS_OUTPUT.put_line('발주가 완료되었습니다.');

ELSE DBMS_OUTPUT.PUT_LINE('재고가 있습니다.');

END IF;

END;

EXECUTE getFood('F100');

ALTER TABLE food add p_no REFERENCES provider(p_no);

UPDATE food SET p_no='S500' WHERE f_no='F304';

--음식추가 프로시저

CREATE OR REPLACE PROCEDURE new_food(p_f_no IN FOOD.F_NO%TYPE,

p_quantity IN food.f_quantity%TYPE, p_price IN food.f_price%TYPE,

p_name IN food.f_name%TYPE, p_p_no IN food.p_no%TYPE,

p_f_date IN FOOD_MANAGEMENT.F_DATE%TYPE,

p_f_shelfdate IN FOOD_MANAGEMENT.F_SHELFDATE%TYPE)

BEGIN

INSERT INTO food VALUES (p_f_no,p_quantity,p_price,p_name,p_p_no);

INSERT INTO food_management VALUES (p_f_no,p_f_date,p_f_shelfdate);

END;

EXECUTE new_food('F305',10,5000,'까눌레','S200',TO_DATE('20-08-20','YY/MM/DD'),TO_DATE('20-10-20','YY/MM/DD'));

CREATE OR REPLACE TRIGGER new_food_trg

AFTER INSERT ON food FOR EACH ROW

BEGIN

DBMS_OUTPUT.PUT_LINE('음식이 추가되었습니다.');

END;

728x90

'FULLSTACK > DB' 카테고리의 다른 글

DB 8차시 - ER다이어그램, 정규화, DB프로젝트 (0)	2020.11.13
DB 7차시 - 패키지, 트리거, 데이터모델링, ER다이어그램 (0)	2020.11.13
DB 6차시 - 제어문(조건문), 예외처리, 커서, 프로시저, 함수 (0)	2020.11.13
DB 5차시 - 인덱스, 뷰, 시퀀스, PL/SQL (0)	2020.11.13
DB 4차시 - 서브쿼리문 (0)	2020.11.13

몽구와 함께하는 개발자의 길

DB PROJECT

'FULLSTACK > DB' 카테고리의 다른 글

+ Recent posts

티스토리툴바