Common Table Expressions is a practical SQL concept used to design, query, secure, operate, or analyze relational data correctly.
WITH totals AS (SELECT customer_id, SUM(total) spent FROM orders GROUP BY customer_id)\nSELECT * FROM totals WHERE spent > 1000;Use the smallest correct statement, test it with representative data, and verify constraints and performance before production use.
An online shop stores customers, products, orders, and order items. The schema must prevent duplicate identities, missing required values, and orders that reference customers that do not exist.
BEGIN;\n-- Preview the exact target set first.\nSELECT id FROM orders WHERE status = 'pending';\n-- Apply the Common Table Expressions operation, verify affected rows, then commit.\nCOMMIT;The schema or operation satisfies the stated rule, rejects invalid data, and can be verified with a repeatable query.
This lesson reuses picostore. Relevant tables: customers, products, orders, order_items. Keep the starter rows from the Introduction lesson so results remain comparable.
SELECT o.order_id, c.name AS customer, o.status, o.total\nFROM orders o\nJOIN customers c ON c.customer_id = o.customer_id\nWHERE o.total >= 1000\nORDER BY o.ordered_at DESC;Run the verification query, compare the returned rows with the starter data, and explain why every included or excluded row is correct.
Start with a small customer table and retrieve active customers in a predictable order.
SELECT customer_id, name, email\nFROM customers\nWHERE status = 'active'\nORDER BY name;Use a CTE and a window function to rank customer revenue while keeping the query readable and testable.
WITH customer_revenue AS (\n SELECT customer_id, SUM(total_amount) AS revenue\n FROM orders\n WHERE order_status = 'completed'\n GROUP BY customer_id\n)\nSELECT customer_id, revenue,\n DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank\nFROM customer_revenue\nORDER BY revenue_rank, customer_id;Model the rule with keys and constraints instead of relying only on application code. MySQL and PostgreSQL support the core relational design, but generated-column, check-constraint, and alteration details can differ by server version.
Common Table Expressions is a practical SQL concept used to design, query, secure, operate, or analyze relational data correctly.
WITH totals AS (SELECT customer_id, SUM(total) spent FROM orders GROUP BY customer_id)\nSELECT * FROM totals WHERE spent > 1000;Use the smallest correct statement, test it with representative data, and verify constraints and performance before production use.
An online shop stores customers, products, orders, and order items. The schema must prevent duplicate identities, missing required values, and orders that reference customers that do not exist.
BEGIN;\n-- Preview the exact target set first.\nSELECT id FROM orders WHERE status = 'pending';\n-- Apply the Common Table Expressions operation, verify affected rows, then commit.\nCOMMIT;The schema or operation satisfies the stated rule, rejects invalid data, and can be verified with a repeatable query.
This lesson reuses picostore. Relevant tables: customers, products, orders, order_items. Keep the starter rows from the Introduction lesson so results remain comparable.
SELECT o.order_id, c.name AS customer, o.status, o.total\nFROM orders o\nJOIN customers c ON c.customer_id = o.customer_id\nWHERE o.total >= 1000\nORDER BY o.ordered_at DESC;Run the verification query, compare the returned rows with the starter data, and explain why every included or excluded row is correct.
Start with a small customer table and retrieve active customers in a predictable order.
SELECT customer_id, name, email\nFROM customers\nWHERE status = 'active'\nORDER BY name;Use a CTE and a window function to rank customer revenue while keeping the query readable and testable.
WITH customer_revenue AS (\n SELECT customer_id, SUM(total_amount) AS revenue\n FROM orders\n WHERE order_status = 'completed'\n GROUP BY customer_id\n)\nSELECT customer_id, revenue,\n DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank\nFROM customer_revenue\nORDER BY revenue_rank, customer_id;Model the rule with keys and constraints instead of relying only on application code. MySQL and PostgreSQL support the core relational design, but generated-column, check-constraint, and alteration details can differ by server version.