0510 DB (3) 3 예제 풀이

drop table dept cascade constraints;
drop table emp cascade constraints;

//dept 테이블 생성하기
create table dept
(deptno number(2),
dname varchar2(14) not null,
loc varchar2(13),
constraint dept_deptno_pk primary key(deptno)
);

// constraint 제약조건
- not null은 column에만 작성
- constraint 제약조건 이름 유형 (컬럼이름)

// emp 테이블 만들기
create table emp
(empno number(4),
ename varchar2(10) not null,
job varchar2(9) not null,
mgr number(4),
hiredate date,
sal number(7,2) not null,
comm number(7,2),
deptno number(2),
constraint emp_empno_pk primary key(empno)
);

// 테이블 생성 후 alter 사용하여 FK 추가하기
alter table emp
add constraint emp_deptno_fk foreign key(deptno) references dept(deptno);

// 데이터 삽입하기
inset into 테이블
values

insert into dept(deptno, dname, loc)
values(10, 'Administration', 1700);
insert into dept(deptno, dname, loc)
values(20, 'Marketing', 1800);
insert into dept(deptno, dname, loc)
values(30, 'Purchasing', 1700);
insert into dept(deptno, dname, loc)
values(40, 'human', 2400);
insert into dept(deptno, dname, loc)
values(50, 'shipping', 1500);


insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
values(100, 'King', 'AD_PRES', null, '1987-01-17',24000,null,10);
insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
values(101, 'Kochar', 'AD_VP', 100, '1989-09-21', 17000, null, 50);
insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
values(102, 'DE Haen', 'AD_VP', 100, '1993-01-13', 17000, null, 50);
insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
values(103, 'Hunold', 'IT_PROG', 102, '1990-07-03', 9000, null, 40);
insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
values(104,'Ernst', 'IT_PROG',103,'1993-07-25', 4800, null,40);

// 저장하기
commit;