DataBrighten

    Employees sample database for PostgreSQL

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

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

    textnumberdateyes/no

    Employees database for PostgreSQL

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

    psql

    createdb practice
    psql -d practice -f employees-postgresql.sql

    pgAdmin or DBeaver

    Create a database, open the Query Tool, open the .sql file and run it.

    Tables and columns

    employees 1,000 rows

    ColumnPostgreSQL type
    employee_idVARCHAR(10)
    first_nameVARCHAR(20)
    last_nameVARCHAR(20)
    emailVARCHAR(40)
    departmentVARCHAR(20)
    job_titleVARCHAR(30)
    hire_dateDATE
    salaryINTEGER
    locationVARCHAR(10)
    manager_idVARCHAR(10)
    employment_typeVARCHAR(20)
    performance_ratingINTEGER

    Practice questions

    answers from PostgreSQL 15.18

    How many employees and what average salary does each department have?

    SQL

    SELECT department, COUNT(*) AS employees, ROUND(AVG(salary), 2) AS avg_salary
    FROM employees
    GROUP BY department
    ORDER BY avg_salary DESC, department;

    AVG of an integer column returns a numeric, so no cast is needed.

    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, e.first_name || ' ' || e.last_name AS employee,
           CASE WHEN m.employee_id IS NOT NULL THEN 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;

    || joins strings.

    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 EXTRACT(YEAR FROM hire_date)::int AS hire_year, COUNT(*) AS hires
    FROM employees
    WHERE EXTRACT(YEAR FROM hire_date)::int >= 2020
    GROUP BY EXTRACT(YEAR FROM hire_date)::int
    ORDER BY hire_year;

    EXTRACT(YEAR FROM date) returns a number; ::int casts it to integer.

    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 on any supported version.

    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(salary), 2) AS avg_salary
    FROM employees
    GROUP BY performance_rating
    ORDER BY performance_rating;

    AVG of an integer column returns a numeric, so no cast is needed.

    Result

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

    PostgreSQL differences used here

    • AVG of an integer column returns a numeric, so no cast is needed.
    • || joins strings.
    • LIMIT n at the end returns the first n rows.
    • RANK() and ROW_NUMBER() OVER (PARTITION BY ...) work on any supported version.
    • EXTRACT(YEAR FROM date) returns a number; ::int casts it to integer.