DataBrighten

    Employees sample database for SQL Server

    1,000rows · 1 table · 6 questions with answers

    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.

    textnumberdateyes/no

    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.sql

    SSMS 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

    ColumnSQL Server type
    employee_idNVARCHAR(10)
    first_nameNVARCHAR(20)
    last_nameNVARCHAR(20)
    emailNVARCHAR(40)
    departmentNVARCHAR(20)
    job_titleNVARCHAR(30)
    hire_dateDATE
    salaryINT
    locationNVARCHAR(10)
    manager_idNVARCHAR(10)
    employment_typeNVARCHAR(20)
    performance_ratingINT

    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

    departmentemployeesavg_salary
    Engineering231118,499.13
    Finance9885,516.33
    IT7379,419.18
    Marketing9273,951.09
    Sales15771,821.02
    Human Resources5470,888.89
    Operations13870,050.00
    Customer Support15765,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_idfirst_namelast_namedepartmentsalary
    E1002PriyaKhanEngineering222800
    E1482WeiKimEngineering185700
    E1195JamesKhanEngineering184100
    E1875KarenMartinezEngineering180900
    E1092JamesYoungEngineering179600
    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_idemployeemanager
    E1001Patricia Anderson
    E1002Priya Khan
    E1003Thomas Robinson
    E1004Kimberly Lopez
    E1005Emma Jackson
    E1006Joseph Johnson
    E1007Priya Gonzalez
    E1008Mary 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_yearhires
    202075
    202174
    202287
    202377
    202483
    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

    departmentemployee_idfirst_namelast_namesalary
    Customer SupportE1007PriyaGonzalez110700
    EngineeringE1002PriyaKhan222800
    FinanceE1004KimberlyLopez162000
    Human ResourcesE1005EmmaJackson132300
    ITE1008MaryMoore159300
    MarketingE1003ThomasRobinson141800
    OperationsE1006JosephJohnson137700
    SalesE1001PatriciaAnderson132300
    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_ratingemployeesavg_salary
    13372,330.30
    213188,623.66
    344781,907.61
    427882,866.55
    511188,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.