What SQL is and how a query runs: SQL for data analysis: from first query to window functions

SQL, Structured Query Language, is how you ask questions of a relational database. Data lives in tables, which look like spreadsheets with strict rules: every column has a name and a type, and every row follows the same shape.

Our practice database has a few tables:

customers: one row per customer, with signup date and country.
orders: one row per order, with customer, order date and status.
order_items: one row per product line in an order, with quantity and price.
products: one row per product, with name and category.

Your first query

SELECT order_id, customer_id, ordered_at, status
FROM orders
WHERE status = 'completed'
ORDER BY ordered_at DESC
LIMIT 10;

Read it as a sentence: from the orders table, keep completed orders, sort newest first, show the first ten, and display these four columns.

The order SQL actually runs in

You write SELECT first, but the database processes the clauses in a different order:

FROM picks the table (and later, joins).
WHERE filters rows.
GROUP BY forms groups.
HAVING filters groups.
SELECT computes the output columns.
ORDER BY sorts.
LIMIT cuts the result.

Knowing this explains many beginner errors. For example, you cannot use a column alias defined in SELECT inside WHERE, because WHERE runs before SELECT exists.

SQL is declarative

You describe what you want, not how to get it. The database's query planner decides whether to use an index, which table to read first and how to join them. That is why the same query can be fast on one database and slow on another, and why we will later look at query plans.

Getting set up

The course resources include a script that creates the database in PostgreSQL. You can install PostgreSQL directly or run it in Docker with docker run -e POSTGRES_PASSWORD=dev -p 5432:5432 postgres:17. Connect with any SQL client you like: psql, DBeaver, pgAdmin or the database panel of your code editor.