SQL Practice Exercises With Answers: 50 Beginner to Advanced Problems (2026)

SELECT name, email
FROM customers;

Why this works: A bare SELECT with no WHERE returns every row. Naming the columns explicitly is preferred over SELECT * for readability and stability.

SELECT *
FROM customers
WHERE country = 'Germany';

Why this works: Single-equals string comparison is the most basic WHERE filter. Quotes must be single quotes in standard SQL, not double.

SELECT id, customer_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC;

Why this works: ORDER BY ... DESC sorts descending; default is ascending. Sorting on a date column is index-friendly when an index exists on order_date.

SELECT COUNT(*) AS total_orders
FROM orders;

Why this works: COUNT(*) counts rows including those with NULLs. COUNT(column) ignores NULLs in that column — pick deliberately.

SELECT SUM(total_amount) AS lifetime_revenue
FROM orders;

Why this works: SUM ignores NULLs automatically, so you don't need COALESCE here unless you want a specific fallback.

SELECT AVG(total_amount) AS avg_order_value
FROM orders;

Why this works: AVG returns SUM/COUNT of non-null values. If you want a rounded display, wrap in ROUND(AVG(...), 2).

SELECT name, email
FROM customers
WHERE email LIKE '%@gmail.com';

Why this works: % matches any sequence of characters in LIKE. Anchor the pattern carefully — '%gmail%' would also match gmail.com.fake.example.

SELECT id, status, total_amount
FROM orders
WHERE status IN ('placed', 'shipped', 'delivered');

Why this works: IN is shorthand for multiple OR equality checks and reads more cleanly. Most planners optimize it identically.

SELECT id, order_date, total_amount
FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-03-31';

Why this works: BETWEEN is inclusive on both ends. For timestamps, prefer order_date >= '2026-01-01' AND order_date < '2026-04-01' to avoid end-of-day off-by-one issues.

SELECT o.id, o.order_date, c.name
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id;

Why this works: INNER JOIN returns rows only where both sides match. Aliasing tables (o, c) keeps the column references readable.

SELECT c.id, c.name, c.email
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Why this works: A LEFT JOIN keeps every customer; rows that didn't match get NULL on the right side. Filtering WHERE o.id IS NULL isolates the non-matchers. This is the canonical "anti-join" pattern.

SELECT DISTINCT category
FROM products
ORDER BY category;

Why this works: DISTINCT deduplicates across the selected columns. Sorting makes the output easier to scan.

SELECT id, name, created_at
FROM customers
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

Why this works: LIMIT caps row count, OFFSET skips rows. For large pagination prefer keyset pagination (WHERE created_at < :cursor) since OFFSET scans skipped rows.

SELECT id, name, email
FROM customers
WHERE country IS NULL;

Why this works: NULL is not equal to anything — country = NULL always returns unknown and matches no rows. Use IS NULL / IS NOT NULL.

SELECT
    id           AS order_no,
    total_amount AS amount_usd
FROM orders;

Why this works: AS assigns column aliases that flow into the result set, JSON exports, and downstream BI tools. The AS keyword itself is optional but improves readability.

SELECT
    p.category,
    SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON p.id = oi.product_id
GROUP BY p.category
ORDER BY revenue DESC;

Why this works: Compute the per-line revenue inside SUM, then group by category. Joining first lets us aggregate across the right grouping key.

SELECT category, COUNT(*) AS num_products
FROM products
GROUP BY category
HAVING COUNT(*) > 10
ORDER BY num_products DESC;

Why this works: WHERE filters rows before aggregation; HAVING filters groups after. You cannot put COUNT(*) > 10 in WHERE.

SELECT
    c.id,
    c.name,
    SUM(o.total_amount) AS lifetime_spend
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY lifetime_spend DESC
LIMIT 10;

Why this works: Group by the primary key plus any non-aggregated columns (PostgreSQL allows just the PK; other dialects require all selected columns). LIMIT after ORDER BY picks the top N.

SELECT
    DATE_TRUNC('month', order_date) AS order_month,
    COUNT(*) AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY order_month;

Why this works: DATE_TRUNC rounds a date down to a unit. MySQL equivalent: DATE_FORMAT(order_date, '%Y-%m-01'). SQL Server: DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1).

