Subquery workshop: NULL, aliases, union, subquery
About this app
Five flashlights on the practice database prodat: 17 of 46 deliveries without a value, sum(days)/count(*) = 12 versus avg(days) = 20.55; table aliases c and o that vanish after the statement, and the same statement with join and on; union as the set union {1, 2, 3} ∪ {1, 3, 5} and on X and Y (7 values, union all 10); the customers from Hamburg as a subquery in where (2 customer IDs, 4 order lines, equal to the join) and a subquery in from named T.
With explain mode: an AI tutor walks you through the app, points out what matters in the scene and answers your questions.
A query returns a table again
The result of every SQL query is a table. That is why a query can be placed inside another one: in WHERE as a set after IN, read as “is an element of”, or in FROM as an ad-hoc table that needs a name. UNION combines two results and removes duplicates, UNION ALL keeps them. Table aliases such as customers c shorten a statement and hold only within that one statement. In averages NULL does not count: avg ignores missing values, count(*) does not.
What the app shows
Five scenes on the practice database prodat: 46 deliveries, 17 of them without a value, with sum(days)/count(*) = 12 versus avg(days) = 20.55; a join statement whose table names shrink to c and o, with the error no such column in the next statement; two sets and two columns falling into one pot while the duplicates fly out; the customers from Hamburg as an inner query whose two customer IDs find four orders, exactly like the join; and the table T built from 184 order lines, of which where total >= 30 keeps ten rows.
Try it yourself
Pick a scene and go through the steps: compute the average with and without NULL, shorten the table names, replace the comma with join, throw the sets into the pot, run the inner query and build T from the table orders. The numbers in the panel show rows, characters, elements and matches at each point.
Frequently asked questions
- What is a subquery in SQL?
- A query inside another query. In WHERE it returns a set of values, such as all customer IDs from Hamburg, and the outer query checks with IN whether a value is in it. In FROM it returns a table that needs an alias, for example (select …) T.
- What is the difference between UNION and UNION ALL?
- UNION combines the results of two queries and removes duplicate rows, like the set union {1, 2, 3} ∪ {1, 3, 5} = {1, 2, 3, 5}. UNION ALL just appends the results and keeps duplicates. Both parts need the same number of columns.
- Why is sum/count(*) not the same as avg?
- count(*) counts all rows, including those with NULL, while avg divides only by the number of known values. In SQLite, dividing two integers also truncates: 596 / 46 gives 12, whereas avg(days) gives 596 / 29 = 20.55.
- What can I try in the app?
- Five scenes with steps: NULL in the average, table aliases with join and on, UNION and UNION ALL on sets and tables, a subquery in WHERE with the equivalent join, and a subquery in FROM. An explanation mode guides you through everything.
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