Chapter 5 Exercises: SQL Fundamentals

These exercises use the Meridian National Bank database. Ensure you have loaded the sample data from Chapter 4 before beginning. Exercises are organized by difficulty level, with a mixed-practice section at the end that blends topics.

Beginner Exercises (1-12)

Exercise 1: Basic Column Selection

Write a SELECT statement that returns the BRANCH_CODE, BRANCH_NAME, and CITY columns from the BRANCHES table.

Expected rows: 5

Exercise 2: Selecting All Columns

Write a query that returns all columns from the ACCOUNT_TYPES table. How many account types are defined?

Expected rows: 8

Exercise 3: Simple WHERE — Equality

Write a query that returns the FIRST_NAME, LAST_NAME, and CITY of all customers who live in Boston.

Expected rows: 3 (Franklin Wu, Grace O'Malley, and the business customer Boston Tech Solutions)

Exercise 4: WHERE with Comparison Operator

Find all accounts where the CURRENT_BALANCE is less than $2,000. Return the ACCOUNT_NUMBER, CUSTOMER_ID, and CURRENT_BALANCE.

Exercise 5: WHERE with BETWEEN

Find all customers who became customers between January 1, 2015 and December 31, 2020 (inclusive). Return CUSTOMER_ID, FIRST_NAME, LAST_NAME, and CUSTOMER_SINCE.

Exercise 6: WHERE with IN

Find all employees whose DEPARTMENT is either 'Lending' or 'Management'. Return EMPLOYEE_NUMBER, FIRST_NAME, LAST_NAME, JOB_TITLE, and DEPARTMENT. Sort by department, then last name.

Exercise 7: WHERE with LIKE

Find all customers whose email address ends with @email.com. Return FIRST_NAME, LAST_NAME, and EMAIL_ADDRESS.

Exercise 8: IS NULL

Find all customers who do not have a PHONE_SECONDARY on file (i.e., it is NULL). Return CUSTOMER_ID, FIRST_NAME, LAST_NAME, and PHONE_PRIMARY.

Exercise 9: ORDER BY — Single Column

Write a query that returns all branches sorted by OPEN_DATE from the oldest to the newest. Return BRANCH_NAME, CITY, and OPEN_DATE.

Exercise 10: ORDER BY — Descending

List all accounts sorted by CURRENT_BALANCE in descending order (highest first). Return ACCOUNT_NUMBER and CURRENT_BALANCE. Show only the top 10.

Exercise 11: DISTINCT

Write a query that returns the distinct list of cities where Meridian National Bank has customers. How many unique cities are there?

Exercise 12: FETCH FIRST

Write a query that returns the 5 oldest customers (by CUSTOMER_SINCE date). Return FIRST_NAME, LAST_NAME, and CUSTOMER_SINCE, sorted by CUSTOMER_SINCE ascending.

Intermediate Exercises (13-25)

Exercise 13: AND / OR Combination

Find all individual customers (CUSTOMER_TYPE = 'I') in either New York or New Jersey. Return CUSTOMER_ID, FIRST_NAME, LAST_NAME, STATE_CODE, and CUSTOMER_TYPE. Use parentheses appropriately.

Exercise 14: Multiple Conditions

Find all accounts that meet ALL of the following criteria: - Account type is 'SAV' (savings) - Current balance is at least $10,000 - The account is active (STATUS = 'A')

Return ACCOUNT_NUMBER, CUSTOMER_ID, CURRENT_BALANCE, and INTEREST_RATE. Sort by balance descending.

Exercise 15: CASE Expression — Simple

Write a query against the CUSTOMERS table that includes a column called CUST_TYPE_DESC that translates the CUSTOMER_TYPE code: 'I' becomes 'Individual', 'B' becomes 'Business'. Return CUSTOMER_ID, FIRST_NAME, LAST_NAME, and CUST_TYPE_DESC.

Exercise 16: CASE Expression — Searched

Write a query against the ACCOUNTS table that categorizes each account's balance: - Balance >= $100,000: 'High Value' - Balance >= $10,000: 'Standard' - Balance >= $1,000: 'Low Balance' - Balance < $1,000: 'At Risk'

Return ACCOUNT_NUMBER, CURRENT_BALANCE, and the category as BALANCE_CATEGORY. Sort by balance descending.

Exercise 17: String Functions

Write a query that displays each customer's name in the format "LAST_NAME, First_Name" (last name in uppercase, first name as-is). Return this as a single column called FORMATTED_NAME. Sort by the formatted name.

Exercise 18: Date Functions

Write a query that returns each customer's FIRST_NAME, LAST_NAME, and the year they became a customer (extracted from CUSTOMER_SINCE). Add a computed column called YEARS_AS_CUSTOMER that calculates the approximate number of whole years since they joined (use YEAR(CURRENT DATE) - YEAR(CUSTOMER_SINCE)). Sort by tenure descending.

Exercise 19: COALESCE

Write a query that returns each customer's FIRST_NAME, LAST_NAME, and a column called CONTACT_PHONE that shows: - PHONE_PRIMARY if it is not NULL - PHONE_SECONDARY if PRIMARY is NULL - 'No phone on file' if both are NULL

Use COALESCE.

Exercise 20: SUBSTR and Pattern Analysis

Using the ACCOUNTS table, extract the account type portion of the ACCOUNT_NUMBER (characters 5-7, e.g., 'CHK', 'SAV', 'MMA') into a column called ACCT_TYPE_EXTRACTED. Compare it with the actual ACCOUNT_TYPE_CODE column. Are they always the same?

Hint: SUBSTR(ACCOUNT_NUMBER, 5, 3)

Exercise 21: NOT IN

Find all customers who do NOT have a checking account (ACCOUNT_TYPE_CODE NOT IN ('CHK', 'CHI')). This requires a subquery — write a query that first identifies customer IDs that DO have checking accounts, then finds customers NOT IN that list.

SELECT CUSTOMER_ID, FIRST_NAME, LAST_NAME
FROM MERIDIAN.CUSTOMERS
WHERE CUSTOMER_ID NOT IN (
    SELECT CUSTOMER_ID
    FROM MERIDIAN.ACCOUNTS
    WHERE ACCOUNT_TYPE_CODE IN ('CHK', 'CHI')
);

Run this query. Does every customer have a checking account? How many (if any) do not?

Exercise 22: CASE in ORDER BY

Write a query that returns all employees, sorted so that Branch Managers appear first, then Senior Loan Officers, then Loan Officers, then all others. Within each group, sort by last name. Use a CASE expression in the ORDER BY clause.

Exercise 23: NULL-Safe Comparison

Write a query that finds all transactions where RELATED_ACCOUNT_ID is NULL (meaning the transaction is not linked to another account). Return TRANSACTION_ID, ACCOUNT_ID, TRANSACTION_TYPE, AMOUNT, and DESCRIPTION. Limit to the first 10 rows.

Exercise 24: Date Arithmetic

Write a query that finds all accounts opened more than 10 years ago. Return ACCOUNT_NUMBER, OPEN_DATE, and a computed column showing how many days ago the account was opened (using the DAYS function).

Exercise 25: Complex Filtering with Functions

Find all customers in Connecticut whose last name is longer than 8 characters and whose email address contains a period before the @ sign. Return FIRST_NAME, LAST_NAME, EMAIL_ADDRESS, and LENGTH(LAST_NAME) as NAME_LENGTH.

Advanced Exercises (26-35)

Exercise 26: Full Name Formatting with NULL Handling

Write a single query that constructs each customer's full name in the format: - "LastName, FirstName MiddleName" if MIDDLE_NAME is not NULL - "LastName, FirstName" if MIDDLE_NAME is NULL

Return this as FORMATTED_NAME along with CUSTOMER_ID. Sort alphabetically by the formatted name.

Hint: Use COALESCE or CASE to handle the MIDDLE_NAME.

Exercise 27: Transaction Summary with CASE

Write a query against the TRANSACTIONS table that classifies each transaction into one of these categories based on the absolute value of the AMOUNT: - 'Micro' for amounts less than $100 - 'Small' for amounts from $100 to $999.99 - 'Medium' for amounts from $1,000 to $4,999.99 - 'Large' for amounts $5,000 and above

Return TRANSACTION_ID, AMOUNT, ABS(AMOUNT) as ABS_AMOUNT, and the category as SIZE_CATEGORY. Order by ABS_AMOUNT descending. Show only the top 20.

Exercise 28: Account Age and Interest Analysis

Write a query that returns all interest-bearing accounts (INTEREST_RATE > 0) along with: - The account number - The interest rate expressed as a percentage (multiplied by 100, rounded to 2 decimal places) - The age of the account in years (approximate, using YEAR functions) - A category: 'New' if opened within the last 5 years, 'Established' if 5-15 years, 'Legacy' if over 15 years

Sort by interest rate descending.

Exercise 29: Employee Salary Bands

Write a query that assigns each active employee to a salary band: - Band 1: Under $50,000 - Band 2: $50,000 - $79,999 - Band 3: $80,000 - $109,999 - Band 4: $110,000 - $139,999 - Band 5: $140,000 and above

Display EMPLOYEE_NUMBER, FIRST_NAME || ' ' || LAST_NAME as EMPLOYEE_NAME, JOB_TITLE, SALARY, and SALARY_BAND. Handle the case where SALARY might be NULL (show 'Unclassified'). Sort by salary band, then salary descending within each band.

Exercise 30: Loan Portfolio Query

Write a query that shows all active loans with: - Loan ID - Loan type decoded to a readable name (MORTGAGE -> 'Mortgage Loan', AUTO -> 'Auto Loan', PERSONAL -> 'Personal Loan', HELOC -> 'Home Equity Line', etc.) - Original amount - Current principal - The percentage of the loan that has been paid off: ROUND((1 - CURRENT_PRINCIPAL / ORIGINAL_AMOUNT) * 100, 1) as PCT_PAID - Rate type decoded: 'F' -> 'Fixed', 'V' -> 'Variable' - Monthly payment

Sort by percentage paid descending.

Exercise 31: Multi-Table Query Preview

Using a WHERE-based join (formal joins are in Chapter 6), write a query that returns each customer's name alongside the number of accounts they hold. You will need the CUSTOMERS and ACCOUNTS tables.

SELECT C.FIRST_NAME, C.LAST_NAME,
       -- COUNT is an aggregate function (Chapter 7 preview)
       COUNT(*) AS NUM_ACCOUNTS
FROM MERIDIAN.CUSTOMERS C,
     MERIDIAN.ACCOUNTS A
WHERE C.CUSTOMER_ID = A.CUSTOMER_ID
GROUP BY C.FIRST_NAME, C.LAST_NAME
ORDER BY NUM_ACCOUNTS DESC;

Run this query. Which customer has the most accounts?

Exercise 32: Transaction Channel Analysis

Write a query against the TRANSACTIONS table that returns only transactions made through the 'ATM' channel, showing: - TRANSACTION_DATE - ACCOUNT_ID - AMOUNT (should be negative for withdrawals) - ABS(AMOUNT) as WITHDRAWAL_AMOUNT - A label: 'Small' if under $100, 'Medium' if $100-$299, 'Large' if $300 or more

Sort by TRANSACTION_DATE DESC, WITHDRAWAL_AMOUNT DESC.

Exercise 33: Data Quality Check

Write a query that identifies potential data quality issues in the CUSTOMERS table. Specifically, find customers where: - EMAIL_ADDRESS is NULL, OR - PHONE_PRIMARY is NULL, OR - DATE_OF_BIRTH is NULL

Return CUSTOMER_ID, FIRST_NAME, LAST_NAME, and a column called MISSING_FIELDS that lists which fields are missing, e.g., 'email, phone' or 'dob'.

Hint: Use CASE expressions inside a concatenation to build the MISSING_FIELDS string.

Exercise 34: Pagination

Write three queries that paginate through all accounts, 10 per page, sorted by ACCOUNT_NUMBER: - Page 1: first 10 accounts - Page 2: accounts 11-20 - Page 3: accounts 21-30

Use OFFSET and FETCH FIRST for each.

Exercise 35: Comprehensive Query

Write a single query that produces a "Customer Dashboard" view showing: - Customer ID - Full name (handling NULL middle names) - Customer type ('Individual' or 'Business') - City and state formatted as "City, ST" - Customer since date - Years as customer (approximate) - Risk rating translated to a label: 1='Very Low', 2='Low', 3='Medium', 4='High', 5='Very High'

Include only active customers, sorted by risk rating (highest first), then by years as customer (longest first).

Mixed Practice Section (36-40)

These exercises combine multiple concepts from the chapter and require you to think through the solution holistically.

Exercise 36: The Manager's Report

The branch manager at Meridian Main Street (BRANCH_ID = 1) needs a report of all employees at her branch. She wants to see: - Employee number - Full name (last name, first name format) - Job title - Hire date - Years of service (approximate) - Salary (display NULL salaries as 'Not disclosed')

Sort by years of service, longest first.

Exercise 37: Fee Investigation

The compliance team wants to review all fee transactions (TRANSACTION_TYPE = 'FEE') across all accounts. For each fee, show: - Account ID - Transaction date - Amount (as a positive number using ABS) - Description - Channel

Also include a column that flags whether the fee is "above average" or "typical." Consider fees above $20 as "Above Average" and $20 or below as "Typical."

Exercise 38: Account Type Deep Dive

Using the ACCOUNT_TYPES table, write a query that returns all account types with: - Type code - Type name - Category - Interest-bearing status (decoded to 'Yes' or 'No') - Minimum balance formatted with a dollar sign (e.g., '$2500.00') - Description truncated to 50 characters (use SUBSTR), with '...' appended if the original was longer

Sort by category, then minimum balance descending.

Exercise 39: Customer Search Utility

Imagine you are building a customer search feature. Write a query that finds customers matching ALL of these criteria: - Last name starts with any letter from A through F (use BETWEEN or LIKE) - They are in an active status - They have been a customer for at least 5 years - Their city is either Hartford, Boston, or New York

Return CUSTOMER_ID, a formatted full name, CITY, and CUSTOMER_SINCE. Sort by LAST_NAME.

Exercise 40: The Complete Transaction Statement

Write a query that produces a bank-statement-style output for Alice Thornton's checking account (ACCOUNT_ID = 1000000) for November 2025. Include: - Transaction date - Description - A 'Debit' column showing the absolute value of negative amounts (NULL for positive amounts) - A 'Credit' column showing positive amounts (NULL for negative amounts) - Running balance - Channel

Sort by TRANSACTION_DATE, then by TRANSACTION_TS (for same-day ordering).

Hint: Use CASE expressions to split the AMOUNT into Debit and Credit columns. Use NULLIF or CASE to show NULL instead of 0.

Answer Key Notes

Suggested solutions for all exercises are available in the instructor's guide. For self-study, run each query and verify the output against the expected row counts listed above. If your row counts differ, check your WHERE clause logic carefully — most errors come from incorrect operator precedence (missing parentheses with AND/OR) or mishandling NULLs.

Exercises for Chapter 5 of "IBM DB2: From First Query to Enterprise Architecture"