SELECT
    c.id,
    c.name,
    SUM(o.total_amount) AS lifetime_spend
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
HAVING SUM(o.total_amount) > (
    SELECT AVG(spend)
    FROM (
        SELECT SUM(total_amount) AS spend
        FROM orders
        GROUP BY customer_id
    ) per_customer
);

Why this works: The inner subquery computes per-customer spend, then the next layer averages those numbers. HAVING compares each group's sum to the constant scalar.

SELECT p.id, p.name
FROM products p
WHERE EXISTS (
    SELECT 1
    FROM order_items oi
    WHERE oi.product_id = p.id
);

Why this works: EXISTS short-circuits at the first match per outer row, which beats IN for big subqueries. The SELECT 1 is convention — the value is ignored.

SELECT name FROM customers
UNION
SELECT name FROM employees
ORDER BY name;

Why this works: UNION deduplicates; UNION ALL preserves duplicates and is faster when you know there are no overlaps. Both sides must have the same column count and compatible types.

-- PostgreSQL / SQL Server
SELECT customer_id AS user_id FROM orders
INTERSECT
SELECT user_id FROM events;

-- Customers who exist in events but never placed an order:
SELECT user_id FROM events
EXCEPT
SELECT customer_id FROM orders;

Why this works: INTERSECT returns rows in both queries, EXCEPT (MINUS in Oracle) returns rows in the first but not the second. MySQL versions before 8.0.31 do not support these — use semi/anti-joins instead.

SELECT
    c.name           AS customer_name,
    o.order_date,
    p.name           AS product_name,
    oi.quantity * oi.unit_price AS line_revenue
FROM orders o
JOIN customers   c  ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products    p  ON p.id = oi.product_id
ORDER BY o.order_date DESC;

Why this works: A 4-table star pattern: orders is the fact table; customers and products are dimensions; order_items is the bridge. Always JOIN through the natural keys.

SELECT
    e.name        AS employee,
    m.name        AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY e.name;

Why this works: A self-join queries the same table twice with different aliases. LEFT JOIN keeps top-of-hierarchy employees whose manager_id is NULL.

SELECT
    id,
    total_amount,
    CASE
        WHEN total_amount < 50  THEN 'small'
        WHEN total_amount <= 200 THEN 'medium'
        ELSE 'large'
    END AS size_tier
FROM orders;

Why this works: CASE expressions evaluate top-to-bottom and return the first matching result. The ELSE is optional but recommended — without it, unmatched rows return NULL.

SELECT
    name,
    COALESCE(country, 'Unknown') AS display_country
FROM customers;

Why this works: COALESCE returns the first non-null argument. Cleaner than CASE WHEN col IS NULL THEN ... and works across dialects.

-- PostgreSQL (FILTER clause)
SELECT
    customer_id,
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_orders
FROM orders
GROUP BY customer_id;

-- Cross-dialect equivalent (CASE inside SUM)
SELECT
    customer_id,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_orders
FROM orders
GROUP BY customer_id;

Why this works: FILTER applies a per-aggregate predicate without splitting into multiple queries. Falls back to SUM(CASE ...) in MySQL and SQL Server.

SELECT DISTINCT
    p.category,
    c.country
FROM products p
CROSS JOIN customers c
WHERE c.country IS NOT NULL
ORDER BY p.category, c.country;

Why this works: CROSS JOIN returns the cartesian product. Use it deliberately for grids and calendar tables; on big tables it explodes row counts (a×b).

SELECT AVG(per_customer_avg) AS avg_of_avgs
FROM (
    SELECT customer_id, AVG(total_amount) AS per_customer_avg
    FROM orders
    GROUP BY customer_id
) t;

Why this works: A derived table (subquery in FROM) lets you aggregate twice. AVG(total_amount) directly would be a row-weighted average, not customer-weighted.

WITH ranked AS (
    SELECT
        o.*,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date DESC
        ) AS rn
    FROM orders o
)
SELECT id, customer_id, order_date, total_amount
FROM ranked
WHERE rn <= 3
ORDER BY customer_id, order_date DESC;

Why this works: ROW_NUMBER with PARTITION BY resets per customer. You must wrap in a CTE/subquery because window functions are evaluated after WHERE.

SELECT
    name,
    salary,
    RANK()       OVER (ORDER BY salary DESC) AS r,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
FROM employees;

