sql (1)

• 기본적인 쿼리 연습
  - create, alter, pk, fk, relation
• 중요한건 도메인을 담을 수 있는 데이터베이스를 설계하는 것
• 링크

create table DEPARTMENT(
    id int primary key,
    name varchar(20) not null unique,
    leader_id INT
);

create table EMPLOYEE(
    id int primary key,
    name varchar(30) not null,
    birth_date DATE,
    sex CHAR(1) CHECK ( sex in ('M', 'F') ),
    position varchar(10),
    salary INT DEFAULT 50000000,
    dept_id INT,
    FOREIGN KEY (dept_id) references DEPARTMENT(id)
        on DELETE SET NULL on update CASCADE,
        CHECK ( salary >= 50000000 )
);

create table PROJECT(
    id INT primary key ,
    name varchar(20) NOT NULL unique ,
    leader_id int,
    start_date Date,
    end_date Date,
    FOREIGN KEY (leader_id) references EMPLOYEE(id) on delete set NULL on update CASCADE ,
    check ( start_date < PROJECT.end_date )

);

create table WORKS_ON (
    empl_id int,
    proj_id int,
    FOREIGN KEY (empl_id) REFERENCES EMPLOYEE(id)
                      on DELETE CASCADE on UPDATE CASCADE,
    FOREIGN KEY (proj_id) REFERENCES PROJECT(id)
                      on DELETE CASCADE on UPDATE CASCADE
);

alter table DEPARTMENT ADD FOREIGN KEY (leader_id)
    REFERENCES EMPLOYEE(id) on update CASCADE on DELETE set null;