DataBrighten

    Employees sample database for MySQL

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

    A ready-to-run MySQL script with an HR table: departments, titles, hire dates, salaries and managers. Tested on MySQL 8.4.13. Updated 19 November 2026.

    textnumberdateyes/no

    Employees database for MySQL

    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 MySQL script: one table and 1,000 rows, with column types chosen for MySQL, 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 MySQL 8.4.13 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

    mysql client

    mysql -u root -p -e "CREATE DATABASE practice"
    mysql -u root -p practice < employees-mysql.sql

    MySQL Workbench

    File > Open SQL Script, choose the database with USE practice; then Execute.

    Tables and columns

    employees 1,000 rows

    ColumnMySQL type
    employee_idVARCHAR(10)
    first_nameVARCHAR(20)
    last_nameVARCHAR(20)
    emailVARCHAR(40)
    departmentVARCHAR(20)
    job_titleVARCHAR(30)
    hire_dateDATE
    salaryINT
    locationVARCHAR(10)
    manager_idVARCHAR(10)
    employment_typeVARCHAR(20)
    performance_ratingINT

    Practice questions

    answers from MySQL 8.4.13

    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;

    The cast to DECIMAL keeps the average exact before rounding.

    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 employee_id, first_name, last_name, department, salary
    FROM employees
    ORDER BY salary DESC, employee_id
    LIMIT 5;

    LIMIT n at the end returns the first n rows.

    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 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
    LIMIT 8;

    CONCAT(a, b, ...) joins strings; || is not string concatenation by default.

    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;

    Window functions need MySQL 8.0 or 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;

    The cast to DECIMAL keeps the average exact before rounding.

    Result

    performance_ratingemployeesavg_salary
    13372,330.30
    213188,623.66
    344781,907.61
    427882,866.55
    511188,200.00

    MySQL differences used here

    • The cast to DECIMAL keeps the average exact before rounding.
    • CONCAT(a, b, ...) joins strings; || is not string concatenation by default.
    • LIMIT n at the end returns the first n rows.
    • Window functions need MySQL 8.0 or later.
    • YEAR(date) returns the year as a number.