오라클_DML_예제_문제풀이

--1. 과목유형 테이블(TB_CLASS_TYPE)에 아래와 같은 데이터를 입력하시오.
ALTER TABLE TB_CLASS_TYPE MODIFY NAME VARCHAR2(20);
--기존 크기 : 10 --> 20으로 변경(기존보다 넣으려는 값이 더 커서 크기 수정)

INSERT INTO TB_CLASS_TYPE(CLASS_TYPE_NO, NAME)
VALUES('01', '전공필수');
INSERT INTO TB_CLASS_TYPE(CLASS_TYPE_NO, NAME)
VALUES('02', '전공선택');
INSERT INTO TB_CLASS_TYPE(CLASS_TYPE_NO, NAME)
VALUES('03', '교양필수');
INSERT INTO TB_CLASS_TYPE(CLASS_TYPE_NO, NAME)
VALUES('04', '교양선택');
INSERT INTO TB_CLASS_TYPE(CLASS_TYPE_NO, NAME)
VALUES('05', '논문지도');

DELETE FROM TB_CLASS_TYPE WHERE CLASS_TYPE_NO = 1;
--숫자 1로 들어간 정보 삭제.
SELECT CLASS_TYPE_NO 번호, NAME 유형 FROM TB_CLASS_TYPE;
--컬럼명 별칭

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

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

SELECT * FROM TB_학생일반정보;

--**3. 국어국문학과 학생들의 정보만이 포함되어 있는 학과정보 테이블을 만들고자 한다.
--아래 내용을 참고하여 적절한 SQL문을 작성하시오.(힌트 : 방법은 다양함, 소신껏
--작성하시오)
--방법1.
CREATE TABLE TB_국어국문학과(학번, 학생이름, 출생년도, 교수이름)
AS SELECT STUDENT_NO, STUDENT_NAME, EXTRACT(YEAR FROM(TO_DATE(SUBSTR(STUDENT_SSN, 1, 2), 'RR'))),PROFESSOR_NAME
    FROM TB_PROFESSOR P
        JOIN TB_STUDENT S ON (PROFESSOR_NO = COACH_PROFESSOR_NO)
        JOIN TB_DEPARTMENT D ON (S.DEPARTMENT_NO = D.DEPARTMENT_NO)
    WHERE DEPARTMENT_NAME = '국어국문학과';

SELECT * FROM TB_국어국문학과;
--방법2.
CREATE TABLE TB_국어국문학과(학번, 학생이름, 출생년도, 교수이름)
AS SELECT STUDENT_NO, STUDENT_NAME, TO_CHAR(TO_DATE(SUBSTR(STUDENT_SSN, 1, 6)), 'YYYY/MM/DD'), NVL(PROFESSOR_NAME, '지도교수 없음')
   FROM TB_STUDENT S
        LEFT JOIN TB_PROFESSOR ON (PROFESSOR_NO = COACH_PROFESSOR_NO)
        LEFT JOIN TB_DEPARTMENT D ON(D.DEPARTMENT_NO = S.DEPARTMENT_NO)
   WHERE DEPARTMENT_NAME = '국어국문학과';

--4. 현 학과들의 정원을 10% 증가시키게 되었다. 이에 사용할 SQL문을 작성하시오.(단,
--반올림을 사용하여 소수점 자릿수는 생기지 않도록 한다.
CREATE TABLE TB_DEPARTMENT_COPY AS SELECT * FROM TB_DEPARTMENT;
--카피한 테이블에 실행.
--방법1.
UPDATE TB_DEPARTMENT_COPY
SET CAPACITY = CEIL(CAPACITY+(CAPACITY*0.1));
--방법2.
UPDATE TB_DEPARTMENT
SET CAPACITY = ROUND(CAPACITY * 1.1);

SELECT * FROM TB_DEPARTMENT_COPY;--조회 확인

--5. 학번 A413042인 박건우 학생의 주소가 "서울시 종로구 숭인동 181-21"로 변경되었다고
--한다. 주소지를 정정하기 위해 사용할 SQL문을 작성하시오.
UPDATE TB_STUDENT
SET STUDENT_ADDRESS = '서울시 종로구 숭인동 181-21'
WHERE STUDENT_NO = 'A413042' AND STUDENT_NAME = '박건우';--학번이 유일하기 때문에 이름은 빼도된다.

SELECT STUDENT_NAME, STUDENT_ADDRESS
FROM TB_STUDENT;

--6. 주민등록번호 보호법에 따라 학생정보 테이블에서 주민번호 뒷자리를 저장하지 않기로
--결정하였다. 이 내용을 반영할 적절한 SQL 문장을 작성하시오.
--(예.830530-2124663 => 830530)
CREATE TABLE TB_STUDENT_COPY
AS SELECT * FROM TB_STUDENT;

UPDATE TB_STUDENT_COPY
SET STUDENT_SSN = SUBSTR(STUDENT_SSN, 1, 6);
--생년월일부분만 잘라서 다시 재업데이트!

--7. 의학과 김명훈 학생은 2005년 1학기에 자신이 수강한 '피부생리학' 점수가 잘못되었다는 것을
--발견하고는 정정을 요청하였다. 담당 교수의 확인 받은 결과 해당 과목의 학점을 3.5로 변경키로
--결정되었다. 적절한 SQL문을 작성하시오.
UPDATE TB_GRADE
SET POINT = 3.5
WHERE STUDENT_NO IN (SELECT STUDENT_NO
                        FROM TB_STUDENT
                        JOIN TB_CLASS USING(DEPARTMENT_NO)
                        JOIN TB_DEPARTMENT USING(DEPARTMENT_NO)
                        JOIN TB_GRADE USING(STUDENT_NO)
                        WHERE DEPARTMENT_NAME = '의학과' AND STUDENT_NAME = '김명훈'
                              AND TERM_NO = 200501 AND CLASS_NAME = '피부생리학');

        
--8. 성적 테이블(TB_GRADE)에서 휴학생들의 성적항목을 제거하시오.
--방법1.
DELETE FROM TB_GRADE
       WHERE EXISTS (SELECT POINT
                     FROM TB_GRADE
                     JOIN TB_STUDENT USING (STUDENT_NO)
                     WHERE ABSENCE_YN = 'Y');
--EXISTS : 서브쿼리에 해당 내용이 존재하면 데이터를 삭제한다.
--방법2.
DELETE FROM TB_GRADE
       WHERE STUDENT_NO IN (SELECT STUDENT_NO
                            FROM TB_STUDENT
                            WHERE ABSENCE_YN = 'Y');