[Oracle/수업 과제 practice] DDL(10~15번 문항)

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

 

DDL 10. ✅

GRANT CREATE VIEW TO C##HOMEWORK;

-- 10. 춘 기술대학교 학생들의 정보만이 포함돼 있는 학생일반정보 VIEW 생성
CREATE OR REPLACE VIEW VW_학생일반정보
(
  학번
, 학생이름
, 주소
)
AS
SELECT STUDENT_NO
     , STUDENT_NAME
     , STUDENT_ADDRESS
  FROM TB_STUDENT;

 

DDL 11. ✅

지도교수 없는 학생까지 출력될 수 있도록 한다: NVL(P.PROFESSOR_NAME, '지도교수 없음')

-- 11. 춘 기술대학교는 1년에 두 번씩 학과별로 학생과 지도교수가 지도 면담을 진행한다.
-- 이를 위해 사용할 학생이름, 학과이름, 담당교수이름으로 구성된 VIEW 생성
-- 이때 지도 교수가 없는 학생이 있을 수 있음을 고려하시오.
-- 단, 이 VIEW는 단순 SELECT만 할 경우 학과별로 정렬되어 화면에 보여지게 만드시오.
CREATE OR REPLACE VIEW VW_지도면담
(
  학생이름
, 학과이름
, 담당교수이름
)
AS
SELECT S.STUDENT_NAME
     , D.DEPARTMENT_NAME
     , NVL(P.PROFESSOR_NAME, '지도교수 없음')
  FROM TB_STUDENT S
  LEFT JOIN TB_DEPARTMENT D ON (S.DEPARTMENT_NO = D.DEPARTMENT_NO)
  LEFT JOIN TB_PROFESSOR P ON (S.COACH_PROFESSOR_NO = P.PROFESSOR_NO)
  ORDER BY 2;

 

DDL 12. ✅

-- 12. 모든 학과의 학과별 학생 수를 확인할 수 있는 VIEW 생성
CREATE OR REPLACE VIEW VW_학과별학생수
(
  DEPARTMENT_NAME
, STUDENT_COUNT
)
AS
SELECT D.DEPARTMENT_NAME
     , COUNT(*)
  FROM TB_STUDENT S
  JOIN TB_DEPARTMENT D ON (S.DEPARTMENT_NO = D.DEPARTMENT_NO)
  GROUP BY D.DEPARTMENT_NAME;

 

DDL 13. ✅

-- 13. 11번 문항에서 생성한 학생일반정보 VIEW를 통해 학번이 A213046인 학생의 이름을
-- 본인 이름으로 변경하는 SQL문 작성
UPDATE 
       VW_학생일반정보 V
   SET V.학생이름 = '김구름'
 WHERE V.학번 = 'A213046';

 

DDL 14. ❎✅

VIEW 옵션 중 WITH READ ONLY를 명시해야 한다.

-- 14. 13번 문항과 같이 VIEW를 통해 데이터가 변경될 수 있는 상황을 막으려면
-- VIEW를 어떻게 생성해야 하는지 작성
CREATE OR REPLACE VIEW VW_학생일반정보
(
  학번
, 학생이름
, 주소
)
AS
SELECT STUDENT_NO
     , STUDENT_NAME
     , STUDENT_ADDRESS
  FROM TB_STUDENT
  WITH READ ONLY;

 

DDL 15. ✅

-- 15. 춘 기술대학교는 매년 수강신청 기간만 되면 특정 인기 과목들에 수강 신청이 몰려
-- 문제가 되고 있다. 최근 3년을 기준으로 수강 인원이 가장 많았던 3과목을 찾는 구문 작성
-- 단, 최근 3년의 기준을 2005년에서 2009년으로 변경한다.
SELECT V.*
  FROM (SELECT C.CLASS_NO 과목번호
             , C.CLASS_NAME 과목이름
             , COUNT(*) "누적수강생수(명)"
          FROM TB_CLASS C
          JOIN TB_GRADE G ON (C.CLASS_NO = G.CLASS_NO)
         WHERE SUBSTR(G.TERM_NO, 1, 4) IN ('2005', '2006', '2007', '2008', '2009')
         GROUP BY C.CLASS_NO, C.CLASS_NAME
         ORDER BY 3 DESC
         ) V
 WHERE ROWNUM <= 3;