[항해99] Spring 입문주차 1주차 과제

• 문제  : 수강생을 관리하는 MANAGER 테이블을 만들어보자

1번 문제

• 컬럼은 총 id, name, student_code 입니다.
• id는 bigint 타입이며 PK입니다.
• name은 최소 2자 이상, varchar 타입, not null 입니다.
• student_code는 STUDENT 테이블을 참조하는 FK이며 not null 입니다.
• FK는 CONSTRAINT 이름을 ‘manager_fk_student_code’ 로 지정해야합니다.

# 1번 문제

CREATE TABLE IF NOT EXISTS MANAGER
(
    id bigint primary key,
    name varchar(100) not null,
    student_code varchar(100) not null,
    CONSTRAINT manager_fk_student_code foreign key(student_code) references student(student_code)
);

 

 

코드 리뷰

1. IF NOT EXIST : 테이블이 이미 존재하는 경우 재생성 X

2. biigint 데이터 유형은 매우 큰 정수 값을 저장하는데 사용

3. varchar(100) : 100자까지 문자열 저장 가능, NOT NULL 제약 조건을 설정해 값이 비어 있지 않도록 함

4. CONSTRAINT manager_fk_student_code foreign key(student_code) references student(student_code): student_code 열에 외래 키 제약 조건을 추가 -> 이 제약 조건은 student 테이블의 student_code 열을 참조하며,  manager_fk_student_code라는 이름이 지정됨.

-> 이를 통해 MANAGER 테이블의 student_code 열이 student 테이블의 해당 열에 대한 참조를 유지

 

2번 문제

• ALTER, MODIFY를 이용하여 MANAGER 테이블의 id 컬럼에 AUTO_INCREMENT 기능을 부여하세요.
• AUTO_INCREMENT 기능을 활용하세요. 

ALTER TABLE MANAGER MODIFY COLUMN  id bigint auto_increment;

 

3번 문제 

•  INSERT를 이용하여 수강생 s1, s2, s3, s4, s5를 관리하는 managerA와 s6, s7, s8, s9를 관리하는 managerB를 추가하세요.

# 3번 문제
INSERT INTO MANAGER(name, student_code) VALUE ('managerA', 's1');
INSERT INTO MANAGER(name, student_code) VALUE ('managerA', 's2');
INSERT INTO MANAGER(name, student_code) VALUE ('managerA', 's3');
INSERT INTO MANAGER(name, student_code) VALUE ('managerA', 's4');
INSERT INTO MANAGER(name, student_code) VALUE ('managerA', 's5');

INSERT INTO MANAGER(name, student_code) VALUE ('managerB', 's6');
INSERT INTO MANAGER(name, student_code) VALUE ('managerB', 's7');
INSERT INTO MANAGER(name, student_code) VALUE ('managerB', 's8');
INSERT INTO MANAGER(name, student_code) VALUE ('managerB', 's9');

 

4번 문제

• JOIN을 사용하여 managerA가 관리하는 수강생들의 이름과 시험 주차 별 성적을 가져오세요.

# 4번 문제
# JOIN을 사용하여 managerA가 관리하는 수강생들의 이름과 시험 주차 별 성적을 가져오세요.
SELECT s.name, e.exam_seq, e.score
FROM MANAGER m JOIN STUDENT S on m.student_code = s.student_code
JOIN EXAM e on m.student_code = e.student_code WHERE m.name = 'managerA';

 

5번 문제

• ALTER, DROP, MODIFY, CASCADE 를 사용하여 EXAM, MANAGER 테이블을 수정합니다.
• STUDENT 테이블에서 s1 수강생을 삭제했을 때 EXAM에 있는 s1수강생의 시험성적과 MANAGER의 managerA가 관리하는 수강생 목록에 자동으로 삭제될 수 있도록 하세요.

# 5번 문제
# STUDENT 테이블에서 s1 수강생을 삭제했을 때 EXAM에 있는 s1수강생의 시험성적과
# MANAGER의 managerA가 관리하는 수강생 목록에 자동으로 삭제될 수 있도록 하세요.
# - ALTER, DROP, MODIFY, CASCADE 를 사용하여 EXAM, MANAGER 테이블을 수정합니다.
ALTER TABLE EXAM DROP CONSTRAINT exam_fk_student_code;
ALTER TABLE EXAM ADD CONSTRAINT exam_fk_student_code FOREIGN KEY(student_code) REFERENCES STUDENT(student_code) ON DELETE CASCADE;
ALTER TABLE MANAGER DROP CONSTRAINT manager_fk_student_code;
ALTER TABLE MANAGER ADD CONSTRAINT manager_fk_student_code FOREIGN KEY(student_code) REFERENCES STUDENT(student_code) ON DELETE CASCADE;

DELETE FROM STUDENT WHERE student_code = 's1';

 

코드 리뷰

1. ALTER TABLE EXAM DROP CONSTRAINT exam_fk_student_code

: EXAM 테이블의 exam_fk_student_code라는 이름의 외래 키 제약 조건을 삭제.

→ 이를 통해 STUDENT 테이블의 학생이 삭제될 때 해당 학생의 시험 점수도 삭제되는 기능을 구현할 준비함

 

2. ALTER TABLE EXAM ADD CONSTRAINT exam_fk_student_code FOREIGN KEY(student_code) REFERENCES STUDENT(student_code) ON DELETE CASCADE

: EXAM 테이블에 새로운 외래 키 제약 조건을 추가.

: 이 조건은 student_code 열을 참조하며, STUDENT 테이블의 해당 열에 대한 참조를 설정함.

: ON DELETE CASCADE 옵션은 부모 테이블에서 행이 삭제될 때 자식 테이블에서도 해당 행을 자동으로 삭제하도록 지정해줌

 

3. ALTER TABLE MANAGER DROP CONSTRAINT manager_fk_student_code

: MANAGER 테이블의 manager_fk_student_code라는 이름의 외래 키 제약 조건을 삭제함

→ STUDENT 테이블의 학생이 삭제될 때 해당 학생을 관리하는 관리자 정보도 삭제되는 기능 구현 준비

 

4. ALTER TABLE MANAGER ADD CONSTRAINT manager_fk_student_code FOREIGN KEY(student_code) REFERENCES STUDENT(student_code) ON DELETE CASCADE

: MANAGER 테이블에 새로운 외래 키 제약 조건을 추가

: 이 제약 조건은 student_code 열을 참조하며, STUDENT 테이블의 해당 열에 대한 참조를 설정함

 

5. DELETE FROM STUDENT WHERE student_code = 's1'

: STUDENT 테이블에서 student_code가 's1'인 학생을 삭제함

: 이 작업은 해당 학생이 EXAM과 MANAGER 테이블에서도 관리되는 정보가 자동으로 삭제되도록 유도.

 

나머지는 해결이 가능했는데, 4번, 5번이 좀 어려웠던 것 같다.
구글링이랑 노션을 많이 참고했다.
책GPT를 통해서 코드리뷰를 하고 정답을 확인했는데 거의 똑같이 짜서 뿌듯했다!!