This lab lets you practise SELECT queries on one small fictional table called Books. It has twelve rows and the columns BookID, Title, Genre, Pages, Price and Stock.
Only read-only queries are accepted, so you can experiment freely, and each result comes with a step-by-step account of how it was worked out.
How do you use it?
- Pick one of the eight exercises. Each one is a plain-English task, for example “Show Title and Price of all Fantasy books.”
- Write your query in the text box. Some exercises start with a query that is almost right. Predict what it will return first.
- Press Run query. Reset returns the lab to its starting state.
- Read How it was worked out, then compare Your result with Expected result.
Supported: SELECT with columns, * or COUNT, SUM, AVG, MIN and MAX; FROM Books; WHERE with =, <>, <, >, <=, >=, AND, OR, NOT, LIKE, BETWEEN and IN; ORDER BY; LIMIT. Text values go in single quotes.
How do you read the result?
The steps section follows the real order of thought: FROM Books starts with all 12 rows, WHERE keeps only rows where the condition is true and says how many are left, ORDER BY sorts them, LIMIT keeps the first few, and SELECT chooses which columns to show.
Then you see your table next to the expected table, with a message on whether they match and whether the order also matters for this exercise.
Example walk-through
Choose the exercise “Show the Title of Mystery or Science books that are out of stock (Stock is 0)”. The starting query is:
SELECT Title FROM Books WHERE Genre = ‘Mystery’ OR Genre = ‘Science’ AND Stock = 0
Run it and you get three rows: Silent Harbour, Paper Lanterns and Night Train. The expected result has two rows. Why?
AND is applied before OR. So the query means “Mystery, or (Science and out of stock)”. Every Mystery book passes, stocked or not. No Science book has Stock 0, so the second part adds nothing.
Fix it with brackets: WHERE (Genre = ‘Mystery’ OR Genre = ‘Science’) AND Stock = 0. Now the genre test is done first, then the stock test. The result is Silent Harbour and Night Train, and it matches.
Try a second one: “Count the books that cost less than 20.” SELECT COUNT(*) FROM Books WHERE Price < 20 returns 5, from the prices 18.5, 14, 19.9, 16.5 and 9.9.
Then test the guard: type DROP TABLE Books and read the rejection.
What are the assumptions and limits?
- The data is fictional and fixed. No personal data is used and no outside database can be connected.
- Only one read-only SELECT is run. Extra statements, comments and file functions are rejected.
- It is a small evaluator, not a full database engine. Joins, GROUP BY and subqueries are not available, and mixing columns with aggregate functions is refused.
- Text comparisons need quotes. A number compared with text gives a clear error.
- It is for original practice. It does not mark assessed work or use your own data.
Which lessons explain the result?
Start with writing a simple SELECT filter. The bracket issue in the example is the subject of combining conditions with the correct Boolean grouping. When you get zero rows, read explaining why a query returns no rows.
For mixed questions, try databases and query reasoning practice, and see the whole topic in databases and query reasoning. If you study the practical ICT route, applying multiple query criteria correctly covers the same grouping idea in database software.
Want a teacher to check your queries with you? See online one-to-one Computer Science tuition or ICT tuition. More tools are in the learning tools directory.