EMP and DEPT

View All Scripts Login to Run Script

Script Name EMP and DEPT
Description Example EMP and DEPT tables. Classic Oracle tables with 4 departments and 14 employees. Includes a join query example.
Area SQL General
Contributor Mike Hichwa (Oracle)
Created Monday October 05, 2015

Statement 1

Create DEPT table which will be the parent table of the EMP table.

create table dept(   
  deptno     number(2,0),   
  dname      varchar2(14),   
  loc        varchar2(13),   
  constraint pk_dept primary key (deptno)   
)

Table created.

Statement 2

Create the EMP table which has a foreign key reference to the DEPT table. The foreign key will require that the DEPTNO in the EMP table exist in the DEPTNO column in the DEPT table.

create table emp(   
  empno    number(4,0),   
  ename    varchar2(10),   
  job      varchar2(9),   
  mgr      number(4,0),   
  hiredate date,   
  sal      number(7,2),   
  comm     number(7,2),   
  deptno   number(2,0),   
  constraint pk_emp primary key (empno),   
  constraint fk_deptno foreign key (deptno) references dept (deptno)   
)

Table created.

Statement 3

Insert row into DEPT table using named columns.

insert into DEPT (DEPTNO, DNAME, LOC) 
values(10, 'ACCOUNTING', 'NEW YORK')

1 row(s) inserted.

Statement 4
Insert a row into DEPT table by column position.
```
insert into dept   
values(20, 'RESEARCH', 'DALLAS')
```
1 row(s) inserted.

Statement 5

insert into dept   
values(30, 'SALES', 'CHICAGO')

1 row(s) inserted.

Statement 6

insert into dept  
values(40, 'OPERATIONS', 'BOSTON')

1 row(s) inserted.

Statement 7

Insert EMP row, using TO_DATE function to cast string literal into an oracle DATE format.

insert into emp   
values(   
 7839, 'KING', 'PRESIDENT', null,   
 to_date('17-11-1981','dd-mm-yyyy'),   
 5000, null, 10   
)

1 row(s) inserted.

Statement 8

insert into emp  
values(  
 7698, 'BLAKE', 'MANAGER', 7839,  
 to_date('1-5-1981','dd-mm-yyyy'),  
 2850, null, 30  
)

1 row(s) inserted.

Statement 9

insert into emp  
values(  
 7782, 'CLARK', 'MANAGER', 7839,  
 to_date('9-6-1981','dd-mm-yyyy'),  
 2450, null, 10  
)

1 row(s) inserted.

Statement 10

insert into emp  
values(  
 7566, 'JONES', 'MANAGER', 7839,  
 to_date('2-4-1981','dd-mm-yyyy'),  
 2975, null, 20  
)

1 row(s) inserted.

Statement 11

insert into emp  
values(  
 7788, 'SCOTT', 'ANALYST', 7566,  
 to_date('13-JUL-87','dd-mm-rr') - 85,  
 3000, null, 20  
)

1 row(s) inserted.

Statement 12

insert into emp  
values(  
 7902, 'FORD', 'ANALYST', 7566,  
 to_date('3-12-1981','dd-mm-yyyy'),  
 3000, null, 20  
)

1 row(s) inserted.

Statement 13

insert into emp  
values(  
 7369, 'SMITH', 'CLERK', 7902,  
 to_date('17-12-1980','dd-mm-yyyy'),  
 800, null, 20  
)

1 row(s) inserted.

Statement 14

insert into emp  
values(  
 7499, 'ALLEN', 'SALESMAN', 7698,  
 to_date('20-2-1981','dd-mm-yyyy'),  
 1600, 300, 30  
)

1 row(s) inserted.

Statement 15

insert into emp  
values(  
 7521, 'WARD', 'SALESMAN', 7698,  
 to_date('22-2-1981','dd-mm-yyyy'),  
 1250, 500, 30  
)

1 row(s) inserted.

Statement 16

insert into emp  
values(  
 7654, 'MARTIN', 'SALESMAN', 7698,  
 to_date('28-9-1981','dd-mm-yyyy'),  
 1250, 1400, 30  
)

1 row(s) inserted.

Statement 17

insert into emp  
values(  
 7844, 'TURNER', 'SALESMAN', 7698,  
 to_date('8-9-1981','dd-mm-yyyy'),  
 1500, 0, 30  
)

1 row(s) inserted.

Statement 18

insert into emp  
values(  
 7876, 'ADAMS', 'CLERK', 7788,  
 to_date('13-JUL-87', 'dd-mm-rr') - 51,  
 1100, null, 20  
)

1 row(s) inserted.

Statement 19

insert into emp  
values(  
 7900, 'JAMES', 'CLERK', 7698,  
 to_date('3-12-1981','dd-mm-yyyy'),  
 950, null, 30  
)

1 row(s) inserted.

Statement 20

insert into emp  
values(  
 7934, 'MILLER', 'CLERK', 7782,  
 to_date('23-1-1982','dd-mm-yyyy'),  
 1300, null, 10  
)

1 row(s) inserted.

Statement 21

Simple natural join between DEPT and EMP tables based on the primary key of the DEPT table DEPTNO, and the DEPTNO foreign key in the EMP table.

select ename, dname, job, empno, hiredate, loc   
from emp, dept   
where emp.deptno = dept.deptno   
order by ename

ENAME	DNAME	JOB	EMPNO	HIREDATE	LOC
ADAMS	RESEARCH	CLERK	7876	23-MAY-87	DALLAS
ALLEN	SALES	SALESMAN	7499	20-FEB-81	CHICAGO
BLAKE	SALES	MANAGER	7698	01-MAY-81	CHICAGO
CLARK	ACCOUNTING	MANAGER	7782	09-JUN-81	NEW YORK
FORD	RESEARCH	ANALYST	7902	03-DEC-81	DALLAS
JAMES	SALES	CLERK	7900	03-DEC-81	CHICAGO
JONES	RESEARCH	MANAGER	7566	02-APR-81	DALLAS
KING	ACCOUNTING	PRESIDENT	7839	17-NOV-81	NEW YORK
MARTIN	SALES	SALESMAN	7654	28-SEP-81	CHICAGO
MILLER	ACCOUNTING	CLERK	7934	23-JAN-82	NEW YORK
SCOTT	RESEARCH	ANALYST	7788	19-APR-87	DALLAS
SMITH	RESEARCH	CLERK	7369	17-DEC-80	DALLAS
TURNER	SALES	SALESMAN	7844	08-SEP-81	CHICAGO
WARD	SALES	SALESMAN	7521	22-FEB-81	CHICAGO

14 rows selected.

Statement 22
The GROUP BY clause in the SQL statement allows aggregate functions of non grouped columns. The join is an inner join thus departments with no employees are not displayed.
```
select dname, count(*) count_of_employees 
from dept, emp 
where dept.deptno = emp.deptno 
group by DNAME 
order by 2 desc
```
DNAME COUNT_OF_EMPLOYEES
SALES 6
RESEARCH 5
ACCOUNTING 3

3 rows selected.

DNAME	COUNT_OF_EMPLOYEES
SALES	6
RESEARCH	5
ACCOUNTING	3

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database