Queries

SQL's SELECT is the read half of every relational database. It can filter, sort, aggregate, join, and transform — all described declaratively. You state what you want; the engine figures out how to fetch it.

Analogy

A SELECT is an order slip at a deli counter: "half a pound of pastrami, sliced thin, vacuum-sealed, no bread." You never tell the deli worker which drawer to open, which knife to use, or whether to weigh before or after slicing. They know the kitchen; your job is to describe the outcome. WHERE is "just the lean cuts." GROUP BY with a SUM is "weigh the total across all the meats I picked." JOIN is "and while you're at it, add the side salad that goes with each sandwich on this list." ORDER BY is "arrange the packages heaviest on the bottom." The clerk decides the steps; you decide the result.

The anatomy of a SELECT

SELECT   <columns>
FROM     <table>
JOIN     <other_table> ON <condition>
WHERE    <row-level filter>
GROUP BY <grouping columns>
HAVING   <group-level filter>
ORDER BY <sort expression> ASC | DESC
LIMIT    <n>

The clauses are evaluated in this order: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. The order you write them does not change this.

WHERE vs HAVING

WHERE filters individual rows before grouping. HAVING filters groups after aggregation.

-- Users who have published more than 2 posts
SELECT user_id, COUNT(*) AS post_count
FROM   posts
WHERE  published = true        -- filter rows first
GROUP BY user_id
HAVING COUNT(*) > 2;           -- then filter groups

JOINs

A JOIN combines rows from two tables. The ON clause specifies how they match.

Join type	Returns
INNER JOIN	Only rows with a match in both tables
LEFT JOIN	All rows from the left table; NULLs for unmatched right rows
RIGHT JOIN	All rows from the right table; NULLs for unmatched left rows
FULL JOIN	All rows from both; NULLs where no match
CROSS JOIN	Cartesian product — every left row × every right row

The Venn diagram visualizer shows which rows each type returns.

Aggregates

Aggregate functions collapse multiple rows into one value:

Function	Produces
COUNT(*)	Number of rows
SUM(col)	Total of a numeric column
AVG(col)	Mean value
MAX(col)	Largest value
MIN(col)	Smallest value

Every non-aggregated column in SELECT must appear in GROUP BY — otherwise the database does not know which row's value to pick.

Subqueries and CTEs

A subquery is a SELECT inside another SELECT:

SELECT name FROM users
WHERE id IN (SELECT user_id FROM posts WHERE likes > 50);

A CTE (Common Table Expression) names the subquery so you can reference it cleanly:

WITH popular_posts AS (
  SELECT user_id FROM posts WHERE likes > 50
)
SELECT name FROM users WHERE id IN (SELECT user_id FROM popular_posts);

CTEs improve readability. Whether they improve performance depends on the planner; modern PostgreSQL can inline them.

Window functions

A window function operates over a frame of rows without collapsing them into one:

SELECT
  id,
  title,
  likes,
  RANK() OVER (ORDER BY likes DESC) AS rank
FROM posts;

OVER is what makes it a window function. PARTITION BY splits the window into groups without losing rows.

Set operations

UNION, INTERSECT, and EXCEPT combine two result sets:

• UNION ALL: all rows from both (keep duplicates)
• UNION: all rows, deduplicated
• INTERSECT: only rows in both
• EXCEPT: rows in the first result that are not in the second

Both sides of the operator must return the same number of columns with compatible types.

The playground

The SQL console in this level runs queries against a seeded in-memory database (users, posts, departments, comments). Try the examples, break things, observe the output. The evaluator supports SELECT, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, INNER JOIN, and LEFT JOIN.