These ten questions cover fields, records, keys, SELECT filters, Boolean grouping and empty results. They go from easy to harder. All use the same fictional Plants table, so no real personal data appears.
Write your own answer first, then open the worked solution. Test every query against all eight records, not only the likely ones.
| 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 |
Q1. How many fields and how many records does the table have?
Show answer
Fields are the columns: PlantID, Name, Type, Price, Stock, Indoor, so 6 fields. Records are the data rows P01 to P08, so 8 records. The header row is not a record.
Q2. Which field is the most suitable primary key, and why are Type and Indoor unsuitable?
Show answer
PlantID is different in every record and is created to be unique. Type repeats (Herb appears twice) and Indoor has only two values, so neither can identify one record.
Q3. Give the most suitable data type for Price, Stock and Indoor.
Show answer
Price: real (it has decimals such as 12.50). Stock: integer (whole numbers). Indoor: Boolean (TRUE or FALSE).
Q4. Write a query that shows the Name of every herb. Give the result.
Show answer
SELECT Name
FROM Plants
WHERE Type = "Herb"Records P04 and P06 pass. Result: Mint, Basil.
Q5. Write a query for the Name and Price of plants costing less than 10, and state how many rows return.
Show answer
SELECT Name, Price
FROM Plants
WHERE Price < 10Cactus 6.00, Mint 4.50, Basil 5.00, Aloe 9.50 pass. 4 rows.
Q6. Write a query that shows the Name of every plant that is out of stock.
Show answer
SELECT Name
FROM Plants
WHERE Stock = 0Only Rose has Stock 0. Result: Rose.
Q7. What does this return?
SELECT Name FROM Plants
WHERE Indoor = TRUE AND Price >= 9.5Show answer
Indoor plants: Fern 12.50, Cactus 6.00, Orchid 28.00, Aloe 9.50. Price at least 9.5: Fern, Orchid, Aloe pass; Cactus fails. Result: Fern, Orchid, Aloe.
Q8. Write a query for the Name of indoor plants, most expensive first, and give the result.
Show answer
SELECT Name
FROM Plants
WHERE Indoor = TRUE
ORDER BY Price DESCIndoor prices: Orchid 28.00, Fern 12.50, Aloe 9.50, Cactus 6.00. Result: Orchid, Fern, Aloe, Cactus.
Q9. A student wants herbs or succulents that cost more than 6, but writes:
SELECT Name FROM Plants
WHERE Type = "Herb" OR Type = "Succulent" AND Price > 6Give the rows this actually returns, then correct the query.
Show answer
AND is worked out first: Herb, OR (Succulent AND Price > 6). Mint and Basil pass as herbs. Cactus (6.00) fails. Aloe (9.50) passes. Actual result: Mint, Basil, Aloe.
Corrected query:
SELECT Name FROM Plants
WHERE (Type = "Herb" OR Type = "Succulent") AND Price > 6This returns Aloe only, because Mint, Basil and Cactus are all 6 or less.
Q10. Explain why this query returns no rows.
SELECT Name FROM Plants
WHERE Type = "Succulent" AND Stock > 20 AND Indoor = FALSEShow answer
Succulents are Cactus and Aloe. Stock above 20 leaves Cactus (25) only, since Aloe has 15. Cactus has Indoor = TRUE, so it fails Indoor = FALSE. No record passes all three, so no rows come back. The query is valid, but the single record that passes two tests fails the last one.
If you got these wrong
- Q1 to Q3 (words, keys, types): go back to distinguish field, record and table and choose a suitable key.
- Q4 to Q6 and Q8 (writing a filter, sorting): revisit write a simple SELECT filter.
- Q7 and Q9 (AND, OR, brackets): revisit combine conditions with the correct Boolean grouping.
- Q10 (empty results): revisit explain why a query returns no rows.
Add each slip to your mistake log with its cause, and retry the query in the read-only SQL practice lab a few days later. Back to the module overview to plan the next topic.
If the same errors keep returning, our teachers can help through online one-to-one Computer Science tuition.