|
REM ********************************************************************
REM Create the EMPLOYEES table to hold the employee personnel
REM information for the company.
REM HR.EMPLOYEES has a self referencing foreign key to this table.
Prompt ****** Creating EMPLOYEES table ....
CREATE TABLE employees
( employee_id NUMBER(6)
,first_name VARCHAR2(20)
,last_name VARCHAR2(25)
CONSTRAINT emp_last_name_nn NOT NULL
,email VARCHAR2(25)
CONSTRAINT emp_email_nn NOT NULL
,phone_number VARCHAR2(20)
,hire_date DATE
CONSTRAINT emp_hire_date_nn NOT NULL
,job_id VARCHAR2(10)
CONSTRAINT emp_job_nn NOT NULL
,salary NUMBER(8,2)
,commission_pct NUMBER(2,department_id NUMBER(4)
,CONSTRAINT emp_salary_min
CHECK (salary > 0)
,CONSTRAINT emp_email_uk
UNIQUE (email)
) ;
CREATE UNIQUE INDEX emp_emp_id_pk
ON employees (employee_id) ;
ALTER TABLE employees
ADD ( CONSTRAINT emp_emp_id_pk
PRIMARY KEY (employee_id)
,CONSTRAINT emp_dept_fk
FOREIGN KEY (department_id)
REFERENCES departments
,CONSTRAINT emp_job_fk
FOREIGN KEY (job_id)
REFERENCES jobs (job_id)
,CONSTRAINT emp_manager_fk
FOREIGN KEY (manager_id)
REFERENCES employees
) ;
ALTER TABLE departments
ADD ( CONSTRAINT dept_mgr_fk
FOREIGN KEY (manager_id)
REFERENCES employees (employee_id)
) ;
Rem Useful for any subsequent addition of rows to employees table
Rem Starts with 207
CREATE SEQUENCE employees_seq
START WITH 207
INCREMENT BY 1
NOCACHE
NOCYCLE;
REM ********************************************************************
REM Create the JOB_HISTORY table to hold the history of jobs that
REM employees have held in the past.
REM HR.JOBS,HR_DEPARTMENTS,and HR.EMPLOYEES have a foreign key to this table.
Prompt ****** Creating JOB_HISTORY table ....
CREATE TABLE job_history
( employee_id NUMBER(6)
CONSTRAINT jhist_employee_nn NOT NULL
,start_date DATE
CONSTRAINT jhist_start_date_nn NOT NULL
,end_date DATE
CONSTRAINT jhist_end_date_nn NOT NULL
,job_id VARCHAR2(10)
CONSTRAINT jhist_job_nn NOT NULL
,CONSTRAINT jhist_date_interval
CHECK (end_date > start_date)
) ;
CREATE UNIQUE INDEX jhist_emp_id_st_date_pk
ON job_history (employee_id,start_date) ;
ALTER TABLE job_history
ADD ( CONSTRAINT jhist_emp_id_st_date_pk
PRIMARY KEY (employee_id,start_date)
,CONSTRAINT jhist_job_fk
FOREIGN KEY (job_id)
REFERENCES jobs
,CONSTRAINT jhist_emp_fk
FOREIGN KEY (employee_id)
REFERENCES employees
,CONSTRAINT jhist_dept_fk
FOREIGN KEY (department_id)
REFERENCES departments
) ;
REM ********************************************************************
REM Create the EMP_DETAILS_VIEW that joins the employees,jobs,
REM departments,countries,and locations table to provide details
REM about employees.
Prompt ****** Creating EMP_DETAILS_VIEW view ...
（编辑：PHP编程网 - 湛江站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
