REM   Script: EMP and DEPT
REM   Example EMP and DEPT tables.  Classic Oracle tables with 4 departments and 14 employees.  Includes a join query example.


DROP TABLE DEPT;
DROP TABLE EMP;
DROP TABLE emp_column_update;
DROP TABLE emp_update_data;

-- 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)  
);

-- 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)  
);

-- update_column_selection_mask = 1 -- will update emp.ename column
-- update_column_selection_mask = 2 -- will update emp.job column
-- update_column_selection_mask = 4 -- will update emp.mgr column
-- update_column_selection_mask = 8 -- will update emp.hireate column
-- update_column_selection_mask = 16 -- will update emp.sal column
-- update_column_selection_mask = 32 -- will update emp.comm column
-- update_column_selection_mask = 64 -- will update emp.deptno column
create table emp_column_update ( 
emp_ptr primary key references emp,
update_column_selection_mask  number(3,0)
);

create table emp_update_data(  
  emp_update_ptr primary key references emp, 
  ename    varchar2(10),  
  job      varchar2(9),  
  mgr      number(4,0),  
  hiredate date,  
  sal      number(7,2),  
  comm     number(7,2),  
  deptno   number(2,0) 
);

-- Insert row into DEPT table using named columns.
insert into DEPT (DEPTNO, DNAME, LOC)
values(10, 'ACCOUNTING', 'NEW YORK');

-- Insert a row into DEPT table by column position.
insert into dept  
values(20, 'RESEARCH', 'DALLAS');

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

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

-- 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  
);

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

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

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

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

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

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

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

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

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

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

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

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

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

-- Insert row into emp_column_update table using named columns.
insert into emp_column_update (emp_ptr, update_column_selection_mask)
values(7839, 10);
insert into emp_column_update (emp_ptr, update_column_selection_mask)
values(7698, 96);
insert into emp_column_update (emp_ptr, update_column_selection_mask)
values(7782, 40);

insert into emp_update_data  
values(  
 7839, NULL, 'CHAIRMAN', null,  
 to_date('17-11-1991','dd-mm-yyyy'),  
 NULL, null, NULL  
);

insert into emp_update_data  
values(  
 7698, NULL, NULL, NULL,  
 NULL,  
 NULL, 1000, 20  
);

insert into emp_update_data  
values(  
 7782, NULL, NULL, NULL,  
 to_date('9-6-1990','dd-mm-yyyy'),  
 NULL, 2000, NULL  
);
-- 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;

-- 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;;

select empno, ename, job,  hiredate, update_column_selection_mask 
from emp, emp_column_update   
where emp.empno = emp_column_update.emp_ptr   
order by empno;