Chapter 8 Exercises: Subqueries and Common Table Expressions

Chapter 8 Exercises: Subqueries and Common Table Expressions

These exercises progress from basic subquery mechanics through advanced CTE patterns. All queries use the Meridian National Bank schema: CUSTOMER, ACCOUNT, BRANCH, TRANSACTION, EMPLOYEE, and LOAN.

Section A: Scalar Subqueries (Exercises 1-5)

Exercise 1: Above-Average Balance

Write a query that returns all accounts whose BALANCE is greater than the average balance across all accounts. Include ACCOUNT_ID, ACCOUNT_TYPE, and BALANCE in the output.

Exercise 2: Scalar Subquery in SELECT

Write a query that lists each branch (BRANCH_ID, BRANCH_NAME) alongside the total number of employees at that branch, computed as a scalar subquery in the SELECT list.

Exercise 3: Comparing to a Specific Branch

Write a query that returns all accounts whose balance exceeds the average balance of accounts at branch ID 101. Use a scalar subquery to compute the branch-specific average.

Exercise 4: Most Recent Transaction Date

Write a query that displays each account's ACCOUNT_ID, BALANCE, and the date of its most recent transaction (as a scalar subquery). Include accounts that have no transactions (the transaction date should appear as NULL).

Exercise 5: Scalar Subquery Error

The following query sometimes produces SQLCODE -811. Explain why and fix it:

SELECT c.CUSTOMER_ID, c.FIRST_NAME,
       (SELECT a.BALANCE FROM ACCOUNT a
        WHERE a.CUSTOMER_ID = c.CUSTOMER_ID) AS BALANCE
FROM   CUSTOMER c;

Section B: Table Subqueries — IN, NOT IN, ANY, ALL (Exercises 6-10)

Exercise 6: Customers with Savings Accounts

Using an IN subquery, find all customers (CUSTOMER_ID, FIRST_NAME, LAST_NAME) who have at least one account of type 'SAVINGS'.

Exercise 7: NOT IN with NULL Safety

Write a query to find customers who do not have a loan. Use NOT IN first, then rewrite using NOT EXISTS. Explain why NOT EXISTS is safer if the LOAN table's CUSTOMER_ID column is nullable.

Exercise 8: ANY Operator

Write a query to find all accounts whose balance is greater than ANY loan amount in the LOAN table. Then rewrite it using a MIN aggregate in a scalar subquery to show the equivalence.

Exercise 9: ALL Operator

Find accounts whose balance is greater than ALL account balances at branch 105. Then rewrite using MAX in a scalar subquery.

Exercise 10: Multi-Column IN

Write a query that finds all transactions where the (ACCOUNT_ID, TRANSACTION_TYPE) combination matches a specific set of accounts that have been flagged for review. Use a subquery that identifies savings accounts opened before 2020.

Section C: Correlated Subqueries (Exercises 11-16)

Exercise 11: Latest Transaction per Account

Write a correlated subquery to find the most recent transaction for each account. Return ACCOUNT_ID, TRANSACTION_ID, TRANSACTION_DATE, and AMOUNT.

Exercise 12: Customers Above Their Branch Average

Find customers whose total account balance exceeds the average total balance of customers at the same branch. This requires a correlated subquery that computes a branch-specific average.

Exercise 13: Accounts with Above-Average Transaction Counts

Find accounts that have more transactions than the average number of transactions per account at the same branch.

Exercise 14: Correlated UPDATE

Write an UPDATE statement that sets each account's LAST_ACTIVITY_DATE to the date of its most recent transaction. Only update accounts that have at least one transaction.

Exercise 15: Correlated DELETE

Write a DELETE statement that removes all transaction records older than 7 years, but only for accounts that have been closed (STATUS = 'CLOSED').

Exercise 16: Rewrite as Join

Take your answer to Exercise 12 and rewrite it without correlated subqueries, using only CTEs and joins. Compare the readability of both versions.

