[Project] DB 설계(3) 학력 테이블 실제 적용하기

-- 특정직원 학력 입력
INSERT INTO education (edu_no, e_code, edu_period, edu_name, edu_major, edu_status, edu_division, edu_order)
VALUES (SEQ_EDU_NO.NEXTVAL, '1', '2017-2019', '가든고등학교', '문과', '졸업', '고등학교', 1);

INSERT INTO education (edu_no, e_code, edu_period, edu_name, edu_major, edu_status, edu_division, edu_order)
VALUES (SEQ_EDU_NO.NEXTVAL, '2', '2020-2023', '타운대학교', '사회복지학과', '졸업', '대학교', 2);

INSERT INTO education (edu_no, e_code, edu_period, edu_name, edu_major, edu_status, edu_division, edu_order)
VALUES (SEQ_EDU_NO.NEXTVAL, '2', '2024-2025', '공공대학원', '실버학과', '졸업', '대학원', 3);

ALTER TABLE education
    ADD e_code VARCHAR2(20) REFERENCES EMP240119(E_CODE);


ALTER TABLE education
    ADD e_code VARCHAR2(20);

ALTER TABLE education
    ADD CONSTRAINT education_EMP240119_E_CODE_fk
        FOREIGN KEY (e_code) REFERENCES EMP240119(e_code);

ALTER TABLE EDUCATION
RENAME column e_code to e_code;

select * from EDUCATION
    order by EDU_ORDER;

-- 특정직원 학력 조회
SELECT *
FROM education
WHERE e_code = 2
ORDER BY edu_order;

-- 특정직원 학력 수정
UPDATE education
SET edu_period = '2020.03-2023.02',
    edu_name = '푸숑대학교',
    edu_major = '강아지학과',
    edu_status = '졸업'
WHERE e_code = 2
  AND edu_order = 2;

-- 특정직원 학력 삭제
DELETE FROM education
WHERE e_code = 2
  AND edu_order = 2;