Level 1 of 13 · Ordering off the menu

SQL SELECT and WHERE for Beginners: Free Interactive Lesson

SELECT picks the columns you want and WHERE picks the rows. By the end of this first lesson you can filter with AND, OR, IN, BETWEEN, LIKE and IS NULL.

Covers: SELECT · columns · AS · DISTINCT · LIMIT · WHERE · AND / OR · IN · BETWEEN · LIKE · IS NULL

Practise SELECT & WHERE

The lesson

Every example runs against the same small pizza shop database: pizzas, customers, orders and staff.

# What are SELECT and WHERE? SQL is the language you use to ask a database for data. SELECT says what you want to see, and WHERE says which rows. Why you need it: every list, report and record screen in an app starts as a SELECT. "Which pizzas cost less than 10?" is one line of SQL. - `SELECT name, price` the columns you want to see - `FROM pizzas` the table they come from - `WHERE price < 10` only the rows where the price is less than 10 The query answers that question. The next slides build it one piece at a time. > Read a query in the order the database works: FROM picks the table, WHERE picks the rows, SELECT picks the columns.

SELECT name, price
FROM pizzas
WHERE price < 10

# Read a whole table A database keeps its data in tables. A table looks like a spreadsheet: each row is one record, each column is one detail about it. This is the pizzas table, one row per pizza. - `SELECT` show me - `*` every column - `FROM pizzas` from the pizzas table > SQL doesn't care about capitals: select and SELECT do the same thing. Writing the keywords in capitals just makes a query easier to read.

SELECT * FROM pizzas

# Pick only some columns Swap the * for the names of the columns you want, with commas between them. - `SELECT name, price` only the name and price columns > In real projects, name your columns instead of using *. The query sends less data, and it won't break when someone adds a column to the table.

SELECT name, price FROM pizzas

# Rename, remove repeats, cut short - `AS town` shows the city column under the name town - `DISTINCT` removes repeated rows, so London appears once - `LIMIT 3` returns 3 rows at most > DISTINCT looks at the whole row, not one column. SELECT DISTINCT city, name still shows every customer, because no two rows are the same.

SELECT DISTINCT city AS town
FROM customers
LIMIT 3

# Keep only some rows: WHERE WHERE checks every row against a condition. Rows that pass come back. The rest are skipped. - `WHERE price > 11` keep the pizzas that cost more than 11 - `>` more than > SELECT picks columns, WHERE picks rows. "Fewer columns" means changing SELECT; "fewer rows" means adding a WHERE.

SELECT *
FROM pizzas
WHERE price > 11

# Comparing values - `=` equal to - `<>` not equal to - `>` `<` more than, less than - `>=` `<=` more than or equal, less than or equal - `'Leeds'` text goes in single quotes, numbers don't - `OR` one of the conditions is enough > Stick to single quotes for text: they work in every database. In some databases, double quotes mean a column name instead.

SELECT *
FROM customers
WHERE city = 'Leeds'
  OR city = 'London'

# AND is checked before OR We want customers with no nickname who live in Leeds or Manchester. But AND is checked before OR, so this query really asks for (no nickname AND Leeds) OR Manchester. Farah from Manchester comes back even though she has a nickname. - `AND` both conditions must be true - `OR` one condition is enough - `( )` brackets decide what is checked together - `IS NULL` the column is empty (the last slide of this level explains it) > Whenever a WHERE mixes AND and OR, add brackets: nickname IS NULL AND (city = 'Leeds' OR city = 'Manchester'). Edit the query above and run it.

SELECT name, city, nickname
FROM customers
WHERE nickname IS NULL
  AND city = 'Leeds'
  OR city = 'Manchester'

# Shortcuts: IN and BETWEEN - `IN ('Leeds', 'Bristol')` equal to any value in the list - `BETWEEN 9 AND 11` from 9 to 11, both ends included > BETWEEN includes both ends. With dates that matters: if the column also stores a time, BETWEEN '2026-01-01' AND '2026-01-31' misses everything after midnight on the 31st.

