부서(DEPT) 와 사원(EMPLOYEE) 테이블 연습

CREATE TABLE DEPT(
deptno number(2) constraint pk_deptno PRIMARY KEY,
dname varchar2(14),
loc varchar2(13)
);

CREATE TABLE EMPLOYEE(
 empno number(4) constraint pk_empno PRIMARY KEY,
 ename varchar2(10) NOT NULL,
 job varchar2(9),
 ngr number(4) NOT NULL,
 hiredate date,
 sal number(7) NOT NULL,
 comm number(7),
 deptno number(2) 
 );
 
 --외래키(foreign) 추가
 ALTER TABLE EMPLOYEE ADD CONSTRAINT FK_DEPTNO
    FOREIGN KEY(deptno) REFERENCES DEPT(deptno);

--부서 추가
insert into dept values(10, 'Accounting', 'New York');
insert into dept values(20, 'Research', 'Dallas');
insert into dept values(30, 'sales', 'Chicago');
 
 --사원 추가   
insert into employee values(7369, 'Smith', 'cleark', 7902, '80/12/17', 800, null, 20);
insert into employee values(7499, 'allen', 'salesman', 7698, '81/02/20', 1600, 300, 30);
insert into employee values(7521, 'war', 'salesman', 7698 ,'81/02/22', 1250, 500, 30);

--조인 서치
select e.empno 사원번호, e.ename 사원이름, e.job 직급, e.ngr 관리자, e.hiredate 입사일,
       e.sal 급여, e.comm 커미션, d.deptno 부서번호, d.dname 부서명, d.loc 지역
from employee e
inner join dept d
     on e.deptno = d.deptno;