Chapter 7 Exercises: Aggregation and Grouping

These exercises build your fluency with aggregate functions, GROUP BY, HAVING, ROLLUP, CUBE, and GROUPING SETS. They progress from straightforward recall to multi-table analytical queries against the Meridian National Bank database. Do not skip the easy ones — retrieval of "obvious" patterns builds the speed you need for harder problems later.

Difficulty Ratings: - Beginner — Recall and basic application. If you read the chapter, you can answer these. - Intermediate — Requires combining multiple concepts or thinking through edge cases. - Advanced — Multi-table queries, complex grouping, or performance-oriented problems.

All exercises assume you are connected to the MERIDIAN database with the schema set to MERIDIAN.

Part A: Aggregate Function Fundamentals

Exercise 7.1 — COUNT Three Ways (Beginner)

Write a single query against the CUSTOMERS table that returns three values in one row: 1. The total number of customers (all rows) 2. The number of customers who have a middle name recorded 3. The number of distinct states where customers reside

Do not use three separate queries. One SELECT, three columns.

Exercise 7.2 — SUM and AVG Basics (Beginner)

Write a query that returns the total salary paid to all active employees and the average salary. Include the count of employees in the result.

Exercise 7.3 — MIN and MAX on Dates (Beginner)

Write a query that finds the date the oldest account was opened and the date the newest account was opened. Also return the number of years between them (use YEAR() on the dates or compute the difference).

Exercise 7.4 — SUM with Conditional Aggregation (Intermediate)

Using only the TRANSACTIONS table, write a single query that returns: 1. Total number of transactions 2. Total credits (positive amounts) 3. Total debits (negative amounts, shown as a positive number) 4. Net amount (credits minus debits)

Use CASE expressions inside SUM. Filter to only completed transactions (STATUS = 'C') in November 2025.

Exercise 7.5 — AVG and NULL Behavior (Intermediate)

The ACCOUNTS table has an INTEREST_RATE column. Some accounts have a rate of 0.0000 (non-interest-bearing checking) while the column is defined as DECIMAL with a default of 0.0000.

Write two queries: 1. Average interest rate across all active accounts (including zero-rate accounts) 2. Average interest rate across only accounts where the rate is greater than zero

Explain in a comment why the two results differ and which one a manager would want for a "what is our average yield?" report.

Exercise 7.6 — Defensive COALESCE (Beginner)

Write a query that returns the total balance of all closed accounts (STATUS = 'C'). Ensure the result is 0 rather than NULL if no closed accounts exist.

Exercise 7.7 — COUNT(DISTINCT) in Context (Intermediate)

Write a single query that returns: 1. Total number of transactions 2. Number of distinct accounts that have at least one transaction 3. Number of distinct transaction dates 4. Number of distinct channels used

Part B: GROUP BY

Exercise 7.8 — Single-Column Grouping (Beginner)

Write a query that shows the number of employees in each department. Order by headcount descending.

Exercise 7.9 — Multi-Column Grouping (Beginner)

Write a query that counts accounts by BRANCH_ID and ACCOUNT_TYPE_CODE. Include the total balance for each group. Order by BRANCH_ID, then ACCOUNT_TYPE_CODE.

Exercise 7.10 — Grouping by Expression (Intermediate)

Write a query that groups transactions by the day of the week (Monday, Tuesday, etc.) and shows the count and total dollar volume for each day. Order by day number (1=Sunday through 7=Saturday) so the days appear in calendar order.

Hint: Use DAYNAME() and DAYOFWEEK() functions.

Exercise 7.11 — Balance Tier Analysis (Intermediate)

Create a balance tier report for all active accounts using these tiers: - Tier 1: Under $5,000 - Tier 2: $5,000 to $49,999 - Tier 3: $50,000 to $249,999 - Tier 4: $250,000 and above

Show the tier label, account count, total balance, average balance, and the percentage of total bank deposits each tier represents.

Hint: To compute percentages, you can use a scalar subquery for the grand total or a window function.

Exercise 7.12 — JOIN then GROUP (Intermediate)

Write a query that shows each branch name, the number of customers at that branch, the total deposit balance, and the average balance. Use a JOIN between BRANCHES and ACCOUNTS. Order by total balance descending.

Exercise 7.13 — Three-Table Aggregation (Intermediate)

Write a query that joins TRANSACTIONS, ACCOUNTS, and BRANCHES to show the total transaction dollar volume per branch for November 2025. Include branch name, transaction count, total credits, total debits, and net amount.

Exercise 7.14 — GROUP BY with LEFT JOIN (Advanced)

Write a query that lists ALL branches (even those with no transactions in December 2025) with their December 2025 transaction counts and total amounts. Use a LEFT JOIN so branches with zero transactions still appear with a count of 0.

Be careful about where you place the date filter — ON clause or WHERE clause.

Part C: HAVING

Exercise 7.15 — Basic HAVING (Beginner)

Write a query that finds customers who have more than 2 accounts. Show the customer ID, account count, and total balance. Use HAVING to filter.

Exercise 7.16 — WHERE and HAVING Together (Intermediate)

Find branches where the average balance of checking accounts (type codes 'CHK' and 'CHI') exceeds the bank-wide average balance of all accounts. Use WHERE to filter to checking accounts and HAVING with a subquery for the comparison.

Exercise 7.17 — HAVING without GROUP BY (Intermediate)

Write a query that returns the total payroll (sum of all salaries) only if the total exceeds $1,000,000. If it does not exceed $1,000,000, the query should return zero rows. Do not use WHERE for this condition.

Exercise 7.18 — Performance Comparison (Advanced)

