Chapter 6 Exercises: Joining Tables

Chapter 6 Exercises: Joining Tables

All exercises use the Meridian National Bank schema:

CUSTOMER (CUSTOMER_ID, FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, EMAIL, PHONE, ADDRESS, CITY, STATE, ZIP_CODE, CREATED_DATE)
ACCOUNT (ACCOUNT_ID, CUSTOMER_ID, BRANCH_ID, ACCOUNT_TYPE, BALANCE, OPENED_DATE, STATUS)
BRANCH (BRANCH_ID, BRANCH_NAME, ADDRESS, CITY, STATE, ZIP_CODE, PHONE, MANAGER_ID)
TRANSACTION (TXN_ID, ACCOUNT_ID, TXN_TYPE, AMOUNT, TXN_DATE, DESCRIPTION, RELATED_ACCOUNT_ID)
EMPLOYEE (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, BRANCH_ID, POSITION, HIRE_DATE, SALARY, MANAGER_EMPLOYEE_ID)

Section A: INNER JOIN Fundamentals (Exercises 1-8)

Exercise 1: Basic Two-Table Join

Write a query that lists every account along with its owner's first name, last name, and email address. Order the results by last name.

Expected columns: FIRST_NAME, LAST_NAME, EMAIL, ACCOUNT_ID, ACCOUNT_TYPE, BALANCE

Solution

SELECT c.FIRST_NAME,
       c.LAST_NAME,
       c.EMAIL,
       a.ACCOUNT_ID,
       a.ACCOUNT_TYPE,
       a.BALANCE
FROM CUSTOMER c
INNER JOIN ACCOUNT a
    ON c.CUSTOMER_ID = a.CUSTOMER_ID
ORDER BY c.LAST_NAME;

Exercise 2: Join with Filter

List all checking accounts with a balance greater than $2,000, showing the customer name and branch name. Order by balance descending.

Expected columns: CUSTOMER_NAME, ACCOUNT_ID, BALANCE, BRANCH_NAME

Solution

SELECT c.FIRST_NAME || ' ' || c.LAST_NAME AS CUSTOMER_NAME,
       a.ACCOUNT_ID,
       a.BALANCE,
       b.BRANCH_NAME
FROM CUSTOMER c
INNER JOIN ACCOUNT a
    ON c.CUSTOMER_ID = a.CUSTOMER_ID
INNER JOIN BRANCH b
    ON a.BRANCH_ID = b.BRANCH_ID
WHERE a.ACCOUNT_TYPE = 'CHECKING'
  AND a.BALANCE > 2000
ORDER BY a.BALANCE DESC;

Exercise 3: Self-Join — Employee Hierarchy

Write a query that shows each employee's name, position, and their manager's name. Include the column headers EMPLOYEE_NAME, POSITION, and MANAGER_NAME.

Solution

SELECT e.FIRST_NAME || ' ' || e.LAST_NAME AS EMPLOYEE_NAME,
       e.POSITION,
       m.FIRST_NAME || ' ' || m.LAST_NAME AS MANAGER_NAME
FROM EMPLOYEE e
INNER JOIN EMPLOYEE m
    ON e.MANAGER_EMPLOYEE_ID = m.EMPLOYEE_ID
ORDER BY MANAGER_NAME, EMPLOYEE_NAME;

Exercise 4: Three-Table Join

Write a query that shows every transaction with the associated account type and the customer's full name. Only include transactions from 2024. Order by transaction date.

Expected columns: TXN_ID, TXN_DATE, CUSTOMER_NAME, ACCOUNT_TYPE, TXN_TYPE, AMOUNT

Solution

SELECT t.TXN_ID,
       t.TXN_DATE,
       c.FIRST_NAME || ' ' || c.LAST_NAME AS CUSTOMER_NAME,
       a.ACCOUNT_TYPE,
       t.TXN_TYPE,
       t.AMOUNT
FROM TRANSACTION t
INNER JOIN ACCOUNT a
    ON t.ACCOUNT_ID = a.ACCOUNT_ID
INNER JOIN CUSTOMER c
    ON a.CUSTOMER_ID = c.CUSTOMER_ID
WHERE YEAR(t.TXN_DATE) = 2024
ORDER BY t.TXN_DATE;

Exercise 5: Multi-Column Join Condition

Suppose there is an AUDIT_LOG table with columns (ACCOUNT_ID, TXN_DATE, AUDIT_STATUS). Write a query that joins TRANSACTION to AUDIT_LOG matching on both ACCOUNT_ID and TXN_DATE to find transactions that have been audited.

Expected columns: TXN_ID, ACCOUNT_ID, TXN_DATE, AMOUNT, AUDIT_STATUS

Solution

SELECT t.TXN_ID,
       t.ACCOUNT_ID,
       t.TXN_DATE,
       t.AMOUNT,
       al.AUDIT_STATUS
FROM TRANSACTION t
INNER JOIN AUDIT_LOG al
    ON t.ACCOUNT_ID = al.ACCOUNT_ID
   AND t.TXN_DATE = al.TXN_DATE
ORDER BY t.TXN_DATE;

Exercise 6: Join with Aggregation

Write a query that counts the number of accounts at each branch. Show the branch name and account count, ordered by count descending.

