Difference between revisions of "HR.EMPLOYEES (table)"
| Line 2: | Line 2: | ||
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | ||
| − | |- | + | |- valign="top" |
| '''Table | | '''Table | ||
| HR.EMPLOYEES | | HR.EMPLOYEES | ||
| Line 9: | Line 9: | ||
| employees table. Contains 107 rows. References with departments, | | employees table. Contains 107 rows. References with departments, | ||
jobs, job_history tables. Contains a self reference. | jobs, job_history tables. Contains a self reference. | ||
| − | |||
|} | |} | ||
| − | |||
=== Columns === | === Columns === | ||
| Line 21: | Line 19: | ||
| '''Default | | '''Default | ||
| '''Description | | '''Description | ||
| − | + | ||
| − | |- | + | |- valign="top" |
| EMPLOYEE_ID | | EMPLOYEE_ID | ||
| NUMBER(6, 0) | | NUMBER(6, 0) | ||
| Line 28: | Line 26: | ||
| | | | ||
| Primary key of employees table. | | Primary key of employees table. | ||
| − | |- | + | |- valign="top" |
| FIRST_NAME | | FIRST_NAME | ||
| VARCHAR2(20) | | VARCHAR2(20) | ||
| Line 34: | Line 32: | ||
| | | | ||
| First name of the employee. A not null column. | | First name of the employee. A not null column. | ||
| − | |- | + | |- valign="top" |
| LAST_NAME | | LAST_NAME | ||
| VARCHAR2(25) | | VARCHAR2(25) | ||
| Line 40: | Line 38: | ||
| | | | ||
| Last name of the employee. A not null column. | | Last name of the employee. A not null column. | ||
| − | |- | + | |- valign="top" |
| EMAIL | | EMAIL | ||
| VARCHAR2(25) | | VARCHAR2(25) | ||
| Line 46: | Line 44: | ||
| | | | ||
| Email id of the employee | | Email id of the employee | ||
| − | |- | + | |- valign="top" |
| PHONE_NUMBER | | PHONE_NUMBER | ||
| VARCHAR2(20) | | VARCHAR2(20) | ||
| Line 52: | Line 50: | ||
| | | | ||
| Phone number of the employee; includes country code and area code | | Phone number of the employee; includes country code and area code | ||
| − | |- | + | |- valign="top" |
| HIRE_DATE | | HIRE_DATE | ||
| DATE(7) | | DATE(7) | ||
| Line 58: | Line 56: | ||
| | | | ||
| Date when the employee started on this job. A not null column. | | Date when the employee started on this job. A not null column. | ||
| − | |- | + | |- valign="top" |
| JOB_ID | | JOB_ID | ||
| VARCHAR2(10) | | VARCHAR2(10) | ||
| Line 65: | Line 63: | ||
| Current job of the employee; foreign key to job_id column of the | | Current job of the employee; foreign key to job_id column of the | ||
jobs table. A not null column. | jobs table. A not null column. | ||
| − | |- | + | |- valign="top" |
| SALARY | | SALARY | ||
| NUMBER(8, 2) | | NUMBER(8, 2) | ||
| Line 72: | Line 70: | ||
| Monthly salary of the employee. Must be greater | | Monthly salary of the employee. Must be greater | ||
than zero (enforced by constraint emp_salary_min) | than zero (enforced by constraint emp_salary_min) | ||
| − | |- | + | |- valign="top" |
| COMMISSION_PCT | | COMMISSION_PCT | ||
| NUMBER(2, 2) | | NUMBER(2, 2) | ||
| Line 79: | Line 77: | ||
| Commission percentage of the employee; Only employees in sales | | Commission percentage of the employee; Only employees in sales | ||
department elgible for commission percentage | department elgible for commission percentage | ||
| − | |- | + | |- valign="top" |
| MANAGER_ID | | MANAGER_ID | ||
| NUMBER(6, 0) | | NUMBER(6, 0) | ||
| Line 87: | Line 85: | ||
departments table. Foreign key to employee_id column of employees table. | departments table. Foreign key to employee_id column of employees table. | ||
(useful for reflexive joins and CONNECT BY query) | (useful for reflexive joins and CONNECT BY query) | ||
| − | |- | + | |- valign="top" |
| DEPARTMENT_ID | | DEPARTMENT_ID | ||
| NUMBER(4, 0) | | NUMBER(4, 0) | ||
| Line 101: | Line 99: | ||
| '''Primary Key | | '''Primary Key | ||
| '''Columns | | '''Columns | ||
| − | |- | + | |- valign="top" |
| EMP_EMP_ID_PK | | EMP_EMP_ID_PK | ||
| EMPLOYEE_ID | | EMPLOYEE_ID | ||
| Line 112: | Line 110: | ||
| '''Type | | '''Type | ||
| '''Columns | | '''Columns | ||
| − | + | ||
| − | |- | + | |- valign="top" |
| EMP_EMAIL_UK | | EMP_EMAIL_UK | ||
| Unique | | Unique | ||
| EMAIL | | EMAIL | ||
| − | |- | + | |- valign="top" |
| EMP_MANAGER_IX | | EMP_MANAGER_IX | ||
| | | | ||
| MANAGER_ID | | MANAGER_ID | ||
| − | |- | + | |- valign="top" |
| EMP_JOB_IX | | EMP_JOB_IX | ||
| | | | ||
| JOB_ID | | JOB_ID | ||
| − | |- | + | |- valign="top" |
| EMP_DEPARTMENT_IX | | EMP_DEPARTMENT_IX | ||
| | | | ||
| DEPARTMENT_ID | | DEPARTMENT_ID | ||
| − | |- | + | |- valign="top" |
| EMP_NAME_IX | | EMP_NAME_IX | ||
| | | | ||
| Line 141: | Line 139: | ||
| '''Expression | | '''Expression | ||
| '''Description | | '''Description | ||
| − | + | ||
| − | |- | + | |- valign="top" |
| EMP_EMAIL_NN | | EMP_EMAIL_NN | ||
| "EMAIL" IS NOT NULL | | "EMAIL" IS NOT NULL | ||
| | | | ||
| − | |- | + | |- valign="top" |
| EMP_HIRE_DATE_NN | | EMP_HIRE_DATE_NN | ||
| "HIRE_DATE" IS NOT NULL | | "HIRE_DATE" IS NOT NULL | ||
| | | | ||
| − | |- | + | |- valign="top" |
| EMP_JOB_NN | | EMP_JOB_NN | ||
| "JOB_ID" IS NOT NULL | | "JOB_ID" IS NOT NULL | ||
| | | | ||
| − | |- | + | |- valign="top" |
| EMP_LAST_NAME_NN | | EMP_LAST_NAME_NN | ||
| "LAST_NAME" IS NOT NULL | | "LAST_NAME" IS NOT NULL | ||
| | | | ||
| − | |- | + | |- valign="top" |
| EMP_SALARY_MIN | | EMP_SALARY_MIN | ||
| salary > 0 | | salary > 0 | ||
| Line 170: | Line 168: | ||
| '''Column | | '''Column | ||
| '''Referenced Column | | '''Referenced Column | ||
| − | + | ||
| − | |- | + | |- valign="top" |
| [[HR.DEPARTMENTS_(table)|HR.DEPARTMENTS]] | | [[HR.DEPARTMENTS_(table)|HR.DEPARTMENTS]] | ||
| DEPARTMENT_ID | | DEPARTMENT_ID | ||
| DEPARTMENT_ID | | DEPARTMENT_ID | ||
| − | |- | + | |- valign="top" |
| [[HR.JOBS_(table)|HR.JOBS]] | | [[HR.JOBS_(table)|HR.JOBS]] | ||
| JOB_ID | | JOB_ID | ||
| JOB_ID | | JOB_ID | ||
| − | |- | + | |- valign="top" |
| [[HR.EMPLOYEES_(table)|HR.EMPLOYEES]] | | [[HR.EMPLOYEES_(table)|HR.EMPLOYEES]] | ||
| MANAGER_ID | | MANAGER_ID | ||
| Line 191: | Line 189: | ||
| '''Column | | '''Column | ||
| '''Referencing Column | | '''Referencing Column | ||
| − | + | ||
| − | |- | + | |- valign="top" |
| [[OE.CUSTOMERS_(table)|OE.CUSTOMERS]] | | [[OE.CUSTOMERS_(table)|OE.CUSTOMERS]] | ||
| EMPLOYEE_ID | | EMPLOYEE_ID | ||
| ACCOUNT_MGR_ID | | ACCOUNT_MGR_ID | ||
| − | |- | + | |- valign="top" |
| [[HR.DEPARTMENTS_(table)|HR.DEPARTMENTS]] | | [[HR.DEPARTMENTS_(table)|HR.DEPARTMENTS]] | ||
| EMPLOYEE_ID | | EMPLOYEE_ID | ||
| MANAGER_ID | | MANAGER_ID | ||
| − | |- | + | |- valign="top" |
| [[HR.EMPLOYEES_(table)|HR.EMPLOYEES]] | | [[HR.EMPLOYEES_(table)|HR.EMPLOYEES]] | ||
| EMPLOYEE_ID | | EMPLOYEE_ID | ||
| MANAGER_ID | | MANAGER_ID | ||
| − | |- | + | |- valign="top" |
| [[HR.JOB_HISTORY_(table)|HR.JOB_HISTORY]] | | [[HR.JOB_HISTORY_(table)|HR.JOB_HISTORY]] | ||
| EMPLOYEE_ID | | EMPLOYEE_ID | ||
| EMPLOYEE_ID | | EMPLOYEE_ID | ||
| − | |- | + | |- valign="top" |
| [[OE.ORDERS_(table)|OE.ORDERS]] | | [[OE.ORDERS_(table)|OE.ORDERS]] | ||
| EMPLOYEE_ID | | EMPLOYEE_ID | ||
| Line 221: | Line 219: | ||
| '''Referencing Object | | '''Referencing Object | ||
| − | |- | + | |- valign="top" |
| Select | | Select | ||
| View | | View | ||
| [[HR.EMP_DETAILS_VIEW_(view)|HR.EMP_DETAILS_VIEW]] | | [[HR.EMP_DETAILS_VIEW_(view)|HR.EMP_DETAILS_VIEW]] | ||
| − | |- | + | |- valign="top" |
| Select | | Select | ||
| View | | View | ||
Revision as of 07:14, 24 June 2010
Contents
wikibot
| Table | HR.EMPLOYEES |
| Description | employees table. Contains 107 rows. References with departments,
jobs, job_history tables. Contains a self reference. |
Columns
| Column | Data Type | Nullable | Default | Description |
| EMPLOYEE_ID | NUMBER(6, 0) | not null | Primary key of employees table. | |
| FIRST_NAME | VARCHAR2(20) | null | First name of the employee. A not null column. | |
| LAST_NAME | VARCHAR2(25) | not null | Last name of the employee. A not null column. | |
| VARCHAR2(25) | not null | Email id of the employee | ||
| PHONE_NUMBER | VARCHAR2(20) | null | Phone number of the employee; includes country code and area code | |
| HIRE_DATE | DATE(7) | not null | Date when the employee started on this job. A not null column. | |
| JOB_ID | VARCHAR2(10) | not null | Current job of the employee; foreign key to job_id column of the
jobs table. A not null column. | |
| SALARY | NUMBER(8, 2) | null | Monthly salary of the employee. Must be greater
than zero (enforced by constraint emp_salary_min) | |
| COMMISSION_PCT | NUMBER(2, 2) | null | Commission percentage of the employee; Only employees in sales
department elgible for commission percentage | |
| MANAGER_ID | NUMBER(6, 0) | null | 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) | null | Department id where employee works; foreign key to department_id
column of the departments table |
Primary Key
| Primary Key | Columns |
| EMP_EMP_ID_PK | EMPLOYEE_ID |
Indexes
| Index | Type | Columns |
| EMP_EMAIL_UK | Unique | |
| EMP_MANAGER_IX | MANAGER_ID | |
| EMP_JOB_IX | JOB_ID | |
| EMP_DEPARTMENT_IX | DEPARTMENT_ID | |
| EMP_NAME_IX | LAST_NAME, FIRST_NAME |
Check Constraints
| Check Constraint | Expression | Description |
| EMP_EMAIL_NN | "EMAIL" IS NOT NULL | |
| EMP_HIRE_DATE_NN | "HIRE_DATE" IS NOT NULL | |
| EMP_JOB_NN | "JOB_ID" IS NOT NULL | |
| EMP_LAST_NAME_NN | "LAST_NAME" IS NOT NULL | |
| EMP_SALARY_MIN | salary > 0 |
Foreign Keys
| Relation | Column | Referenced Column |
| HR.DEPARTMENTS | DEPARTMENT_ID | DEPARTMENT_ID |
| HR.JOBS | JOB_ID | JOB_ID |
| HR.EMPLOYEES | MANAGER_ID | EMPLOYEE_ID |
Detail Tables
| Detail Table | Column | Referencing Column |
| OE.CUSTOMERS | EMPLOYEE_ID | ACCOUNT_MGR_ID |
| HR.DEPARTMENTS | EMPLOYEE_ID | MANAGER_ID |
| HR.EMPLOYEES | EMPLOYEE_ID | MANAGER_ID |
| HR.JOB_HISTORY | EMPLOYEE_ID | EMPLOYEE_ID |
| OE.ORDERS | EMPLOYEE_ID | SALES_REP_ID |
Dependencies
| Reference Type | Object Type | Referencing Object |
| Select | View | HR.EMP_DETAILS_VIEW |
| Select | View | OE.DEPTVIEW |
