Workbook [DDL]

✏️문제 1

계열 정보를 저장한 카테고리 테이블을 만들려고 한다. 다음과 같은 테이블을 작성하시오.

예시

🔍풀이

CREATE TABLE TB_CATEGORY (
  NAME VARCHAR2(10),
  USE_YN CHAR(1) DEFAULT 'Y'
);

✏️문제 2

과목 구분을 저장한 테이블을 만들려고 한다. 다음과 같은 테이블을 작성하시오.

예시

🔍풀이

CREATE TABLE TB_CLASS_TYPE(
    NO VARCHAR2(5) PRIMARY KEY,
    NAME VARCHAR2(10)
);

✏️문제 3

TB_CATAGORY 테이블의 NAME 컬럼에 PRIMARY KEY를 생성하시오. (KEY 이름을 생성하지 않아도 무방함. 맊일 KEY 이를 지정하고자 한다면 이름은 본인이 알아서 적당한 이름을 사용한다.)

🔍풀이

ALTER TABLE TB_CATEGORY
  ADD CONSTRAINT CATEGORY_KEY PRIMARY KEY(NAME);

✏️문제 4

4. TB_CLASS_TYPE 테이블의 NAME 컬럼에 NULL 값이 들어가지 않도록 속성을 변경하시오.

🔍풀이

ALTER TABLE TB_CLASS_TYPE MODIFY NAME NOT NULL;

✏️문제 5

두 테이블에서 컬럼 명이 NO인 것은 기존 타입을 유지하면서 크기는 10 으로, 컬럼명이 NAME 인 것은 마찬가지로 기존 타입을 유지하면서 크기 20 으로 변경하시오.

🔍풀이

ALTER TABLE TB_CLASS_TYPE MODIFY (NAME VARCHAR2(20), NO VARCHAR2(10));
ALTER TABLE TB_CATEGORY MODIFY (NAME VARCHAR2(20));

✏️문제 6

두 테이블의 NO 컬럼과 NAME 컬럼의 이름을 각 각 TB_ 를 제외한 테이블 이름이 앞에 붙은 형태로 변경한다.
(ex. CATEGORY_NAME)

🔍풀이

ALTER TABLE TB_CATEGORY
RENAME COLUMN NO TO CATEGORY_NO;
ALTER TABLE TB_CATEGORY
RENAME COLUMN NAME TO CATEGORY_NAME 등

✏️문제 7

TBCATAGORY 테이블과 TB_CLASS_TYPE 테이블의 PRIMARY KEY 이름을 다음과 같이 변경하시오.
->
Primary Key의 이름은 ‚PK + 컬럼이름‛으로 지정하시오. (ex. PK_CATEGORY_NAME )

🔍풀이

ALTER TABLE TB_CLASS_TYPE DROP CONSTRAINT SYS_C008671;
ALTER TABLE TB_CATEGORY DROP CONSTRAINT CATEGORY_PRIMARY_KEY

ALTER TABLE TB_CATEGORY ADD CONSTRAINT PK_CATEGORY_NAME PRIMARY KEY(CATEGORY_NAME);
이하동일

💡참고

Oracle에서는 PRIMARY KEY 제약조건의 이름을 직접 RENAME하는 기능이 제공되지 않는다. PRIMARY KEY 제약조건을 삭제하고 다시 추가하는 방식으로 이름을 변경하는 것이 일반적인 접근이다.

이것은 데이터베이스의 무결성을 보장하기 위한 일반적인 절차이다.

✏️문제 8

TBDEPARTMENT의 CATEGORY 컬럼이 TB_CATEGORY 테이블의 CATEGORY_NAME 컬럼을 부모 값으로 참조하도록 FOREIGN KEY를 지정하시오.
이 때 KEY 이름은 FK테이블이름_컬럼이름으로 지정한다.
(ex. FK_DEPARTMENT_CATEGORY )

🔍풀이

