DBMS Day9 school 예제

• 과목 테이블 생성

CREATE TABLE `school`.`subjects`
(
    `code` VARCHAR(3)  NOT NULL,
    `name` VARCHAR(10) NOT NULL,
    CONSTRAINT PRIMARY KEY (`code`)
);

• 선생님 테이블 생성

CREATE TABLE `school`.`teachers`
(
    `id`           VARCHAR(6)  NOT NULL,
    `name`         VARCHAR(10) NOT NULL,
    `gender_code`  VARCHAR(1)  NOT NULL,
    `contact`      VARCHAR(11) NOT NULL,
    `subject_code` VARCHAR(3)  NOT NULL,
    `designated_grade` TINYINT(1) UNSIGNED NOT NULL COMMENT '담임 학년',
    `designated_class` TINYINT(1) UNSIGNED NOT NULL COMMENT '담임 반',
    CONSTRAINT PRIMARY KEY (`id`),
    CONSTRAINT FOREIGN KEY (`gender_code`) REFERENCES `school`.`genders` (`code`),
    CONSTRAINT FOREIGN KEY (`subject_code`) REFERENCES `school`.`subjects` (`code`),
    CONSTRAINT FOREIGN KEY (`designated_grade`,`designated_class`) REFERENCES `school`.`classes` (`grade`,`class`)
);

-> subjects 추가

-> 0학년 0반 추가

• 담임을 맡고 있는 학년, 반이 쌍으로 외래키가 걸려져있어서 반듯이 들어가야하는데 담임을 맡지 않는 선생님도 존재함으로 0학년 0반을 추가로 만들어 준다.

문제 1 ) 사진과 같이 만드시오.

SELECT CONCAT(`student`.`grade` , '학년', `student`.`class`, '반', `student`.`number` , '번') AS `학년/반/번호`,
       `student`.`name` AS `이름`,
       `teacher`.`name` AS `담임 이름`,
       `subject`.`name` AS `담임 과목`
FROM `school`.`students` AS `student`
    LEFT JOIN `school`.`teachers` AS `teacher` ON `student`.`grade` = `teacher`.`designated_grade`
         AND `student`.`class` = `teacher`.`designated_class`
    LEFT JOIN `school`.`subjects` AS `subject` ON `teacher`.`subject_code` = `subject`.`code`;

문제 2) school의 teachers 테이블을 SELECT 하여 교번( id )이 '201002'인 교사가 담임인 반의 학생 수를 반환하는 쿼리를 작성하세요. 단, 문제에서 주어진 리터럴(Literal)을 제외한 리터럴을 사용하여서는 안된다.

SELECT CONCAT(COUNT(0), '명') AS `학생 수`
FROM `school`.`teachers` AS `teacher`
    LEFT JOIN `school`.`students` AS `student` ON `teacher`.`designated_grade` = `student`.`grade`
        AND `teacher`.`designated_class` = `student`.`class`
WHERE `teacher`.`id` = '201002';

< 문제 2 결과 >