Complex Queries

Once you’ve mastered the basics, it’s time to tackle more complex queries that solve real-world problems.

Subqueries

A query nested inside another query:

SELECT title, price
FROM books
WHERE price > (SELECT AVG(price) FROM books);

Common Table Expressions (CTEs)

WITH high_value_orders AS (
    SELECT customer_id, SUM(total_amount) as total
    FROM orders
    GROUP BY customer_id
    HAVING SUM(total_amount) > 100
)
SELECT c.first_name, c.last_name, h.total
FROM customers c
JOIN high_value_orders h ON c.customer_id = h.customer_id;

Window Functions

SELECT
    title,
    price,
    AVG(price) OVER (PARTITION BY genre_id) as avg_genre_price
FROM books;

CASE Expressions

SELECT title,
    CASE
        WHEN price < 30 THEN 'Budget'
        WHEN price < 60 THEN 'Standard'
        ELSE 'Premium'
    END as price_category
FROM books;

Practice these techniques to become an SQL expert!