SQL · Guides · October 29, 2026 · 11 min read

SQL practice problems with answers: 15 tasks from easy to hard

Below are 15 SQL practice problems on one database, ordered from easy to hard. Each has the question, the answer query under “Solution”, the real result and a line or two on why the solution works. Try each one before you read the answer: the point of practice is the minute you spend stuck, not the reading.

All problems use the airline database from the Nomto SQL course. It is a PostgreSQL-style schema with real volumes: 104 airports, 9 aircraft models, 33,121 flights from 12 July to 10 September 2026, 262,788 bookings and 366,733 tickets. Times are in the airline’s home time (Moscow, UTC+3); money is in US dollars. The data snapshot was taken on 11 August 2026 at 18:00, so flights before that moment have happened and later ones are only scheduled. You can run every query in the Nomto SQL room and compare your output with the tables here.

The tables you need

Easy: SELECT, WHERE, ORDER BY

1. How many airports and how many cities are there?

Solution

SELECT COUNT(*) AS airports, COUNT(DISTINCT city) AS cities
FROM airports;

COUNT(*) counts rows, COUNT(DISTINCT city) counts different values. Three fewer cities than airports means some cities have several airports (problem 6 finds them).

2. List the aircraft models with a range of at least 5,000 km, longest first

Solution

SELECT model, range
FROM aircrafts
WHERE range >= 5000
ORDER BY range DESC;

“At least” means >=. A common slip is >, which would give the same answer here only by luck.

3. The five easternmost airports outside Moscow time

Solution

SELECT airport_code, airport_name, city, timezone
FROM airports
WHERE timezone <> 'Europe/Moscow'
ORDER BY longitude DESC
LIMIT 5;

“Easternmost” is the largest longitude. 60 of the 104 airports are outside Moscow time.

Easy to medium: GROUP BY and HAVING

4. How many flights depart on each day of the week?

Use four full weeks, 13 July to 9 August, so every weekday appears the same number of times.

Solution

SELECT EXTRACT(ISODOW FROM scheduled_departure) AS weekday, COUNT(*) AS flights
FROM flights
WHERE scheduled_departure >= '2026-07-13'
  AND scheduled_departure <  '2026-08-10'
GROUP BY EXTRACT(ISODOW FROM scheduled_departure)
ORDER BY weekday;

ISODOW numbers Monday as 1 and Sunday as 7. Saturday is the busiest day, Friday the quietest. Comparing weekdays over a period that is not a whole number of weeks would give some days an extra week of flights.

5. The five busiest routes by number of flights

Solution

SELECT departure_airport, arrival_airport, COUNT(*) AS flights
FROM flights
GROUP BY departure_airport, arrival_airport
ORDER BY flights DESC, departure_airport, arrival_airport
LIMIT 5;

A route is a pair of airports, so both go in GROUP BY. The extra sort columns make the order stable when counts tie.

6. Which cities have more than one airport?

Solution

SELECT city, COUNT(*) AS airports
FROM airports
GROUP BY city
HAVING COUNT(*) > 1
ORDER BY airports DESC;

The condition is on a count, so it belongs in HAVING, not WHERE. More on that in GROUP BY vs HAVING.

7. Which aircraft models have more than 200 seats?

Solution

SELECT a.model, COUNT(*) AS seats
FROM aircrafts a
JOIN seats s ON s.aircraft_code = a.aircraft_code
GROUP BY a.model
HAVING COUNT(*) > 200
ORDER BY seats DESC;

seats has one row per seat, so counting rows per model gives cabin size; the join only adds the readable model name.

8. Revenue, segments and average price by class

Solution

SELECT fare_conditions,
       COUNT(*) AS segments,
       SUM(amount) AS revenue,
       ROUND(AVG(amount), 2) AS avg_price
FROM ticket_flights
GROUP BY fare_conditions
ORDER BY revenue DESC;

Revenue per class lives in ticket_flights, one row per passenger per segment. Summing bookings.total_amount after joining it to segments would count each booking many times.

Medium: dates, conditions, subqueries

9. The longest scheduled flights

Solution

SELECT DISTINCT flight_no, departure_airport, arrival_airport,
       EXTRACT(EPOCH FROM scheduled_arrival - scheduled_departure) / 60 AS minutes
FROM flights
ORDER BY minutes DESC, flight_no
LIMIT 3;

Subtracting timestamps gives an interval; EXTRACT(EPOCH FROM ...) turns it into seconds. Without DISTINCT, the same flight number would fill the top rows once for every day it flies. Several flights tie at 8 h 50 min, so LIMIT 3 shows only some of them.

10. What share of arrived flights landed more than an hour late?

Solution

SELECT COUNT(*) AS arrived,
       COUNT(*) FILTER (WHERE actual_arrival - scheduled_arrival > INTERVAL '1 hour') AS late_1h,
       ROUND(100.0 * COUNT(*) FILTER (WHERE actual_arrival - scheduled_arrival > INTERVAL '1 hour')
             / COUNT(*), 2) AS pct
FROM flights
WHERE status = 'Arrived';

FILTER (WHERE ...) counts only rows that match, in the same pass as the total. SUM(CASE WHEN ... THEN 1 ELSE 0 END) does the same in databases without FILTER. Multiply by 100.0, not 100, to avoid integer division.

11. How many bookings are above the average booking amount?

Solution

