◀ Course contents Part 1 · Module 1-01

SELECT & FROM

Your first working query, in about four minutes

A database is a set of tables, and a table is a grid: named columns, and one row per thing. Every query you will ever write asks the same two questions of that grid — what do you want, and where is it. This module covers those two words, the shortcut that shows you everything, and the map you should read before writing anything at all.

Ready?

A Table Is a Grid With Named Columns

Think of a spreadsheet, with one important rule added: every column has a name and a type, and every row has the same columns. A table called users holds one row per user; a table called orders holds one row per order. What one row represents is called the table's grain, and knowing it is most of understanding a table.

SQL is a declarative language. You describe the result you want, not the steps to produce it, and the database works out how. That is why a query reads almost like a sentence and why there are no loops in it.

Everyday example, a filing cabinet

A drawer labelled "customers" with one card per customer, every card laid out the same way. You never ask the cabinet "walk to drawer two, open it, read card seven". You ask for "the cards for customers in Spain" and someone fetches them. SQL is the sentence you say; the database is the person who walks to the drawer.

Row

One thing

A single user, order or event. Also called a record.

Column

One fact about it

A name, a price, a date. Every row has all of them, though some may be empty.

Quick check

The orders table has 24 rows. What does one row represent?

Two Words, One Query

SELECT names the columns you want. FROM names the table they live in. That is a complete query:

SELECT name, plan
FROM users;

Two columns come back, for every row in the table, in the order you listed them. Swap name and plan around in the SELECT and the result's columns swap too — the SELECT list is the output layout, not just a filter.

SQL ignores line breaks and extra spaces, so the same query on one line means exactly the same thing. Put each clause on its own line anyway. Queries grow, and a six-clause query written on one line is unreadable by the person who has to change it, which is usually you.

SELECT

What you want. A comma-separated list of columns, or *.

FROM

Where it lives. Get this wrong and you get "no such table".

;

The semicolon

Ends a statement. Optional for a single query, required when you run several at once.

SELECT changes nothing

Reading a table never edits it. That is why exploring is free.

Quick check

What does SELECT plan, name FROM users; return?

SELECT * Is for Looking, Not for Keeping

* means every column, in the order the table defines them. It is the right first move against a table you have never seen: run it, look at what comes back, then write the real query.

SELECT *
FROM products;

It is the wrong move in anything you save. A query with a star does not return a fixed set of columns — it returns whatever the table has today. Add a column next quarter and every saved report quietly starts carrying it, which at best widens a dashboard and at worst leaks a column somebody had assumed was internal.

The cost is real, not stylistic

Naming your columns is not tidiness. It pins down what the query returns, so the thing reading it — a chart, a spreadsheet, another query — keeps working when the table changes underneath. It also documents intent: a reader can see what the query is for without opening the table.

Quick check

A saved daily report uses SELECT * FROM users. Someone adds an internal_notes column to the table. What happens?

Read the Schema Before You Write the Query

The schema is the map: which tables exist, what columns they have, and what type each column is. The Lab tab shows you the schema of this dojo's database, above the exercises, permanently. That is not a training wheel — analysts keep the schema open all day.

Two things on that panel are worth reading properly. key marks the column that identifies a row uniquely, which is what later modules will join tables on. nullable marks a column allowed to hold no value at all, which is the column that will break your first filter in module 1-05.

users          user_id, name, country, company, plan, signup_date, referred_by, is_active
products       product_id, name, category, price, launched_on
orders         order_id, user_id, product_id, quantity, amount, ordered_at, status
subscriptions  subscription_id, user_id, plan, mrr, started_on, cancelled_on
events         event_id, user_id, event_name, event_at, device

Nothing here can break

The editor in the next tab runs real PostgreSQL inside this browser tab. It touches nothing on your computer and sends nothing to a server, and the database is rebuilt from its seed before every single run. A query that takes too long is stopped after ten seconds; a mistake costs one click.

The Run button is free. Use it far more often than feels necessary.

Quick check

Your query returns no such column: singup_date. What has happened?

0 of 9 completed

Loading the tables…

To do

Every query answers the same two questions: what do you want, and where is it. SELECT answers the first, FROM answers the second.

The * is a wildcard meaning "every column". It is the fastest way to see what a table actually holds, which is why it is the first thing anyone runs against a table they have never met.

Your task: return every column of every row in the users table.

query.sql

-- Write your query below, then press Run & Check.

PostgreSQL

Hint

Two words and a table name: SELECT * FROM users; — the semicolon at the end is optional here but it is a good habit.

Output

To do

SELECT * is for looking around. Once you know what you need, name it: list the columns you want, separated by commas.

SELECT name, plan
FROM users;

The order you list them is the order they come back in. Nothing else about the table changes — you are choosing what to look at, not editing anything.

Your task: return just the name and country of every user, in that order.

query.sql

SELECT
FROM users;

PostgreSQL

Hint

SELECT name, country FROM users; — a comma between the two column names, and no comma after the last one.

Output

To do

The columns come back in the order you list them, not the order they sit in the table. That makes the list part of the answer rather than a detail — a report with the right values under the wrong headings is a different report.

