[Oracle] Solution to puzzles

🧩 DDL을 활용하여 쿼리문 짜기

Q1

💁‍ 계열 정보를 저장할 카테고리 테이블을 만들려고 합니다. 다음과 같은 테이블을 작성하세요.

CREATE TABLE TB_CATEGORY (
    NAME VARCHAR2(10)
 ,  USE_YN CHAR(1) DEFAULT 'Y'
);

---

Q2

💁‍ 과목 구분을 저장할 테이블을 만들려고 합니다. 다음과 같은 테이블을 작성하세요.

CREATE TABLE TB_CLASS_TYPE (
    NO VARCHAR2(5) PRIMARY KEY
,   NAME VARCHAR2(10)
);

---

Q3

💁‍ TB_CATEGORY 테이블의 NAME 컬럼에 PRIMARY KEY 를 생성하세요.
(KEY 이름을 생성하지 않아도 무방. 만일 KEY 이를 지정하고자 한다면 이름은 본인이 알아서 적당한 이름을 사용)

ALTER TABLE TB_CATEGORY 
ADD CONSTRAINT PK_CATE_NAME PRIMARY KEY(NAME);

---

Q4

💁‍ TB_CLASS_TYPE 테이블의 NAME 컬럼에 NULL 값이 들어가지 않도록 속성을 변경하세요.

ALTER TABLE TB_CLASS_TYPE
MODIFY NAME CONSTRAINT NN_CLASS_NAME NOT NULL;

---

Q5

💁‍ 두 테이블에서 컬럼 명이 NO인 것은 기존 타입을 유지하면서 크기는 10 으로, 컬럼명이 NAME 인 것은 마찬가지로 기존 타입을 유지하면서 크기 20 으로 변경하세요.

ALTER TABLE TB_CATEGORY
MODIFY NAME VARCHAR2(20);

ALTER TABLE TB_CLASS_TYPE
MODIFY NO VARCHAR2(10)
MODIFY NAME VARCHAR2(20);

---

Q6

💁‍ 두 테이블의 NO 컬럼과 NAME 컬럼의 이름을 각 각 TB_ 를 제외한 테이블 이름이 앞에 붙은 형태로 변경하세요. (ex. CATEGORY_NAME)

ALTER TABLE TB_CATEGORY
RENAME COLUMN NAME TO CATEGORY_NAME;

ALTER TABLE TB_CLASS_TYPE
RENAME COLUMN NO TO CLASS_TYPE_NO;
ALTER TABLE TB_CLASS_TYPE
RENAME COLUMN NAME TO CLASS_TYPE_NAME;
-- 함께 RENAME을 했을 때, '이 작업을 다른 작업과 결합할 수 없음' 오류

---

Q7

💁‍ TBCATEGORY 테이블과 TB_CLASS_TYPE 테이블의 PRIMARY KEY 이름을 다음과 같이 변경하세요. Primary Key 의 이름은 "PK + 컬럼이름"으로 지정하세요. (ex. PK_CATEGORY_NAME )

/* TB_CATEGORY 제약 조건명 변경(NAME) */
ALTER TABLE TB_CATEGORY
RENAME CONSTRAINT PK_CATE_NAME TO PK_CATEGORY_NAME;

/* TB_CLASS_TYPE 제약 조건명 변경(NO) */
ALTER TABLE TB_CLASS_TYPE
RENAME CONSTRAINT SYS_C008168 TO PK_CLASS_TYPE_NO; -- 테이블에서 찾아와야함

---

Q8

💁‍ 다음과 같은 INSERT 문을 수행하세요.

INSERT
    INTO TB_CATEGORY
VALUES (
    '공학', 'Y'
);

INSERT
    INTO TB_CATEGORY
VALUES (
    '자연과학', 'Y'
);

INSERT
    INTO TB_CATEGORY
VALUES (
    '의학', 'Y'
);

INSERT
    INTO TB_CATEGORY
VALUES (
    '예체능', 'Y'
);

