Writing simple SQL queries

A simple SQL query picks columns with SELECT, names the table with FROM, filters rows with WHERE and sorts with ORDER BY. To predict the output, filter the rows first, then choose the columns.

This lesson follows reading an entity relationship diagram. Later, joining related tables extends these ideas.

What does a simple query look like?

SELECT column1, column2
FROM table
WHERE condition
ORDER BY column1;

To predict the output, work in this order: find the table, keep matching rows, show the columns, then sort.

Worked example: a canteen table

The original table Item has five rows.

Query:

SELECT ItemName, Price
FROM Item
WHERE Category = 'Drink' AND Price < 3
ORDER BY Price;

Step 1: filter. Keep rows where Category is Drink and Price is below 3. Rows 1 (2.50) and 4 (1.50) pass. Row 3 fails because 3.00 is not less than 3.

Step 2: choose columns. Show ItemName and Price.

Step 3: sort. ORDER BY Price puts the smaller price first.

Output.

The mistake that costs marks

Students swap AND and OR. Change AND to OR in the same query and more rows appear.

With OR, the row only needs one true condition. Milo is a drink, so it passes even though its price is 3.00. Another slip is forgetting quotation marks around ‘Drink’.

Check yourself

Using the Item table, write a query that shows the names of all food items, sorted by price. Then write what it returns.

SELECT ItemName
FROM Item
WHERE Category = 'Food'
ORDER BY Price;

What to study next

Test the four lessons together with the data and databases practice set. When you are ready to query more than one table, read joining related tables.

If you want a teacher to trace queries with you, see online one-to-one Computer Science tuition.