SQL for the ISC CPA Exam: Commands, Joins, Aggregates and Completeness Checks

1. What You Need to Know

SQL on ISC is about audit/assurance data work: pulling populations, joining tables correctly, summarizing data, and performing completeness checks (did you get all records, once, and tied to totals?). Expect questions that test whether you can:

• Use core commands (primarily SELECT, plus basic DDL/DCL concepts)
• Choose the right join and join keys to avoid missing/duplicating rows
• Use aggregates (COUNT, SUM, etc.) with GROUP BY / HAVING
• Design completeness tests: missing items, orphan records, duplicates, gaps, tie-outs (record counts and control totals)

Why it matters (ISC context): SQL is a common CAAT for testing completeness and accuracy assertions, evaluating IT-dependent controls, and supporting reconciliations (e.g., subledger-to-GL, order-to-cash, procure-to-pay).

Core idea to internalize:

Most SQL errors on exams come from (1) the wrong join type/key, (2) filters placed in WHERE that accidentally turn an outer join into an inner join, and (3) aggregates used without correct GROUP BY logic.

2. Step-by-Step Breakdown

A. Building a clean audit query (population + logic)

1. Define the population (FROM): Identify the “base table” that represents the full population you care about (e.g., all invoices, all POs, all GL entries).
2. Select only needed fields (SELECT): Include IDs/keys, dates, amounts, status fields.
3. Add filters carefully (WHERE): Put row-level filters here (date range, status). Avoid filtering joined tables in WHERE if you need to preserve non-matches (outer join issue).
4. Join supporting tables (JOIN … ON):
   • Join on stable keys (e.g., invoice_id, vendor_id)—not names/descriptions.
   • Validate join cardinality (1:1, 1:many) to avoid multiplication of amounts.
5. Summarize if needed (GROUP BY): If you use aggregates, every non-aggregated column in SELECT must be in GROUP BY (standard SQL rule).
6. Apply group filters (HAVING): Use HAVING for conditions on aggregates (e.g., duplicates where COUNT(*) > 1).
7. Sanity checks: Run quick checks (counts, totals, min/max dates) and compare to control totals/reports.

B. Completeness check workflow (high-yield)

Use this sequence when asked to “test completeness” of a dataset:

1. Row count tie-out: Compare COUNT(*) to an expected number (report count, interface count, batch header, or control total).
2. Control total tie-out: Compare SUM(amount) (or other key measure) to a control total (batch total, GL total, management report total).
3. Missing/Orphan detection: Use an outer join to find records that should have a match but don’t (e.g., shipments without invoices).
4. Duplicate detection: Use GROUP BY key HAVING COUNT(*) > 1.
5. Gap detection (sequence completeness): Identify missing document numbers (often via anti-join logic or window functions if supported).
6. Exception output: Return the exception list (IDs) for follow-up.

C. Mini worked example (completeness via outer join)

Goal: Find shipments that have no matching invoice (possible revenue completeness issue).

SELECT s.shipment_id, s.ship_date, s.customer_id
FROM shipments s
LEFT JOIN invoices i
  ON i.shipment_id = s.shipment_id
WHERE i.invoice_id IS NULL;

Key insight: LEFT JOIN keeps all shipments; WHERE i.invoice_id IS NULL isolates non-matches.

Critical: If you instead put a condition on i in the WHERE clause (other than the IS NULL test), you can accidentally drop the null-extended rows and lose exceptions.

3. Key Formulas, Rules & Facts

A. Core SQL command families (know the purpose)

B. Join types (what you get back)

C. Filter order & clauses (practical rules)

D. Aggregate functions (completeness & accuracy)

E. High-yield completeness checks (patterns)

4. Examples & Applications

Example 1: Subledger-to-GL completeness tie-out

Scenario: Tie AP invoice totals (subledger) to GL postings.

SELECT
  COUNT(*) AS invoice_count,
  SUM(i.invoice_amount) AS invoice_total
FROM ap_invoices i
WHERE i.invoice_date >= DATE '2026-01-01'
  AND i.invoice_date <  DATE '2027-01-01';

Then compare to GL (by account / period):

SELECT
  SUM(g.amount) AS gl_total
FROM gl_entries g
WHERE g.posting_date >= DATE '2026-01-01'
  AND g.posting_date <  DATE '2027-01-01'
  AND g.account_no = '2000';

Key insight: You’re validating completeness of posting (did subledger activity fully hit GL?) and/or completeness of extraction.

Example 2: Find invoices missing required approvals (workflow completeness)