Expected columns: BRANCH_NAME, ACCOUNT_COUNT

Solution

SELECT b.BRANCH_NAME,
       COUNT(a.ACCOUNT_ID) AS ACCOUNT_COUNT
FROM BRANCH b
INNER JOIN ACCOUNT a
    ON b.BRANCH_ID = a.BRANCH_ID
GROUP BY b.BRANCH_NAME
ORDER BY ACCOUNT_COUNT DESC;

Exercise 7: Non-Equi Join

Using a hypothetical INTEREST_RATE table with columns (MIN_BALANCE, MAX_BALANCE, RATE), write a query that assigns each account its applicable interest rate based on its balance falling within the range.

Expected columns: ACCOUNT_ID, ACCOUNT_TYPE, BALANCE, RATE

Solution

SELECT a.ACCOUNT_ID,
       a.ACCOUNT_TYPE,
       a.BALANCE,
       ir.RATE
FROM ACCOUNT a
INNER JOIN INTEREST_RATE ir
    ON a.BALANCE >= ir.MIN_BALANCE
   AND a.BALANCE < ir.MAX_BALANCE
ORDER BY a.BALANCE;

Exercise 8: Four-Table Join

Write a query that shows, for each transaction, the customer name, account type, branch name, and branch city. Only include deposit transactions. Order by amount descending.

Expected columns: CUSTOMER_NAME, ACCOUNT_TYPE, BRANCH_NAME, BRANCH_CITY, TXN_ID, AMOUNT, TXN_DATE

Solution

SELECT c.FIRST_NAME || ' ' || c.LAST_NAME AS CUSTOMER_NAME,
       a.ACCOUNT_TYPE,
       b.BRANCH_NAME,
       b.CITY AS BRANCH_CITY,
       t.TXN_ID,
       t.AMOUNT,
       t.TXN_DATE
FROM TRANSACTION t
INNER JOIN ACCOUNT a
    ON t.ACCOUNT_ID = a.ACCOUNT_ID
INNER JOIN CUSTOMER c
    ON a.CUSTOMER_ID = c.CUSTOMER_ID
INNER JOIN BRANCH b
    ON a.BRANCH_ID = b.BRANCH_ID
WHERE t.TXN_TYPE = 'DEPOSIT'
ORDER BY t.AMOUNT DESC;

Section B: OUTER JOINs (Exercises 9-16)

Exercise 9: Basic LEFT OUTER JOIN

Write a query that lists all customers and their accounts. Customers with no accounts should appear with NULL values in the account columns. Order by customer last name.

Solution

SELECT c.CUSTOMER_ID,
       c.FIRST_NAME,
       c.LAST_NAME,
       a.ACCOUNT_ID,
       a.ACCOUNT_TYPE,
       a.BALANCE
FROM CUSTOMER c
LEFT OUTER JOIN ACCOUNT a
    ON c.CUSTOMER_ID = a.CUSTOMER_ID
ORDER BY c.LAST_NAME;

Exercise 10: Anti-Join — Finding Missing Data

Find all customers who have never made a transaction on any of their accounts. Show customer ID, name, and email.

Solution

SELECT c.CUSTOMER_ID,
       c.FIRST_NAME || ' ' || c.LAST_NAME AS CUSTOMER_NAME,
       c.EMAIL
FROM CUSTOMER c
INNER JOIN ACCOUNT a
    ON c.CUSTOMER_ID = a.CUSTOMER_ID
LEFT OUTER JOIN TRANSACTION t
    ON a.ACCOUNT_ID = t.ACCOUNT_ID
WHERE t.TXN_ID IS NULL
GROUP BY c.CUSTOMER_ID, c.FIRST_NAME, c.LAST_NAME, c.EMAIL
ORDER BY c.LAST_NAME;

Note: The GROUP BY is necessary because a customer may have multiple accounts, all with no transactions. Without it, the customer would appear once per account.

Exercise 11: LEFT JOIN with Aggregation

For each branch, show the branch name and the total balance of all accounts at that branch. Branches with no accounts should show a total of 0. Order by total balance descending.

Solution

SELECT b.BRANCH_NAME,
       COALESCE(SUM(a.BALANCE), 0) AS TOTAL_BALANCE
FROM BRANCH b
LEFT OUTER JOIN ACCOUNT a
    ON b.BRANCH_ID = a.BRANCH_ID
GROUP BY b.BRANCH_NAME
ORDER BY TOTAL_BALANCE DESC;

Exercise 12: Outer Join Self-Join

List all employees, including top-level managers who have no manager themselves. Show employee name, position, and manager name (display 'No Manager' for top-level employees).

Solution

SELECT e.EMPLOYEE_ID,
       e.FIRST_NAME || ' ' || e.LAST_NAME AS EMPLOYEE_NAME,
       e.POSITION,
       COALESCE(m.FIRST_NAME || ' ' || m.LAST_NAME, 'No Manager') AS MANAGER_NAME
FROM EMPLOYEE e
LEFT OUTER JOIN EMPLOYEE m
    ON e.MANAGER_EMPLOYEE_ID = m.EMPLOYEE_ID
ORDER BY e.EMPLOYEE_ID;

