Chapter 9: SQL Essentials for Accounting Analytics

Accounting Analytics: An Integrated Approach

In this chapter

Part III of V: Accounting Analytics with SQL

Chapter 1 of 4 in this Part

• 9.1 Standard Costs in Brief
• 9.2 Why Query the Database Directly
• 9.3 SQLite and DB Browser for SQLite
• 9.4 The SELECT Statement
• 9.5 When a Query Fails
• 9.6 Calculated Columns and Functions

• Guided Tutorial 9.1
• 9.8 Filtering Rows with WHERE
• 9.9 Missing Values and NULL
• Guided Tutorial 9.2
• 9.11 Sorting and Limiting Results
• 9.12 Saving, Documenting, and Sharing Queries
• 9.13 Reading and Correcting Queries
• Guided Tutorial 9.3
• 9.15 Looking Ahead

This chapter opens Part III and introduces SQL through the Charles River database. Each module follows a section of the chapter, and the three guided tutorials build one saved script that finds and ranks the manufacturing variance.

Learning objectives

1. Explain when querying a database directly serves an accounting analysis better than working in a spreadsheet, and how a saved query documents the work.
2. Set up DB Browser for SQLite with a working copy of the Charles River database, and read a table’s structure before querying it.
3. Write SELECT statements that choose columns, rename them with aliases, calculate new values with arithmetic and functions, and list the distinct values a column holds.

The first three objectives cover why to query a database, how to set up the tool, and the SELECT statement with its calculated columns.

Learning objectives (continued)

4. Filter rows with WHERE, using comparison and logical operators, BETWEEN, IN, LIKE, and tests for missing values, and compare values of the right type.
5. Sort and limit query results to rank transactions and find the largest and smallest items.
6. Save queries in documented scripts, move their results into Excel, and find and correct syntax and logical errors.

The last three cover filtering, sorting and limiting, and saving, documenting and correcting queries.

This one is a database question

• The controller turns to cost of goods sold, and one line in it: manufacturing variance
• It rose each year, to about three-quarters above fiscal 2024, while sales grew less than a tenth
• Is production less efficient, or do the standards no longer describe the plant?
• The detail sits in tables the Part II workbook never imported

Those tables hold the work orders and their closes, the time employees recorded, the payroll, and a ledger of hundreds of thousands of rows. “This one is a database question,” she says. “Start with the database.” Volume, prices and the timing of the records can move the variance too, so the two explanations are hypotheses to test. This chapter sets up the tool and writes the first queries.

9.1 Standard Costs in Brief

Standard costing moves the leftover to a variance

• Materials go to work in process; conversion cost to a clearing account as incurred
• Completed units go to finished goods at standard, relieving both
• At a work order’s close, actual less standard moves to the manufacturing variance account
• Reported with cost of goods sold; a positive overhead part is underapplied overhead

A standard cost system values each item at what one unit should require, at standard prices and rates (Datar and Rajan). A positive variance, actual above standard, is unfavorable and lowers the margin; a negative one is favorable, and a negative overhead part is overapplied. The close record splits the conversion part into direct labor and overhead, and the year-end closing entry clears the variance account with the other income statement accounts. Chapter 10 follows the whole flow through the ledger.

9.2 Why Query the Database Directly

A query asks the database for only what you need

• Part II’s imports copy rows, and a ledger-sized table slows a workbook
• A spreadsheet analysis grows hard to implement and to audit (Borthick et al.)
• A query is a written request: nothing is copied until you ask, and it can be rerun
• SQL is declarative: you state what you want, and the database finds it

SQL was developed at IBM soon after Codd proposed the relational model of Chapter 3, and it became the standard language of relational databases (Chamberlin). One query describes a whole result, whether it holds ten rows or a million, unlike an Excel formula that calculates one cell at a time.

Import or query: two paths to an analysis

Figure 9.1 · Importing Data Compared with Querying It

Neither replaces the other: Excel is strongest for ad hoc analysis, modeling and traceable workpapers, and SQL for large data, related tables and repeated procedures. The database holds the same data as the workbook, table for table; only the tool changes.

