Employee Database: table structure and overview
The Employee database (Firebird) is a sample dataset used to learn SQL and explore Firebird DBMS capabilities.
This page describes table structure, key columns, and relationships for practical SQL querying.
The Employee database contains 9 main tables.
ER diagram of the Employee database
List of tables
COUNTRY - countries table.
- COUNTRYName of the country
- CURRENCYCurrency used in the country
| COUNTRY |
CURRENCY |
| USA |
Dollar |
JOB - company's staff schedule.
- JOB_CODEJob code
- JOB_GRADEJob grade
- JOB_COUNTRYCountry associated with the job
- JOB_TITLEJob title
- MIN_SALARYMinimum salary for the job
- MAX_SALARYMaximum salary for the job
- JOB_REQUIREMENTJob requirements
- LANGUAGE_REQLanguage requirements
| JOB_CODE |
JOB_GRADE |
JOB_COUNTRY |
JOB_TITLE |
MIN_SALARY |
MAX_SALARY |
JOB_REQUIREMENT |
LANGUAGE_REQ |
| CEO |
1 |
USA |
Chief Executive Officer |
130000.00 |
250000.00 |
No specific requirements. |
[null] |
- FOREIGN KEY (JOB_COUNTRY) REFERENCES COUNTRY(COUNTRY)
DEPARTMENT - company divisions.
- DEPT_NODepartment number
- DEPARTMENTDepartment name
- HEAD_DEPTHead department (can be null)
- MNGR_NOManager number
- BUDGETDepartment budget
- LOCATIONDepartment location
- PHONE_NOPhone number for the department
| DEPT_NO |
DEPARTMENT |
HEAD_DEPT |
MNGR_NO |
BUDGET |
LOCATION |
PHONE_NO |
| 000 |
Corporate Office |
[null] |
105 |
1000000.00 |
Monterey |
(408) 555-1234 |
- FOREIGN KEY (HEAD_DEPT) REFERENCES DEPARTMENT(DEPT_NO)
EMPLOYEE - list of employees.
- EMP_NOEmployee number
- FIRST_NAMEFirst name of the employee
- LAST_NAMELast name of the employee
- PHONE_EXTPhone extension for the employee
- HIRE_DATEDate of employee's hire
- DEPT_NODepartment number
- JOB_CODEJob code for the employee
- JOB_GRADEJob grade for the employee
- JOB_COUNTRYCountry associated with the employee's job
- SALARYSalary of the employee
- FULL_NAMEFull name of the employee
| EMP_NO |
FIRST_NAME |
LAST_NAME |
PHONE_EXT |
HIRE_DATE |
DEPT_NO |
JOB_CODE |
JOB_GRADE |
JOB_COUNTRY |
SALARY |
FULL_NAME |
| 2 |
Robert |
Nelson |
250 |
1988-12-28 00:00:00 |
600 |
VP |
2 |
USA |
105900.00 |
Nelson, Robert |
- FOREIGN KEY (DEPT_NO) REFERENCES DEPARTMENT(DEPT_NO)
- FOREIGN KEY (JOB_CODE) REFERENCES JOB(JOB_CODE)
PROJECT - list of projects.
- PROJ_IDProject ID
- PROJ_NAMEProject name
- PROJ_DESCProject description
- TEAM_LEADERTeam leader for the project
- PRODUCTProduct associated with the project
| PROJ_ID |
PROJ_NAME |
PROJ_DESC |
TEAM_LEADER |
PRODUCT |
| VBASE |
Video Database |
Development of a video database management system for managing video distribution on demand. |
45 |
software |
- FOREIGN KEY (TEAM_LEADER) REFERENCES EMPLOYEE(EMP_NO)
EMPLOYEE_PROJECT - employee-project mapping.
- EMP_NOEmployee number
- PROJ_IDProject ID
- FOREIGN KEY (EMP_NO) REFERENCES EMPLOYEE(EMP_NO)
- FOREIGN KEY (PROJ_ID) REFERENCES PROJECT(PROJ_ID)
PROJ_DEPT_BUDGET - project budgets.
- FISCAL_YEARFiscal year
- PROJ_IDProject ID
- DEPT_NODepartment number
- QUART_HEAD_CNTQuarter headcount (can be null)
- PROJECTED_BUDGETProjected budget for the fiscal year
| FISCAL_YEAR |
PROJ_ID |
DEPT_NO |
QUART_HEAD_CNT |
PROJECTED_BUDGET |
| 1994 |
GUIDE |
100 |
[null] |
200000.00 |
- FOREIGN KEY (PROJ_ID) REFERENCES PROJECT(PROJ_ID)
- FOREIGN KEY (DEPT_NO) REFERENCES DEPARTMENT(DEPT_NO)
SALARY_HISTORY - history of employee salary changes.
- EMP_NOEmployee number
- CHANGE_DATEDate of salary change
- UPDATER_IDUpdater ID
- OLD_SALARYPrevious salary
- PERCENT_CHANGEPercentage change in salary
- NEW_SALARYNew salary after the change
| EMP_NO |
CHANGE_DATE |
UPDATER_ID |
OLD_SALARY |
PERCENT_CHANGE |
NEW_SALARY |
| 28 |
1992-12-15 00:00:00 |
admin2 |
20000.00 |
10.000000 |
22000.000000 |
- FOREIGN KEY (EMP_NO) REFERENCES EMPLOYEE(EMP_NO)
CUSTOMER - company clients.
- CUST_NOCustomer number
- CUSTOMERCustomer name
- CONTACT_FIRSTFirst name of the contact person
- CONTACT_LASTLast name of the contact person
- PHONE_NOPhone number for the customer
- ADDRESS_LINE1 Address line 1
- ADDRESS_LINE2Address line 2 (can be null)
- CITYCity of the customer
- STATE_PROVINCEState or province of the customer
- COUNTRYCountry of the customer
- POSTAL_CODEPostal code of the customer
- ON_HOLDOn hold status (can be null)
| CUST_NO |
CUSTOMER |
CONTACT_FIRST |
CONTACT_LAST |
PHONE_NO |
ADDRESS_LINE1 |
ADDRESS_LINE2 |
CITY |
STATE_PROVINCE |
COUNTRY |
POSTAL_CODE |
ON_HOLD |
| 1001 |
Signature Design |
Dale J. |
Little |
(619) 530-2710 |
15500 Pacific Heights Blvd. |
[null] |
San Diego |
CA |
USA |
92121 |
[null] |
- FOREIGN KEY (COUNTRY) REFERENCES COUNTRY(COUNTRY)
SALES - list of sales.
- PO_NUMBERPurchase order number
- CUST_NOCustomer number associated with the order
- SALES_REPSales representative number
- ORDER_STATUSOrder status
- ORDER_DATEDate of the order
- SHIP_DATEDate of shipment
- DATE_NEEDEDDate needed (can be null)
- PAIDPayment status
- QTY_ORDEREDQuantity ordered
- TOTAL_VALUETotal value of the order
- DISCOUNTDiscount applied
- ITEM_TYPEType of item in the order
- AGEDAged value
| PO_NUMBER |
CUST_NO |
SALES_REP |
ORDER_STATUS |
ORDER_DATE |
SHIP_DATE |
DATE_NEEDED |
PAID |
QTY_ORDERED |
TOTAL_VALUE |
DISCOUNT |
ITEM_TYPE |
AGED |
| V91E0210 |
1004 |
11 |
shipped |
1991-03-04 00:00:00 |
1991-03-05 00:00:00 |
[null] |
y |
10 |
5000.00 |
0.100000 |
hardware |
1.000000000 |
- FOREIGN KEY (CUST_NO) REFERENCES CUSTOMER(CUST_NO)
- FOREIGN KEY (SALES_REP) REFERENCES EMPLOYEE(EMP_NO)
Below is a list of this DB views:
PHONE_LIST - employee phone list view.
- EMP_NOEmployee number
- FIRST_NAMEFirst name of the employee
- LAST_NAMELast name of the employee
- PHONE_EXTPhone extension for the employee
- LOCATIONDepartment location
- PHONE_NODepartment phone number
| EMP_NO |
FIRST_NAME |
LAST_NAME |
PHONE_EXT |
LOCATION |
PHONE_NO |
| 2 |
Robert |
Nelson |
250 |
Monterey |
(408) 555-1234 |
SQL Essentials for Data Analysis: A 50-Day Hands-on Challenge Book by Benjamin Bennett Alexander
If you’re wondering whether you should learn SQL, the answer is simple: absolutely.
SQL (Structured Query Language) is the language of data, and data powers every modern business. Whether you’re a data analyst, data scientist, business analyst, software engineer, financial analyst, product manager, or marketing professional, SQL helps you retrieve, analyze, and understand the information that drives decisions.