20.DML

/*
    DML
*/
-- 1.과목유형 테이블(tb_class_type)에 아래와 같은 데이터를 입력
DELETE tb_class_type;

CREATE SEQUENCE seq_1;
DROP SEQUENCE seq_1;

-- 하나씩 입력
INSERT INTO tb_class_type VALUES(seq_1.NEXTVAL, '전공필수');
INSERT INTO tb_class_type VALUES(seq_1.NEXTVAL, '전공선택');
INSERT INTO tb_class_type VALUES(seq_1.NEXTVAL, '교양선택');
INSERT INTO tb_class_type VALUES(seq_1.NEXTVAL, '논문지도');
INSERT INTO tb_class_type VALUES(seq_1.NEXTVAL, '교양필수');

-- 시퀀스 사용 한번에 입력
INSERT INTO tb_class_type (
    SELECT seq_1.NEXTVAL, class_type
    FROM ( SELECT DISTINCT class_type FROM tb_class )
);

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

-- 서브쿼리의 결과집합으로 테이블을 생성함
CREATE TABLE 학생일반정보(학번, 학생이름, 주소)
AS
    SELECT student_no, student_name, student_address
    FROM tb_student;
    
-- 3번
-- 국어국문학과 학생들의 정보만이 포함되어 있는 학과정보 테이블을 만들고자 한다.
-- 아래 내용을 참고하여 적절한 SQL 문을 작성하시오.
-- 학번, 학과이름, 출생년도, 교수이름
CREATE TABLE 국어국문학과(학번, 학과이름, 출생년도, 교수이름)
AS
    SELECT student_no, department_name, DECODE(SUBSTR(student_ssn,8,1),'1','19','2','19','3','20','4','20','')||SUBSTR(student_ssn,1,2) AS 출생년도, NVL(professor_name,'지도교수없음')
    FROM tb_student s, tb_department d, tb_professor p
    WHERE s.department_no = d.department_no
    AND s.coach_professor_no = p.professor_no(+)
    AND department_name = '국어국문학과';

SELECT * FROM 국어국문학과;

 SELECT student_no, department_name, DECODE(SUBSTR(student_ssn,8,1),'1','19','2','19','3','20','4','20','')||SUBSTR(student_ssn,1,2) AS 출생년도, NVL(professor_name,'지도교수없음')
    FROM tb_student s
    LEFT JOIN tb_professor ON(coach_professor_no = professor_no) 
    JOIN tb_department d /*USING(department_no)*/ ON(s.department_no=d.department_no) 
    --join할때 관계를 잘 봐야함 순서도 중요 세 테이블 모두 department_no 열을 가지고 있기 때문에 열의 정의가 애매하다고 오류남 순서를 바꾸던지 ON을 사용해서 어떤 테이블의 열인지 정확히 알려줘야함
    WHERE department_name = '국어국문학과';
    
-- 4번
-- 현 학과들의 정원을 10% 증가시키게 되었다. 이에 사용할 SQL 문을 작성하시오.
-- (단, 반올림을 사용하여 소수점 자릿수는 생기지 않도록 한다.)
UPDATE tb_department
SET capacity = ROUND(capacity*1.1);

-- 5번
-- 학번 A413042인 박건우 학생의 주소가 "서울시 종로구 숭인동 181-21"로 변경되었다고 한다.
UPDATE tb_student 
SET student_address = '서울시 종로구 숭인동 181-21' 
WHERE student_name = ( SELECT student_name FROM tb_student WHERE student_no = 'A413042' );
COMMIT;

SELECT * FROM tb_student WHERE student_no = 'A413042';

-- 6번
-- 주민등록번호 보호법에 따라 학생정보 테이블에서 주민번호 뒷자리를 저장하지 않기로 결정하였다.
-- 이 내용을 반영할 적절한 SQL 문장을 작성하시오.
UPDATE tb_student
SET student_ssn = SUBSTR(student_ssn,1,8)||'******';
-- SET student_ssn = RPAD(SUBSTR(student_ssn,1,8),14,'*')

COMMIT;

SELECT *
FROM tb_student;

-- 7번
-- 의학과 김명훈 학생은 2005년 1학기에 자신이 수강한 '피부생리학' 점수가 
-- 잘못되었다는 것을 발견하고는 정정을 요청하였다.
-- 담당 교수의 확인 받은 결과 해당 과목의 학점을 3.5로 변경키로 결정되었다. 적절한 SQL 문을 작성하시오
UPDATE tb_grade 
SET point = 3.5
WHERE term_no = '200501'
AND class_no = (
    SELECT class_no
    FROM tb_class
    WHERE class_name = '피부생리학' 
    )
AND student_no = (
    SELECT student_no
    FROM tb_student
    JOIN tb_department USING(department_no)
    WHERE student_name = '김명훈' AND department_name = '의학과'
);

SELECT *
FROM tb_grade
WHERE (term_no, student_no, class_no) = (
    SELECT '200501', student_no, class_no
    FROM tb_student
    JOIN tb_department USING(department_no) 
    JOIN tb_class USING(department_no)
    WHERE student_name = '김명훈'
    AND   department_name = '의학과'
    AND   class_name = '피부생리학'
);

SELECT *
FROM tb_grade
WHERE (student_no, class_no) = (
    SELECT student_no, class_no
    FROM tb_student
    JOIN tb_department USING(department_no) 
    JOIN tb_class USING(department_no)
    WHERE student_name = '김명훈'
    AND   department_name = '의학과'
    AND   class_name = '피부생리학'
    )
AND term_no = '200501';

SELECT * FROM tb_grade WHERE student_no = 'A331101' AND class_no = 'C3843900';

-- 8번
-- 성적 테이블에서 휴학생들의 성적항목을 제거하시오
-- 483건
SELECT COUNT(*)
FROM tb_grade 
WHERE student_no IN (
    SELECT student_no 
    FROM tb_student
    WHERE absence_yn = 'Y'
);

DELETE tb_grade WHERE student_no IN (
    SELECT student_no 
    FROM tb_student
    WHERE absence_yn = 'Y'
);

ROLLBACK;