9.3 SQLite and DB Browser for SQLite

One database file, one tool, one working copy

• SQLite keeps a whole database in a single file, with no server to install
• Core SQL works the same in every major system; each adds its own dialect
• DB Browser’s four tabs: Database Structure, Browse Data, Edit Pragmas, Execute SQL
• Query a copy, CharlesRiver_Work.sqlite, so the download is never changed

DB Browser can change a database as well as read it, which is why the tutorials work in a copy. Open Database Read Only, from the arrow beside Open Database, prevents changes to the database; the working copy is still needed because Chapter 11 saves queries in it.

Database Structure lists every column and its type

Figure 9.2 · The Database Structure Tab of DB Browser for SQLite (detail)

Look here first when you plan a query: it gives the exact name of every table and column and the type each holds. As Chapter 3 explained, the Charles River database stores whole numbers as INTEGER, amounts as REAL, and dates as TEXT in year-month-day form.

Watch out: do not type in Browse Data

• A value typed into a cell there edits the table
• Write Changes and Revert Changes, normally grayed out, become available
• Write Changes saves the edit into the file; Revert Changes undoes it
• If asked on closing whether to save changes you did not intend, click Discard

On a Mac the button reads Don’t Save. Working in a copy means no mistake can reach the file you downloaded.

9.4 The SELECT Statement

SELECT names the columns; FROM names the table

SELECT AccountNumber, AccountName, AccountType
FROM Account;

• SELECT * returns every column; a list returns only those, in that order
• The result set is a new answer each time: the table never changes
• With several queries in the editor, select one and run the selection

A statement is a complete instruction ending with a semicolon, built from clauses that each begin with a keyword. The book writes keywords in capitals and each clause on its own line, a convention for readers; SQLite ignores case in keywords and names.

9.5 When a Query Fails

SQLite names the problem

Table 9.1 · Common SQLite error messages and their usual causes

Message	Usual cause
no such column: AccountNumbr	A column name misspelled, or a text value written without quotes
no such table: Accounts	A table name misspelled
near "WHERE": syntax error	Clauses out of order, or a comma or keyword missing before the word named
unrecognized token: "'Asset"	A quotation mark opened but never closed

An error like these stops the query, turns the message pane red, and gives the line where the statement starts; the message can point near the mistake rather than at it. A misspelled name, which SQLite finds before it runs anything, is one cause to rule out, and the Database Structure tab is where to check the spelling.

Watch out: when a query misbehaves, check these first

• Was the whole query selected before you ran it?
• Does the title bar show CharlesRiver_Work.sqlite?
• Are the names spelled as on the Database Structure tab?
• Save SQL file saves the script; Ctrl+S is Write Changes, which saves the database

Running with nothing selected runs the whole script and shows only the last result. Also check that you undid any change a step asked you to make only for a moment.

9.6 Calculated Columns and Functions

Aliases and arithmetic make new columns

• AS gives a result column a new heading; the table keeps its names
• A calculated column applies +, -, * and / to each row
• ROUND to two places rounds an amount to the cent
• Two INTEGER values divide as integers: multiply one by 1.0 for a fraction

A calculated column exists only in the result, not in the table. The percent operator returns a remainder, but it drops decimals first, which makes it unsuitable for amounts.

Functions that round, convert and clean text

Table 9.2 · Scalar functions and expressions used in this Part

Function	Returns	Example	Result
ROUND(x, d)	x rounded to d decimal places	ROUND(1234.567, 2)	1234.57
ABS(x)	x without its sign	ABS(-2011.14)	2011.14
CAST(x AS type)	x converted to INTEGER, REAL, or TEXT	CAST('2026' AS INTEGER)	2026
UPPER(x)	x in capital letters	UPPER('llc')	LLC
TRIM(x)	x without spaces at either end	TRIM(' LLC ')	LLC

ROUND to two places matches the cents of the dataset’s amounts, and CAST converts text to a number when a calculation needs one. UPPER of TRIM puts inconsistently entered text into one form before it is compared.

Functions that take text apart and fill gaps

