Code Lab BetaBUILD. RUN. DISCOVER.
Code Lab is in development. New lessons and improvements are on the way. Progress can be deleted.
Loading…
Code Lab is in development. New lessons and improvements are on the way. Progress can be deleted.
Loading…
Code Lab is in development. New lessons and improvements are on the way. Progress can be deleted.
SQL / POSTGRESQL / Learn / LESSON 02
A subquery is a SELECT in parentheses nested inside another query. A scalar subquery returns at most one row with one column and is used like a plain value: WHERE price > (SELECT AVG(price) FROM products). An aggregate such as AVG or MAX without GROUP BY always yields a single row, so it is a safe choice.
The outer query compares every row with that single value. If the subquery returns no rows its value is NULL and the comparison is not true. If it returns more than one row, PostgreSQL raises an error.
Common mistakes: forgetting the parentheses; a subquery that returns several rows or columns where one value is expected; a hardcoded number instead of a subquery — it silently breaks when the data changes.
Small example:
SELECT id, total FROM orders WHERE total > (SELECT AVG(total) FROM orders);
Working tables for this task. Grading also uses other datasets with the same schema.
| id | name | category | price |
|---|---|---|---|
| 1 | Atlas | books | 30 |
| 2 | Novel | books | 15 |
| 3 | Comic | books | 14.99 |
| 4 | Lamp | home | 29.99 |
| 5 | Vase | home | 30 |
| 6 | Rug | home | 45 |
| 7 | Hammer | tools | 15 |
| 8 | Drill | tools | 60 |
| 9 | Tape | tools | 15 |
| 10 | Candle | NULL | 8.5 |
| 11 | Gift Card | NULL | 25 |
| id | name | city |
|---|---|---|
| 1 | Mira | Sofia |
| 2 | Niko | Varna |
| 3 | Iva | Sofia |
| 4 | Alex | NULL |
| 5 | Boris | Plovdiv |
| 6 | Lena | Varna |
| 7 | Petya | Sofia |
| id | customer_id | total | order_date |
|---|---|---|---|
| 1 | 1 | 120 | 2024-01-05 |
| 2 | 1 | 35.5 | 2024-01-20 |
| 3 | 2 | 40 | 2024-01-20 |
| 4 | 3 | 99.99 | 2024-01-31 |
| 5 | NULL | 60 | 2024-02-01 |
| 6 | 1 | 40 | 2024-02-01 |
| 7 | 2 | 75.25 | 2024-02-14 |
| 8 | 3 | 100 | 2024-02-15 |
| 9 | 5 | 22 | 2024-02-15 |
| 10 | 1 | 39.99 | 2024-02-29 |
| 11 | 2 | 55 | 2024-03-01 |
| 12 | 3 | 18 | 2024-03-10 |
| 13 | 1 | 130 | 2024-03-15 |
| 14 | 2 | 40 | 2024-03-15 |
| 15 | 2 | 12.5 | 2024-03-31 |
| 16 | 3 | 47 | 2024-04-01 |
Expected columns: name, price · row order is not required
Expected result (displayed data):
Loading…A subquery is a SELECT in parentheses nested inside another query. A scalar subquery returns at most one row with one column and is used like a plain value: WHERE price > (SELECT AVG(price) FROM products). An aggregate such as AVG or MAX without GROUP BY always yields a single row, so it is a safe choice. The outer query compares every row with that single value. If the subquery returns no rows its value is NULL and the comparison is not true. If it returns more than one row, PostgreSQL raises an error. Common mistakes: forgetting the parentheses; a subquery that returns several rows or columns where one value is expected; a hardcoded number instead of a subquery — it silently breaks when the data changes.
QUERY STANDBY
›