SELECT name, price
FROM pizzas
WHERE price BETWEEN 9 AND 11

# Search inside text: LIKE - `%` any number of characters, even none - `_` exactly one character - `'P%'` starts with P - `'%cheese%'` contains cheese > A pattern that starts with %, like '%cheese', can't use an index, so it is slow on big tables. 'P%' can use one.

SELECT *
FROM pizzas
WHERE name LIKE 'P%'

# Empty values: NULL NULL means "no value". It is not zero and not empty text: it means nobody knows. - `IS NULL` the value is empty - `IS NOT NULL` the value is filled in > nickname = NULL never matches anything, not even the empty nicknames. It's the most common NULL mistake, and a favourite interview question. Always write IS NULL.

SELECT name
FROM customers
WHERE nickname IS NULL

Appian corner

In Appian, a!queryRecordType does the SELECT: its fields parameter is your column list, and a!pagingInfo(batchSize: 3) is LIMIT 3. WHERE is a!queryFilter(field, operator, value), with operators like "=", "<>", ">", "in", "between", "starts with" and "is null", combined with a!queryLogicalExpression(operator: "AND" or "OR").

Practice questions

Try each one before opening the answer. To write and run them against the database in your browser, practise the level.

1. Every city your customers live in, each city only once.
Show the answer
```
SELECT DISTINCT city FROM customers
```
DISTINCT goes right after SELECT and drops the repeats.
2. Show only the name and price of every pizza.
```
SELECT ___, ___ FROM pizzas
```
Show the answer
```
SELECT name, price FROM pizzas
```
The two column names, as they appear in the pizzas table.
3. This should list every customer name, but Ace sat on the keyboard. Fix it.
```
SELECT name FORM customers
```
Show the answer
```
SELECT name FROM customers
```
Read the keywords slowly. One of them is misspelled.
4. Show the name of the first 3 pizzas, in a column called pizza_name.
Show the answer
```
SELECT name AS pizza_name
FROM pizzas
LIMIT 3
```
SELECT name AS pizza_name FROM pizzas LIMIT 3

5. Show the customers who live in Leeds.

SELECT *
FROM customers
WHERE city = ___

Show the answer

SELECT *
FROM customers
WHERE city = 'Leeds'

Text needs single quotes: 'Leeds'

6. Spicy pizzas (spicy = 1) that cost less than 12.
Show the answer
```
SELECT *
FROM pizzas
WHERE spicy = 1 AND price < 12
```
Two rules joined with AND.
7. How many rows come back?
```
SELECT name
FROM customers
WHERE nickname = NULL
```
- 3
- 4
- 0
- 7
Show the answer
0. Zero! = NULL is never true, not even for NULL. That is why IS NULL exists.
8. Every pizza with Cheese anywhere in its name.
Show the answer
```
SELECT *
FROM pizzas
WHERE name LIKE '%Cheese%'
```
LIKE '%Cheese%': % on both sides means anything before and anything after.
9. Customers in Leeds, Bristol or York, using IN.
Show the answer
```
SELECT *
FROM customers
WHERE city IN ('Leeds', 'Bristol',
  'York')
```
city IN ('Leeds', 'Bristol', 'York'). York has nobody, and that's fine.
10. Pizzas priced from 9 up to 11, both included.
Show the answer
```
SELECT *
FROM pizzas
WHERE price BETWEEN 9 AND 11
```
WHERE price BETWEEN 9 AND 11
11. Boss question! Customers with no nickname who live in Leeds or Manchester.
Show the answer
```
SELECT *
FROM customers
WHERE nickname IS NULL
  AND city IN ('Leeds',
    'Manchester')
```
nickname IS NULL AND (city = 'Leeds' OR city = 'Manchester'). The brackets matter!

Practise SELECT & WHERE