Section D: EXISTS and NOT EXISTS (Exercises 17-22)

Exercise 17: Customers with Accounts

Rewrite Exercise 6 (customers with savings accounts) using EXISTS instead of IN.

Exercise 18: Dormant Accounts

Find all accounts that have had no transactions in the last 90 days. Use NOT EXISTS.

Exercise 19: Branches with No Loans

Find branches where no customer has taken out a loan. Use NOT EXISTS with appropriate correlation.

Exercise 20: Multi-Table EXISTS

Find customers who have both a savings account AND a checking account. Use two EXISTS subqueries combined with AND.

Exercise 21: EXISTS vs. COUNT

A colleague wrote this query to find customers with exactly one account:

SELECT c.CUSTOMER_ID, c.FIRST_NAME, c.LAST_NAME
FROM   CUSTOMER c
WHERE  EXISTS (SELECT 1 FROM ACCOUNT a WHERE a.CUSTOMER_ID = c.CUSTOMER_ID)
  AND  NOT EXISTS (
    SELECT 1 FROM ACCOUNT a1
    JOIN ACCOUNT a2 ON a1.CUSTOMER_ID = a2.CUSTOMER_ID
                   AND a1.ACCOUNT_ID < a2.ACCOUNT_ID
    WHERE a1.CUSTOMER_ID = c.CUSTOMER_ID
  );

Rewrite this more simply using a subquery with COUNT. Which version do you think DB2 would execute more efficiently? Why?

Exercise 22: Anti-Join Pattern

Rewrite Exercise 18 (dormant accounts) as a LEFT JOIN with a NULL check instead of NOT EXISTS. Explain why both forms express the anti-join pattern.

Section E: Derived Tables (Exercises 23-25)

Exercise 23: Pre-Aggregation Join

Write a query that shows each branch name alongside the total number of accounts, using a derived table for the aggregation. Join the derived table to BRANCH for the name.

Exercise 24: Filtering on Aggregates

Find branches where the average account balance is above $25,000. Use a derived table that computes the branch-level average, then filter in the outer query.

Exercise 25: Derived Table vs. HAVING

Rewrite Exercise 24 using GROUP BY and HAVING directly (without a derived table). Compare the two approaches and state when each is preferable.

Section F: Common Table Expressions (Exercises 26-32)

Exercise 26: Basic CTE

Rewrite Exercise 23 (branch account counts) using a CTE instead of a derived table.

Exercise 27: Multi-CTE Chain

Write a query with two CTEs: 1. First CTE: Compute each customer's total balance across all accounts. 2. Second CTE: Compute the bank-wide average of those customer totals.

The main query should return customers whose total balance is above the bank-wide average, showing their name, total balance, the bank average, and the difference.

Exercise 28: CTE Referenced Twice

Using a single CTE that computes average balance per branch, write a query that finds pairs of branches where one branch's average balance is more than double the other's. Reference the CTE twice with different aliases.

Exercise 29: CTE with EXISTS

Write a CTE that identifies "high-activity accounts" (more than 50 transactions in the last year). Use this CTE with EXISTS in the main query to find customers who own at least one high-activity account.

Exercise 30: Multi-Step Business Logic

Meridian Bank wants a report: "For each branch, show the number of premium customers (total deposits > $100,000), the number of regular customers, and the percentage of customers who are premium." Build this with at least three CTEs.

Exercise 31: CTE for Running Comparison

Write a query using CTEs to compare each month's total deposits to the prior month's total deposits across the entire bank for the current year. Show month, current total, prior total, and percentage change.

Exercise 32: Refactor Nested Subqueries

Refactor the following deeply nested query into CTEs:

