Employees sample database for SQL Server
A ready-to-run SQL Server script with an HR table: departments, titles, hire dates, salaries and managers. Tested on SQL Server 2022 16.0.4325.4. Updated 19 November 2026.
Employees database for SQL Server
Each row is one fictional employee: department, job title, hire date, salary, office, manager and performance rating. The first eight rows are department heads; everyone else reports to a manager_id in the file.
This page has it as a SQL Server script: one table and 1,000 rows, with column types chosen for SQL Server, primary keys. The script drops the table if it exists, creates it and inserts the rows in one transaction, so you can run it again to reset the data. It is the same fictional data, seed 42, as on the employees dataset page, where it is also available as CSV and Excel.
We loaded this exact file into SQL Server 2022 16.0.4325.4 and ran every question below on it; the answers shown are that output. The same questions, written for SQLite, PostgreSQL, MySQL, SQL Server and DuckDB, return the same results on all five.
Load it
sqlcmd
sqlcmd -S localhost -Q "CREATE DATABASE practice"
sqlcmd -S localhost -d practice -i employees-sqlserver.sqlSSMS or Azure Data Studio
Create a database, open the .sql file in a new query window connected to it, and press Execute.
Tables and columns
employees 1,000 rows
| Column | SQL Server type |
|---|---|
| employee_id | NVARCHAR(10) |
| first_name | NVARCHAR(20) |
| last_name | NVARCHAR(20) |
| NVARCHAR(40) | |
| department | NVARCHAR(20) |
| job_title | NVARCHAR(30) |
| hire_date | DATE |
| salary | INT |
| location | NVARCHAR(10) |
| manager_id | NVARCHAR(10) |
| employment_type | NVARCHAR(20) |
| performance_rating | INT |
Practice questions
answers from SQL Server 2022 16.0.4325.4
How many employees and what average salary does each department have?
SQL
SELECT department, COUNT(*) AS employees, ROUND(AVG(CAST(salary AS DECIMAL(18,4))), 2) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC, department;AVG of an INT column returns an INT in SQL Server, so the column is cast to DECIMAL first.
Result
| department | employees | avg_salary |
|---|---|---|
| Engineering | 231 | 118,499.13 |
| Finance | 98 | 85,516.33 |
| IT | 73 | 79,419.18 |
| Marketing | 92 | 73,951.09 |
| Sales | 157 | 71,821.02 |
| Human Resources | 54 | 70,888.89 |
| Operations | 138 | 70,050.00 |
| Customer Support | 157 | 65,672.61 |
Who are the 5 highest-paid employees?
SQL
SELECT TOP 5 employee_id, first_name, last_name, department, salary
FROM employees
ORDER BY salary DESC, employee_id;SELECT TOP n ... ORDER BY returns the first n rows; SQL Server has no LIMIT.
Result
| employee_id | first_name | last_name | department | salary |
|---|---|---|---|---|
| E1002 | Priya | Khan | Engineering | 222800 |
| E1482 | Wei | Kim | Engineering | 185700 |
| E1195 | James | Khan | Engineering | 184100 |
| E1875 | Karen | Martinez | Engineering | 180900 |
| E1092 | James | Young | Engineering | 179600 |
Show each of the first 8 employees (by id) next to their manager's name. (self-join)
SQL
SELECT TOP 8 e.employee_id, CONCAT(e.first_name, ' ', e.last_name) AS employee,
CASE WHEN m.employee_id IS NOT NULL THEN CONCAT(m.first_name, ' ', m.last_name) END AS manager
FROM employees e
LEFT JOIN employees m ON m.employee_id = e.manager_id
ORDER BY e.employee_id;CONCAT(a, b, ...) joins strings and treats NULL as an empty string.
Result
| employee_id | employee | manager |
|---|---|---|
| E1001 | Patricia Anderson | |
| E1002 | Priya Khan | |
| E1003 | Thomas Robinson | |
| E1004 | Kimberly Lopez | |
| E1005 | Emma Jackson | |
| E1006 | Joseph Johnson | |
| E1007 | Priya Gonzalez | |
| E1008 | Mary Moore |
How many people were hired each year since 2020?
SQL
SELECT YEAR(hire_date) AS hire_year, COUNT(*) AS hires
FROM employees
WHERE YEAR(hire_date) >= 2020
GROUP BY YEAR(hire_date)
ORDER BY hire_year;YEAR(date) returns the year as a number.
Result
| hire_year | hires |
|---|---|
| 2020 | 75 |
| 2021 | 74 |
| 2022 | 87 |
| 2023 | 77 |
| 2024 | 83 |
Who is the highest-paid person in each department? (window function)
SQL
WITH r AS (
SELECT department, employee_id, first_name, last_name, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC, employee_id) AS rn
FROM employees
)
SELECT department, employee_id, first_name, last_name, salary
FROM r
WHERE rn = 1
ORDER BY department;RANK() and ROW_NUMBER() OVER (PARTITION BY ...) work in SQL Server 2012 and later.
Result
| department | employee_id | first_name | last_name | salary |
|---|---|---|---|---|
| Customer Support | E1007 | Priya | Gonzalez | 110700 |
| Engineering | E1002 | Priya | Khan | 222800 |
| Finance | E1004 | Kimberly | Lopez | 162000 |
| Human Resources | E1005 | Emma | Jackson | 132300 |
| IT | E1008 | Mary | Moore | 159300 |
| Marketing | E1003 | Thomas | Robinson | 141800 |
| Operations | E1006 | Joseph | Johnson | 137700 |
| Sales | E1001 | Patricia | Anderson | 132300 |
How does average salary change with performance rating?
SQL
SELECT performance_rating, COUNT(*) AS employees, ROUND(AVG(CAST(salary AS DECIMAL(18,4))), 2) AS avg_salary
FROM employees
GROUP BY performance_rating
ORDER BY performance_rating;AVG of an INT column returns an INT in SQL Server, so the column is cast to DECIMAL first.
Result
| performance_rating | employees | avg_salary |
|---|---|---|
| 1 | 33 | 72,330.30 |
| 2 | 131 | 88,623.66 |
| 3 | 447 | 81,907.61 |
| 4 | 278 | 82,866.55 |
| 5 | 111 | 88,200.00 |
SQL Server differences used here
- AVG of an INT column returns an INT in SQL Server, so the column is cast to DECIMAL first.
- CONCAT(a, b, ...) joins strings and treats NULL as an empty string.
- SELECT TOP n ... ORDER BY returns the first n rows; SQL Server has no LIMIT.
- RANK() and ROW_NUMBER() OVER (PARTITION BY ...) work in SQL Server 2012 and later.
- YEAR(date) returns the year as a number.