Table 9.2 · Scalar functions and expressions used in this Part

Function	Returns	Example	Result
REPLACE(x, a, b)	x with every a replaced by b	REPLACE('WO-2026', '-', '')	WO2026
LENGTH(x)	the number of characters in x	LENGTH('WO-2026')	7
SUBSTR(x, s, n)	n characters of x, from position s	SUBSTR('WO-2026', 4, 4)	2026
SUBSTR(x, s)	the rest of x, from position s	SUBSTR('JE-2026-000296', 9)	000296
COALESCE(x, y)	x, or y when x is NULL	COALESCE(NULL, 'open')	open

Document numbers carry a year in a fixed place, which SUBSTR extracts as text, counting from one. COALESCE returns its second value when the first is missing, and the double bar joins text into a label.

DISTINCT shows the values a column holds

• SELECT DISTINCT SourceDocumentType FROM GLEntry lists each kind of source once
• Use it before filtering: a filter on a value no row holds returns nothing, silently
• SQLite’s ROUND treats negative places as zero, unlike Excel’s
• A SELECT without FROM is a quick way to test an expression

An empty result from a mistyped value is easy to mistake for a finding, which is why DISTINCT comes first. The SQLite documentation describes every function.

Check your understanding

What does each expression return in SQLite?

1. 7 / 2
2. 7 / 2.0
3. ROUND(1234.567, -2)
4. UPPER(TRIM(' llc '))

Ask students to predict each answer before revealing it, then to name the Excel result that would differ.

SQLite has its own rules

1. 3: two INTEGER values divide as integers
2. 3.5: a REAL operand keeps the fraction
3. 1235.0: negative places count as zero, unlike Excel’s 1200
4. LLC: spaces trimmed, then capitals

A SELECT without a table tests all four at once, which is how to check an expression before using it in a query.

Guided Tutorial 9.1: Opening the Database and Writing Your First Queries

Open the database and query it in seven steps

1. Make a working copy of the database
2. Open the working copy and read the structure of the ledger
3. Start the chapter’s script
4. Run your first query
5. Choose the columns you need
6. Make and correct a deliberate error
7. Calculate a check on the variance records

Students make the working copy, read the ledger’s structure, start Chapter09.sql with a header comment, and write their first queries, including one deliberate error and a check on the variance records.

Choose the columns you need

Figure 9.3 · Choosing Columns from the Account Table (detail)

The window’s parts: the editor tab named after the saved script; Open SQL file(s) and Save SQL file, icons on the real toolbar; Execute all/selected SQL; the selected query; the result grid; and the message pane below it. The outlined rows show the clearing and variance accounts, whose AccountIDs differ from their account numbers.

A misspelled column turns the pane red

Figure 9.4 · A Misspelled Column in the Message Pane

The message names the column SQLite could not find and the line where the statement starts. Correcting the spelling and running the query again clears the error.

Every close adds up

Figure 9.5 · A Calculated Check on the Variance Records (detail)

The query adds the material and conversion parts of each close and subtracts the recorded total. The CheckDifference column is zero in every row shown; Tutorial 9.2 tests every close at once.

Checkpoint

Check that:

• the title bar shows the path of CharlesRiver_Work.sqlite, not the file you downloaded, and Write Changes is grayed out;
• Chapter09.sql holds the header comment and three queries, each under a comment;
• you have recorded AccountID 92 for account 1090 and AccountID 93 for account 5080;
• the misspelled column produced a red message pane, and the corrected query ran;
• the CheckDifference column is zero in the rows you can see.

Students now know where the variance is recorded: in the WorkOrderClose table, one row per closed work order, and in the ledger, under an AccountID that differs from its account number.

9.8 Filtering Rows with WHERE

WHERE keeps the rows that meet a condition

• Numbers go without quotes; text goes in single quotes and must match exactly
• UPPER(TRIM(x)) compares text whose case or spaces cannot be trusted
• A value compared with a column takes the column’s type where it can
• Text compared with a number finds nothing: a year cut from text needs quotes or CAST

