PostgreSQL Practice Exercises and Learning Roadmap

Set up the practice schema

CREATE DATABASE practice;
\c practice

CREATE TABLE departments (
  code    varchar(10) PRIMARY KEY,
  name_en text NOT NULL
);

CREATE TABLE students (
  id          integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name_en     text NOT NULL,
  name_bn     text,
  dept_code   varchar(10) REFERENCES departments(code),
  cgpa        numeric(3,2),
  admitted_on date
);

CREATE TABLE courses (
  id      integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title   text NOT NULL,
  credits smallint DEFAULT 3
);

CREATE TABLE enrollments (
  student_id integer REFERENCES students(id) ON DELETE CASCADE,
  course_id  integer REFERENCES courses(id),
  grade      char(2),
  PRIMARY KEY (student_id, course_id)
);

INSERT INTO departments VALUES ('CSE','Computer Science'),('EEE','Electrical'),('BBA','Business');
INSERT INTO students (name_en, name_bn, dept_code, cgpa, admitted_on) VALUES
 ('Rahim Uddin','রহিম উদ্দিন','CSE',3.85,'2023-01-10'),
 ('Karim Ali','করিম আলী','CSE',3.20,'2023-01-10'),
 ('Fatema Begum','ফাতেমা বেগম','EEE',3.95,'2024-02-01'),
 ('Nusrat Jahan','নুসরাত জাহান','BBA',2.90,'2024-02-01'),
 ('Tanvir Hasan','তানভীর হাসান','CSE',3.60,'2025-01-15');
INSERT INTO courses (title, credits) VALUES ('Database Systems',3),('Algorithms',3),('Accounting',2);
INSERT INTO enrollments VALUES (1,1,'A'),(1,2,'B'),(2,1,'B'),(3,2,'A'),(4,3,'C');

Beginner

List every student sorted by CGPA, highest first.
Show students admitted in 2024, using extract.
Count the students in each department.
Find students whose English name starts with "T", using ILIKE.
Show the three highest CGPAs with RETURNING-style feedback after bumping them by 0.05 (hint: use UPDATE … RETURNING).

Intermediate

Show each student with their department's full English name.
List students who are not enrolled in any course, using NOT EXISTS.
Find the average CGPA per department using FILTER, restricted to departments with at least two students.
Show each course with how many students take it, including courses nobody takes.
Use DISTINCT ON to show the top student in each department.

Advanced

Rank students within their department by CGPA using RANK() and DENSE_RANK().
Write a recursive CTE that lists integers 1 through 10.
For each student, show their CGPA and the difference from their department's average using a window function.
Build a monthly admissions report with no gaps, using generate_series and date_trunc.
Write a PL/pgSQL function that takes a department code and returns its student count and average CGPA as OUT parameters.
Add an AFTER UPDATE trigger that records every CGPA change in an audit table, firing only WHEN the value actually changed.
Add a JSONB prefs column to students, index it with GIN, and query for a specific key.

Try each one yourself, then check with EXPLAIN ANALYZE whether your query uses an index.

Learning roadmap

Stage	Focus
Weeks 1–2	`SELECT`, `WHERE`, `ORDER BY`, functions, `GROUP BY`
Weeks 3–4	Joins, subqueries, CTEs, window functions
Weeks 5–6	Schema design, normalization, keys, constraints
Weeks 7–8	Indexes, `EXPLAIN`, transactions, MVCC and locking
Weeks 9–10	JSONB, arrays, full-text search, extensions
Ongoing	Backups, replication, security, tuning

PostgreSQL-specific interview questions

How does MVCC work, and why does Postgres need VACUUM?
Why is DDL transactional in Postgres but not in MySQL, and why does it matter?
WHERE vs HAVING; UNIQUE vs EXCLUDE
What RETURNING is for, and where a data-modifying CTE helps
The difference between a view and a materialized view
serial vs GENERATED ALWAYS AS IDENTITY
What NOT IN does with a NULL in the subquery, and why NOT EXISTS is safer
How partial and expression indexes differ from a plain index
What row-level security does and why it is stronger than an application-level filter
The three isolation levels Postgres actually implements, and what changes between them

Where to go next

Read the official PostgreSQL documentation — it is unusually good and doubles as a reference for exact behaviour. Explore extensions relevant to your domain: PostGIS for geospatial work, pgvector for AI/embedding search, TimescaleDB for time-series. And practise on a real, messy dataset — index tuning and query plans only really click once you are staring at an EXPLAIN ANALYZE output for a query that is actually slow.

Practice Exercises & Next Steps