Employees sample database

An org chart: people, departments, salaries and who reports to whom.

2 tables · 42 rows · 12 KB

Tables

departments 5 rows
  • id integer PK
  • name text
  • location text
idnamelocation
1EngineeringSan Francisco
2SalesNew York
3MarketingLos Angeles
employees 37 rows
  • id integer PK
  • first_name text
  • last_name text
  • email text
  • department_id integer → departments
  • salary integer
  • hire_date text
  • manager_id integer → employees
  • title text
idfirst_namelast_nameemaildepartment_idsalaryhire_datemanager_idtitle
1AliceChen[email protected]12500002015-01-15NULLCEO
2RobertMartinez[email protected]21800002016-03-20NULLCEO
3SarahJohnson[email protected]11500002017-06-101VP Engineering

How the tables connect

Try a query

SELECT d.name AS department, COUNT(*) AS people, ROUND(AVG(e.salary)) AS avg_salary
FROM employees e
JOIN departments d ON d.id = e.department_id
GROUP BY d.name
ORDER BY avg_salary DESC;

Use it on your computer

The SQLite file opens with the sqlite3 command-line tool or any SQLite app:

sqlite3 employees.sqlite
.tables

For MySQL, PostgreSQL or SQL Server, run the SQL script instead. It is plain CREATE TABLE and INSERT statements written for SQLite, so another database may need a small change.

29 practice questions on this database

  1. Easy Second Highest Salary Find the Runner-Up Salary Free
  2. Easy Departments With More Than 5 Employees Large Department Report Free
  3. Easy Employees Hired in the Last 90 Days New Hire Onboarding List Free
  4. Easy Employees Without a Manager Employees Without Managers Free
  5. Easy Salaries Over $100,000 Six-Figure Earners Free
  6. Easy Earliest and Latest Date Hiring Date Range Free
  7. Easy Replace NULL With COALESCE Handle Missing Data with COALESCE Free
  8. Easy Salary Bands With CASE WHEN Classify Employees by Salary Tier Free
  9. Easy Conditional Count With SUM and CASE Conditional Counting with SUM+CASE Free
  10. Easy UPDATE Rows With a Condition Engineering Gets a Raise Free
  11. Easy WHERE vs HAVING Filter Rows and Groups With WHERE and HAVING Free
  12. Medium Top 3 Salaries per Department Identify Top 3 Earners per Department Premium
  13. Medium Employee and Manager Self Join Employee Reporting Structure Premium
  14. Medium 90th Percentile Salary Calculate 90th Percentile Salary Free
  15. Medium Employees Hired on the Same Day Find Employees Hired on Same Day Premium
  16. Medium Salary vs Department Average Compare Employee Salary to Department Average Premium
  17. Medium UPDATE With CASE Tiered Annual Bonus Premium
  18. Medium Reassign Rows With UPDATE The Manager Moved On Premium
  19. Medium Nth Highest Salary Find the Nth Highest Salary Free
  20. Medium Highest Salary in Each Department Find the Highest Salary in Each Department Free
  21. Medium Employees Who Are Managers Find Employees Who Manage Someone Free
  22. Medium Employees With the Same Salary Find Employees Who Share a Salary Free
  23. Medium Second Highest Salary per Department Second Highest Salary in Each Department Free
  24. Medium Department With the Most Employees Departments With the Most Recent Hires Free
  25. Hard Median Without a MEDIAN Function Department Salary Median (No MEDIAN Function) Premium
  26. Hard Org Chart Hierarchy With a Recursive CTE Full Organizational Hierarchy Depth Premium
  27. Hard Cumulative Distribution Cumulative Salary Distribution Premium
  28. Hard Fibonacci With a Recursive CTE Generate Fibonacci Sequence with SQL Premium
  29. Hard SAVEPOINT and ROLLBACK TO The Reversible Experiment Premium

Practice on your iPhone

Query Dojo for iPhone has 85 interview questions, timed mock interviews and a tile builder, so you can write queries without a keyboard.

Get the app

More sample databases