Why this works: Both functions assign the same value to ties. RANK leaves a gap after a tie (1, 2, 2, 4), DENSE_RANK does not (1, 2, 2, 3). Use DENSE_RANK when "top 3 distinct salaries" is the goal.

SELECT
    name,
    department,
    salary,
    ROUND(
        100.0 * salary / SUM(salary) OVER (PARTITION BY department),
        2
    ) AS pct_of_dept
FROM employees;

Why this works: SUM(...) OVER (PARTITION BY department) returns the department total on every row without collapsing — perfect for share-of-total calculations.

WITH daily AS (
    SELECT
        order_date,
        SUM(total_amount) AS daily_revenue
    FROM orders
    GROUP BY order_date
)
SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM daily
ORDER BY order_date;

Why this works: The explicit frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW tells the engine exactly which rows to sum. Without it, the default frame is the same here, but being explicit avoids surprises with ties.

WITH daily AS (
    SELECT order_date, SUM(total_amount) AS daily_revenue
    FROM orders
    GROUP BY order_date
)
SELECT
    order_date,
    daily_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY order_date
        RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM daily
ORDER BY order_date;

Why this works: RANGE uses value-based windowing, so missing days don't shift the window. ROWS would average "the last 7 rows," which lies if days are missing.

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(total_amount) AS revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
        2
    ) AS mom_pct
FROM monthly
ORDER BY month;

Why this works: LAG returns the previous row's value within the window. Wrap the denominator in NULLIF(..., 0) to avoid divide-by-zero on the first row.

SELECT DISTINCT
    customer_id,
    FIRST_VALUE(total_amount) OVER w AS first_order_amount,
    LAST_VALUE(total_amount)  OVER w AS last_order_amount
