SQL in Practice: Your First PostgreSQL Queries

In late 2023, I taught systems analysis at the Digital Academy’s open school. I built a small practice database for the students: two tables, readable data, and queries they could change without risking a real system.

The original post included installation screenshots. They are not part of this site, so this revision does not pretend to be an installer for every operating system. It is a standalone SQL lab with an optional Docker shortcut for PostgreSQL.

Start a practice database

If you already have access to PostgreSQL, skip to the schema. With Docker installed, the following commands prepare a local practice environment:

docker run --name sql-practice \
  -e POSTGRES_PASSWORD=postgres \
  -p 127.0.0.1:5432:5432 \
  -d postgres:16

docker exec sql-practice pg_isready -U postgres

Wait for accepting connections; repeat the last check until startup completes. Then open the client:

docker exec -it sql-practice psql -U postgres

Run the SQL below inside psql. A production setup needs proper credentials, network restrictions, roles, backups, and persistent storage; this container is only for local practice.

Create the schema

We will use departments and employees. The identifiers follow snake_case, which avoids PostgreSQL’s quoting rules for mixed-case names.

CREATE TABLE departments (
  department_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  department_name text NOT NULL UNIQUE
);

CREATE TABLE employees (
  employee_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name text NOT NULL,
  department_id integer NOT NULL REFERENCES departments(department_id),
  salary numeric(12, 2) NOT NULL CHECK (salary >= 0)
);

Use a fresh practice database without these tables. The next insert assumes the unused department sequence produces IDs 1–3. Application code should obtain IDs through RETURNING or a stable key, not guess them. Add the test dataset:

INSERT INTO departments (department_name) VALUES
  ('IT'),
  ('Finance'),
  ('Marketing');

INSERT INTO employees (full_name, department_id, salary) VALUES
  ('Ivan Ivanov', 1, 90000),
  ('Anna Petrova', 1, 110000),
  ('Oleg Sidorov', 2, 95000),
  ('Elena Volkova', 2, 105000),
  ('Maria Orlova', 3, 85000);

SELECT, filtering, and sorting

Start with an explicit column list. In application queries it is usually clearer than SELECT * because the result contract is visible.

SELECT employee_id, full_name, salary
FROM employees
ORDER BY employee_id;

Now find employees earning at least 100,000 and sort them from highest to lowest:

SELECT full_name, salary
FROM employees
WHERE salary >= 100000
ORDER BY salary DESC, full_name;

WHERE filters rows before the result is formed. ORDER BY controls how that result is presented.

JOIN employees to departments

The employees table stores a department_id; the human-readable name lives in departments.

SELECT
  e.full_name,
  d.department_name,
  e.salary
FROM employees AS e
JOIN departments AS d
  ON d.department_id = e.department_id
ORDER BY d.department_name, e.full_name;

Here, JOIN means INNER JOIN: only matching rows appear. The foreign-key constraint prevents an employee from referring to a department that does not exist.

Aggregation and GROUP BY

Count employees and calculate the average salary in each department:

SELECT
  d.department_name,
  COUNT(*) AS employee_count,
  ROUND(AVG(e.salary), 2) AS average_salary
FROM employees AS e
JOIN departments AS d
  ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
ORDER BY average_salary DESC, d.department_name;

When a regular column appears next to an aggregate in SELECT, it must be included in the grouping columns or functionally dependent on a grouped key. For an introductory query, listing the grouping columns explicitly keeps the intent clear.

Use HAVING to filter groups after aggregation:

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) >= 2;

A window function

Window functions keep the original rows. This query ranks employees by salary inside each department:

SELECT
  full_name,
  department_id,
  salary,
  ROW_NUMBER() OVER (
    PARTITION BY department_id
    ORDER BY salary DESC, employee_id
  ) AS salary_position
FROM employees
ORDER BY department_id, salary_position;

The ORDER BY inside OVER controls numbering, not final output order. That is why the query has a separate outer ORDER BY.

INSERT, UPDATE, and DELETE

CRUD is a useful model for operations on data. In SQL, SELECT usually covers reads, while INSERT, UPDATE, and DELETE change rows.

Insert an employee and inspect the new row immediately:

INSERT INTO employees (full_name, department_id, salary)
VALUES ('Sergey Kotov', 3, 88000)
RETURNING employee_id, full_name, salary;

Update the salary:

UPDATE employees
SET salary = 92000
WHERE full_name = 'Sergey Kotov'
RETURNING employee_id, full_name, salary;

Remove the practice row:

DELETE FROM employees
WHERE full_name = 'Sergey Kotov'
RETURNING employee_id, full_name;

Before running an UPDATE or DELETE, it is worth executing a SELECT with the same WHERE clause. That simple habit catches many overly broad conditions.

In this fixed dataset, the name identifies the practice row. Real names are not unique: in a working application, use the employee_id returned by INSERT for updates and deletes.

Transactions

A transaction groups several statements into one atomic unit. The example below moves 5,000 between two test salaries, shows the result, and then rolls the exercise back:

BEGIN;

UPDATE employees
SET salary = salary + 5000
WHERE employee_id = 1;

UPDATE employees
SET salary = salary - 5000
WHERE employee_id = 2;

SELECT employee_id, full_name, salary
FROM employees
WHERE employee_id IN (1, 2)
ORDER BY employee_id;

ROLLBACK;

ROLLBACK cancels both changes. Use COMMIT when the result should be kept. The CHECK (salary >= 0) constraint blocks a negative value, but it is only one guard and does not replace business validation.

What to learn next

After this lab, I would continue in this order:

1. NULL and three-valued logic;
2. join types and accidental row multiplication;
3. subqueries, CTEs, and window functions;
4. isolation levels and concurrent updates;
5. indexes and reading EXPLAIN output;
6. roles, privileges, and differences between SQL dialects.

You do not need to memorise every statement. The durable skill is turning a question into a query, checking the keys and relationships, and proving that the result contains the rows you intended.

Continue with the official PostgreSQL tutorial.

Check yourself: a department without employees

Add an empty department:

INSERT INTO departments (department_name) VALUES ('Research');

An INNER JOIN will omit it. To report every department:

SELECT
  d.department_name,
  COUNT(e.employee_id) AS employee_count
FROM departments AS d
LEFT JOIN employees AS e
  ON e.department_id = d.department_id
GROUP BY d.department_id, d.department_name
ORDER BY d.department_name;

After the preceding exercises, Finance and IT have two employees each, Marketing one and Research zero. COUNT(e.employee_id) excludes the missing employee’s NULL; COUNT(*) would count the joined row and report one for an empty department.

Next, count only salaries of at least 100,000 while retaining all departments. Compare placing the condition in ON and in WHERE. The latter removes unmatched rows and changes the report’s meaning.

---

Originally published on my Telegram channel @it_underside.

Yours, DPUPP