← All lessons
Querying data · Lesson 1 of 7

SELECT and WHERE

SQL is the language for asking a database questions. A database keeps data in tables: rows and columns, like a spreadsheet with strict rules about what goes in each column. This course uses a small shop's database with five tables: customers, products, orders, order_items and a signups list. Every query here runs in your browser on SQLite, and every result shown is real.

Other databases (PostgreSQL, MySQL, SQL Server, and warehouses like BigQuery or Snowflake) speak almost the same SQL. Where they differ, the lesson says so.

A query starts with SELECT: which columns, FROM which table. * means every column, which is handy for a quick look but best left out of real queries.

SELECT name, city
FROM customers;
Returns
name | city
-----+-------
Ana  | Austin
Ben  | Denver
Cleo | Austin
Dev  | Boston
Eve  | Denver
Finn | NULL
Gus  | Boston
Hana | Austin
(8 rows)

WHERE keeps only the rows that match. Text goes in single quotes. Join conditions with AND and OR (use brackets when you mix them), and use IN (…) to match any value in a list.

SELECT name, price
FROM products
WHERE category = 'Garden' AND price < 30;
Returns
name        | price
------------+------
Duck feeder | 24
Pond net    | 12.5
(2 rows)

ORDER BY sorts the result: smallest first by default, or add DESC for largest first. LIMIT keeps only the first few rows. SQL Server writes SELECT TOP 3 instead of LIMIT 3.

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;
Returns
name       | price
-----------+------
Binoculars | 120
Raincoat   | 59
Bird bath  | 45.5
(3 rows)

A missing value is NULL, which means unknown. Nothing equals NULL, not even another NULL, so WHERE city = NULL matches no rows at all. Write city IS NULL or city IS NOT NULL instead. LIKE matches a pattern: % stands for any run of characters and _ for exactly one.

SELECT name
FROM customers
WHERE city IS NULL;
Returns
name
----
Finn
(1 row)

The order you write a query in isn't the order the database works in. It starts with FROM, keeps rows with WHERE, works out the SELECT list, then sorts with ORDER BY and cuts with LIMIT. That's why, in most databases, WHERE can't use a column alias you made in SELECT, but ORDER BY can.

Practice

List the name and price of every product in the Books category.

The tables
  • customers (id, name, city, joined)
  • products (id, name, category, price)
  • orders (id, customer_id, ordered_on, status)
  • order_items (order_id, product_id, qty)
  • signups (id, email, name, signed_up_at)
  • payments (id, order_id, amount, currency, paid_at)
More info

Quick reference

SELECT a, b FROM tColumns a and b from table t.
WHERE …Keep only rows where the condition is true.
AND, OR, NOT, IN (…)Combine conditions; IN matches any value in the list.
IS NULL, IS NOT NULLTest for a missing value. Never = NULL.
LIKE 'a%', 'a_'% is any characters, _ is exactly one.
ORDER BY x DESCSort, largest first. ASC (smallest first) is the default.
LIMIT nOnly the first n rows (TOP n in SQL Server).

The other AI and data engineering courses