FROM orders
WINDOW w AS (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

Why this works: LAST_VALUE with the default frame stops at the current row, which would just echo total_amount. The explicit UNBOUNDED FOLLOWING frame fixes that. WINDOW aliases keep the SQL DRY.

WITH per_customer AS (
    SELECT customer_id, SUM(total_amount) AS lifetime_spend
    FROM orders
    GROUP BY customer_id
)
SELECT
    customer_id,
    lifetime_spend,
    NTILE(4) OVER (ORDER BY lifetime_spend DESC) AS revenue_quartile
FROM per_customer;

Why this works: NTILE(n) splits an ordered set into n roughly equal buckets. Use 10 for deciles, 100 for percentiles. Much simpler than a self-join with manual thresholds.

WITH RECURSIVE chain AS (
    -- Anchor: top-level employees (no manager)
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recurse: join children to their parent in the CTE
    SELECT e.id, e.name, e.manager_id, c.depth + 1
    FROM employees e
    JOIN chain c ON c.id = e.manager_id
)
SELECT id, name, depth
FROM chain
ORDER BY depth, name;

Why this works: A recursive CTE has an anchor query and a recursive query joined by UNION ALL. The recursive part keeps firing until it returns no new rows. Always put a stop condition in mind — circular data infinite-loops.

-- PostgreSQL: built-in generate_series is simpler
SELECT generate_series(
    CURRENT_DATE - INTERVAL '29 days',
    CURRENT_DATE,
    INTERVAL '1 day'
)::date AS day;

-- Portable recursive CTE version
WITH RECURSIVE days(day) AS (
    SELECT CURRENT_DATE - INTERVAL '29 days'
    UNION ALL
    SELECT day + INTERVAL '1 day'
    FROM days
    WHERE day < CURRENT_DATE
)
SELECT day FROM days;

Why this works: A recursive CTE is the dialect-portable way to build a calendar table on the fly. PostgreSQL's generate_series is shorter when available.

WITH active_users AS (
    SELECT user_id, COUNT(*) AS logins
    FROM events
    WHERE event_name = 'login'
      AND event_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY user_id
    HAVING COUNT(*) >= 3
),
recent_spend AS (
    SELECT customer_id, SUM(total_amount) AS spend_30d
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY customer_id
)
SELECT
    a.user_id,
    a.logins,
    COALESCE(r.spend_30d, 0) AS spend_30d
FROM active_users a
LEFT JOIN recent_spend r ON r.customer_id = a.user_id
ORDER BY spend_30d DESC;

Why this works: Chaining CTEs lets you name intermediate result sets, which beats nested subqueries for readability. The optimizer treats them like inline views in modern PostgreSQL.

WITH signup_week AS (
    SELECT
        user_id,
        DATE_TRUNC('week', MIN(event_date))::date AS cohort_week
    FROM events
    WHERE event_name = 'signup'
    GROUP BY user_id
),
return_week AS (
    SELECT DISTINCT
        s.user_id,
        s.cohort_week,
        DATE_TRUNC('week', e.event_date)::date AS active_week
    FROM signup_week s
    JOIN events e ON e.user_id = s.user_id
    WHERE e.event_date >= s.cohort_week + INTERVAL '7 days'
      AND e.event_date <  s.cohort_week + INTERVAL '14 days'
)
SELECT
    s.cohort_week,
    COUNT(DISTINCT s.user_id) AS cohort_size,
    COUNT(DISTINCT r.user_id) AS week1_returners,
    ROUND(
        100.0 * COUNT(DISTINCT r.user_id) / NULLIF(COUNT(DISTINCT s.user_id), 0),
        2
    ) AS week1_retention_pct
FROM signup_week s
LEFT JOIN return_week r
       ON r.user_id = s.user_id
      AND r.cohort_week = s.cohort_week
GROUP BY s.cohort_week
ORDER BY s.cohort_week;

Why this works: Cohort math is two date keys: cohort week (when the user signed up) and activity week. Bucket signups, then probe activity in week+1 with a date-range join.

SELECT
    DATE_TRUNC('month', o.order_date)::date AS month,
    SUM(CASE WHEN p.category = 'electronics'
             THEN oi.quantity * oi.unit_price ELSE 0 END) AS electronics_rev,
    SUM(CASE WHEN p.category = 'books'
             THEN oi.quantity * oi.unit_price ELSE 0 END) AS books_rev,
    SUM(CASE WHEN p.category = 'clothing'
             THEN oi.quantity * oi.unit_price ELSE 0 END) AS clothing_rev
FROM orders o
JOIN order_items oi ON oi.order_id  = o.id
JOIN products    p  ON p.id         = oi.product_id
GROUP BY DATE_TRUNC('month', o.order_date)
ORDER BY month;

Why this works: Conditional aggregation pivots rows into columns without a dialect-specific PIVOT clause. SQL Server has a native PIVOT, PostgreSQL has the tablefunc extension's crosstab, but CASE-inside-SUM is portable.

-- Portable UNION ALL approach
SELECT product_id, 'Q1' AS quarter, q1_sales AS sales FROM product_sales_wide
UNION ALL
SELECT product_id, 'Q2', q2_sales FROM product_sales_wide
UNION ALL
SELECT product_id, 'Q3', q3_sales FROM product_sales_wide
UNION ALL
SELECT product_id, 'Q4', q4_sales FROM product_sales_wide;

-- PostgreSQL shortcut: VALUES + LATERAL
SELECT s.product_id, t.quarter, t.sales
FROM product_sales_wide s,
     LATERAL (VALUES
        ('Q1', s.q1_sales),
        ('Q2', s.q2_sales),
        ('Q3', s.q3_sales),
        ('Q4', s.q4_sales)
     ) AS t(quarter, sales);

Why this works: UNION ALL is the universal unpivot; LATERAL VALUES in PostgreSQL keeps it on a single source pass. SQL Server has a native UNPIVOT operator.

WITH user_days AS (
    SELECT DISTINCT user_id, event_date::date AS day
    FROM events
),
flagged AS (
    SELECT
        user_id,
        day,
        day - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day))::int AS island
    FROM user_days
)
SELECT
    user_id,
    MIN(day) AS run_start,
    MAX(day) AS run_end,
    COUNT(*) AS run_length
FROM flagged
GROUP BY user_id, island
ORDER BY user_id, run_start;

Why this works: The classic gaps-and-islands trick: subtract a row number from the date. Consecutive days have the same offset; gaps create new offsets, which become group keys.