SELECT COUNT(*) AS above_avg
FROM bookings
WHERE total_amount > (SELECT AVG(total_amount) FROM bookings);

The subquery returns one number (the average is $790.26), which the outer WHERE compares with each row. Only about a third of the 262,788 bookings are above average: a few large bookings pull the mean up.

12. The three routes with the highest ticket revenue

Solution

SELECT f.departure_airport, f.arrival_airport, SUM(tf.amount) AS revenue
FROM ticket_flights tf
JOIN flights f ON f.flight_id = tf.flight_id
GROUP BY f.departure_airport, f.arrival_airport
ORDER BY revenue DESC
LIMIT 3;

Each segment belongs to one flight, so this join never multiplies rows: the sum stays at segment grain. Moscow Domodedovo to Khabarovsk earns the most, not the most frequent route from problem 5. Frequency and money are different rankings.

Hard: joins at the right grain and window functions

13. Seat load factor by aircraft model on arrived flights

Load factor = boarded passengers / seats offered.

Solution

WITH cap AS (
  SELECT aircraft_code, COUNT(*) AS seats
  FROM seats
  GROUP BY aircraft_code
), bp AS (
  SELECT flight_id, COUNT(*) AS boarded
  FROM boarding_passes
  GROUP BY flight_id
)
SELECT f.aircraft_code,
       COUNT(*) AS flights,
       ROUND(100.0 * SUM(COALESCE(bp.boarded, 0)) / SUM(cap.seats), 1) AS load_pct
FROM flights f
JOIN cap ON cap.aircraft_code = f.aircraft_code
LEFT JOIN bp ON bp.flight_id = f.flight_id
WHERE f.status = 'Arrived'
GROUP BY f.aircraft_code
ORDER BY load_pct DESC;

Both seats and boarding passes are aggregated to one row per model and per flight before joining. Joining raw seats and raw boarding_passes to flights would multiply them by each other. LEFT JOIN plus COALESCE keeps flights with nobody on board as zeros instead of dropping them. The SQL joins guide explains this fan-out in detail.

14. The top-earning route for each aircraft model

Solution

WITH r AS (
  SELECT f.aircraft_code, f.departure_airport, f.arrival_airport,
         SUM(tf.amount) AS revenue,
         ROW_NUMBER() OVER (PARTITION BY f.aircraft_code ORDER BY SUM(tf.amount) DESC) AS rn
  FROM ticket_flights tf
  JOIN flights f ON f.flight_id = tf.flight_id
  GROUP BY f.aircraft_code, f.departure_airport, f.arrival_airport
)
SELECT aircraft_code, departure_airport, arrival_airport, revenue
FROM r
WHERE rn = 1
ORDER BY revenue DESC;

The window runs after GROUP BY, so it can rank by SUM(tf.amount). PARTITION BY restarts the numbering for each model, and the filter rn = 1 has to sit outside, because a window function cannot be used in WHERE. See SQL window functions for RANK and DENSE_RANK alternatives when ties matter.

15. Week-over-week change in bookings, and the trap in the last week

Solution

WITH w AS (
  SELECT DATE_TRUNC('week', book_date) AS week, COUNT(*) AS bookings
  FROM bookings
  WHERE book_date >= '2026-07-13'
  GROUP BY DATE_TRUNC('week', book_date)
)
SELECT CAST(week AS date) AS week,
       bookings,
       LAG(bookings) OVER (ORDER BY week) AS prev_week,
       ROUND(100.0 * (bookings - LAG(bookings) OVER (ORDER BY week))
             / LAG(bookings) OVER (ORDER BY week), 1) AS change_pct
FROM w
ORDER BY week;

DATE_TRUNC('week', ...) maps each timestamp to its Monday; LAG brings the previous week onto the current row. The query is correct, but the last line is not a 71.5% collapse: the data ends on Tuesday 11 August at 18:00, so that week contains less than two days. Before reporting a change, check that both periods are complete; here the honest answer is “+8.5% in the last full week”.

How to use these problems

• Write first, then compare. Your query may differ and still be right. Compare results, not syntax.
• Check the row count. Before trusting a SUM after a join, compare COUNT(*) with what you expect from the table grains.
• Add a tie-breaker to any ORDER BY ... LIMIT that might tie, so your output is reproducible.
• Ask whether the period is complete whenever you compare dates near the edge of the data.

If you want interview-style variants of these tasks, with model answers, see SQL interview questions for data analysts.

FAQ

What database are these SQL practice problems for?

They are written in PostgreSQL syntax on an airline database (flights, bookings, tickets, boarding passes) used in the Nomto SQL course. Apart from FILTER and ISODOW, the queries run with minor or no changes on most SQL databases.

My query gives the same result but looks different. Is it wrong?

Not necessarily. Many problems have several correct solutions, for example FILTER versus CASE, or a subquery versus a CTE. If your result matches on the full data, including edge cases such as ties and empty groups, the query is fine.

In what order should I practise SQL topics?

The order of this list works: filtering and sorting, then aggregates and GROUP BY/HAVING, then joins, then subqueries and CTEs, then window functions. Joins and aggregation together cause most real-world errors, so spend extra time on problems 8, 12 and 13.

Are these problems similar to SQL interview tasks?

Problems 6 and 9–15 are close to what analyst interviews ask: grouping with conditions, joins without double counting, top N per group and period-over-period change.

Also in Português (Brasil), Tiếng Việt, Español, 简体中文