Chapter 44: PostgreSQL Exercises
What are PostgreSQL Exercises?
PostgreSQL exercises are small-to-medium sized tasks that force you to:
- Write real SELECT, JOIN, GROUP BY, HAVING, CASE, EXISTS, UNION queries
- Fix broken queries
- Optimize slow queries
- Design small schemas
- Clean messy data
- Solve business-like problems using only SQL
They usually come in three difficulty levels:
| Level | Typical task description | Time needed (beginner) | Example platforms that have this level |
|---|---|---|---|
| Beginner | Simple SELECT + WHERE + ORDER BY + LIMIT, basic JOIN, basic aggregates | 5–15 min per exercise | SQLZoo, W3Schools, HackerRank (easy) |
| Intermediate | Multi-table JOIN, GROUP BY + HAVING, CASE, subqueries, EXISTS / NOT EXISTS, date functions | 15–40 min | LeetCode, HackerRank, Mode Analytics |
| Advanced | Window functions, recursive CTE, complex aggregates, query optimization, schema design | 30–90 min | LeetCode (hard), Codewars, PostgreSQL Exercises sites |
Recommended Exercise Platforms (2026 reality – still relevant)
| Platform | Best for | Free? | PostgreSQL support | Difficulty levels | Comment from teacher (2026) |
|---|---|---|---|---|---|
| pgexercises.com | Pure PostgreSQL exercises | Yes | Excellent | Beginner → Advanced | Best starting point right now |
| LeetCode – Database | Interview-style problems | Partial free | Very good | Easy → Hard | Must-do for job preparation |
| HackerRank – SQL | Structured learning path | Yes | Good | Easy → Medium | Good warm-up |
| Mode Analytics SQL Tutorial | Real-world analytics style | Free | Very good | Intermediate | Great for reporting queries |
| SQLZoo | Interactive tutorial + exercises | Yes | Good | Beginner → Medium | Classic – still useful |
| w3resource PostgreSQL exercises | 100+ small tasks | Yes | Good | Beginner → Intermediate | Very large collection |
My personal recommendation path (2026 edition)
- First 3–5 days → pgexercises.com (do all sections in order)
- Next 7–14 days → LeetCode Database section (solve 50–80 problems, easy → medium)
- After that → HackerRank SQL track + Mode Analytics free exercises
- Ongoing → revisit pgexercises + try hard LeetCode problems
Example exercises (with solutions) – real PostgreSQL style
We’ll use the DVD Rental sample database (very popular, available on pgexercises.com too) or our own small schema.
Exercise 1 – Beginner (SELECT + WHERE + ORDER BY)
Task List all customers whose first name starts with ‘A’ or ‘K’, living in district ‘California’, sorted by last name.
Your answer should look like:
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 |
SELECT first_name, last_name, address.district FROM customer JOIN address ON customer.address_id = address.address_id WHERE (first_name LIKE 'A%' OR first_name LIKE 'K%') AND district = 'California' ORDER BY last_name; |
Exercise 2 – Intermediate (JOIN + GROUP BY + HAVING)
Task Show all film categories that have more than 60 films, along with the total number of films and average rental rate in that category. Sort by number of films descending.
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT c.name AS category_name, COUNT(f.film_id) AS film_count, ROUND(AVG(f.rental_rate), 2) AS avg_rental_rate FROM category c JOIN film_category fc ON fc.category_id = c.category_id JOIN film f ON f.film_id = fc.film_id GROUP BY c.name HAVING COUNT(f.film_id) > 60 ORDER BY film_count DESC; |
Exercise 3 – Intermediate / Advanced (EXISTS + JOIN + CASE)
Task Show all customers who have rented at least one film in the “Horror” category in the last 6 months (assume current date is 2026-02-14). For each such customer, show:
- Full name
- Number of horror rentals
- Their most expensive horror rental (use CASE or subquery)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SELECT c.first_name || ' ' || c.last_name AS customer_name, COUNT(r.rental_id) AS horror_rentals_count, MAX(f.rental_rate) AS most_expensive_horror_rental FROM customer c WHERE EXISTS ( SELECT 1 FROM rental r JOIN inventory i ON i.inventory_id = r.inventory_id JOIN film f ON f.film_id = i.film_id JOIN film_category fc ON fc.film_id = f.film_id JOIN category cat ON cat.category_id = fc.category_id WHERE r.customer_id = c.id AND cat.name = 'Horror' AND r.rental_date >= CURRENT_DATE - INTERVAL '6 months' ) GROUP BY c.customer_id, c.first_name, c.last_name HAVING COUNT(r.rental_id) > 0 ORDER BY horror_rentals_count DESC; |
Your Action Plan for Next 30 Days
- Today / Tomorrow → Go to https://pgexercises.com → Do first 3 sections completely (Basic / Joins / Aggregates)
- Next 7 days → Finish pgexercises.com → Solve 20–30 easy/medium LeetCode Database problems
- Days 10–20 → Solve 30–50 medium LeetCode + HackerRank SQL problems → Try writing queries without looking at schema first (force yourself to think)
- Days 20–30 → Re-do the hardest 20 problems from pgexercises & LeetCode → Start timing yourself (aim < 10 min per medium problem)
Final Teacher Advice (from 10+ years of teaching SQL)
- Do not just read solutions — write them yourself first
- Make mistakes — that’s how you learn
- Explain your query out loud (to yourself, to a friend, to me here)
- Keep a notebook (or Notion / Obsidian) with your favorite patterns
- When stuck → break the problem into smaller SELECTs, then combine
Want to start right now?
Tell me:
- You want 5–10 PostgreSQL exercises (beginner / intermediate / advanced) right now?
- You want me to correct your solution to any exercise?
- You prefer a specific topic (JOINs, GROUP BY + HAVING, window functions, recursive CTE…)?
- Or you want a full 30-day practice plan with daily tasks?
Your guru is ready — let’s move from theory to muscle memory! 🚀