Exercise 13: FULL OUTER JOIN

Write a query using FULL OUTER JOIN between CUSTOMER and ACCOUNT to find: (a) customers with no accounts, and (b) accounts with no valid customer (orphaned records). Show customer ID, customer name, account ID, and a STATUS column that says 'No Account', 'Orphaned Account', or 'Matched'.

Solution

SELECT c.CUSTOMER_ID,
       c.FIRST_NAME || ' ' || c.LAST_NAME AS CUSTOMER_NAME,
       a.ACCOUNT_ID,
       CASE
           WHEN a.ACCOUNT_ID IS NULL THEN 'No Account'
           WHEN c.CUSTOMER_ID IS NULL THEN 'Orphaned Account'
           ELSE 'Matched'
       END AS STATUS
FROM CUSTOMER c
FULL OUTER JOIN ACCOUNT a
    ON c.CUSTOMER_ID = a.CUSTOMER_ID
ORDER BY STATUS, c.CUSTOMER_ID;

Exercise 14: ON vs. WHERE in Outer Joins

Write two versions of a query that shows all customers and their SAVINGS accounts:

Version A: Place the account type filter in the ON clause (correct for preserving all customers).

Version B: Place the account type filter in the WHERE clause (which effectively converts to an INNER JOIN).

Explain the difference in results.

Solution

-- Version A: Filter in ON clause (correct)
-- Returns ALL customers; non-savings account holders show NULLs
SELECT c.CUSTOMER_ID,
       c.FIRST_NAME,
       c.LAST_NAME,
       a.ACCOUNT_ID,
       a.ACCOUNT_TYPE,
       a.BALANCE
FROM CUSTOMER c
LEFT OUTER JOIN ACCOUNT a
    ON c.CUSTOMER_ID = a.CUSTOMER_ID
   AND a.ACCOUNT_TYPE = 'SAVINGS'
ORDER BY c.CUSTOMER_ID;

-- Version B: Filter in WHERE clause (incorrect for this requirement)
-- Only returns customers who HAVE a savings account
SELECT c.CUSTOMER_ID,
       c.FIRST_NAME,
       c.LAST_NAME,
       a.ACCOUNT_ID,
       a.ACCOUNT_TYPE,
       a.BALANCE
FROM CUSTOMER c
LEFT OUTER JOIN ACCOUNT a
    ON c.CUSTOMER_ID = a.CUSTOMER_ID
WHERE a.ACCOUNT_TYPE = 'SAVINGS'
ORDER BY c.CUSTOMER_ID;

**Explanation:** In Version A, the ACCOUNT_TYPE filter is part of the join condition. The LEFT OUTER JOIN preserves all CUSTOMER rows, only matching them with SAVINGS accounts. Customers without savings accounts appear with NULLs. In Version B, the WHERE clause runs after the join and filters out any row where ACCOUNT_TYPE is not 'SAVINGS', including the NULL rows from unmatched customers — effectively converting the outer join into an inner join.

Exercise 15: Multiple Outer Joins

Write a query showing all branches, their managers (if assigned), and their employee count (including branches with no employees). A branch might have no manager assigned.

Expected columns: BRANCH_NAME, MANAGER_NAME, EMPLOYEE_COUNT

Solution

SELECT b.BRANCH_NAME,
       COALESCE(mgr.FIRST_NAME || ' ' || mgr.LAST_NAME, 'Unassigned') AS MANAGER_NAME,
       COUNT(e.EMPLOYEE_ID) AS EMPLOYEE_COUNT
FROM BRANCH b
LEFT OUTER JOIN EMPLOYEE mgr
    ON b.MANAGER_ID = mgr.EMPLOYEE_ID
LEFT OUTER JOIN EMPLOYEE e
    ON b.BRANCH_ID = e.BRANCH_ID
GROUP BY b.BRANCH_NAME, mgr.FIRST_NAME, mgr.LAST_NAME
ORDER BY b.BRANCH_NAME;

Exercise 16: Anti-Join — Accounts Without Recent Activity

Find all active accounts that have had no transactions in the last 60 days. Show account ID, account type, balance, and customer name. Use the anti-join pattern with LEFT OUTER JOIN.

Solution

SELECT a.ACCOUNT_ID,
       a.ACCOUNT_TYPE,
       a.BALANCE,
       c.FIRST_NAME || ' ' || c.LAST_NAME AS CUSTOMER_NAME
FROM ACCOUNT a
INNER JOIN CUSTOMER c
    ON a.CUSTOMER_ID = c.CUSTOMER_ID
LEFT OUTER JOIN TRANSACTION t
    ON a.ACCOUNT_ID = t.ACCOUNT_ID
   AND t.TXN_DATE >= CURRENT DATE - 60 DAYS
WHERE a.STATUS = 'ACTIVE'
  AND t.TXN_ID IS NULL
ORDER BY a.BALANCE DESC;

Section C: CROSS JOIN and LATERAL (Exercises 17-22)

Exercise 17: Basic Cross Join

Using CROSS JOIN with a VALUES clause, generate a row for each combination of branch and account type ('CHECKING', 'SAVINGS', 'CD'). Show branch name and account type.

Solution