ALTER TABLE TB_DEPARTMENT 
  ADD CONSTRAINT FK_DEPARTMENT_CATEGORY FOREIGN KEY(CATEGORY)
REFERENCES TB_CATEGORY(CATEGORY_NAME);

✏️문제 9

춘 기술대학교 학생들의 정보만이 포함되어 있는 학생일반정보 VIEW를 만들고자 한다. 아래 내용을 참고하여 적절한 SQL 문을 작성하시오.

예시

🔍풀이

CREATE VIEW VW_학생일반정보 AS
SELECT STUDENT_NO 학번
     , STUDENT_NAME 학생이름
     , STUDENT_ADDRESS 주소
  FROM TB_STUDENT;

✏️문제 10

춘 기술대학교는 1년에 두 번씩 학과별로 학생과 지도교수가 지도 면담을 진행한다. 이를 위해 사용한 학생이름, 학과이름, 담당교수이름 으로 구성되어 있는 VIEW 를 만드시오. 이때 지도 교수가 없는 학생이 있을 수 있음을 고려하시오 (단, 이 VIEW 는 단순 SELECT 만을 할 경우 학과별로 정렬되어 화면에 보여지게 만드시오.)

예시

🔍풀이

CREATE VIEW VW_지도면담 AS
SELECT S.STUDENT_NAME 학생이름
     , DEPARTMENT_NAME 학과이름
     , P.PROFESSOR_NAME 지도교수이름
  FROM TB_STUDENT S
  LEFT JOIN TB_PROFESSOR P ON(S.COACH_PROFESSOR_NO = P.PROFESSOR_NO)
       JOIN TB_DEPARTMENT D ON(S.DEPARTMENT_NO = D.DEPARTMENT_NO)
 ORDER BY S.DEPARTMENT_NO;

✏️문제 11

모든 학과의 학과별 학생 수를 확인할 수 있도록 적절한 VIEW 를 작성해 보자.

예시

🔍풀이

CREATE VIEW VW_학과별학생수 AS
SELECT DEPARTMENT_NAME 학과이름
     , COUNT(STUDENT_NO) 학생수
  FROM TB_DEPARTMENT
JOIN TB_STUDENT USING(DEPARTMENT_NO)
GROUP BY DEPARTMENT_NAME;

✏️문제 12

위에서 생성한 학생일반정보 View를 통해서 학번이 A213046인 학생의 이름을 본인 이름으로 변경하는 SQL 문을 작성하시오.

🔍풀이

UPDATE VW_학생일반정보
   SET 학생이름 = '조성일'
 WHERE 학번 = 'A213046';

💡참고

VIEW는 별칭을 사용해서 만들기 때문에,
해당 COLUMN을 선택할 때에도 별칭을 이용해 지정한다.

✏️문제 13

🔍풀이

CREATE 시 마지막에 
WITH READ ONLY 옵션을 넣는다.

DML 할 시 아래와 같이 오류가 발생

✏️문제 14

춘 기술대학교는 매년 수강신청 기간만 되면 특정 인기 과목들에 수강 신청이 몰려 문제가 되고 있다. 2005~2009년 간 누적수강생이 가장 많은 과목 3개를 조회하시오.

예시

🔍풀이

 SELECT 과목번호, 과목이름, "누적수강생수(명)"
  FROM 
    (SELECT CLASS_NO 과목번호
          , CLASS_NAME 과목이름
          , RANK() OVER (ORDER BY COUNT(STUDENT_NO) DESC) AS RANK1
          , COUNT(STUDENT_NO) "누적수강생수(명)"
       FROM TB_CLASS
       JOIN TB_GRADE USING(CLASS_NO)
       WHERE SUBSTR(TERM_NO, 1, 4) IN (2005, 2006, 2007, 2008, 2009)
      GROUP BY (CLASS_NO, CLASS_NAME)
     )
WHERE RANK1 <= 3;

💡참고

서브쿼리로 SELECT 문 별칭을 주었을 때에는 
본문 SELECT에서도 별칭으로 가져온다. 
아니면 에러발생.