SELECT 문

-- 1. 실행용 데이터 준비 (Oracle)
CREATE TABLE CULTURE_ENROLLMENT (
    ENROLL_ID    NUMBER(4) PRIMARY KEY,
    LEARNER_NAME VARCHAR2(40 CHAR) NOT NULL,
    COURSE_TYPE  VARCHAR2(20 CHAR) NOT NULL,
    FEE          NUMBER(8),
    STATUS       CHAR(1) NOT NULL,
    CONSTRAINT CK_CE_STATUS CHECK (STATUS IN ('Y', 'N'))
);

INSERT INTO CULTURE_ENROLLMENT VALUES (201, '서유진', '도예', 50000, 'Y');
INSERT INTO CULTURE_ENROLLMENT VALUES (202, '하진우', '사진', 60000, 'Y');
INSERT INTO CULTURE_ENROLLMENT VALUES (203, '서유진', '사진', 60000, 'N');
INSERT INTO CULTURE_ENROLLMENT VALUES (204, '정서윤', '도예', 50000, 'Y');
INSERT INTO CULTURE_ENROLLMENT VALUES (205, '유민호', '도예', NULL, 'Y');
COMMIT;

-- 2. WHERE: 유효 신청만 선택 (201, 202, 204, 205)
SELECT ENROLL_ID, LEARNER_NAME
  FROM CULTURE_ENROLLMENT
 WHERE STATUS = 'Y'
 ORDER BY ENROLL_ID;

-- 3. GROUP BY + HAVING: 유효 신청이 2건 이상인 강좌
SELECT COURSE_TYPE, COUNT(*) AS ENROLL_CNT
  FROM CULTURE_ENROLLMENT
 WHERE STATUS = 'Y'
 GROUP BY COURSE_TYPE
HAVING COUNT(*) >= 2
 ORDER BY COURSE_TYPE;

-- 4. DISTINCT: 강좌 종류만 두 행으로 조회
SELECT DISTINCT COURSE_TYPE
  FROM CULTURE_ENROLLMENT
 ORDER BY COURSE_TYPE;

-- 5. 연산 결과: NULL 수강료는 계산 결과도 NULL
SELECT ENROLL_ID, FEE * 2 AS DOUBLE_FEE
  FROM CULTURE_ENROLLMENT
 ORDER BY ENROLL_ID;

-- 1. DDL: 방문객과 전시 예약 테이블 생성
CREATE TABLE VISITOR (
    VISITOR_ID   NUMBER(6) PRIMARY KEY,
    VISITOR_NAME VARCHAR2(40 CHAR) NOT NULL,
    EMAIL        VARCHAR2(100 CHAR) UNIQUE
);

CREATE TABLE EXHIBIT_RESERVATION (
    RESERVATION_ID NUMBER(8) PRIMARY KEY,
    VISITOR_ID     NUMBER(6) NOT NULL,
    VISIT_DATE     DATE NOT NULL,
    TICKET_COUNT   NUMBER(3) NOT NULL,
    STATUS         CHAR(1) DEFAULT 'R' NOT NULL,
    CREATED_AT     TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
    CONSTRAINT FK_ER_VISITOR FOREIGN KEY (VISITOR_ID)
        REFERENCES VISITOR (VISITOR_ID),
    CONSTRAINT CK_ER_TICKET CHECK (TICKET_COUNT >= 1),
    CONSTRAINT CK_ER_STATUS CHECK (STATUS IN ('R', 'C'))
);

-- 선택적으로 생성하는 조회용 인덱스
CREATE INDEX IDX_ER_VISITOR
    ON EXHIBIT_RESERVATION (VISITOR_ID);

-- 2. DML: 방문객과 예약 데이터 입력
INSERT INTO VISITOR VALUES (101, '윤가은', 'gaeun@example.test');
INSERT INTO VISITOR VALUES (102, '최민서', 'minseo@example.test');
INSERT INTO VISITOR VALUES (103, '하태원', 'taewon@example.test');

INSERT INTO EXHIBIT_RESERVATION
    (RESERVATION_ID, VISITOR_ID, VISIT_DATE, TICKET_COUNT, STATUS)
VALUES (9001, 101, DATE '2026-11-03', 2, 'R');

INSERT INTO EXHIBIT_RESERVATION
    (RESERVATION_ID, VISITOR_ID, VISIT_DATE, TICKET_COUNT, STATUS)
VALUES (9002, 101, DATE '2026-11-04', 1, 'R');

INSERT INTO EXHIBIT_RESERVATION
    (RESERVATION_ID, VISITOR_ID, VISIT_DATE, TICKET_COUNT, STATUS)
VALUES (9003, 102, DATE '2026-11-05', 3, 'C');

-- 3. TCL: 데이터 변경 확정
COMMIT;

-- 4. DML(조회): 방문 예정인 예약만 확인
SELECT R.RESERVATION_ID,
       V.VISITOR_NAME,
       TO_CHAR(R.VISIT_DATE, 'YYYY-MM-DD') AS VISIT_DATE,
       R.TICKET_COUNT
  FROM EXHIBIT_RESERVATION R
  JOIN VISITOR V ON V.VISITOR_ID = R.VISITOR_ID
 WHERE R.STATUS = 'R'
 ORDER BY R.RESERVATION_ID;