SELECT b.BRANCH_NAME,
       at.ACCOUNT_TYPE
FROM BRANCH b
CROSS JOIN (VALUES ('CHECKING'), ('SAVINGS'), ('CD')) AS at(ACCOUNT_TYPE)
ORDER BY b.BRANCH_NAME, at.ACCOUNT_TYPE;

Exercise 18: Cross Join for Reporting Skeleton

Create a reporting skeleton that shows every branch for every day in February 2024 (use a recursive CTE for dates). The result should have one row per branch per day.

Solution

WITH DATE_RANGE(D) AS (
    VALUES DATE('2024-02-01')
    UNION ALL
    SELECT D + 1 DAY FROM DATE_RANGE WHERE D < DATE('2024-02-29')
)
SELECT dr.D AS REPORT_DATE,
       b.BRANCH_ID,
       b.BRANCH_NAME
FROM DATE_RANGE dr
CROSS JOIN BRANCH b
ORDER BY dr.D, b.BRANCH_ID;

Exercise 19: Cross Join with Left Join for Gap Analysis

Extend Exercise 18 by LEFT OUTER JOINing actual transaction counts for each branch and date. Dates/branches with no transactions should show 0.

Solution

WITH DATE_RANGE(D) AS (
    VALUES DATE('2024-02-01')
    UNION ALL
    SELECT D + 1 DAY FROM DATE_RANGE WHERE D < DATE('2024-02-29')
)
SELECT dr.D AS REPORT_DATE,
       b.BRANCH_ID,
       b.BRANCH_NAME,
       COALESCE(txn.TXN_COUNT, 0) AS TXN_COUNT
FROM DATE_RANGE dr
CROSS JOIN BRANCH b
LEFT OUTER JOIN (
    SELECT a.BRANCH_ID,
           t.TXN_DATE,
           COUNT(*) AS TXN_COUNT
    FROM TRANSACTION t
    INNER JOIN ACCOUNT a ON t.ACCOUNT_ID = a.ACCOUNT_ID
    GROUP BY a.BRANCH_ID, t.TXN_DATE
) txn
    ON b.BRANCH_ID = txn.BRANCH_ID
   AND dr.D = txn.TXN_DATE
ORDER BY dr.D, b.BRANCH_ID;

Exercise 20: LATERAL — Top 3 Transactions Per Account

For each account, find the top 3 largest transactions by amount. Use LATERAL to correlate the subquery.

Expected columns: ACCOUNT_ID, ACCOUNT_TYPE, TXN_ID, AMOUNT, TXN_DATE

Solution

SELECT a.ACCOUNT_ID,
       a.ACCOUNT_TYPE,
       lt.TXN_ID,
       lt.AMOUNT,
       lt.TXN_DATE
FROM ACCOUNT a,
LATERAL (
    SELECT t.TXN_ID, t.AMOUNT, t.TXN_DATE
    FROM TRANSACTION t
    WHERE t.ACCOUNT_ID = a.ACCOUNT_ID
    ORDER BY t.AMOUNT DESC
    FETCH FIRST 3 ROWS ONLY
) AS lt
ORDER BY a.ACCOUNT_ID, lt.AMOUNT DESC;

Exercise 21: LEFT OUTER JOIN LATERAL

Modify Exercise 20 to include accounts that have no transactions, showing NULLs for the transaction columns.

Solution

SELECT a.ACCOUNT_ID,
       a.ACCOUNT_TYPE,
       lt.TXN_ID,
       lt.AMOUNT,
       lt.TXN_DATE
FROM ACCOUNT a
LEFT OUTER JOIN LATERAL (
    SELECT t.TXN_ID, t.AMOUNT, t.TXN_DATE
    FROM TRANSACTION t
    WHERE t.ACCOUNT_ID = a.ACCOUNT_ID
    ORDER BY t.AMOUNT DESC
    FETCH FIRST 3 ROWS ONLY
) AS lt ON 1=1
ORDER BY a.ACCOUNT_ID, lt.AMOUNT DESC;

Exercise 22: LATERAL with Aggregation

For each branch, use LATERAL to find the single highest-balance account and the single lowest-balance account at that branch.

Solution

SELECT b.BRANCH_NAME,
       hi.ACCOUNT_ID AS HIGHEST_ACCT,
       hi.BALANCE AS HIGHEST_BALANCE,
       lo.ACCOUNT_ID AS LOWEST_ACCT,
       lo.BALANCE AS LOWEST_BALANCE
FROM BRANCH b,
LATERAL (
    SELECT a.ACCOUNT_ID, a.BALANCE
    FROM ACCOUNT a
    WHERE a.BRANCH_ID = b.BRANCH_ID
    ORDER BY a.BALANCE DESC
    FETCH FIRST 1 ROW ONLY
) AS hi,
LATERAL (
    SELECT a.ACCOUNT_ID, a.BALANCE
    FROM ACCOUNT a
    WHERE a.BRANCH_ID = b.BRANCH_ID
    ORDER BY a.BALANCE ASC
    FETCH FIRST 1 ROW ONLY
) AS lo
ORDER BY b.BRANCH_NAME;

Section D: Complex Multi-Table Joins (Exercises 23-30)

