ExamFinch.com My Study

Study guide

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

Practice this exam

CPA Information Systems and Controls ISC

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Introduction & CPA Evolution Exam Overview

1.1 CPA Evolution Architecture & ISC Exam Logistics 1.2 Blueprint Navigation & Study Methodology 1.3 Task-Based Simulations & IT Audit Mechanics

2 Chapter 2: IT Infrastructure, Architecture & Cloud Environments

2.1 Hardware, Operating Systems & Network Architecture 2.2 Cloud Computing Service & Deployment Models 2.3 Enterprise Resource Planning (ERP) & AIS Integration 2.4 Cloud Governance, End-User Computing & AIS Process Improvement 2.5 Blockchain, Distributed Ledgers & COSO Internal Control

3 Chapter 3: IT General Controls, SDLC & Business Process Controls

3.1 IT General Controls (ITGC) Architecture 3.2 System Development Life Cycle (SDLC) & Implementation 3.3 Program Change Management & Release Controls 3.4 Business Process & Automated Application Controls 3.5 Business Process Models, Flowcharts, DFDs & BPMN

4 Chapter 4: Data Governance, Relational Databases & Data Extraction

4.1 Data Governance Frameworks, Lineage & Quality Dimensions 4.2 Relational Database Architecture & Normalization 4.3 SQL Fundamentals, Data Extraction & Analytical Pipelines 4.4 The Data Life Cycle, Extraction Methods & Data Integration 4.5 SQL Operators, Aggregate & String Functions: Reviewing Retrieved Data Sets

5 Chapter 5: Security Governance, Frameworks & Threat Landscape

5.1 Cybersecurity & IT Risk Frameworks 5.2 Threat Modeling & Modern Cyber Attack Vectors 5.3 Vulnerability Management, Patching & Security Testing 5.4 The NIST Framework Family: CSF Parts, Privacy Framework & SP 800-53 5.5 COBIT 2019 Governance System, CIS Controls v8.1 & PCI DSS Requirements 5.6 Threat Agents, Attack Techniques & the Stages of a Cyber-Attack 5.7 Cloud, IoT, Mobile & Third-Party Connection Risk

6 Chapter 6: Identity and Access Management (IAM) & Cryptography

6.1 Identity & Access Management (IAM) Architecture 6.2 Cryptography Fundamentals: Encryption & Hashing 6.3 Public Key Infrastructure (PKI), TLS & Network Security 6.4 Network & Endpoint Protection, Defense in Depth & Acceptable Use 6.5 Security Awareness, Control Testing & Security Assessment Reporting

7 Chapter 7: Incident Response, Business Continuity & Privacy Regulations

7.1 Incident Response Lifecycle & Forensic Procedures 7.2 Business Continuity, Disaster Recovery & Resiliency 7.3 Privacy Regulations & Compliance Frameworks 7.4 Data Classification, Sanitization & Privacy Controls 7.5 System Availability Measures, Mirroring & Replication 7.6 Protecting Confidential Data in Development, Operations & Data Loss Prevention

8 Chapter 8: Attestation Standards & the SOC Framework

8.1 AICPA Attestation Standards & SSAE 21 Framework 8.2 The System and Organization Controls (SOC) Reporting Suite 8.3 SOC 1 Engagements & Financial Statement Reporting (AT-C 320) 8.4 Service Auditor vs. User Auditor Responsibilities 8.5 Planning a SOC Engagement: Independence, Materiality, Risk Assessment & Representations

9 Chapter 9: SOC 2 Trust Services Criteria & Reporting Mechanics

9.1 Trust Services Criteria (TSC) for SOC 2 Engagements 9.2 Type 1 vs. Type 2 Reports & Testing Procedures 9.3 Complementary User Entity Controls (CUECs) 9.4 Subservice Organizations & Reporting Approaches 9.5 System Description Criteria, Boundaries, Service Commitments & Complaint Channels

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

Key Takeaways Explain This Topic AI

SQL statements divide into DML (SELECT, INSERT, UPDATE, DELETE), DDL (CREATE, ALTER, DROP, TRUNCATE) and DCL (GRANT, REVOKE); an audit extraction account should hold SELECT and nothing else.

AND binds more tightly than OR, so a mixed condition written without parentheses returns a different population than intended - the single most common cause of a wrong audit population.

Any comparison to NULL evaluates to UNKNOWN rather than TRUE, so a filter such as approver_id <> 'JSMITH' silently drops every unapproved row, which is usually the exact population under investigation.

COUNT(*) counts all rows while COUNT(column) counts only non-NULL values, and AVG excludes NULL rows from its denominator, so an average computed over a partially null column overstates the true average across the full population.

String functions matter because values that look identical on screen fail to join: UPPER and TRIM normalization should be applied before concluding that two systems share no matching records.

Last updated: September 2026

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.

Family Statements Purpose Who Should Hold It

DML — Data Manipulation SELECT, INSERT, UPDATE, DELETE Read and change the data in tables Applications; auditors get SELECT only

DDL — Data Definition CREATE, ALTER, DROP, TRUNCATE Change the structure of the database Database administrators, under change management

DCL — Data Control GRANT, REVOKE Change who can do what Security administration, segregated from DBAs where practical

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:

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 .

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.

= NULL never matches anything. The only valid tests are IS NULL and IS NOT NULL .

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

  • Function Behavior With NULLs Audit Use
  • COUNT(*) Counts every row, NULLs included The population count to reconcile against a system control report

COUNT(column) Counts rows where that column is not NULL Quantifies missing values: COUNT(*) - COUNT(approval_date) is the number of unapproved items

COUNT(DISTINCT column) Counts distinct non-NULL values Number of distinct vendors paid; detecting a vendor master that has more IDs than legal entities

SUM(column) Ignores NULLs; returns NULL if every row is NULL The control total to tie to the general ledger

AVG(column) Ignores NULLs in the denominator as well Dangerous: the average of 100 rows where 40 are NULL is computed over 60, not 100

MIN / MAX Ignore NULLs Date range of the population; largest single transaction for scoping

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.

  • Function (typical form) What It Does Why an Auditor Uses It
  • UPPER(s) / LOWER(s) Forces case Matching vendor names across systems where one stores uppercase

TRIM(s) / LTRIM / RTRIM Removes leading and trailing spaces Fixed-width legacy extracts pad every field; the padding breaks equality joins invisibly

LENGTH(s) / LEN(s) Character count Validating that every taxpayer identification number has nine digits, or finding truncated fields

SUBSTRING(s, start, count) Extracts part of a string Pulling a cost center out of an embedded account code

LEFT(s, n) / RIGHT(s, n) Leading or trailing characters Grouping general ledger accounts by their first four digits

CONCAT(a, b) or a || b Joins strings Building a composite match key from vendor ID plus invoice number

REPLACE(s, old, new) Substitutes text Stripping punctuation from names or hyphens from identifiers before matching

  • POSITION / CHARINDEX Locates a substring Finding where a delimiter falls before splitting a field
  • 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?

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.

NULL handling. Does any filter use <> , NOT IN , or a comparison operator on a nullable column without an explicit NULL branch?

Date boundaries. Half-open range, or an inclusive endpoint on a timestamp column that drops the last day?

Operator precedence. Are there mixed AND/OR conditions without parentheses?

Row limits. Is there a LIMIT, TOP or FETCH FIRST clause left over from testing?

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?

Scope filters. Are entity, ledger, currency and period filters present and correct for the objective?

Grain. Does the join change the grain and inflate aggregates? Check any SUM taken across a one-to-many join.

Duplicates. Is DISTINCT being used to mask a join defect rather than to state a real requirement?

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.