← Back to the catalog

SQL for Beginners: SELECT, WHERE, JOIN and GROUP BY

Write SQL queries on a small sample database in a free SQLite playground: filter and sort rows, join customers to orders, and summarize with GROUP BY, COUNT, SUM and HAVING. You'll also learn to predict row counts and check that your totals make sense.

Language
English
Lessons
6
Minutes
180

Price: · The subscription includes the whole catalog at no extra cost.

Course outline

  1. Query one table with confidence

    1. Your first SELECT on a real table (30′)
    2. Filter and sort: WHERE and ORDER BY (30′)
    3. Patterns, ranges and missing values (30′)
  2. Join, summarize and check your answer

    1. Join two tables with INNER JOIN (30′)
    2. Why rows multiply or disappear in a join (30′)
    3. Summarize with GROUP BY, COUNT and HAVING (30′)

Free first lesson

Your first SELECT on a real table

From your spreadsheet to a table

At work you keep orders in a spreadsheet: one row per order, a column for the date, another for the amount, another for the customer. A database holds the same thing with different words. The workbook becomes the database; each sheet becomes a table with a name — in our sample data, customers and orders. Rows and columns mean what they already mean to you: each row is one record (one customer, one order), each column is one field.

One difference will matter to you. In a spreadsheet, a column can hold a number in one cell and a note in the next. In a table, every column has a data type — TEXT, INTEGER or REAL — fixed when the table was built, and the values are expected to match it. The date column holds dates written as text, like '2024-03-02' (SQLite has no separate date type, and this year-month-day format sorts correctly); the amount column holds numbers.

Why does a fixed data type make a column easier to query than a free-form spreadsheet cell?

Run your first query in the playground

You'll see the answer to that as you go: because the database knows what each column holds, it can sort, compare and total a whole column without guessing what's in it — the work you now do by cleaning up spreadsheet columns by hand.

One thing to know before you type: a SELECT query only reads. Running it never changes the table (the setup script in step 2 is the one exception: it builds the tables once); the result is a separate table, built fresh for you each time.

  1. Open your SQL playground in your browser — a free online SQLite playground, nothing to install — or DB Browser for SQLite if you installed it (create a new database file, then use the Execute SQL tab). In an online playground that offers several databases, pick SQLite.
  2. Paste this script into the query box and run it once (a second run stops with "table customers already exists"). It creates and fills the two tables we'll use all course:

``sql CREATE TABLE customers ( customer_id INTEGER, name TEXT, city TEXT, state TEXT); INSERT INTO customers VALUES (1,'Ana Ruiz','Austin','TX'), (2,'Ben Carter','Denver','CO'), (3,'Carla Diaz','Miami','FL'), (4,'David Kim','Seattle','WA'), (5,'Elena Lopez','Houston','TX'), (6,'Frank Moore','Dallas','TX'), (7,'Grace Lee',NULL,NULL); CREATE TABLE orders ( order_id INTEGER, customer_id INTEGER, order_date TEXT, amount REAL, status TEXT, state TEXT); INSERT INTO orders VALUES (101,1,'2024-03-02',89.90,'shipped','TX'), (102,2,'2024-03-05',150.00,'shipped','CO'), (103,1,'2024-03-09',42.50,'processing','TX'), (104,3,'2024-03-11',220.75,'shipped','FL'), (105,4,'2024-03-15',15.25,'returned','WA'), (106,2,'2024-03-18',310.00,'processing','CO'), (107,5,'2024-03-21',75.40,'shipped','TX'), (108,6,'2024-03-24',128.60,'shipped','TX'), (109,1,'2024-03-28',64.00,'shipped','TX'); ``

  1. Check the table list (the left panel in most playgrounds, the Database Structure tab in DB Browser): customers and orders now appear. Click orders to see its six columns and their types — its state is the state each order shipped to. Customer 7 has no city or state recorded yet; that empty value, NULL, gets its own lesson.
  2. Clear the box, type the query below and run it:

``sql SELECT * FROM orders; ``

The star means "every column". You get all 9 rows and 6 columns — the whole table, like unhiding every column of a sheet. The data itself is untouched.

Ask for the columns you want

SELECT * drags along columns you don't need — in a real work table that's 40 columns of scrolling before you find the two you came for. SQL lets you list what you want:

``sql SELECT name, city FROM customers; ``

Read it as: select the name and city columns, from customers. The result shows exactly those two columns, in the order you wrote them. And that settles the opening question: listing columns never removes rows — all 7 customers are still there. Dropping rows is the next lesson's job.

To rename a header in the result, add AS next to the column it renames:

``sql SELECT order_id, amount AS order_total FROM orders; ``

The header now reads order_total, which says far more to whoever you share results with. Why doesn't the table's column get renamed too? Because a query never edits the table — it builds a separate result, a new little table, every time you run it. The stored orders keeps its own names, always.

Worked example: a question about customers

A question arrives. A teammate asks: "List every customer's name and state." The question is about customers, so the table is customers; the answer needs two fields, so they go in the list:

``sql SELECT name, state FROM customers; ``

Run it: seven rows come back. Read the first one — Ana Ruiz | TX — that is one whole record, customer 1. Reading a result row by row like this is how you'll check every query you ever write.

For a header that says more, rename it in the result:

``sql SELECT name, state AS region FROM customers; ``

Same seven rows, same two columns of data — only the header changed to region.

Your turn: write the column list

Your turn — you write the query. Your manager wants: the order id, the order date and the amount, with the amount labeled order_total. The table is orders. The step left to you is the whole column list: the three columns in a sensible order, the rename, and the right table. Write it and run it before you look at the answer.

How many rows should come back? Count them and check the headers before comparing below.

Common mistakes

  • A missing comma silently renames a column. SELECT order_id amount does not error: SQLite reads amount as the new name for order_id, so you get one column of order ids headed amount — and no warning. The clue is a missing column with a strange header; count your result's columns against your list.
  • A typo in a column or table name gives "no such column" or "no such table". Check the spelling against the table list in your playground's panel.
  • Worrying that the query changed something. SELECT only reads; there is nothing to undo. Run any query as many times as you like.

What to keep

  • A table is a sheet with rules: rows are records, each column has one data type.
  • SELECT lists the columns you want, FROM names the table, AS renames a header in the result only.
  • A query only reads: the result is a fresh table and the stored data is never changed.

Practice

Your teammate asks: "Which orders have shipped, and when?" Write and run one query on orders that answers it — the order id, the status and the date — with the date column renamed to placed_on. Then run SELECT * FROM orders; and check that your query returned the same number of rows.

table
The database equivalent of one spreadsheet sheet: rows of records under named columns, for example orders or customers.
record
One row of a table, such as a single order or a single customer.
data type
The kind of value a column holds, such as TEXT (text), INTEGER (whole number) or REAL (decimal number), declared when the table was built.
query
A question written in SQL that the database answers with a table of results.
result set
The table a query returns, built fresh each time you run it; it is separate from the stored table.
alias
The new name a column gets in the result when you write AS; the table's own column name is unchanged.