Exercise 23: Five-Table Join

Write a query joining all five Meridian Bank tables to show: customer name, account type, branch name, branch manager name, and transaction details — for all withdrawal transactions over $500.

Solution

SELECT c.FIRST_NAME || ' ' || c.LAST_NAME AS CUSTOMER_NAME,
       a.ACCOUNT_ID,
       a.ACCOUNT_TYPE,
       b.BRANCH_NAME,
       mgr.FIRST_NAME || ' ' || mgr.LAST_NAME AS BRANCH_MANAGER,
       t.TXN_ID,
       t.AMOUNT,
       t.TXN_DATE,
       t.DESCRIPTION
FROM TRANSACTION t
INNER JOIN ACCOUNT a
    ON t.ACCOUNT_ID = a.ACCOUNT_ID
INNER JOIN CUSTOMER c
    ON a.CUSTOMER_ID = c.CUSTOMER_ID
INNER JOIN BRANCH b
    ON a.BRANCH_ID = b.BRANCH_ID
INNER JOIN EMPLOYEE mgr
    ON b.MANAGER_ID = mgr.EMPLOYEE_ID
WHERE t.TXN_TYPE = 'WITHDRAWAL'
  AND t.AMOUNT > 500
ORDER BY t.TXN_DATE DESC;

Exercise 24: Mixed Join Types

Write a query that shows all branches with their employee count and total account balances. Branches with no employees should show 0 employees. Branches with no accounts should show $0 total balance.

Solution

SELECT b.BRANCH_NAME,
       COALESCE(emp.EMP_COUNT, 0) AS EMPLOYEE_COUNT,
       COALESCE(acct.TOTAL_BALANCE, 0) AS TOTAL_BALANCE
FROM BRANCH b
LEFT OUTER JOIN (
    SELECT BRANCH_ID, COUNT(*) AS EMP_COUNT
    FROM EMPLOYEE
    GROUP BY BRANCH_ID
) emp ON b.BRANCH_ID = emp.BRANCH_ID
LEFT OUTER JOIN (
    SELECT BRANCH_ID, SUM(BALANCE) AS TOTAL_BALANCE
    FROM ACCOUNT
    GROUP BY BRANCH_ID
) acct ON b.BRANCH_ID = acct.BRANCH_ID
ORDER BY b.BRANCH_NAME;

Note: We aggregate in subqueries to avoid the row-multiplication problem described in Section 6.10.

Exercise 25: Transfer Self-Join

For each transfer transaction, show the source account holder's name, the destination account holder's name, the amount, and the date. Hint: TRANSACTION.RELATED_ACCOUNT_ID links to the destination account.

Solution

SELECT t.TXN_ID,
       t.TXN_DATE,
       t.AMOUNT,
       src_c.FIRST_NAME || ' ' || src_c.LAST_NAME AS FROM_CUSTOMER,
       src_a.ACCOUNT_ID AS FROM_ACCOUNT,
       dst_c.FIRST_NAME || ' ' || dst_c.LAST_NAME AS TO_CUSTOMER,
       dst_a.ACCOUNT_ID AS TO_ACCOUNT
FROM TRANSACTION t
INNER JOIN ACCOUNT src_a
    ON t.ACCOUNT_ID = src_a.ACCOUNT_ID
INNER JOIN CUSTOMER src_c
    ON src_a.CUSTOMER_ID = src_c.CUSTOMER_ID
INNER JOIN ACCOUNT dst_a
    ON t.RELATED_ACCOUNT_ID = dst_a.ACCOUNT_ID
INNER JOIN CUSTOMER dst_c
    ON dst_a.CUSTOMER_ID = dst_c.CUSTOMER_ID
WHERE t.TXN_TYPE = 'TRANSFER'
ORDER BY t.TXN_DATE;

Exercise 26: Customer Account Summary with Transaction Stats

For each customer, show: customer name, number of accounts, total balance across all accounts, and total number of transactions across all accounts. Use subqueries to avoid row multiplication.

Solution

SELECT c.FIRST_NAME || ' ' || c.LAST_NAME AS CUSTOMER_NAME,
       acct_stats.NUM_ACCOUNTS,
       acct_stats.TOTAL_BALANCE,
       COALESCE(txn_stats.NUM_TRANSACTIONS, 0) AS NUM_TRANSACTIONS
FROM CUSTOMER c
INNER JOIN (
    SELECT CUSTOMER_ID,
           COUNT(*) AS NUM_ACCOUNTS,
           SUM(BALANCE) AS TOTAL_BALANCE
    FROM ACCOUNT
    GROUP BY CUSTOMER_ID
) acct_stats ON c.CUSTOMER_ID = acct_stats.CUSTOMER_ID
LEFT OUTER JOIN (
    SELECT a.CUSTOMER_ID,
           COUNT(*) AS NUM_TRANSACTIONS
    FROM TRANSACTION t
    INNER JOIN ACCOUNT a ON t.ACCOUNT_ID = a.ACCOUNT_ID
    GROUP BY a.CUSTOMER_ID
) txn_stats ON c.CUSTOMER_ID = txn_stats.CUSTOMER_ID
ORDER BY TOTAL_BALANCE DESC;