WITH gaps AS (
    SELECT
        user_id,
        event_name,
        event_date,
        CASE
            WHEN event_date - LAG(event_date) OVER (
                     PARTITION BY user_id ORDER BY event_date
                 ) > INTERVAL '30 minutes'
              OR LAG(event_date) OVER (
                     PARTITION BY user_id ORDER BY event_date
                 ) IS NULL
            THEN 1 ELSE 0
        END AS new_session
    FROM events
),
sessionized AS (
    SELECT
        user_id,
        event_name,
        event_date,
        SUM(new_session) OVER (
            PARTITION BY user_id ORDER BY event_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS session_id
    FROM gaps
)
SELECT
    user_id,
    session_id,
    MIN(event_date) AS session_start,
    MAX(event_date) AS session_end,
    COUNT(*)        AS event_count
FROM sessionized
GROUP BY user_id, session_id
ORDER BY user_id, session_start;

Why this works: Mark every "session-starting" event with 1, then take a running sum — each new 1 increments a stable session id per user. Standard analytics primitive.

-- PostgreSQL / Oracle / SQL Server (modern): PERCENTILE_CONT
SELECT
    c.country,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY o.total_amount) AS median_order_value
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.country
ORDER BY median_order_value DESC;

-- MySQL fallback: average of middle row(s) using window functions
WITH ranked AS (
    SELECT
        c.country,
        o.total_amount,
        ROW_NUMBER() OVER (PARTITION BY c.country ORDER BY o.total_amount) AS rn,
        COUNT(*)    OVER (PARTITION BY c.country) AS cnt
    FROM customers c JOIN orders o ON o.customer_id = c.id
)
SELECT country, AVG(total_amount) AS median_order_value
FROM ranked
WHERE rn IN ((cnt + 1) / 2, (cnt + 2) / 2)
GROUP BY country;

Why this works: PERCENTILE_CONT is the standard median (interpolated). For older MySQL, manually pick the middle row(s) by row number and average them.

WITH first_event AS (
    SELECT
        user_id,
        MIN(event_date) FILTER (WHERE event_name = 'signup')      AS signup_at,
        MIN(event_date) FILTER (WHERE event_name = 'first_query') AS first_query_at,
        MIN(event_date) FILTER (WHERE event_name = 'checkout')    AS checkout_at
    FROM events
    GROUP BY user_id
)
SELECT
    COUNT(*) FILTER (WHERE signup_at IS NOT NULL)      AS signups,
    COUNT(*) FILTER (WHERE first_query_at >= signup_at) AS reached_first_query,
    COUNT(*) FILTER (WHERE checkout_at    >= first_query_at) AS reached_checkout,
    ROUND(100.0 *
        COUNT(*) FILTER (WHERE checkout_at >= first_query_at)
        / NULLIF(COUNT(*) FILTER (WHERE signup_at IS NOT NULL), 0), 2) AS overall_conversion_pct
FROM first_event;

Why this works: Pre-compute the first timestamp of each step per user with MIN ... FILTER, then count step-to-step adherence with the ordering constraint baked in. Funnels are easier when each user becomes one row.

WITH monthly AS (
    SELECT
        EXTRACT(YEAR  FROM order_date)::int AS yr,
        EXTRACT(MONTH FROM order_date)::int AS mo,
        SUM(total_amount) AS revenue
    FROM orders
    GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
)
SELECT
    cur.mo                AS month,
    cur.revenue           AS revenue_2026,
    prv.revenue           AS revenue_2025,
    ROUND(
        100.0 * (cur.revenue - prv.revenue) / NULLIF(prv.revenue, 0),
        2
    ) AS yoy_pct
FROM monthly cur
LEFT JOIN monthly prv
       ON prv.mo = cur.mo
      AND prv.yr = cur.yr - 1
WHERE cur.yr = 2026
ORDER BY cur.mo;

Why this works: Self-join the monthly aggregate to itself, lining up by mo and shifting the year. Wrap the denominator in NULLIF to keep months without a 2025 baseline from blowing up.

WITH first_seen AS (
    -- The day each customer placed their first order
    SELECT customer_id, MIN(order_date) AS first_order_date
    FROM orders
    GROUP BY customer_id
),
daily_new AS (
    SELECT first_order_date AS day, COUNT(*) AS new_customers
    FROM first_seen
    GROUP BY first_order_date
)
SELECT
    day,
    new_customers,
    SUM(new_customers) OVER (
        ORDER BY day
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_unique_customers
FROM daily_new
ORDER BY day;

Why this works: Cumulative COUNT DISTINCT is not a standard window function. Trick: per customer, find the day they were first seen, count those per day, then take a running sum. The cumulative sum equals the cumulative distinct count.