The conversion comes from the column’s affinity, or from a CAST; a function’s result and a typed value have none, so a year taken from a document number with SUBSTR, compared with a bare number, returns no rows and no error.

Comparison and logical operators

Table 9.3 · Comparison and logical operators in a WHERE clause

Operator	Meaning	Example
= and <>	Equal to, not equal to	AccountID = 93
<, >, <=, >=	Less than, greater than, or equal	Debit > 1000
BETWEEN a AND b	Within a range, including both ends	FiscalYear BETWEEN 2025 AND 2026
IN (a, b, c)	Equal to any value in a list	AccountNumber IN (1090, 5080)
LIKE	Matches a pattern	VoucherNumber LIKE 'JE-%'
IS NULL, IS NOT NULL	Has no value, has a value	ClosedDate IS NULL
AND, OR, NOT	Combine or reverse conditions	NOT Status = 'Closed'

IN shortens several conditions joined by OR, and NOT reverses BETWEEN, IN or LIKE. Each operator returns only the rows for which its condition is true.

AND is evaluated before OR

• AND binds first, the way multiplication comes before addition
• One account’s rows in two years, written without parentheses, returns every row of the second year
• That is hundreds of thousands of rows from every account
• Whenever AND meets OR, add parentheses

The parentheses make the logic clear to a reviewer as well as to the database. Comparing the row counts of the two versions shows the difference at once.

Dates are text in year-month-day form

• Compare a date column with a quoted date written the same way
• Any other form, such as month/day/year, compares wrongly without a warning
• BETWEEN includes both ends, but a date with a time sorts after the end date
• LIKE matches patterns: % for any run of characters, _ for one

For a column that stores a time, such as a journal entry’s CreatedDate, end the range with less than the next day. In SQLite, LIKE ignores the difference between capital and small letters of the English alphabet, unlike the equals sign.

9.9 Missing Values and NULL

NULL is unknown, so = NULL is never true

• NULL is not zero or empty text: the value is unknown or does not exist
• Find missing values with IS NULL; exclude them with IS NOT NULL
• NULL spreads through calculations: a NULL in an addition makes the result NULL
• COALESCE replaces a NULL, which is a decision, not a correction

A missing value is often the most important one in an audit test: a shipment with no tracking number, an entry with no approver, a time record with no clock-out. Writing them as IS NULL conditions makes the test explicit and repeatable.

IS NULL finds what = NULL cannot

Figure 9.6 · How WHERE Treats a Missing Value

The equals comparison is NULL for every row, so a WHERE clause that uses it keeps none. IS NULL keeps the open work orders, and COALESCE shows the word open in place of their missing dates.

Check your understanding

How many work orders or ledger rows does each condition keep?

1. ClosedDate = NULL
2. ClosedDate IS NULL
3. SourceDocumentType = 'workorderclose'
4. SourceDocumentType LIKE 'workorderclose'

Ask students which of the four would fail silently in a real analysis.

Two of the four silently return no rows

1. None: an ordinary comparison with NULL is never true
2. Every work order with no ClosedDate
3. None: = matches capitals exactly here, and the data says WorkOrderClose
4. Every posting of a work order close, to any account: LIKE ignores case in SQLite

The first and third fail silently, with no rows and no error. The fourth finds the work order closes’ postings to work in process and the clearing account as well as the variance account, so the variance postings of a year still need the account and the fiscal year. Case rules differ between databases and their collations, which is why the book writes text values exactly as the data spells them.

Guided Tutorial 9.2: Filtering the Ledger to the Variance Postings

Filter the ledger in eight steps

1. Find the accounts by their numbers
2. Filter the ledger to the variance account for fiscal 2026
3. List the source document types
4. Examine the journal entry and exclude it
5. Confirm the check for every work order close
6. Find the work orders that have not yet closed
7. Label the open work orders with functions
8. Test a year taken from the text

Students find the variance and clearing accounts by their numbers, filter the ledger to the variance account for the year, list its source document types, set the closing entry apart, check every close, and find the work orders not yet closed.

Two kinds of source post to the variance account

Figure 9.7 · The Source Document Types Behind the Variance Account

