Step-by-step lessons

From a Table to a Result

Four short lessons: what a table actually is, the two words every query is built from, when the star is a good idea and when it is not, and how to read a schema so you stop guessing column names.

1

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?

2

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.

1

SELECT

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

2

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?

3

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?

4

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 stages 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 stage 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 SQLite 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?

Write it yourself

Query Lab

Four short queries against the practice database. Write the SQL, press Run, and the checks tell you straight away whether it returned what was asked for. Getting it wrong costs nothing — the database is rebuilt every single run.

0 of 4 cleared

Real SQLite runs right here in your browser — nothing to install, nothing sent to a server. The database is rebuilt from scratch before every single run, so nothing you write can break it and nothing carries over between exercises.

The Northwind Analytics database A small SaaS product's database: who signed up, what they subscribed to, what they bought, and what they did in the app.

Loading the tables…

01

Read a whole table

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.

SQLite

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

02

Ask for the columns you want

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;

SQLite

Hint

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

Output

03

A different table

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

SQLite

Hint

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

Output

04

The event table

To do

events is the table most of the later stages 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

SQLite

Hint

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

Output

Boss round

Assignment: The Subscription Pull

One graded query. Pass every check and the stage is cleared and the title is yours — one more stage towards the SQL Practitioner certificate.

This one is graded. You can see 3 of the checks up front, and 1 stay hidden until you run. The hidden ones test the same job from angles you have not been shown, so code that genuinely solves the problem clears this and code shaped around the visible examples does not. Pass them all and the stage is yours.

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. The hidden checks confirm the exact result set, so a query that returns the right values in the wrong columns will not clear this.

query.sql

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

SQLite

Hint

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

Output

Lock it in

Guess the Term

Read the clues and name the concept. The fewer clues you need, the more points you score.

Round 1 Score 0

Keep it handy

SELECT & FROM Quick Reference

The whole stage on one screen.

The shape of a query

1

`SELECT a, b`

The columns you want, in the order you want them.

2

`FROM t`

The table they live in.

3

`;`

Ends the statement. Needed only when running several.

The star

Exploring

SELECT * FROM t to see what a table holds.

Anything saved

The column list changes when the table does.

Instead

Name the columns. It pins the result and documents intent.

The practice database

12

`users`

Who signed up. referred_by points back at this table.

8

`products`

The catalogue, with a price on each row.

24

`orders`

Who bought what, and for how much.

36

`events`

What people did in the app.

Common errors

no such table

The name after FROM is wrong or misspelled.

no such column

A name in the SELECT list is not on that table. Check the schema panel.

syntax error near

Usually a stray comma, or a missing one between two column names.

Sign in to train

Not unlocked yet

SELECT & FROM

From a Table to a Result

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

Query Lab

Read a whole table

Ask for the columns you want

A different table

The event table

Assignment: The Subscription Pull

The subscription pull

Guess the Term

SELECT & FROM Quick Reference

The shape of a query

SELECT a, b

FROM t

;

The star

Exploring

Anything saved

Instead

The practice database

users

products

orders

events

Common errors

no such table

no such column

syntax error near

Notification

`SELECT a, b`

`FROM t`

`;`

`users`

`products`

`orders`

`events`