SELECT country, name FROM users;   -- country first
SELECT name, country FROM users;   -- name first

Both are valid; only one is what was asked for.

Your task: support wants the plan first, then the name, then the country — in that order — for every user.

query.sql

SELECT name, country, plan
FROM users;

PostgreSQL

Hint

Nothing is wrong with the query except the order of the three names after SELECT. Put plan first.

Output

To do

Postgres's errors name the thing that is wrong. Run this and it says:

no such column: nmae

That is the whole diagnosis. The database has no idea what you meant, so it tells you exactly which word it could not resolve and stops — which is the good outcome. A database that guessed would hand you a report built from the wrong column.

Your task: run it, read the error, and fix the two misspelled column names. The query should return the name and country of every user.

query.sql

SELECT nmae, contry
FROM users;

PostgreSQL

Hint

Both mistakes are in the column list. The schema panel above the editor lists every column of every table — check the spelling there.

Output

To do

Nothing about SELECT is tied to one table. Change the name after FROM and the same query shape reads something else.

The practice database has five tables, listed above the exercises. Open that panel whenever you are not sure what a table holds — real analysts keep the schema in front of them permanently, and pretending otherwise helps nobody.

Your task: from the products table, return name, category and price — in that order.

query.sql

SELECT
FROM

PostgreSQL

Hint

Three column names separated by commas, then FROM products. Column order matters here: name, then category, then price.

Output

To do

SELECT * is for meeting a table. It is a poor thing to leave in a saved query, for two reasons that both bite later.

It changes under you

Someone adds a column and every report built on * silently gains one. Someone renames one and the report breaks.

It hides the intent

A named list says what the query is for. A * says the author had not decided.

Your task: this query pulls every column of the products table for a price list that only shows three of them. Name exactly what the price list needs: the product name, its category, and its price, in that order.

query.sql

SELECT *
FROM products;

PostgreSQL

Hint

Replace the star with the three column names, separated by commas. The schema panel has their exact spelling.

Output

To do

events is the table most of the later modules lean on: one row every time somebody did something in the app. It is long and repetitive, which is exactly what makes it useful — counting, grouping and ranking all need a table with repeats in it.

SQL does not care about line breaks. Splitting a query across lines, one clause per line, costs nothing and makes a long query readable:

SELECT user_id, event_name
FROM events;

Your task: return user_id, event_name and event_at from events, in that order.

query.sql

-- One clause per line reads better once queries get long.
SELECT
FROM

PostgreSQL

Hint

SELECT user_id, event_name, event_at FROM events; — three columns, comma separated, in the order the task lists them.

Output

To do

You will spend more of your working life reading schemas than writing queries. The panel above the editor lists every table in this database and every column in it — that is the same information a real client gives you, and it is where the answer to "what can I even ask" lives.

subscriptions is one you have not used yet. Look at it in the schema, then pull what a finance report would want.

Your task: return the plan, the monthly recurring revenue and the start date of every subscription, in that order.

query.sql

-- Check the schema panel for the exact column names.

PostgreSQL

Hint

The three columns are plan, mrr and started_on. MRR is monthly recurring revenue — the amount that subscription bills every month.

Output

To do

Half of writing a query is deciding where to look. The tables here divide the same business into five views of it:

`users`

who signed up — one row per person

`products`

what is for sale — one row per thing

`orders`

what was bought — one row per purchase

`subscriptions`

who pays monthly — one row per subscription

`events`

what people did — one row per action

The question: "which devices are people using?"

Your task: pick the table that records what people did, and return the event name, the device and when it happened — in that order — for every event.

query.sql

-- Which of the five tables knows about devices?

PostgreSQL

Hint

Only one table has a device column, and it is the one with a row per action rather than a row per person. The columns are event_name, device and event_at.

Output

The subscription pull

To do

Someone in finance wants a list of every subscription on file: who it belongs to, which plan it is, and what it bills per month. They do not want the internal subscription id, and they do not want the dates.

Your task: from the subscriptions table, return user_id, plan and mrr — in that order, and nothing else.

Three columns, ten rows — in that order. A query that returns the right values under the wrong column names is a different answer to the one finance asked for.

query.sql

-- Finance wants: user_id, plan, mrr — from the subscriptions table.
SELECT
FROM

PostgreSQL

Hint

SELECT user_id, plan, mrr FROM subscriptions; — three column names in the order asked for, and no fourth column sneaking in.

Output

Sign in to open this module

SELECT & FROM

A Table Is a Grid With Named Columns

Everyday example, a filing cabinet

One thing

One fact about it

Quick check

Two Words, One Query

SELECT

FROM

The semicolon

SELECT changes nothing

Quick check

SELECT * Is for Looking, Not for Keeping

The cost is real, not stylistic

Quick check

Read the Schema Before You Write the Query

Nothing here can break

Quick check

Read a whole table

Ask for the columns you want

The order you ask is the order you get

Reading the error

A different table

From looking to keeping

It changes under you

It hides the intent

The event table

A table you have not met

Which table answers the question?

users

products

orders

subscriptions

events

The subscription pull

Notification

`users`

`products`

`orders`

`subscriptions`

`events`