Database reference - HR

HR -  tables -  HR.EMPLOYEES

Description

employees table. Contains 107 rows. References with departments, jobs, job_history tables. Contains a self reference.

Table properties

namevalue
nameHR.EMPLOYEES 
tablespaceUSERS 
statusVALID 
percent free10 
loggingYES 
backed up
rows107 
blocks
average row length68 

Columns

columndatatypedefaultnullsPKFKUQcomment
EMPLOYEE_ID  NUMBER (6,0)     YES    YES  Primary key of employees table. 
FIRST_NAME  VARCHAR2 (20)           First name of the employee. A not null column. 
LAST_NAME  VARCHAR2 (25)           Last name of the employee. A not null column. 
EMAIL  VARCHAR2 (25)         YES  Email id of the employee 
PHONE_NUMBER  VARCHAR2 (20)           Phone number of the employee; includes country code and area code 
HIRE_DATE  DATE          Date when the employee started on this job. A not null column. 
JOB_ID  VARCHAR2 (10)       HR.JOBS.JOB_ID    Current job of the employee; foreign key to job_id column of the jobs table. A not null column. 
SALARY  NUMBER (8,2)           Monthly salary of the employee. Must be greater than zero (enforced by constraint emp_salary_min) 
COMMISSION_PCT  NUMBER (2,2)           Commission percentage of the employee; Only employees in sales department elgible for commission percentage 
MANAGER_ID  NUMBER (6,0)       HR.EMPLOYEES.EMPLOYEE_ID    Manager id of the employee; has same domain as manager_id in departments table. Foreign key to employee_id column of employees table. (useful for reflexive joins and CONNECT BY query) 
DEPARTMENT_ID  NUMBER (4,0)       HR.DEPARTMENTS.DEPARTMENT_ID    Department id where employee works; foreign key to department_id column of the departments table 

Indexes

namecolumnsuniquetype
EMP_DEPARTMENT_IXDEPARTMENT_ID  no  NORMAL 
EMP_EMAIL_UKEMAIL  yes  NORMAL 
EMP_EMP_ID_PKEMPLOYEE_ID  yes  NORMAL 
EMP_JOB_IXJOB_ID  no  NORMAL 
EMP_MANAGER_IXMANAGER_ID  no  NORMAL 
EMP_NAME_IXLAST_NAME, FIRST_NAME  no  NORMAL 

References

name
DEPARTMENTS
EMPLOYEES
JOBS

Referenced by

name
DEPARTMENTS
EMPLOYEES
JOB_HISTORY

Foreign key graph

DEPARTMENTSEMPLOYEESJOBSEMPLOYEESDEPARTMENTSEMPLOYEESJOB_HISTORY

Foreign keys

namecolumns
EMP_DEPT_FKDEPARTMENT_ID 
EMP_JOB_FKJOB_ID 
EMP_MANAGER_FKMANAGER_ID 

Check constraints

namecolumn
EMP_SALARY_MINSALARY 
EMP_JOB_NNJOB_ID 
EMP_HIRE_DATE_NNHIRE_DATE 
EMP_EMAIL_NNEMAIL 
EMP_LAST_NAME_NNLAST_NAME 

Triggers

namedescription
SECURE_EMPLOYEESsecure_employees BEFORE INSERT OR UPDATE OR DELETE ON employees 
UPDATE_JOB_HISTORYupdate_job_history AFTER UPDATE OF job_id, department_id ON employees FOR EACH ROW 

Dependency graph

Objects that depend on HR.EMPLOYEES

nameobject typelevel
EMPLOYEES_MVmaterialized view1
EMP_MGMTpackage1
REMOVE_EMPstored procedure2
REMOVE_DEPTstored procedure2
INCREASE_SALstored procedure2
INCREASE_COMMstored procedure2
HIREstored procedure2
CREATE_DEPTstored procedure2
EMP_MGMT_TWOpackage1
REMOVE_EMPstored procedure2
REMOVE_DEPTstored procedure2
INCREASE_SALstored procedure2
INCREASE_COMMstored procedure2
HIREstored procedure2
CREATE_DEPTstored procedure2
SECURE_EMPLOYEEStrigger1
UPDATE_JOB_HISTORYtrigger1
EMP_DETAILS_VIEWview1

Sample rows

EMPLOYEE_IDFIRST_NAMELAST_NAMEEMAILPHONE_NUMBERHIRE_DATEJOB_IDSALARYCOMMISSION_PCTMANAGER_IDDEPARTMENT_ID
100  Steven  King  SKING  515.123.4567  6/17/1987 12:00:00 AM  AD_PRES  24000      90 
101  Neena  Kochhar  NKOCHHAR  515.123.4568  9/21/1989 12:00:00 AM  AD_VP  17000    100  90 
102  Lex  De Haan  LDEHAAN  515.123.4569  1/13/1993 12:00:00 AM  AD_VP  17000    100  90 
103  Alexander  Hunold  AHUNOLD  590.423.4567  1/3/1990 12:00:00 AM  IT_PROG  9000    102  60 
104  Bruce  Ernst  BERNST  590.423.4568  5/21/1991 12:00:00 AM  IT_PROG  6000    103  60 
105  David  Austin  DAUSTIN  590.423.4569  6/25/1997 12:00:00 AM  IT_PROG  4800    103  60 
106  Valli  Pataballa  VPATABAL  590.423.4560  2/5/1998 12:00:00 AM  IT_PROG  4800    103  60 
107  Diana  Lorentz  DLORENTZ  590.423.5567  2/7/1999 12:00:00 AM  IT_PROG  4200    103  60 
108  Nancy  Greenberg  NGREENBE  515.124.4569  8/17/1994 12:00:00 AM  FI_MGR  12000    101  100 
109  Daniel  Faviet  DFAVIET  515.124.4169  8/16/1994 12:00:00 AM  FI_ACCOUNT  9000    108  100 

Code

  CREATE TABLE "HR"."EMPLOYEES" 
   (    "EMPLOYEE_ID" NUMBER(6,0), 
    "FIRST_NAME" VARCHAR2(20), 
    "LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE, 
    "EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE, 
    "PHONE_NUMBER" VARCHAR2(20), 
    "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE, 
    "JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE, 
    "SALARY" NUMBER(8,2), 
    "COMMISSION_PCT" NUMBER(2,2), 
    "MANAGER_ID" NUMBER(6,0), 
    "DEPARTMENT_ID" NUMBER(4,0), 
     CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE, 
     CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE, 
     CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE, 
     CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID")
      REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE, 
     CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID")
      REFERENCES "HR"."JOBS" ("JOB_ID") ENABLE, 
     CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID")
      REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" 
 



Documentation generated by SqlSpec