4.5 SQL Operators, Aggregate & String Functions: Reviewing Retrieved Data Sets

SQL Operators, Aggregate & String Functions

Quick Answer: The ISC blueprint task reads: "Examine a standard SQL query (common commands, clauses, operators, aggregate functions and string functions) to determine whether the retrieved data set is relevant and complete." You are not asked to write production SQL. You are asked to read someone else's query and decide whether the rows it returns are the rows the audit objective requires — no more and no fewer.

---

1. Common Commands

SQL statements fall into three families. Confusing them is a control issue, not just a vocabulary issue, because an extraction account should be able to execute exactly one family.

Exam trap: TRUNCATE is DDL, not DML. It removes all rows without logging individual deletions and generally cannot be rolled back the way a DELETE can. A finding that an application service account holds TRUNCATE rights on a financial table is severe.

---

2. Operators and the Three-Valued Logic Problem

Comparison and Range

=, <> (or !=), <, >, <=, >=, BETWEEN x AND y (inclusive of both endpoints), IN (list), NOT IN (list).

Pattern Matching

LIKE with % matching any sequence of characters and _ matching exactly one. So LIKE 'INV%' finds every invoice number beginning INV; LIKE '%2026%' finds it anywhere in the string.

Logical Operators and Precedence

AND binds more tightly than OR. This single fact is responsible for more wrong audit populations than any other SQL feature:

-- WRONG: returns every 2026 disbursement of any amount,
-- plus every wire of any date, because AND evaluates first.
WHERE payment_type = 'WIRE'
   OR payment_type = 'ACH'
  AND payment_date >= '2026-01-01'

-- RIGHT: parentheses force the intended grouping.
WHERE (payment_type = 'WIRE' OR payment_type = 'ACH')
  AND payment_date >= '2026-01-01'

NULL: The Completeness Killer

SQL uses three-valued logic — TRUE, FALSE, and UNKNOWN. Any comparison to NULL returns UNKNOWN, and a WHERE clause keeps only rows that evaluate to TRUE. Four consequences a reviewer must check:

1. WHERE approver_id <> 'JSMITH' silently drops every row where approver_id is NULL. If the audit objective is "everything not approved by Smith," the unapproved items — the highest-risk population — are exactly the ones excluded. Use WHERE approver_id <> 'JSMITH' OR approver_id IS NULL.
2. NOT IN with a subquery containing a single NULL returns no rows at all. Prefer NOT EXISTS or add an IS NOT NULL filter inside the subquery.
3. = NULL never matches anything. The only valid tests are IS NULL and IS NOT NULL.
4. NULL is not zero and not an empty string. A NULL discount and a 0.00 discount behave differently in both filtering and averaging.

Date Boundaries

WHERE invoice_date <= '2026-12-31' excludes everything stamped 2026-12-31 at 09:14 if the column is a timestamp rather than a date, because the literal is interpreted as midnight. The safe construction is a half-open range: >= '2026-01-01' AND < '2027-01-01'.

---

3. Aggregate Functions

The AVG trap, concretely: ten invoices, six carrying a discount of 10 and four carrying NULL. AVG(discount) returns 10, not 6. If the audit question is "what is the average discount granted across all invoices," the query must convert NULL to zero explicitly (AVG(COALESCE(discount, 0))).

Remember the clause rule from the query-processing order: aggregate conditions belong in HAVING, never in WHERE, because WHERE is evaluated before groups exist.

---

4. String Functions

String functions appear in ISC questions for one reason: data that looks identical on screen does not match on a join.

The Canonical Failure

Two systems both show vendor "ACME CORP". One stores ACME CORP and the other stores acme corp with a trailing space. a.vendor_name = b.vendor_name returns zero matches, and the analyst concludes there are no shared vendors. Normalizing both sides — UPPER(TRIM(a.vendor_name)) = UPPER(TRIM(b.vendor_name)) — is what makes the comparison meaningful. A zero-match join result should always be treated as a suspected normalization failure before it is treated as a finding.

---

5. Reviewing a Query for Relevance and Completeness

This is the actual blueprint task. Work the checklist in order.

Completeness — could a row that belongs in the population have been excluded?

1. Table and join. Does the FROM clause start from the driving table for the objective? An INNER JOIN silently drops unmatched rows — which are frequently the exceptions being hunted. Testing for shipments without invoices requires a LEFT JOIN with an IS NULL filter, not an INNER JOIN.
2. NULL handling. Does any filter use <>, NOT IN, or a comparison operator on a nullable column without an explicit NULL branch?
3. Date boundaries. Half-open range, or an inclusive endpoint on a timestamp column that drops the last day?
4. Operator precedence. Are there mixed AND/OR conditions without parentheses?
5. Row limits. Is there a LIMIT, TOP or FETCH FIRST clause left over from testing?
6. Status filters. Does a status = 'POSTED' condition exclude voided, reversed or in-flight items that the objective requires?

Relevance — does the result contain rows that do not belong, or omit needed columns?

1. Scope filters. Are entity, ledger, currency and period filters present and correct for the objective?
2. Grain. Does the join change the grain and inflate aggregates? Check any SUM taken across a one-to-many join.
3. Duplicates. Is DISTINCT being used to mask a join defect rather than to state a real requirement?
4. Columns. Does the SELECT list carry the fields needed to evidence the conclusion — identifiers, dates, amounts, approver — or only a total that cannot be traced?

Then reconcile. A reviewed query is not a verified population. The final step is always to tie COUNT(*) and SUM(amount) to an independently generated system control report.