[Oracle] Solution to puzzles

🧩 DML

Q1

💁‍ 과목유형 테이블(TB_CLASS_TYPE)에 아래와 같은 데이터를 입력하세요.

INSERT
    INTO TB_CLASS_TYPE
(
    CLASS_TYPE_NO, CLASS_TYPE_NAME
)
VALUES
(
    01, '전공필수'
);

INSERT
    INTO TB_CLASS_TYPE
(
    CLASS_TYPE_NO, CLASS_TYPE_NAME
)
VALUES
(
    02, '전공선택'
);

INSERT
    INTO TB_CLASS_TYPE
(
    CLASS_TYPE_NO, CLASS_TYPE_NAME
)
VALUES
(
    03, '교양필수'
);

INSERT
    INTO TB_CLASS_TYPE
(
    CLASS_TYPE_NO, CLASS_TYPE_NAME
)
VALUES
(
    04, '교양선택'
);

INSERT
    INTO TB_CLASS_TYPE
(
    CLASS_TYPE_NO, CLASS_TYPE_NAME
)
VALUES
(
    05, '논문지도'
);

---

Q2

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

CREATE TABLE TB_학생일반정보 (
    학번
,   학생이름
,   주소
)
AS
SELECT
        STUDENT_NO
    ,   STUDENT_NAME
    ,   STUDENT_ADDRESS
    FROM TB_STUDENT;

---

Q3

💁‍ 국어국문학과 학생들의 정보만이 포함되어 있는 학과정보 테이블을 만들고자 합니다. 아래 내용을 참고하여 적절한 SQL문을 작성하세요. (힌트 : 방법은 다양함, 소신껏 작성하세요.)

CREATE TABLE TB_국어국문학과 (
    학번
,   학생이름
,   출생년도
,   교수이름
)
AS
SELECT
        STUDENT_NO
    ,   STUDENT_NAME
    ,   19 || SUBSTR(STUDENT_SSN , 1, 2)
    ,   PROFESSOR_NAME
    FROM TB_STUDENT S
    JOIN TB_PROFESSOR ON (COACH_PROFESSOR_NO = PROFESSOR_NO)
    JOIN TB_DEPARTMENT D ON (S.DEPARTMENT_NO = D.DEPARTMENT_NO)
    WHERE D.DEPARTMENT_NAME = '국어국문학과';

---

Q4

💁‍ 현 학과들의 정원을 10% 증가시키게 되었습니다. 이에 사용한 SQL 문을 작성하세요. (단, 반올림을 사용하여 소수점 자릿수는 생기지 않도록 합니다.)

UPDATE 
        TB_DEPARTMENT
    SET CAPACITY = ROUND(CAPACITY + (CAPACITY * 0.1), -1);

---

Q5

💁‍ 학번 A413042 인 박건우 학생의 주소가 "서울시 종로구 숭인동 181-21 "로 변경되었다고 합니다. 주소지를 정정하기 위해 사용할 SQL 문을 작성하세요.

UPDATE
        TB_STUDENT
    SET STUDENT_ADDRESS = '서울시 종로구 숭인동 181-21'
    WHERE STUDENT_NO = 'A413042'
    AND STUDENT_NAME = '박건우';

---

Q6

💁‍ 주민등록번호 보호법에 따라 학생정보 테이블에서 주민번호 뒷자리를 저장하지 않기로 결정했습니다. 이 내용을 반영할 적절한 SQL 문장을 작성하세요. (예. 830530-2124663 ==> 830530 )

UPDATE
        TB_STUDENT
    SET STUDENT_SSN = SUBSTR(STUDENT_SSN, 1, 6);

---

Q7

💁‍ 의학과 김명훈 학생은 2005 년 1 학기에 자신이 수강한 '피부생리학' 점수가 잘못되었다는 것을 발견하고는 정정을 요청했습니다. 담당 교수의 확인 받은 결과 해당 과목의 학점을 3.5 로 변경키로 결정되었습니다. 적절한 SQL 문을 작성하세요.

UPDATE
        TB_GRADE
    SET POINT = 3.5
    WHERE TERM_NO = '200501'
    AND (STUDENT_NO, CLASS_NO) = (SELECT
                            STUDENT_NO
                        ,   CLASS_NO
                        FROM TB_GRADE
                        JOIN TB_STUDENT S USING (STUDENT_NO)
                        JOIN TB_CLASS USING (CLASS_NO)
                        JOIN TB_DEPARTMENT D ON (S.DEPARTMENT_NO = D.DEPARTMENT_NO)
                        WHERE STUDENT_NAME = '김명훈'
                        AND CLASS_NAME = '피부생리학'
                        AND DEPARTMENT_NAME = '의학과');

💬 Overall Comment

* DML문 안에서 JOIN을 사용하지 못 한다는 것을 알게해준 문항이었다. 
  이럴 때는 서브쿼리 안에서 사용하는 방향으로 가야겠다.

---

Q8

💁‍ 성적 테이블(TB_GRADE)에서 휴학생들의 성적항목을 제거하세요.

COMMIT;
DELETE 
    FROM TB_GRADE
    WHERE STUDENT_NO IN (SELECT
                                STUDENT_NO
                            FROM TB_GRADE
                            JOIN TB_STUDENT USING (STUDENT_NO)
                            WHERE ABSENCE_YN = 'Y');

💬 Overall Comment

* WHERE절에서 다중행 서브쿼리를 사용할 때 '='이 아닌 'IN'을 사용해야한다는 것을 상기시켜준 
  문항이었다. 상식적으로 행이 여러 개이면 '=(같다)'를 사용하면 적절하지 않으니 잘 기억해두자!

---