SQL query workshop
About this app
WHERE as a sieve: the tools table from the lecture is checked row by row, matches move into the result, the rest drops out. The quote trap tag = A shows 'no such column: A', plus price/weight calculated row by row, as and order by … desc with rows visibly changing places.
With explain mode: an AI tutor walks you through the app, points out what matters in the scene and answers your questions.
What an SQL query does
A query describes its result. FROM supplies the rows of a table, WHERE checks each row on its own against a condition, SELECT chooses and computes columns, ORDER BY orders the result. The table itself stays unchanged. Text goes in single quotes: without them SQL reads the word as a column name, and tag = A looks for a column A.
What the app shows
At the top is the query as a line of code, below it the table tools from the lecture with five tools (name, weight, price, tag). Next to it a check column compares row by row, for example 540 >= 500, and sets a tick or a cross. Matches glide into the result, the rest sinks away. SELECT computes price/weight row by row (320/109 = 2.936) and drops the other columns, as renames the column, and when sorting the rows visibly change places.
Try it yourself
Choose a prepared query, change the comparison operator and the threshold and combine conditions with and or or. Write tag = A without quotes and see the message no such column: A; a thought experiment shows what would be compared with a made-up column A. Switch the column type from REAL to INTEGER and see how SQLite divides as integers.
Frequently asked questions
- What does WHERE do in SQL?
- WHERE checks every row of the table on its own against a condition. Only rows for which the condition is true end up in the result. The table itself does not change.
- Why does SQL report no such column when I write tag = A?
- Without quotes A is not text but a name. SQL therefore looks for a column A, finds none and stops with no such column: A. The correct form is tag = 'A' with single quotes.
- Why does 320/109 give 2 in SQLite?
- If both columns are of type INTEGER, SQLite divides as integers and cuts off the decimals. With price * 1.0 / weight or with REAL columns the result is 2.936.
- What can I try in the app?
- Play prepared queries, change the comparison operator and threshold, combine conditions with and and or, look at the quote trap with its thought experiment, rename computed columns, sort ascending and descending and switch the column type between REAL and INTEGER.
Subject: Computer science
More from Computer science
- Ackermann function
- Elevator simulator
- Binary workshop
- Binary versus linear search
- The Collatz problem
- Inside a CPU
- Swap the inside
- One frame in 16 milliseconds — how a game engine renders an image (3D)
A VisuApp by heyprof: interactive, free in your browser, no sign-up. What is a VisuApp? · All apps