Work order closes post the variance through the year, and one journal entry, the year-end close, clears the account. Setting the close apart leaves the variance postings of the year; in another year, read each journal entry first, because one that adjusts the variance belongs in it.

Checkpoint

Check that:

• the ledger holds several thousand variance postings for fiscal 2026, of two source document types;
• the only journal entry is the closing entry JE-2026-000296, found by its source document type or by LIKE, and excluding it leaves the WorkOrderClose postings;
• no work order close has parts that differ from its total;
• ClosedDate IS NULL finds the open work orders, and ClosedDate = NULL finds none;
• SUBSTR(WorkOrderNumber, 4, 4) = 2026 finds no rows, while = '2026' finds every open work order.

The open work orders, all released during the year, show that the year’s variance covers only the work orders that closed during it.

9.11 Sorting and Limiting Results

ORDER BY sorts; LIMIT keeps the first rows

• ASC is the default; DESC sorts from largest to smallest
• Later columns break ties; you can sort by an expression, such as ABS of the variance
• In an ascending sort, SQLite places NULLs first
• LIMIT with ORDER BY ranks; LIMIT alone returns whichever rows come first

ORDER BY works like the levels of Excel’s Sort dialog, and WHERE like a filter written where a reviewer can read it. Never use LIMIT without ORDER BY to select rows for an analysis.

Watch out: LIMIT is a dialect difference

• SQLite, PostgreSQL and MySQL use LIMIT
• Microsoft SQL Server uses SELECT TOP
• The SQL standard and Oracle use FETCH FIRST … ROWS ONLY
• If a book query fails elsewhere, look for a dialect difference first

The core of SQL taught in this Part works in every major system; LIMIT is one of the few places where the wording changes.

9.12 Saving, Documenting, and Sharing Queries

A commented script is evidence

• Save SQL file saves the script; reopen it, send it, run it again
• -- starts a one-line comment; /* and */ enclose several lines
• A header records the purpose, the database, the preparer and date, and the checks
• A comment above each query says what it answers

The header is the SQL counterpart of the Documentation worksheet of Chapter 4. Audit teams and finance departments keep libraries of saved queries, so a one-off extraction becomes a procedure the next engagement can rerun.

Copy with Headers takes a result to Excel

• Select the result’s cells, or click one and press Ctrl+A
• Right-click and choose Copy with Headers, then paste into Excel
• DB Browser loads a large result in blocks, so it may ask to load the rest
• For a large result or a file to keep, Export to CSV

The query stays in the script, so the extraction can be repeated when the data changes. Export to CSV is under the Save the results view button on the Execute SQL toolbar.

9.13 Reading and Correcting Queries

Clauses come in a fixed order

Figure 9.8 · The Anatomy of a SQL Query

SELECT, FROM, WHERE, ORDER BY and LIMIT: only SELECT is always needed, and a clause out of order stops the query with a syntax error.

A logical error runs and answers another question

• An error that stops a query names the problem; a logical error does neither
• Errors recur in similar ways from student to student (Taipalus et al.)
• SELECT AccountNumber AccountName runs: the second name becomes an alias
• Know what the result should look like: row count, first rows, a control total

Missing parentheses, a comparison with NULL, text in the wrong case and text compared with a number are all logical errors. The defense is the same as in a workbook: check the result against what it should be.

Watch out: SQLite’s tolerance can hide mistakes

• It accepts a double-quoted value as text when no column has that name
• It treats a column’s declared type as a preference, not a rule
• It converts values in some comparisons and not in others
• Single quotes for text and like-for-like comparisons make queries travel

A query that relies on any of these habits may run in SQLite and fail, or return a different result, in another system.

Check your understanding

Name the logical error in each case.

1. A query meant to show two columns shows one
2. One account’s rows in two years returns rows from every account
3. A year cut from a document number matches nothing
4. A filter on a status returns no rows

Each runs without an error message, which is what makes them dangerous.

Each one ran without complaint

1. A missing comma: the second column became an alias
2. AND and OR without parentheses
3. Text compared with a number: quote the year or CAST it
4. A value no row holds: check it with DISTINCT first