Exercise 27: Employee Peer Comparison

Write a self-join that pairs each employee with every other employee in the same branch. Show both employee names and the salary difference. Avoid duplicate pairs (show each pair only once).

Solution

SELECT e1.FIRST_NAME || ' ' || e1.LAST_NAME AS EMPLOYEE_1,
       e2.FIRST_NAME || ' ' || e2.LAST_NAME AS EMPLOYEE_2,
       b.BRANCH_NAME,
       ABS(e1.SALARY - e2.SALARY) AS SALARY_DIFFERENCE
FROM EMPLOYEE e1
INNER JOIN EMPLOYEE e2
    ON e1.BRANCH_ID = e2.BRANCH_ID
   AND e1.EMPLOYEE_ID < e2.EMPLOYEE_ID
INNER JOIN BRANCH b
    ON e1.BRANCH_ID = b.BRANCH_ID
ORDER BY b.BRANCH_NAME, SALARY_DIFFERENCE DESC;

Exercise 28: Orphan Detection Across Tables

Write a comprehensive data quality query that finds: (a) accounts with no valid customer, (b) accounts with no valid branch, (c) transactions with no valid account. Use FULL OUTER JOINs or LEFT OUTER JOINs as appropriate.

Solution

-- Part A: Accounts with no valid customer
SELECT 'Account without customer' AS ISSUE,
       a.ACCOUNT_ID,
       a.CUSTOMER_ID AS INVALID_FK
FROM ACCOUNT a
LEFT OUTER JOIN CUSTOMER c ON a.CUSTOMER_ID = c.CUSTOMER_ID
WHERE c.CUSTOMER_ID IS NULL

UNION ALL

-- Part B: Accounts with no valid branch
SELECT 'Account without branch' AS ISSUE,
       a.ACCOUNT_ID,
       a.BRANCH_ID AS INVALID_FK
FROM ACCOUNT a
LEFT OUTER JOIN BRANCH b ON a.BRANCH_ID = b.BRANCH_ID
WHERE b.BRANCH_ID IS NULL

UNION ALL

-- Part C: Transactions with no valid account
SELECT 'Transaction without account' AS ISSUE,
       t.TXN_ID,
       t.ACCOUNT_ID AS INVALID_FK
FROM TRANSACTION t
LEFT OUTER JOIN ACCOUNT a ON t.ACCOUNT_ID = a.ACCOUNT_ID
WHERE a.ACCOUNT_ID IS NULL

ORDER BY ISSUE;

Exercise 29: Conditional Join Logic

Write a query that shows each account and its most recent transaction. For accounts with no transactions, show 'No transactions' in the description column. Use LEFT OUTER JOIN with LATERAL.

Solution

SELECT a.ACCOUNT_ID,
       a.ACCOUNT_TYPE,
       a.BALANCE,
       COALESCE(CHAR(lt.TXN_ID), '-') AS LAST_TXN_ID,
       COALESCE(CHAR(lt.TXN_DATE), '-') AS LAST_TXN_DATE,
       COALESCE(lt.DESCRIPTION, 'No transactions') AS DESCRIPTION
FROM ACCOUNT a
LEFT OUTER JOIN LATERAL (
    SELECT t.TXN_ID, t.TXN_DATE, t.DESCRIPTION
    FROM TRANSACTION t
    WHERE t.ACCOUNT_ID = a.ACCOUNT_ID
    ORDER BY t.TXN_DATE DESC
    FETCH FIRST 1 ROW ONLY
) AS lt ON 1=1
ORDER BY a.ACCOUNT_ID;

Exercise 30: Complete Branch Performance Report

Build a single query that produces a branch performance report with: branch name, manager name, employee count, total account balances, total number of transactions, and average transaction amount. Include all branches even if they have no accounts or employees.

Solution

SELECT b.BRANCH_NAME,
       COALESCE(mgr.FIRST_NAME || ' ' || mgr.LAST_NAME, 'Vacant') AS MANAGER_NAME,
       COALESCE(emp.EMP_COUNT, 0) AS EMPLOYEE_COUNT,
       COALESCE(acct.TOTAL_BALANCE, 0) AS TOTAL_BALANCE,
       COALESCE(txn.TXN_COUNT, 0) AS TOTAL_TRANSACTIONS,
       COALESCE(txn.AVG_AMOUNT, 0) AS AVG_TXN_AMOUNT
FROM BRANCH b
LEFT OUTER JOIN EMPLOYEE mgr
    ON b.MANAGER_ID = mgr.EMPLOYEE_ID
LEFT OUTER JOIN (
    SELECT BRANCH_ID, COUNT(*) AS EMP_COUNT
    FROM EMPLOYEE
    GROUP BY BRANCH_ID
) emp ON b.BRANCH_ID = emp.BRANCH_ID
LEFT OUTER JOIN (
    SELECT BRANCH_ID, SUM(BALANCE) AS TOTAL_BALANCE
    FROM ACCOUNT
    GROUP BY BRANCH_ID
) acct ON b.BRANCH_ID = acct.BRANCH_ID
LEFT OUTER JOIN (
    SELECT a.BRANCH_ID,
           COUNT(*) AS TXN_COUNT,
           AVG(t.AMOUNT) AS AVG_AMOUNT
    FROM TRANSACTION t
    INNER JOIN ACCOUNT a ON t.ACCOUNT_ID = a.ACCOUNT_ID
    GROUP BY a.BRANCH_ID
) txn ON b.BRANCH_ID = txn.BRANCH_ID
ORDER BY b.BRANCH_NAME;

