Employee database (Firebird): schema, tables and SQL exercises

Employee is the sample database that comes with Firebird: employees, departments, jobs, projects, customers and sales of a small company. On SQLtest.online you can query it right in your browser: solve exercises with automatic checking and run your own queries in the playground, with nothing to install.

• 10 tables and 1 view
• 42 employees
• 21 departments
• 33 SQL exercises

What is Employee

Employee is the classic example database of Firebird, inherited from InterBase. It describes a small international company: its department tree, staff and salary history, projects with budgets, and sales to customers.

The database is small, so results are easy to check by eye, but it has interesting relationships: a department hierarchy, a composite foreign key from employees to jobs and many-to-many links between employees and projects. It is also the place to practice the Firebird SQL dialect.

ER diagram

The diagram shows the Employee tables and the foreign keys between them. Click it to open the full-size version.

What's inside

The tables fall into three groups.

The key thing to remember: a job is identified by three columns at once (code, grade and country), so joining EMPLOYEE to JOB needs all three. The view PHONE_LIST combines employees with their department phone numbers.

How much data the tables hold:

Table structure

Click a table to see its columns, a sample row and its keys.

Sample queries

These queries show how the data is connected. Copy any of them and run it in the playground.

An employee, department and job: a join on a composite key of three columns.

SELECT FIRST 3 e.FIRST_NAME, e.LAST_NAME, d.DEPARTMENT, j.JOB_TITLE
FROM EMPLOYEE e
JOIN DEPARTMENT d ON d.DEPT_NO = e.DEPT_NO
JOIN JOB j ON j.JOB_CODE = e.JOB_CODE
          AND j.JOB_GRADE = e.JOB_GRADE
          AND j.JOB_COUNTRY = e.JOB_COUNTRY
ORDER BY e.EMP_NO;

Orders and customers: Firebird uses FIRST n to limit rows.

SELECT FIRST 3 s.PO_NUMBER, c.CUSTOMER, s.ORDER_DATE, s.TOTAL_VALUE
FROM SALES s
JOIN CUSTOMER c ON c.CUST_NO = s.CUST_NO
ORDER BY s.ORDER_DATE;

SQL exercises by topic

There are 33 exercises on the Employee database, from simple selections to window functions and data changes. Solutions are checked automatically on a real Firebird server. The number on the right is how many exercises a topic has; the colored dots show its difficulty range.

• SQL Basics 3
• Aggregation Functions 3
• Window Functions 2
• Analytical queries 1
• Data manipulation queries (DML) 1

Where to start

The first exercises on the Employee database:

1. List Departments
2. Find non-Dollar/Euro countries
3. Sub-departments List (JOIN)
4. List of Sub-Departments
5. Identify Foreign Employees
6. Find Employees by Department
7. Retrieve Employee Salary
8. Employees with High Salaries
9. Employees with Above-Average Salaries
10. Find the Managed Department

All Employee exercises →