All four are caught by knowing what the result should look like before running the query.

Guided Tutorial 9.3: Ranking the Largest Work-Order Variances

Rank the variances in six steps

1. Rank the largest variances of fiscal 2026
2. Measure the share of overhead
3. Look at the favorable end of the ranking
4. List the closes above a review threshold
5. Copy a result into Excel
6. Complete and save the script

Students rank the year’s closes by total variance, measure the share of overhead, look at the favorable end, list the closes above a review threshold, copy a result into Excel, and complete the script.

Overhead makes up most of each large variance

Figure 9.9 · The Ten Largest Work-Order Variances of Fiscal 2026 (detail)

The ten largest variances of the year are each a few thousand dollars, and overhead is most of each one. Sorted the other way, the ranking starts with favorable variances, and a review threshold finds several hundred closes, only a small share of them favorable.

The script documents itself

Figure 9.10 · The Chapter’s Script After Tutorial 9.3 (detail)

The header records the purpose, the database, the preparer and the checks, and a comment above each query says what it answers. Saved beside the working copy, the script can be reviewed and rerun.

Checkpoint

Check that:

• the ten largest variances of fiscal 2026 are each a few thousand dollars, and overhead makes up most of each one;
• sorted in ascending order, the ranking starts with favorable variances of up to about two thousand dollars;
• the review threshold finds several hundred closes above a thousand dollars either way, only a small share of them favorable;
• the ten rows pasted into Excel with their headings;
• Chapter09.sql is saved beside your working copy, with a completed header and a comment above each query.

The ranking points the controller toward overhead, which carries most of the conversion variance. It does not yet say which products those work orders made or how the variance changed from year to year.

9.15 Looking Ahead

Back to the controller’s question

• The variance is recorded when a work order closes, in material and conversion parts
• In the largest closes of the year, most of it is overhead
• The ledger holds the same amounts, plus a year-end close to set apart
• Which products, which years, what drives the overhead: Chapter 10 joins the tables

Most of this chapter’s work was descriptive analytics: finding, listing and ranking what the records hold. A single table cannot say more; the next questions span the work orders, the items, the payroll and the ledger.

Summary

• A saved query extracts only what it asks for, and documents how
• SELECT, FROM, aliases, calculated columns, functions and DISTINCT build a result
• WHERE filters with exact types, parentheses, quoted dates and IS NULL
• ORDER BY and LIMIT rank; scripts with comments make the work reviewable
• Errors that stop a query name themselves; logical errors need a check

Ask students which logical error they think they are most likely to make, and how they would catch it. The AND and OR precedence and the comparison with NULL are two to discuss.

Key terms

Calculated column · Clause · Column alias · Comment · Declarative language · DISTINCT · Logical error · Manufacturing variance · NULL · Operator precedence · Query · Result set · Scalar function · SQL (Structured Query Language) · SQL dialect · SQL script · SQLite · Standard cost · Syntax error · WHERE clause · Wildcard · Working copy

Every term is defined at the end of the chapter. Result set, NULL and logical error are the ideas the rest of Part III relies on most.

Practice and next steps

• Financial Accounting
  • Exercise 9.1: Reviewing the Chart of Accounts
  • Exercise 9.2: The Purchase Price Variance in the Ledger
• Managerial Accounting
  • Exercise 9.3: Standard Cost Components of Manufactured Items
  • Exercise 9.4: Open Work Orders at Year-End
• Auditing
  • Exercise 9.5: Shipments That Need Follow-Up
  • Exercise 9.6: Missing Values in Time and Labor Records

Next: Chapter 10: Joining and Summarizing Data in SQL

Assign one exercise from each perspective; Exercise 9.6 looks for missing values in the time and labor records, an IS NULL test with audit consequences. Chapter 10 joins and summarizes the tables.

About these slides

These slides accompany Accounting Analytics: An Integrated Approach, licensed under CC BY-SA 4.0. Every figure and table comes from the chapter:

https://aa.accountinganalyticshub.com/chapters/09-sql-essentials/chapter.html

Students can find each figure and table, with the full explanation, in the online chapter.