Write the following query two ways: - Version A: Filter BRANCH_ID = 1 using HAVING - Version B: Filter BRANCH_ID = 1 using WHERE

Both should return the same result (account count and total balance for branch 1). In a comment, explain which version is more efficient and why.

Part D: ROLLUP, CUBE, and GROUPING SETS

Exercise 7.19 — Basic ROLLUP (Intermediate)

Write a query using ROLLUP to show account counts and total balances grouped by STATE_CODE and BRANCH_NAME, with subtotals for each state and a grand total. Join BRANCHES and ACCOUNTS.

Exercise 7.20 — Three-Level ROLLUP (Advanced)

Write a query using ROLLUP on three columns: STATE_CODE, BRANCH_NAME, and ACCOUNT_TYPE_NAME (from the ACCOUNT_TYPES table). The report should show: 1. Detail rows (state + branch + account type) 2. Branch subtotals (state + branch) 3. State subtotals (state only) 4. Grand total

Use GROUPING() with CASE to label the summary rows clearly.

Exercise 7.21 — CUBE Cross-Tabulation (Advanced)

Create a cross-tabulation report using CUBE that shows transaction counts and total amounts grouped by TRANSACTION_TYPE and CHANNEL. The result should include: - Detail: each type + channel combination - Marginal: totals for each transaction type (across all channels) - Marginal: totals for each channel (across all transaction types) - Grand total

Use GROUPING() to label summary rows.

Exercise 7.22 — Custom GROUPING SETS (Advanced)

Management wants a report that shows: 1. Total deposits by branch 2. Total deposits by account category (CHECKING, SAVINGS, etc.) 3. Grand total

They do NOT want the branch + category cross-tabulation detail. Write this using GROUPING SETS (not ROLLUP or CUBE, since neither produces exactly this set of groupings).

Exercise 7.23 — GROUPING() for Clean Output (Advanced)

Take your answer from Exercise 7.19 and enhance it with GROUPING() so that: - State subtotal rows show "All Branches" in the BRANCH_NAME column - The grand total row shows "All States" in the STATE_CODE column and "Grand Total" in the BRANCH_NAME column - Detail rows show the actual state code and branch name

Order the output so detail rows come first within each state, followed by the state subtotal, with the grand total last.

Part E: Real-World Reporting

Exercise 7.24 — Monthly Trend Report (Advanced)

Create a monthly transaction trend report for Meridian Bank that shows, for each month: - Number of transactions - Number of unique customers involved (requires joining to ACCOUNTS and CUSTOMERS) - Total credits - Total debits - Net amount - Average transaction size

Include a grand total row using ROLLUP.

Exercise 7.25 — Customer Segmentation (Advanced)

Segment Meridian's customers into these categories based on their total relationship value (sum of all account balances): - Mass Market: Under $25,000 - Affluent: $25,000 to $249,999 - High Net Worth: $250,000 to $999,999 - Ultra High Net Worth: $1,000,000 and above

For each segment, show: customer count, total deposits, average deposits, minimum deposits, and maximum deposits.

Exercise 7.26 — Loan-to-Deposit Ratio by Branch (Advanced)

Calculate the loan-to-deposit ratio for each branch. This is: total outstanding loan principal / total deposit balance. Join ACCOUNTS, LOANS, and BRANCHES. Show the branch name, total deposits, total outstanding loans, and the ratio as a percentage. Use COALESCE to handle branches with no loans.

Exercise 7.27 — Fee Revenue Analysis (Advanced)

Write a query that shows fee revenue (TRANSACTION_TYPE = 'FEE') by branch and month using ROLLUP. Include: - Branch name - Month - Number of fees charged - Total fee amount - Average fee amount

Label summary rows using GROUPING().

Exercise 7.28 — Top N per Group (Advanced)

Find the top 2 customers by total balance for EACH branch. This requires combining aggregation with a ranking technique. You may use a common table expression (CTE) or a subquery if you have encountered them, or solve it with correlated subqueries.

Hint: One approach is to compute the customer totals per branch, then use ROW_NUMBER() OVER (PARTITION BY branch ORDER BY total DESC) to rank within each branch.

Exercise 7.29 — Year-Over-Year Comparison (Advanced)

Although our sample data only covers late 2025, write a query structure that would compare the current month's transaction volume against the same month last year. Use CASE expressions and aggregation to put both months' data side by side. Include columns for: current month count, prior year count, count change, current month amount, prior year amount, amount change.

Exercise 7.30 — The Complete Dashboard Query (Advanced)

Write a single query that produces a dashboard for each branch showing: 1. Branch name and state 2. Number of customers 3. Number of accounts 4. Total deposits (CHECKING + SAVINGS + CERTIFICATE categories) 5. Total loan balance 6. Checking total 7. Savings total 8. Number of transactions in the last 30 days 9. Total transaction volume in the last 30 days

This will require joining five or more tables and using conditional aggregation. It is the kind of query that a real dashboard application would execute.

Solutions Approach

For exercises marked Advanced, consider your solution in stages: 1. Write the FROM and JOIN clauses first 2. Add the WHERE filters 3. Add GROUP BY 4. Add the SELECT with aggregate functions 5. Add HAVING if needed 6. Add ORDER BY last

This bottom-up approach prevents the common mistake of trying to write the entire query at once and getting lost in the complexity.

If you completed all 30 exercises, you have a strong command of DB2 aggregation. The patterns in Part E — conditional aggregation, ROLLUP/CUBE for reporting, LEFT JOIN with defensive COALESCE — will appear in nearly every database position you ever hold.