CREATE TABLE 테이블_이름 (
    속성_이름 데이터_타입 [NOT NULL] [DEFAULT 기본값],
    ...
    [PRIMARY KEY (속성_리스트)],
    [UNIQUE (속성_리스트)],
    [FOREIGN KEY (속성_리스트) REFERENCES 테이블(속성)]
        [ON DELETE 옵션] [ON UPDATE 옵션],
    [CONSTRAINT 이름 CHECK(조건)]
);

-- 기본키 (NULL 불가, 유일)
PRIMARY KEY (고객번호)

-- 대체키 (NULL 가능, 유일)
UNIQUE (주민번호)

-- 외래키 (참조 무결성)
FOREIGN KEY (학과코드) REFERENCES 학과(학과코드)
    ON DELETE SET NULL
    ON UPDATE CASCADE

-- 고객 테이블
CREATE TABLE 고객 (
    고객번호    VARCHAR(10)  NOT NULL,
    이름       VARCHAR(50)  NOT NULL,
    연락처     VARCHAR(20),
    등급       VARCHAR(10)  DEFAULT 'Silver',
    가입일     DATE         DEFAULT CURRENT_DATE,
    PRIMARY KEY (고객번호),
    CONSTRAINT chk_등급 CHECK (등급 IN ('VIP', 'Gold', 'Silver'))
);