Section E: Challenge Problems (Exercises 31-35)

Exercise 31: Mutual Transfers

Find all pairs of customers who have transferred money to each other (i.e., Customer A transferred to Customer B AND Customer B transferred to Customer A). Show both customer names and the total transferred in each direction.

Solution

WITH TRANSFER_SUMMARY AS (
    SELECT src_c.CUSTOMER_ID AS FROM_CUST_ID,
           src_c.FIRST_NAME || ' ' || src_c.LAST_NAME AS FROM_CUST_NAME,
           dst_c.CUSTOMER_ID AS TO_CUST_ID,
           dst_c.FIRST_NAME || ' ' || dst_c.LAST_NAME AS TO_CUST_NAME,
           SUM(t.AMOUNT) AS TOTAL_TRANSFERRED
    FROM TRANSACTION t
    INNER JOIN ACCOUNT src_a ON t.ACCOUNT_ID = src_a.ACCOUNT_ID
    INNER JOIN CUSTOMER src_c ON src_a.CUSTOMER_ID = src_c.CUSTOMER_ID
    INNER JOIN ACCOUNT dst_a ON t.RELATED_ACCOUNT_ID = dst_a.ACCOUNT_ID
    INNER JOIN CUSTOMER dst_c ON dst_a.CUSTOMER_ID = dst_c.CUSTOMER_ID
    WHERE t.TXN_TYPE = 'TRANSFER'
    GROUP BY src_c.CUSTOMER_ID, src_c.FIRST_NAME, src_c.LAST_NAME,
             dst_c.CUSTOMER_ID, dst_c.FIRST_NAME, dst_c.LAST_NAME
)
SELECT t1.FROM_CUST_NAME AS CUSTOMER_A,
       t1.TO_CUST_NAME AS CUSTOMER_B,
       t1.TOTAL_TRANSFERRED AS A_TO_B_AMOUNT,
       t2.TOTAL_TRANSFERRED AS B_TO_A_AMOUNT
FROM TRANSFER_SUMMARY t1
INNER JOIN TRANSFER_SUMMARY t2
    ON t1.FROM_CUST_ID = t2.TO_CUST_ID
   AND t1.TO_CUST_ID = t2.FROM_CUST_ID
WHERE t1.FROM_CUST_ID < t1.TO_CUST_ID
ORDER BY t1.FROM_CUST_NAME;

Exercise 32: Organizational Depth

Using a recursive self-join (CTE), show each employee's name and their depth in the organizational hierarchy (top-level manager = depth 0, their direct reports = depth 1, etc.).

Solution

WITH ORG_HIERARCHY (EMPLOYEE_ID, EMPLOYEE_NAME, POSITION, DEPTH) AS (
    -- Anchor: top-level managers (no manager)
    SELECT EMPLOYEE_ID,
           FIRST_NAME || ' ' || LAST_NAME,
           POSITION,
           0
    FROM EMPLOYEE
    WHERE MANAGER_EMPLOYEE_ID IS NULL

    UNION ALL

    -- Recursive: employees reporting to someone already in the hierarchy
    SELECT e.EMPLOYEE_ID,
           e.FIRST_NAME || ' ' || e.LAST_NAME,
           e.POSITION,
           oh.DEPTH + 1
    FROM EMPLOYEE e
    INNER JOIN ORG_HIERARCHY oh
        ON e.MANAGER_EMPLOYEE_ID = oh.EMPLOYEE_ID
)
SELECT DEPTH,
       REPEAT('  ', DEPTH) || EMPLOYEE_NAME AS EMPLOYEE_NAME,
       POSITION
FROM ORG_HIERARCHY
ORDER BY DEPTH, EMPLOYEE_NAME;

Exercise 33: Daily Balance Reconstruction

For a given account (ACCOUNT_ID = 5001), reconstruct the daily running balance by cross-joining a date range with the account, then left-joining transactions by date, and computing a cumulative sum.

Solution

WITH DATE_RANGE(D) AS (
    VALUES DATE('2024-01-01')
    UNION ALL
    SELECT D + 1 DAY FROM DATE_RANGE WHERE D < DATE('2024-01-31')
),
DAILY_TXN AS (
    SELECT t.TXN_DATE,
           SUM(CASE WHEN t.TXN_TYPE IN ('DEPOSIT', 'TRANSFER_IN') THEN t.AMOUNT
                    WHEN t.TXN_TYPE IN ('WITHDRAWAL', 'TRANSFER_OUT') THEN -t.AMOUNT
                    ELSE 0 END) AS NET_AMOUNT
    FROM TRANSACTION t
    WHERE t.ACCOUNT_ID = 5001
    GROUP BY t.TXN_DATE
)
SELECT dr.D AS REPORT_DATE,
       COALESCE(dt.NET_AMOUNT, 0) AS DAY_NET,
       SUM(COALESCE(dt.NET_AMOUNT, 0)) OVER (ORDER BY dr.D) AS RUNNING_BALANCE
