CS/데이터베이스
[데이터베이스] myCompiler 실습_중간
rngPwns
2025. 4. 26. 20:10
CREATE TABLE 고객
(
고객아이디 VARCHAR(20) NOT NULL,
고객이름 VARCHAR(10) NOT NULL,
나이 INT,
등급 VARCHAR(10) NOT NULL,
직업 VARCHAR(20),
적립금 INT DEFAULT 0,
PRIMARY KEY(고객아이디)
);
CREATE TABLE 제품
(
제품번호 CHAR(3) NOT NULL,
제품명 VARCHAR(20),
재고량 INT,
단가 INT,
제조업체 VARCHAR(20),
PRIMARY KEY(제품번호),
CHECK (재고량>=0 AND 재고량 <= 10000)
);
CREATE TABLE 주문(
주문번호 CHAR(3) NOT NULL,
주문고객 VARCHAR(20),
주문제품 CHAR(3),
수량 INT,
배송지 VARCHAR(30),
주문일자 DATE,
PRIMARY KEY (주문번호) ,
FOREIGN KEY (주문고객) REFERENCES 고객(고객아이디),
FOREIGN KEY (주문제품) REFERENCES 제품(제품번호)
);
INSERT INTO 고객 VALUES('apple', '정소화', 20, 'gold', '학생', 1000);
INSERT INTO 고객 VALUES('banana', '김선우', 25, 'vip', '간호사', 2500);
INSERT INTO 고객 VALUES('carrot', '고명석', 28, 'gold', '교사', 4500);
INSERT INTO 고객 VALUES('orange', '김용욱', 22, 'silver', '학생', 0);
INSERT INTO 고객 VALUES('melon', '성원용', 35, 'gold', '회사원', 5000);
INSERT INTO 고객 VALUES('peach', '오형준', NULL, 'silver', '의사', 300);
INSERT INTO 고객 VALUES('pear', '채광주', 31, 'silver', '회사원', 500);
INSERT INTO 제품 VALUES('p01','그냥만두',5000, 4500, '대한식품');
INSERT INTO 제품 VALUES('p02','매운쫄면',2500, 5500, '민국푸드');
INSERT INTO 제품 VALUES('p03','쿵떡파이',3600, 2600, '한빛제과');
INSERT INTO 제품 VALUES('p04','맛난초콜릿',1250, 2500, '한빛제과');
INSERT INTO 제품 VALUES('p05','얼큰라면',2200, 1200, '대한식품');
INSERT INTO 제품 VALUES('p06','통통우동',1000, 1550, '민국푸드');
INSERT INTO 제품 VALUES('p07','달콤비스킷',1650, 1500, '한빛제과');
INSERT INTO 주문 VALUES('o01','apple','p03', 10, '서울시 마포구', '22/01/01');
INSERT INTO 주문 VALUES('o02','melon','p01', 5, '인천시 계양구', '22/01/10');
INSERT INTO 주문 VALUES('o03','banana','p06', 45, '경기도 부천시', '22/01/11');
INSERT INTO 주문 VALUES('o04','carrot','p02', 8, '부산시 금정구', '22/02/01');
INSERT INTO 주문 VALUES('o05','melon','p06', 36, '경기도 용인시', '22/02/20');
INSERT INTO 주문 VALUES('o06','banana','p01', 19, '충청북도 보은군', '22/03/02');
INSERT INTO 주문 VALUES('o07','apple','p03', 22, '서울시 영등포구', '22/03/15');
INSERT INTO 주문 VALUES('o08','pear','p02', 50, '강원도 춘천시', '22/04/10');
INSERT INTO 주문 VALUES('o09','banana','p04', 15, '전라남도 목포시', '22/04/11');
INSERT INTO 주문 VALUES('o10','carrot','p03', 20, '경기도 안양시', '22/05/22');
SELECT 제품명, 단가, 제조업체
FROM 제품 P1
WHERE 단가 = (SELECT MAX(단가)
FROM 제품 P2
WHERE P1.제조업체= P2.제조업체);
-- 1. 메인에서 제품 테이블의 각 행을 처리하는데
-- 2. 서브는 메인의 제조업체를 참고하여 업체 별 최고 단가를 찾고
-- 3. 메인의 단가가 서브의 결과와 일치하는 행만 반환
-- 존재여부를 확인하는 경우
/* SELECT 고객이름
FROM 고객
WHERE EXISTS = (SELECT MAX(단가)
FROM 주문
WHERE 주문고객 = 고객아이디);*/
-- 1. 메인에서 제품 테이블의 각 행(고객 개인들)을 접근
-- 2. 서브쿼리에서 해당 고객의 주문내역이 존재하는지 확인
-- 3. EXISTS 연산자로 존재여부 평가
--내장함수(수학, 문자열, 시간/날짜, ...)
--수학
SELECT ABS(-78), ROUND(4.875, 1);
SELECT CEILING (4.1), FLOOR(4.1);
SELECT POWER(2,3), LOG(1000), SQRT(3);
SELECT
FROM 제품 INNER JOIN 주문 ON 제품번호=주문제품
--TOP n 질의
Select
-- 부속질의
--1. 중첩질의 : where절에서
--2. 스칼라 부속질의 : select절에서
--3. 인라인 뷰 : from절에서
-- 스칼라 부속질의 : 부속질의의 결과 ㄱ밧이 단일 값이 단일 행, 단일 열로 반환
-- select 문과 UPDATE 문에서 사용
select 주문고객,(select 고객이름
from 고객
where 고객아이디 = 주문고객), AS 이름, count(*) 총주문수
from 주문
group by 주문고객
--주문테이블에 있는 제품번호와 부속테이블에 있는 제품번호 매칭
alter table 주문 add 제품명 varchar(20);
update 주문
set 제품명 = (select 제품명
from 제품
where 주문제품 = 제품번호);
--인라인 뷰 : 테이블 대신 실제 테이블처럼 사용
-- 다중 행, 다중 열로 반환
select 등급, avg(적립금) as 평균적립금
from 고객
group by 등급
having avg(적립금) >= 1000;
select 등급,평균적립금
from (select 등급, avg(적립금) as 평균적립금
from 고객
group by 등급)등급평균
where 평균적립금 >= 1000;
select 고객이름, count(*)
from(select 고객아이디, 고객이름
from 고객
where 나이 >= 25) C, 주문 O
where C. 고객아이디 = O.주문고객
group by 고객이름
-- rank() over(order by 순위를 정하는 데 사용되는 열)
select 주문번호, 수량, rank() over(order by 수량 desc) NGROUP
from 주문
------
select 주문고객, 고객이름 ,sum(수량*단가) 주문금액
from (제품 inner join 주문 on 제품번호 = 주문제품)
inner join 고객 on 주문고객 = 고객아이디
group by 주문고객, 고객이름
------
select 주문고객, 고객이름, sum(수량*단가) 총구매액,
case
when (sum(수량*단가) >= 200000) then '최우수고객'
when (sum(수량*단가) >= 100000) then '우수고객'
when (sum(수량*단가) >= 80000) then '일반고객'
else '고객'
end 고객등급
from (제품 inner join 주문 on 제품번호 = 주문제품)
inner join 고객 on 주문고객 = 고객아이디
group by 주문고객, 고객이름