[Oracle/수업 과제 practice] DML

-- SQL DML
-- 춘대학시스템
✅ 답안과 비교하여 스스로 SQL 구문 개선점 짚어보기 완료(2022.01.30)

 

-- 기존 데이터에 변경이 일어나므로 사용을 마친 뒤엔 ROLLBACK; 처리한다.
ROLLBACK;

 

DML 1. ✅

-- 1. 과목 유형 테이블(TB_CLASS_TYPE)에 아래와 같은 데이터 입력
COMMIT;
INSERT
  INTO TB_CLASS_TYPE
VALUES
(
  01
, '전공필수'
);
INSERT INTO TB_CLASS_TYPE VALUES (02, '전공선택');
INSERT INTO TB_CLASS_TYPE VALUES (03, '교양필수');
INSERT INTO TB_CLASS_TYPE VALUES (04, '교양선택');
INSERT INTO TB_CLASS_TYPE VALUES (05, '논문지도');

 

DML 2. ✅

(컬럼명)을 괄호 안에 작성해도 되고, SELECT문 컬럼 레벨에서 지정할 수도 있다.

-- 2. 춘 기술대학교 학생들의 정보가 포함돼 있는 학생일반정보 테이블 생성
-- 서브쿼리를 이용하시오.
CREATE TABLE TB_학생일반정보
(
  학번
, 학생이름
, 주소
)
AS
SELECT STUDENT_NO
     , STUDENT_NAME
     , STUDENT_ADDRESS
  FROM TB_STUDENT;

 

DML 3. ✅

(컬럼명)을 괄호 안에 작성해도 되고, SELECT문 컬럼 레벨에서 지정할 수도 있다.

-- 3. 국어국문학과 학생들의 정보만이 포함돼 있는 학과정보 테이블 생성
-- 출생년도는 네 자리 년도로 표기하시오.
CREATE TABLE TB_국어국문학과
(
  학번
, 학생이름
, 출생년도
, 교수이름
)
AS
SELECT S.STUDENT_NO
     , S.STUDENT_NAME
--     , TO_CHAR(TO_DATE(SUBSTR(S.STUDENT_SSN, 1, 2), 'RRRR'), 'YYYY')
     , '19' || SUBSTR(S.STUDENT_SSN, 1, 2)
     , P.PROFESSOR_NAME
  FROM TB_STUDENT S
  JOIN TB_PROFESSOR P ON (S.COACH_PROFESSOR_NO = P.PROFESSOR_NO)
  JOIN TB_DEPARTMENT D ON (D.DEPARTMENT_NO = P.DEPARTMENT_NO)
 WHERE D.DEPARTMENT_NAME = '국어국문학과';

 

DML 4. ❎✅

반올림 조건에 대해 잊지 말자: CAPACITY = ROUND(CAPACITY * 1.1);

-- 4. 현 학과들의 정원을 10% 증가시키게 되었다. 이에 사용할 SQL문 작성
-- 단, 반올림 사용하여 소수점 자릿수는 생기지 않도록 한다.
UPDATE 
       TB_DEPARTMENT
   SET CAPACITY = (CAPACITY + (CAPACITY * 0.1));

 

DML 5. ✅

-- 5. 학번 A413042인 박건우 학생의 주소가 "서울시 종로구 숭인동 181-21"로 변경되었다.
-- 주소지 정정하기 위한 SQL문 작성
UPDATE
       TB_STUDENT
   SET STUDENT_ADDRESS = '서울시 종로구 숭인동 181-21'
 WHERE STUDENT_NO = 'A413042';

 

DML 6. ✅

-- 6. 주민등록번호 보호법에 따라 학생정보 테이블에서 주민번호 뒷자리를 저장하지 않기로 결정하였다.
-- 이 내용을 반영할 적절한 SQL문 작성
-- 예) 830530-2124663 => 830530
UPDATE
       TB_STUDENT
   SET STUDENT_SSN = SUBSTR(STUDENT_SSN, 1, 6);

 

DML 7. ✅

다중열 서브쿼리 대신 WHERE~AND 조건을 나열할 수도 있다.

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

 

DML 8. ✅

-- 8. 성적 테이블(TB_GRADE)에서 휴학생들의 성적항목을 제거하시오.
DELETE
  FROM TB_GRADE G
 WHERE G.STUDENT_NO IN (SELECT S.STUDENT_NO
                          FROM TB_STUDENT S
                         WHERE S.ABSENCE_YN = 'Y');