FROM DATE_RANGE dr
LEFT OUTER JOIN DAILY_TXN dt
    ON dr.D = dt.TXN_DATE
ORDER BY dr.D;

Note: This uses a window function (SUM OVER) which is covered in Chapter 14. The focus here is the join pattern — the CROSS JOIN to generate dates and the LEFT OUTER JOIN to fill in actuals.

Exercise 34: Branch Comparison Matrix

Create a matrix showing every pair of branches and the number of inter-branch transfers between them (transfers where the source account is at one branch and the destination account is at another).

Solution

SELECT src_b.BRANCH_NAME AS FROM_BRANCH,
       dst_b.BRANCH_NAME AS TO_BRANCH,
       COUNT(t.TXN_ID) AS TRANSFER_COUNT,
       COALESCE(SUM(t.AMOUNT), 0) AS TOTAL_AMOUNT
FROM TRANSACTION t
INNER JOIN ACCOUNT src_a ON t.ACCOUNT_ID = src_a.ACCOUNT_ID
INNER JOIN BRANCH src_b ON src_a.BRANCH_ID = src_b.BRANCH_ID
INNER JOIN ACCOUNT dst_a ON t.RELATED_ACCOUNT_ID = dst_a.ACCOUNT_ID
INNER JOIN BRANCH dst_b ON dst_a.BRANCH_ID = dst_b.BRANCH_ID
WHERE t.TXN_TYPE = 'TRANSFER'
  AND src_a.BRANCH_ID <> dst_a.BRANCH_ID
GROUP BY src_b.BRANCH_NAME, dst_b.BRANCH_NAME
ORDER BY TRANSFER_COUNT DESC;

Exercise 35: Comprehensive Data Integrity Report

Write a single query (using UNION ALL) that checks for all of the following data integrity issues and reports them in a unified format:

Customers with no accounts
Accounts with no transactions ever
Branches with no employees
Employees whose BRANCH_ID does not match their manager's BRANCH_ID
Transactions where RELATED_ACCOUNT_ID points to a non-existent account

Expected columns: ISSUE_TYPE, ENTITY_ID, DETAILS

Solution

-- 1. Customers with no accounts
SELECT 'Customer with no accounts' AS ISSUE_TYPE,
       CHAR(c.CUSTOMER_ID) AS ENTITY_ID,
       c.FIRST_NAME || ' ' || c.LAST_NAME AS DETAILS
FROM CUSTOMER c
LEFT OUTER JOIN ACCOUNT a ON c.CUSTOMER_ID = a.CUSTOMER_ID
WHERE a.ACCOUNT_ID IS NULL

UNION ALL

-- 2. Accounts with no transactions
SELECT 'Account with no transactions',
       CHAR(a.ACCOUNT_ID),
       a.ACCOUNT_TYPE || ' - Balance: ' || CHAR(a.BALANCE)
FROM ACCOUNT a
LEFT OUTER JOIN TRANSACTION t ON a.ACCOUNT_ID = t.ACCOUNT_ID
WHERE t.TXN_ID IS NULL

UNION ALL

-- 3. Branches with no employees
SELECT 'Branch with no employees',
       CHAR(b.BRANCH_ID),
       b.BRANCH_NAME
FROM BRANCH b
LEFT OUTER JOIN EMPLOYEE e ON b.BRANCH_ID = e.BRANCH_ID
WHERE e.EMPLOYEE_ID IS NULL

UNION ALL

-- 4. Employee/manager branch mismatch
SELECT 'Employee-manager branch mismatch',
       CHAR(e.EMPLOYEE_ID),
       e.FIRST_NAME || ' ' || e.LAST_NAME || ' (Branch: ' || CHAR(e.BRANCH_ID)
           || ') reports to ' || m.FIRST_NAME || ' ' || m.LAST_NAME
           || ' (Branch: ' || CHAR(m.BRANCH_ID) || ')'
FROM EMPLOYEE e
INNER JOIN EMPLOYEE m ON e.MANAGER_EMPLOYEE_ID = m.EMPLOYEE_ID
WHERE e.BRANCH_ID <> m.BRANCH_ID

UNION ALL

-- 5. Invalid RELATED_ACCOUNT_ID references
SELECT 'Invalid transfer reference',
       CHAR(t.TXN_ID),
       'RELATED_ACCOUNT_ID ' || CHAR(t.RELATED_ACCOUNT_ID) || ' does not exist'
FROM TRANSACTION t
LEFT OUTER JOIN ACCOUNT a ON t.RELATED_ACCOUNT_ID = a.ACCOUNT_ID
WHERE t.RELATED_ACCOUNT_ID IS NOT NULL
  AND a.ACCOUNT_ID IS NULL

ORDER BY ISSUE_TYPE, ENTITY_ID;

Difficulty Guide

Difficulty	Exercises
Beginner	1, 2, 3, 6, 9, 11, 12, 17
Intermediate	4, 5, 7, 8, 10, 13, 14, 15, 16, 18, 20
Advanced	19, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30
Challenge	31, 32, 33, 34, 35