-- 주문 테이블
CREATE TABLE 주문 (
    주문번호    VARCHAR(20)  NOT NULL,
    주문일     DATETIME     DEFAULT CURRENT_TIMESTAMP,
    총금액     INT          CHECK (총금액 > 0),
    고객번호   VARCHAR(10)  NOT NULL,
    PRIMARY KEY (주문번호),
    FOREIGN KEY (고객번호) REFERENCES 고객(고객번호)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

-- 속성 추가
ALTER TABLE 고객 ADD 이메일 VARCHAR(100);

-- 속성 삭제
ALTER TABLE 고객 DROP COLUMN 이메일;

-- 제약조건 추가
ALTER TABLE 고객 ADD CONSTRAINT chk_나이 CHECK (나이 >= 0);

-- 제약조건 삭제
ALTER TABLE 고객 DROP CONSTRAINT chk_나이;

DROP TABLE 주문;  -- 참조하는 테이블 먼저 삭제
DROP TABLE 고객;

SELECT [ALL | DISTINCT] 속성_리스트
FROM 테이블_리스트
[WHERE 조건]
[GROUP BY 속성_리스트 [HAVING 조건]]
[ORDER BY 속성_리스트 [ASC | DESC]];

-- 모든 속성 검색
SELECT * FROM 고객;

-- 특정 속성만 검색
SELECT 이름, 등급 FROM 고객;

-- 중복 제거
SELECT DISTINCT 등급 FROM 고객;

-- 별칭 사용
SELECT 이름 AS 고객명, 등급 AS 회원등급 FROM 고객;

-- 산술식
SELECT 상품명, 가격, 가격 * 0.9 AS 할인가 FROM 상품;

-- 비교 연산자
SELECT * FROM 상품 WHERE 가격 >= 10000;
SELECT * FROM 고객 WHERE 등급 = 'VIP';
SELECT * FROM 주문 WHERE 주문일 > '2024-01-01';

-- 논리 연산자 (AND, OR, NOT)
SELECT * FROM 상품 WHERE 가격 >= 10000 AND 재고 > 0;
SELECT * FROM 고객 WHERE 등급 = 'VIP' OR 등급 = 'Gold';
SELECT * FROM 상품 WHERE NOT 카테고리 = '전자제품';

-- BETWEEN
SELECT * FROM 상품 WHERE 가격 BETWEEN 10000 AND 50000;

-- IN
SELECT * FROM 고객 WHERE 등급 IN ('VIP', 'Gold');

-- '김'으로 시작하는 이름
SELECT * FROM 고객 WHERE 이름 LIKE '김%';

-- '동'으로 끝나는 이름
SELECT * FROM 고객 WHERE 이름 LIKE '%동';

-- '영'이 포함된 이름
SELECT * FROM 고객 WHERE 이름 LIKE '%영%';

-- 두 번째 글자가 '길'인 이름
SELECT * FROM 고객 WHERE 이름 LIKE '_길%';

-- NULL인 데이터 검색
SELECT * FROM 고객 WHERE 연락처 IS NULL;

-- NULL이 아닌 데이터 검색
SELECT * FROM 고객 WHERE 연락처 IS NOT NULL;

-- 오름차순 (기본값)
SELECT * FROM 상품 ORDER BY 가격 ASC;

-- 내림차순
SELECT * FROM 상품 ORDER BY 가격 DESC;

-- 다중 정렬
SELECT * FROM 고객 ORDER BY 등급 ASC, 가입일 DESC;

-- 전체 고객 수
SELECT COUNT(*) FROM 고객;

-- 등급별 고객 수 (중복 제거)
SELECT COUNT(DISTINCT 등급) FROM 고객;

-- 상품 가격 통계
SELECT
    COUNT(*) AS 상품수,
    SUM(가격) AS 가격합계,
    AVG(가격) AS 평균가격,
    MAX(가격) AS 최고가,
    MIN(가격) AS 최저가
FROM 상품;

-- 등급별 고객 수
SELECT 등급, COUNT(*) AS 고객수
FROM 고객
GROUP BY 등급;

-- 고객별 주문 총액
SELECT 고객번호, SUM(총금액) AS 주문총액
FROM 주문
GROUP BY 고객번호;

-- HAVING: 그룹 조건
SELECT 고객번호, COUNT(*) AS 주문횟수
FROM 주문
GROUP BY 고객번호
HAVING COUNT(*) >= 5;  -- 5회 이상 주문한 고객

-- 암시적 조인 (WHERE 절)
SELECT 고객.이름, 주문.주문번호, 주문.총금액
FROM 고객, 주문
WHERE 고객.고객번호 = 주문.고객번호;

-- 명시적 조인 (INNER JOIN)
SELECT 고객.이름, 주문.주문번호, 주문.총금액
FROM 고객 INNER JOIN 주문 ON 고객.고객번호 = 주문.고객번호;

-- 별칭 사용
SELECT c.이름, o.주문번호, o.총금액
FROM 고객 c INNER JOIN 주문 o ON c.고객번호 = o.고객번호;

-- LEFT OUTER JOIN: 왼쪽 테이블 모든 행 포함
SELECT c.이름, o.주문번호
FROM 고객 c LEFT OUTER JOIN 주문 o ON c.고객번호 = o.고객번호;

-- RIGHT OUTER JOIN: 오른쪽 테이블 모든 행 포함
SELECT c.이름, o.주문번호
FROM 고객 c RIGHT OUTER JOIN 주문 o ON c.고객번호 = o.고객번호;

-- FULL OUTER JOIN: 양쪽 모든 행 포함
SELECT c.이름, o.주문번호
FROM 고객 c FULL OUTER JOIN 주문 o ON c.고객번호 = o.고객번호;

-- 단일 행 서브쿼리 (=, <, > 등 사용)
SELECT * FROM 상품
WHERE 가격 > (SELECT AVG(가격) FROM 상품);

-- 다중 행 서브쿼리 (IN, EXISTS, ALL, ANY 사용)
SELECT * FROM 고객
WHERE 고객번호 IN (SELECT 고객번호 FROM 주문);

-- EXISTS 예제
SELECT * FROM 고객 c
WHERE EXISTS (
    SELECT 1 FROM 주문 o WHERE o.고객번호 = c.고객번호
);

-- ALL 예제: 모든 VIP 고객보다 많이 주문한 고객
SELECT 고객번호, SUM(총금액) AS 총액
FROM 주문
GROUP BY 고객번호
HAVING SUM(총금액) > ALL (
    SELECT SUM(총금액)
    FROM 주문
    WHERE 고객번호 IN (SELECT 고객번호 FROM 고객 WHERE 등급 = 'VIP')
    GROUP BY 고객번호
);

-- 전체 속성 삽입
INSERT INTO 고객 VALUES ('C001', '홍길동', '010-1234-5678', 'VIP', '2024-01-01');

-- 특정 속성만 삽입 (나머지는 NULL 또는 DEFAULT)
INSERT INTO 고객 (고객번호, 이름) VALUES ('C002', '김철수');

-- 다른 테이블에서 데이터 복사
INSERT INTO VIP고객 (고객번호, 이름)
SELECT 고객번호, 이름 FROM 고객 WHERE 등급 = 'VIP';

-- 조건에 맞는 데이터 수정
UPDATE 상품 SET 가격 = 가격 * 1.1 WHERE 카테고리 = '식품';

-- 여러 속성 수정
UPDATE 고객 SET 등급 = 'VIP', 연락처 = '010-9999-9999' WHERE 고객번호 = 'C001';

-- 서브쿼리 활용
UPDATE 고객 SET 등급 = 'VIP'
WHERE 고객번호 IN (
    SELECT 고객번호 FROM 주문 GROUP BY 고객번호 HAVING SUM(총금액) > 1000000
);

-- 조건에 맞는 데이터 삭제
DELETE FROM 주문 WHERE 주문일 < '2023-01-01';

-- 모든 데이터 삭제
DELETE FROM 주문;  -- 테이블 구조는 유지

-- 서브쿼리 활용
DELETE FROM 고객
WHERE 고객번호 NOT IN (SELECT DISTINCT 고객번호 FROM 주문);

CREATE VIEW 뷰_이름 [(속성_리스트)]
AS SELECT 문
[WITH CHECK OPTION];

-- VIP 고객 뷰
CREATE VIEW VIP고객뷰 AS
SELECT 고객번호, 이름, 연락처
FROM 고객
WHERE 등급 = 'VIP';

-- 주문 통계 뷰
CREATE VIEW 고객별주문통계 AS
SELECT c.고객번호, c.이름, COUNT(o.주문번호) AS 주문수, SUM(o.총금액) AS 총주문액
FROM 고객 c LEFT JOIN 주문 o ON c.고객번호 = o.고객번호
GROUP BY c.고객번호, c.이름;

-- WITH CHECK OPTION: 뷰 정의 조건 강제
CREATE VIEW VIP고객뷰 AS
SELECT * FROM 고객 WHERE 등급 = 'VIP'
WITH CHECK OPTION;  -- VIP가 아닌 데이터 삽입/수정 불가

-- 뷰 조회 (일반 테이블처럼 사용)
SELECT * FROM VIP고객뷰;
SELECT * FROM 고객별주문통계 WHERE 총주문액 > 1000000;

DROP VIEW VIP고객뷰;