SELECT b.BRANCH_NAME
FROM   BRANCH b
WHERE  b.BRANCH_ID IN (
    SELECT a.BRANCH_ID
    FROM   ACCOUNT a
    WHERE  a.CUSTOMER_ID IN (
        SELECT c.CUSTOMER_ID
        FROM   CUSTOMER c
        WHERE  c.CUSTOMER_ID IN (
            SELECT l.CUSTOMER_ID
            FROM   LOAN l
            WHERE  l.AMOUNT > 50000
        )
    )
    GROUP BY a.BRANCH_ID
    HAVING COUNT(*) > 3
);

Section G: Mixed Patterns and Business Scenarios (Exercises 33-38)

Exercise 33: Loan Eligibility Check

A customer is eligible for a new loan if: (a) they have been a customer for at least 1 year, (b) their total account balance is at least $10,000, and (c) they have no delinquent loans. Write a single query using CTEs that returns all eligible customers with their total balance and tenure.

Exercise 34: Transaction Anomaly Detection

Find all transactions where the amount is more than 3 standard deviations above the average transaction amount for that account type. Use a CTE to compute the per-type statistics.

Exercise 35: Branch Performance Quartiles

Rank all branches into quartiles by total deposits. Use CTEs to compute branch totals, then use NTILE (or a manual approach with subqueries) to assign quartiles. Return branch name, total deposits, and quartile.

Exercise 36: Customer 360 View

Write a single query that returns, for each customer: their name, number of accounts, total balance, number of active loans, total loan amount, number of transactions in the last 30 days, and whether they qualify as "premium" (total balance > $100,000). Use CTEs for each dimension.

Exercise 37: Rewrite Challenge — Subquery to Join

Rewrite the following correlated subquery as an equivalent query using only joins and GROUP BY:

SELECT c.CUSTOMER_ID, c.FIRST_NAME, c.LAST_NAME
FROM   CUSTOMER c
WHERE  (SELECT COUNT(DISTINCT a.ACCOUNT_TYPE)
        FROM   ACCOUNT a
        WHERE  a.CUSTOMER_ID = c.CUSTOMER_ID) >= 3;

Exercise 38: Comparative Analysis

Write a query that compares each branch's average account balance to the bank-wide average, the average of branches in the same city, and the branch's own average from the prior year. Use CTEs for each comparison baseline.

Section H: Challenge Problems (Exercises 39-40)

Exercise 39: Consecutive Monthly Activity

Find customers who have had at least one transaction in every month of the current year (through the current month). This requires checking for the existence of transactions in each month — a classic set-based problem. Hint: use a CTE to generate the list of months, then check completeness with GROUP BY and HAVING.

Exercise 40: Comprehensive Bank Report

Build a single SQL statement that produces the following report:

BRANCH_NAME	TOTAL_CUSTOMERS	TOTAL_ACCOUNTS	TOTAL_DEPOSITS	AVG_BALANCE	ACTIVE_LOANS	PCT_PREMIUM	RANK

Where: - TOTAL_CUSTOMERS: distinct customers at the branch - TOTAL_ACCOUNTS: count of accounts - TOTAL_DEPOSITS: sum of all account balances - AVG_BALANCE: average account balance - ACTIVE_LOANS: count of active loans for customers at the branch - PCT_PREMIUM: percentage of customers with total balance > $100,000 - RANK: branch rank by total deposits (1 = highest)

Use at least four CTEs. The main query should be a simple SELECT with JOINs to the CTEs.

Answer Key Notes

Exercises 1-5: Focus on scalar subquery mechanics and the single-row requirement.
Exercises 6-10: Drill the IN/NOT IN/ANY/ALL operators and the NULL trap.
Exercises 11-16: Practice correlated subqueries and converting them to joins/CTEs.
Exercises 17-22: Build fluency with EXISTS/NOT EXISTS and the semi-join pattern.
Exercises 23-25: Bridge from derived tables to CTEs.
Exercises 26-32: Build CTE skills progressively.
Exercises 33-38: Apply all patterns to realistic business scenarios.
Exercises 39-40: Integration challenges requiring multiple techniques.