INSERT
    INTO TB_CATEGORY
VALUES (
    '인문사회', 'Y'
);

COMMIT;

---

Q9

💁‍ TBDEPARTMENT 의 CATEGORY 컬럼이 TB_CATEGORY 테이블의 CATEGORY_NAME 컬럼을 부모 값으로 참조하도록 FOREIGN KEY 를 지정하세요. 이 때 KEY 이름은 FK테이블이름_컬럼이름으로 지정합니다.
(ex. FK_DEPARTMENT_CATEGORY )

ALTER TABLE TB_DEPARTMENT 
ADD CONSTRAINT FK_DEPARTMENT_CATEGORY FOREIGN KEY(CATEGORY) REFERENCES TB_CATEGORY(CATEGORY_NAME);

---

Q10

💁‍ 춘 기술대학교 학생들의 정보만이 포함되어 있는 학생일반정보 VIEW 를 만들고자 합니다. 아래 내용을 참고하여 적절한 SQL 문을 작성하세요.

CREATE OR REPLACE VIEW VW_학생일반정보
(
    학번
,   학생이름
,   주소
)
AS
SELECT
        STUDENT_NO
    ,   STUDENT_NAME
    ,   STUDENT_ADDRESS
    FROM TB_STUDENT;
    
/* 뷰를 생성할 수 있는 권한을 주는 구문 (system 계정에서 실행) */
GRANT CREATE VIEW TO C##HOMEWORK;

---

Q11

💁‍ 춘 기술대학교는 1 년에 두 번씩 학과별로 학생과 지도교수가 지도 면담을 진행합니다. 이를 위해 사용한 학생이름, 학과이름, 담당교수이름 으로 구성되어 있는 VIEW 를 만드세요. 이때 지도 교수가 없는 학생이 있을 수 있음을 고려하세요. (단, 이 VIEW 는 단순 SELECT만을 할 경우 학과별로 정렬되어 화면에 보여지게 만드세요.)

CREATE OR REPLACE VIEW VW_지도면담
(
    학생이름
,   학과이름
,   지도교수이름
)
AS
SELECT
        STUDENT_NAME
    ,   DEPARTMENT_NAME
    ,   NVL(PROFESSOR_NAME, '지도교수없음')
    FROM TB_STUDENT
    JOIN TB_DEPARTMENT USING (DEPARTMENT_NO)
    LEFT JOIN TB_PROFESSOR ON (COACH_PROFESSOR_NO = PROFESSOR_NO);
    
SELECT
        VW.*
    FROM VW_지도면담 VW;

---

Q12

💁‍ 모든 학과의 학과별 학생 수를 확인할 수 있도록 적절한 VIEW를 작성하세요.

CREATE OR REPLACE VIEW VW_학과별학생수
(
    DEPARTMENT_NAME
,   STUDENT_COUNT
)
AS
SELECT
        DEPARTMENT_NAME
    ,   COUNT(S.DEPARTMENT_NO)
    FROM TB_DEPARTMENT D
    JOIN TB_STUDENT S ON(D.DEPARTMENT_NO = S.DEPARTMENT_NO)
    GROUP BY DEPARTMENT_NAME;

---

Q13

💁‍ 위에서 생성한 학생일반정보 View를 통해서 학번이 A213046 인 학생의 이름을 본인 이름으로 변경하는 SQL 문을 작성하세요.

UPDATE
       VW_학생일반정보
   SET 학생이름 = '조효연'
 WHERE 학번 = 'A213046';
 >>> 별칭을 달아줬다면 별칭을 적어줘야함 !

---

Q14

💁‍ 13 번에서와 같이 VIEW를 통해서 데이터가 변경될 수 있는 상황을 막으려면 VIEW 를 어떻게 생성해야 하는지 작성하세요.

VIEW 생성 시, WITH READ ONLY(DML 수행이 불가능하게 하는 옵션)를 입력하여 삽입/수정/삭제를 막는다.

---