SQL Lab — Subqueries and Derived Tables

🖥️

SQL Terminal

📚

Concepts

📊

Schema

📌 CHALLENGES

○Scalar subquery: avg delivery time

○IN subquery: electronics suppliers

○Correlated: suppliers above avg

○EXISTS: suppliers with no orders

○FROM subquery: derived table

○Final report submitted

0 / 6 complete

SQL TERMINAL — procurement_db

MySQL 8.0 — procurement_db Tables: suppliers, orders, products, categories Task: identify slow suppliers using subqueries Tip: open the Concepts window for syntax reference ──────────────────────────────────────────────

mysql>

SUBQUERY REFERENCE

SCALAR SUBQUERY

SELECT name FROM suppliers WHERE avg_days > ( SELECT AVG(avg_days) FROM suppliers );

IN SUBQUERY

SELECT * FROM suppliers WHERE id IN ( SELECT supplier_id FROM orders WHERE category = 'Electronics' );

CORRELATED SUBQUERY

SELECT s.name, s.avg_days FROM suppliers s WHERE s.avg_days > ( SELECT AVG(s2.avg_days) FROM suppliers s2 WHERE s2.category = s.category );

EXISTS

SELECT name FROM suppliers s WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.supplier_id = s.id );

FROM SUBQUERY (DERIVED TABLE)

SELECT cat, avg_del FROM ( SELECT category AS cat, AVG(avg_days) AS avg_del FROM suppliers GROUP BY category ) AS category_avg WHERE avg_del > 5;

SCHEMA — procurement_db

suppliers

⚿ id

name

MISSION BRIEFING

SUBQUERIES — MERIDIAN PROCUREMENT

SCENARIO

Meridian Procurement is losing money on late deliveries. The procurement director needs to know which suppliers are performing below average — but only within their own category. A supplier that takes 8 days in the Electronics category (where the average is 6) is more problematic than one taking 8 days in Heavy Freight (average 12). You need correlated subqueries to answer this correctly.

CHALLENGES

Find the overall average delivery time using a scalar subquery
Use IN to find all suppliers who have handled Electronics orders
Write a correlated subquery: suppliers whose avg_days exceeds their category average
Use NOT EXISTS to find suppliers with zero orders placed
Use a FROM subquery (derived table) to get avg delivery per category, then filter categories where that average exceeds 5 days
Submit your findings

KEY KEYWORDS

SELECT AVG(avg_days) FROM suppliers — scalar result
WHERE id IN (SELECT supplier_id FROM orders WHERE ...)
WHERE avg_days > (SELECT AVG(...) FROM suppliers s2 WHERE s2.category = s.category)
WHERE NOT EXISTS (SELECT 1 FROM orders WHERE supplier_id = s.id)
FROM (SELECT ... GROUP BY ...) AS alias

TIP

The Concepts window has working syntax templates for each subquery type. The terminal recognises the key clauses — you don't need to type every column name exactly.

Display Mode