SQL lab: circuit inventory to activation handoff
Twelve small exercises using one circuit inventory. Find missing records, count readiness, and rehearse changes.
GUIDE12 published lessons · Updated 2026-09-25
Before you begin
Open a fresh SQLite database. Run lesson 1 once, then continue in order. All SQL runs locally; no production system is needed. The constraint failures in lesson 10 are intentional.
Working toward: Produce an exception report and explain what it leaves out.
Read each explanation, work through the example, and attempt the exercise before opening its answer. Published lessons are ready to study; unfinished roadmap topics remain planned.
Validation: Queries executed against an in-memory SQLite database; rollback and constraint failures checked.
1. Build a circuit inventory
Use a disposable SQLite database. This tiny inventory is the shared fixture for the next eleven exercises. A site can have several circuits; Helsinki deliberately has none. Keep unknown bandwidth as NULL.
PRAGMA foreign_keys = ON;
CREATE TABLE sites(id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);
CREATE TABLE circuits(id INTEGER PRIMARY KEY, site_id INTEGER NOT NULL REFERENCES sites(id), provider TEXT NOT NULL, state TEXT NOT NULL CHECK(state IN ('ready','pending','failed')), mbps INTEGER CHECK(mbps > 0), ticket TEXT);
INSERT INTO sites VALUES(1,'Phoenix'),(2,'Tokyo'),(3,'Helsinki');
INSERT INTO circuits VALUES(1,1,'A','ready',1000,NULL),(2,1,'B','pending',100,'INC-20'),(3,2,'A','ready',500,NULL),(4,2,'C','failed',NULL,'INC-40');What to expect
3 sites and 4 circuits.
Your turn
Count both tables.
Show answer and reasoning
SELECT COUNT(*) FROM sites; -- 3
SELECT COUNT(*) FROM circuits; -- 4Watch for: Run setup once in a fresh database; later examples assume it exists.
Lesson references
Link to this lesson2. Find the unfinished work
Start with the actual question: which circuits are not ready? Return identifiers you can act on and choose a stable sort order.
SELECT id, provider, state FROM circuits WHERE state <> 'ready' ORDER BY id;What to expect
2 | B | pending; 4 | C | failed.
Your turn
Return only the failed circuit.
Show answer and reasoning
SELECT id FROM circuits WHERE state = 'failed'; -- 4Watch for: A row appearing first without ORDER BY is not a priority rule.
Lesson references
Link to this lesson3. Unknown is not zero
NULL means the bandwidth was not recorded. Testing it with equals does not produce true. Use IS NULL so missing evidence stays visible.
SELECT id FROM circuits WHERE mbps IS NULL;What to expect
4.
Your turn
Display unknown bandwidth as text.
Show answer and reasoning
SELECT id, COALESCE(CAST(mbps AS TEXT), 'unknown') FROM circuits ORDER BY id;Watch for: Replacing unknown bandwidth with zero in storage loses the distinction between missing data and a measured value.
Lesson references
Link to this lesson4. Find sites with no circuits
A left join preserves the sites on the left. A missing match gives NULL circuit columns. Test the non-nullable circuit ID.
SELECT s.name FROM sites s LEFT JOIN circuits c ON c.site_id=s.id WHERE c.id IS NULL;What to expect
Helsinki.
Your turn
List every site and its circuit count.
Show answer and reasoning
SELECT s.name, COUNT(c.id) FROM sites s LEFT JOIN circuits c ON c.site_id=s.id GROUP BY s.id,s.name ORDER BY s.id;Watch for: COUNT(*) counts the preserved Helsinki row; COUNT(c.id) correctly reports zero.
Lesson references
Link to this lesson5. Count ready circuits without losing sites
Aggregate a conditional value so zero remains an explicit result. The output answers readiness, not whether a site has any inventory at all.
SELECT s.name, SUM(CASE WHEN c.state='ready' THEN 1 ELSE 0 END) AS ready FROM sites s LEFT JOIN circuits c ON c.site_id=s.id GROUP BY s.id,s.name ORDER BY s.id;What to expect
Phoenix | 1; Tokyo | 1; Helsinki | 0.
Your turn
Count failed circuits instead.
Show answer and reasoning
SELECT s.name, SUM(CASE WHEN c.state='failed' THEN 1 ELSE 0 END) FROM sites s LEFT JOIN circuits c ON c.site_id=s.id GROUP BY s.id,s.name ORDER BY s.id;Watch for: Filtering ready circuits in WHERE after the join removes sites with no ready match.
Lesson references
Link to this lesson6. Keep groups that need attention
WHERE filters input rows; HAVING filters the grouped result. This query finds sites with more than one recorded circuit.
SELECT site_id, COUNT(*) AS total FROM circuits GROUP BY site_id HAVING COUNT(*) > 1 ORDER BY site_id;What to expect
1 | 2; 2 | 2.
Your turn
Find providers used by more than one circuit.
Show answer and reasoning
SELECT provider, COUNT(*) FROM circuits GROUP BY provider HAVING COUNT(*)>1; -- A | 2Watch for: Grouping by both provider and circuit ID defeats the provider-level count.
Lesson references
Link to this lesson7. Calculate a readiness percentage
Integer arithmetic can silently discard the fraction. Multiply by 100.0 to request a real-number calculation. NULLIF protects the denominator for an empty table.
SELECT ROUND(100.0 * SUM(CASE WHEN state='ready' THEN 1 ELSE 0 END) / NULLIF(COUNT(*),0),1) AS ready_percent FROM circuits;What to expect
50.0.
Your turn
What should the interface display for an empty inventory?
Show answer and reasoning
No inventory, not 0% ready. A missing denominator is a different operational state.Watch for: A convenient zero can make missing data look like a measured failure rate.
Lesson references
Link to this lesson8. Require at least one ready circuit
EXISTS asks whether a matching row is present. It does not multiply the site result when several circuits qualify.
SELECT s.name FROM sites s WHERE EXISTS (SELECT 1 FROM circuits c WHERE c.site_id=s.id AND c.state='ready') ORDER BY s.id;What to expect
Phoenix; Tokyo.
Your turn
Find sites with no ready circuit.
Show answer and reasoning
SELECT s.name FROM sites s WHERE NOT EXISTS (SELECT 1 FROM circuits c WHERE c.site_id=s.id AND c.state='ready'); -- HelsinkiWatch for: A join may return a site several times; DISTINCT can conceal that you asked the wrong question.
Lesson references
Link to this lesson9. Rehearse a change and roll it back
A transaction lets you inspect an update before keeping it. This rehearsal changes one circuit, checks the result, and rolls back. Nothing remains changed.
BEGIN;
UPDATE circuits SET state='ready' WHERE id=2 AND state='pending';
SELECT state FROM circuits WHERE id=2;
ROLLBACK;
SELECT state FROM circuits WHERE id=2;What to expect
Inside transaction: ready. After rollback: pending.
Your turn
Why also check the old state in the UPDATE?
Show answer and reasoning
It avoids overwriting a different state that appeared after you last looked. Check the affected-row count before treating the operation as successful.Watch for: ROLLBACK cannot undo an earlier committed transaction.
Lesson references
Link to this lesson10. Let the database reject bad relationships
Foreign-key enforcement is enabled in setup for this connection. A circuit referring to site 99 must fail because that site does not exist.
INSERT INTO circuits VALUES(5,99,'D','ready',100,NULL);What to expect
FOREIGN KEY constraint failed; no new row.
Your turn
Try a bandwidth of zero for a valid site.
Show answer and reasoning
INSERT INTO circuits VALUES(5,1,'D','ready',0,NULL); -- CHECK constraint failedWatch for: In SQLite, foreign-key enforcement must be enabled for each connection before starting its transaction.
Lesson references
Link to this lesson11. State what an average excludes
AVG ignores NULL values. This result averages three known bandwidths, not four circuits. Return the counts beside it so the denominator is visible.
SELECT ROUND(AVG(mbps),2), COUNT(mbps), COUNT(*) FROM circuits;What to expect
533.33 | 3 known | 4 total.
Your turn
Compare with treating missing bandwidth as zero.
Show answer and reasoning
SELECT AVG(COALESCE(mbps,0)) FROM circuits; -- 400.0Watch for: Those two averages answer different questions. Label the population rather than silently filling missing observations.
Lesson references
Link to this lesson12. Produce a useful exception handoff
Combine the relationship, state, and ticket into one small result. The person receiving it can identify the site and follow the existing ticket.
SELECT s.name,c.id,c.state,COALESCE(c.ticket,'no ticket') AS ticket FROM sites s JOIN circuits c ON c.site_id=s.id WHERE c.state <> 'ready' ORDER BY s.name,c.id;What to expect
Phoenix | 2 | pending | INC-20; Tokyo | 4 | failed | INC-40.
Your turn
Which site is absent even though its activation may need work?
Show answer and reasoning
Helsinki. It has no circuits, so this inner join cannot show it. Pair this report with the missing-inventory query from lesson 4.Watch for: An exception report only covers the failure classes its query expresses.
Lesson references
Link to this lessonReferences
Original AUWEN lessons, with upstream documentation for further study and version checks.
All learning paths and update notes →