SELECT i.invoice_id, i.vendor_id, i.invoice_amount
FROM ap_invoices i
LEFT JOIN approvals a
  ON a.invoice_id = i.invoice_id
WHERE a.approval_id IS NULL
  AND i.status = 'POSTED';

Key insight: Posted invoices without approvals indicate a control exception (or data/interface issue).

Example 3: Duplicate invoice detection (common fraud/error analytic)

SELECT i.vendor_id, i.invoice_number, COUNT(*) AS dup_count, SUM(i.invoice_amount) AS dup_total
FROM ap_invoices i
GROUP BY i.vendor_id, i.invoice_number
HAVING COUNT(*) > 1;

Variation: If invoice numbers can repeat across vendors, include vendor in the key (as above). If not, group by invoice number alone.

Example 4: Join grain trap (header-to-line multiplication)

Problem: You want total invoice amount, but you join header to lines and accidentally multiply header amounts.
Bad pattern:

SELECT SUM(h.invoice_amount)
FROM invoice_header h
JOIN invoice_line l
  ON l.invoice_id = h.invoice_id;

If each invoice has 5 lines, header amount is counted 5 times.

Fix options:

• Sum line amounts instead:

SELECT SUM(l.line_amount)
FROM invoice_line l;

• Or aggregate lines first then join:

SELECT SUM(h.invoice_amount)
FROM invoice_header h
JOIN (
  SELECT invoice_id
  FROM invoice_line
  GROUP BY invoice_id
) x
  ON x.invoice_id = h.invoice_id;

Key insight: Always match your aggregation level to your question.

5. Common Mistakes & Traps

1. Filtering the outer-joined table in WHERE

   • Wrong: LEFT JOIN ... WHERE right.col = 'X'
   • Why wrong: Null-extended rows fail the WHERE predicate, so you lose the very exceptions you wanted.
   • Fix: Put right-table predicates in the ON clause (or keep WHERE limited to null-test exceptions).

2. Using COUNT(col) when you meant COUNT(*) (or vice versa)

   • Wrong: Assuming COUNT(col) counts rows.
   • Why wrong: COUNT(col) ignores NULLs.
   • Fix: Use COUNT(*) for row counts; use COUNT(col) to measure field completeness.

3. Join key mismatch (joining on names/descriptions)

   • Wrong: Joining vendor_name to vendor_name.
   • Why wrong: Non-unique, spelling/case differences, name changes lead to missed/false matches.
   • Fix: Join on stable surrogate/business keys (vendor_id, invoice_id).

4. Row multiplication causing overstated totals

   • Wrong: Summing header amounts after joining to line items.
   • Why wrong: 1-to-many join duplicates header rows.
   • Fix: Aggregate at the correct grain (sum lines, or de-duplicate/aggregate before join).

5. Confusing WHERE vs HAVING

   • Wrong: WHERE COUNT(*) > 1
   • Why wrong: WHERE runs before aggregation.
   • Fix: Use HAVING COUNT(*) > 1.

6. NOT IN with NULLs producing “no rows” surprises

   • Wrong: WHERE key NOT IN (SELECT key FROM t2) when t2.key can be NULL.
   • Why wrong: NULL in the subquery can make NOT IN evaluate unpredictably (often returning zero rows).
   • Fix: Prefer NOT EXISTS or filter NULLs in the subquery.

7. Assuming DISTINCT “fixes” bad joins

   • Wrong: Slapping SELECT DISTINCT on a query with duplicate rows.
   • Why wrong: It hides symptoms while totals may still be wrong and keys unclear.
   • Fix: Fix join logic/grain; use DISTINCT only when conceptually correct.

8. Missing date boundary logic (inclusive/exclusive errors)

   • Wrong: Using <= on end dates with timestamps.
   • Why wrong: You can unintentionally exclude late-day transactions.
   • Fix: Use half-open intervals: >= start_date and < next_day/next_period.

6. Memory Aids & Quick Tricks

7. Quick Review Checklist

• You can explain and apply INNER vs LEFT (outer) joins—especially for completeness exceptions.
• You remember: filters on the outer-joined table belong in ON, not WHERE (unless you’re doing IS NULL to find exceptions).
• You can write duplicates logic: GROUP BY ... HAVING COUNT(*) > 1.
• You know when to use COUNT(*) vs COUNT(col) (NULL handling).
• You sanity-check any audit query with counts, sums, min/max dates and tie-outs to control totals.
• You avoid join multiplication by matching grain (header vs line) before summing.
• You can produce common completeness outputs: orphans, missing required fields, duplicates, tie-out totals.

You’ve got this—if you keep joins and grain straight, most ISC SQL questions become free points.