Module 17 · SQL and relational algebra · Drill

SQL and relational algebra drill

Scenario prompts that check whether sql and relational algebra is usable under pressure. Answer out loud, then compare.

4 prompts25 minNew

0 of 4 graded

Prompt 1
Tables: customers(id, name, city), orders(id, customer_id, ordered_at, total). Write SQL for: (1) every customer with their number of orders in 2026, including customers with none; (2) customers who have never ordered; (3) the top 2 customers by total spend in each city. Explain each choice of join and function.
Say your answer out loud first. Thirty seconds is enough.
Prompt 2
Write the relational algebra and then SQL for: 'names of students who have passed every subject in the Mathematics department', given Student(roll_no, name), Subject(subject_id, dept), Result(roll_no, subject_id, passed). Then explain how you would optimise the query tree.
Say your answer out loud first. Thirty seconds is enough.
Prompt 3
A dashboard query 'SELECT o.id, SUM(l.amount), SUM(p.amount) FROM orders o JOIN order_lines l ON l.order_id = o.id JOIN payments p ON p.order_id = o.id GROUP BY o.id' reports totals several times too large. Explain precisely why and rewrite it correctly in two ways.
Say your answer out loud first. Thirty seconds is enough.
Prompt 4
The product team wants an audit trail of every change to account balances, a rule that balances never go negative, and a read-only view of customer accounts for the support team without emails. Decide what to implement with constraints, triggers, views or application code, and justify each.
Say your answer out loud first. Thirty seconds is enough.