A SELECT query chooses which fields to show (SELECT), which table to use (FROM) and which records to keep (WHERE). The result is a new, smaller table.
This is the core skill in databases and query reasoning. The same Plants table from the first lesson is used here.
| PlantID | Name | Type | Price | Stock | Indoor |
|---|---|---|---|---|---|
| P01 | Fern | Foliage | 12.50 | 8 | TRUE |
| P02 | Cactus | Succulent | 6.00 | 25 | TRUE |
| P03 | Rose | Flowering | 15.00 | 0 | FALSE |
| P04 | Mint | Herb | 4.50 | 40 | FALSE |
| P05 | Orchid | Flowering | 28.00 | 3 | TRUE |
| P06 | Basil | Herb | 5.00 | 12 | FALSE |
| P07 | Aloe | Succulent | 9.50 | 15 | TRUE |
| P08 | Hibiscus | Flowering | 18.00 | 6 | FALSE |
How do you build a query?
Write it in the same order every time:
SELECT field1, field2
FROM table
WHERE conditionUse * after SELECT to show every field. Put text values in quotation marks and leave numbers bare. The comparison operators are =, <> (not equal), <, <=, > and >=.
To sort the result, add ORDER BY field at the end (ascending by default, DESC for descending).
Worked example
Question: list the name and price of every plant that costs more than 10.00, cheapest first.
Step 1, choose fields to show: Name, Price.
Step 2, choose the table: Plants.
Step 3, write the condition: Price > 10.
Step 4, add the sort: ORDER BY Price.
SELECT Name, Price
FROM Plants
WHERE Price > 10
ORDER BY PriceStep 5, test every record. Fern 12.50 passes, Cactus 6.00 fails, Rose 15.00 passes, Mint 4.50 fails, Orchid 28.00 passes, Basil 5.00 fails, Aloe 9.50 fails, Hibiscus 18.00 passes.
Step 6, sort by price: 12.50, 15.00, 18.00, 28.00.
| Name | Price |
|---|---|
| Fern | 12.50 |
| Rose | 15.00 |
| Hibiscus | 18.00 |
| Orchid | 28.00 |
Four rows come back. Checking all eight records, not only the likely ones, is what keeps the answer right.
The mistake to watch for
A common slip is to put the tested field in SELECT instead of WHERE.
Mistaken answer:
SELECT Price > 10 FROM PlantsThis asks the database to display a comparison, not to filter records.
The correction: SELECT lists columns to display; WHERE holds the test. A second slip is leaving quotation marks off text, as in Type = Herb.
Check yourself
1. Write a query for the names of all herbs.
Show answer
SELECT Name
FROM Plants
WHERE Type = "Herb"Result: Mint, Basil.
2. How many rows does SELECT * FROM Plants WHERE Stock < 10 return?
Show answer
Stock under 10: Fern (8), Rose (0), Orchid (3), Hibiscus (6). That is 4 rows.
3. What changes if you write Price >= 28 instead of Price > 28?
Show answer
> 28 returns no rows, as no price is above 28.00. >= 28 returns Orchid (28.00).
Where this leads next
Next, combine conditions with correct Boolean grouping, then try the module practice set. Run your own queries in the read-only SQL practice lab.
If you want a teacher to check how you build each query, see our online one-to-one Computer Science tuition.