9  SQL Essentials for Accounting Analytics

Learning Objectives

After completing this chapter, you will be able to:

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.
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.

Chapter slides View slides Download PowerPoint

Opening Scenario

It is early 2027, and the controller has put the promotion question behind her. You are still the staff accountant who worked through that question with her in Part II, and she brings you the next one. Her attention has moved down the income statement to cost of goods sold. One line in it, manufacturing variance, has grown in every year of the data. It is now about three-quarters larger than in fiscal 2024, while sales grew by less than a tenth. Manufacturing variance is the difference between what production actually cost and the standard cost that Charles River assigns to the items it makes. A variance that grows year after year suggests either that production is becoming less efficient or that the standards no longer describe how the plant works, although volume, prices, or the timing of the records can move it too. She wants to know which explanation the records support.

She also knows where the answer lives. The detail behind the variance sits in tables that the analysis workbook of Part II never imported. They hold the work orders and their closes, the time employees recorded against them, and the payroll. They also hold the manufacturing entries in the general ledger, which alone has more than 800,000 rows. “This one is a database question,” she says. “Start with the database.” This chapter teaches you to do that. You will set up a tool for querying the Charles River database and write your first queries in SQL. With them, you will find the variance in the ledger, separate it from the entry that closes the books, and rank the work orders that produced it.

9.1 Standard Costs in Brief

The controller’s question rests on standard costing, which you met in managerial accounting. A standard cost system values each item the plant makes at its standard cost: the materials, direct labor, and overhead that one unit should require, at standard prices and rates (Datar & Rajan, 2021). Charles River’s ledger records production in three steps:

• Materials issued to a work order are charged to work in process, and the actual cost of conversion, the manufacturing payroll and the factory overhead, is charged to account 1090 Manufacturing Cost Clearing as it is incurred.
• When units are completed, finished goods inventory is charged with their standard cost, and work in process and account 1090 are relieved of the standard material and conversion cost of those units.
• When a work order closes, whatever is left, actual cost less standard, is moved to account 5080 Manufacturing Variance: the material part from work in process, and the conversion part, which the close record splits into direct labor and overhead, from account 1090.

Account 5080 is reported with cost of goods sold. A positive variance, actual cost above standard, is unfavorable and lowers the gross margin; a negative one is favorable and raises it. A cost accounting textbook would call a positive overhead part underapplied overhead: overhead incurred beyond what the standard rates charged to the units made, the opposite of overapplied overhead. The closing entry at each year-end clears the account with the other income statement accounts. Chapter 10 follows the whole flow through the ledger.

9.2 Why Query the Database Directly

In Part II you brought data into Excel with Power Query. You chose the Tables an analysis needed, filtered them, and loaded the result into a workbook of your own, and Power Query recorded every step so that the workbook could be refreshed. That approach works well when the data fits comfortably in a workbook and the analysis draws on a few tables. It becomes strained as the data grows and the questions reach across more tables. In the workflow of Part II, the rows an analysis uses are copied into the workbook, a table the size of the general ledger slows it down, and each new table has to be merged or looked up before the formulas can combine it with the others. Borthick et al. (2017) built a teaching case around exactly this experience. An analysis that begins in a spreadsheet becomes hard to implement and hard to audit as it needs more attributes from more sheets. The case then moves the data into a database, where the tables are joined and the analysis is done with queries.

A query is a written request for data. You describe the rows and columns you want, the database finds them where they are stored, and it returns only the result. Nothing is copied until you ask for it, and the request itself is a record of exactly what was extracted and how. You can save it, send it to a reviewer, and run it again next quarter against new data. Queries are written in SQL, which stands for Structured Query Language. SQL was developed at IBM in the 1970s, soon after Codd (1970) proposed the relational model you studied in Chapter 3, and it became the standard language of relational databases (Chamberlin, 2012).

SQL is a declarative language, which means that you state what you want rather than how to get it. A query that asks for the ledger rows of one account in one year says nothing about how to search the table. The database decides that for itself. This makes SQL quite different from the formulas you wrote in Excel, which calculate one cell at a time. A single query describes a whole result, whether it holds ten rows or a million.

Figure 9.1 contrasts the two ways of getting data to an analysis. Neither replaces the other. As Table 1.2 in Chapter 1 summarized, Excel is strongest for ad hoc analysis, modeling, and workpapers a reviewer can trace. SQL is strongest when data is large, when it spans related tables, and when a procedure must be repeated. Much professional work uses both, with SQL extracting and summarizing the data and Excel taking the result from there.

Figure 9.1: Importing Data Compared with Querying It.

NoteConnecting the Dots

The Charles River database holds the same data as CharlesRiver.xlsx, table for table. The tables, primary keys, and foreign keys you studied in Chapter 3 are the ones you will query in this Part, and the ledger rows you filtered in Excel in Part I are the same rows you will now filter with SQL. What changes is the tool, not the data.

9.3 SQLite and DB Browser for SQLite

SQL is a language, not a program. Many database systems understand it, including Microsoft SQL Server, Oracle, PostgreSQL, MySQL, and SQLite. This book uses SQLite, which keeps an entire database in a single file on your computer. There is no server to install, no account to create, and no network connection to configure. You open the file and query it. The core of SQL that this Part teaches works the same way in every major system. Each system also has its own dialect, with small differences in functions and syntax, and this Part points them out where they matter.

To query the file, you will use DB Browser for SQLite, a free program for Windows, macOS, and Linux. Its main window has four tabs. Database Structure lists the objects in the database in groups: the tables, with their columns and data types, and the indices, views, and triggers. Browse Data shows the rows of one table at a time, much like a worksheet. Edit Pragmas holds technical settings that you will not need. Execute SQL is where you type queries, run them, and read the results. Most of its buttons show only an icon, and hovering over one shows its name.

Before you open the database, make a copy of it. DB Browser can change a database as well as read it: a value typed into Browse Data edits the table, and the Write Changes button saves the edit into the file. The queries in this chapter only read data, but a stray keystroke can still alter a record. So you will work in a copy named CharlesRiver_Work.sqlite and keep the downloaded file untouched, for the same reason that the Part II tutorials never edited CharlesRiver.xlsx. If the copy is ever damaged, you replace it with a fresh copy of the download. When you only need to read a database, you can also open it with Open Database Read Only, from the arrow beside the Open Database button, and DB Browser then prevents changes to the database. The working copy is still needed, because Chapter 11 saves queries in it.

Figure 9.2 shows the working copy open in DB Browser, on the Database Structure tab. The tab is the first place to look when you plan a query, because it tells you the exact name of every table and column and the type of data each column holds. As Chapter 3 explained, the Charles River database stores whole numbers as INTEGER, amounts as REAL, and dates as TEXT in the form year-month-day.

Figure 9.2: The Database Structure Tab of DB Browser for SQLite.

WarningWatch out

Do not type into the cells of the Browse Data tab unless you mean to change the data. An edit there is held as a change that has not yet been saved, and the Write Changes and Revert Changes buttons, grayed out while nothing has changed, become available. Clicking Write Changes writes the edit into the file. If those buttons become available when you did not mean to change anything, click Revert Changes. If DB Browser asks as you close it whether to save changes you did not intend, click Discard (Don’t Save on a Mac). Working in a copy of the database means that no mistake can reach the file you downloaded.

9.4 The SELECT Statement

A statement is a complete instruction to the database, ended by a semicolon. Every statement in this chapter is a SELECT statement, which asks for data. A statement is built from clauses. Each clause begins with a keyword, a word with a fixed meaning in SQL, and does one job. SELECT names the columns you want, and FROM names the table they come from. The simplest query asks for every column of a table:

SELECT *
FROM Account;

The asterisk means all columns. When you run the query, DB Browser shows the result as a grid, with one column for each column of the table and one row for each account. This grid is called the result set. A message below it reports whether the query ran without errors, how many rows it returned, and how long it took. A query never changes the table it reads: the result set is a new answer, produced each time you ask. When the editor holds several queries and you run all of them, only the last result is shown, so you select the query you want and run the selection.

Selecting every column is useful for a first look at a table, but an analysis usually needs only some of them. List the columns you want instead of the asterisk, separated by commas, in the order you want them to appear:

SELECT AccountNumber, AccountName, AccountType
FROM Account;

SQL keywords such as SELECT and FROM can be written in any case, and SQLite also ignores case in table and column names, so select accountname from account runs as well. The queries in this book follow a common convention that makes them easier to read: keywords in capitals, table and column names exactly as the database spells them, and each clause on its own line. The line breaks do not change what a query does. They make its parts easy to see, which matters more as queries grow.

9.5 When a Query Fails

A mistake in the wording of a query is a syntax error, and a name the database does not know, such as a misspelled column, is a similar error that SQLite finds before it runs anything. In both cases SQLite cannot run the statement, so it stops and says why. In DB Browser, the message pane turns red and reports that execution finished with errors. It gives SQLite’s message and the line of the editor at which the statement starts. Table 9.1 lists the messages you are most likely to see and what usually causes them.

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

A misspelled name is one cause to rule out. A query that asks for AccountNumbr, or for a table called Accounts, fails, and the Database Structure tab is the place to check the spelling. The message is a useful clue, but it can point near the mistake rather than at it, and it does not say how to repair it. A second kind of error runs without any complaint, and this chapter returns to it once you have written a few queries.

WarningWatch out

When a query does not behave as the tutorial says, check these first. Was the whole query selected before you ran it? A partial selection runs only part of it, and running with nothing selected runs the whole script, so that only the result of its last statement appears. Does the title bar show CharlesRiver_Work.sqlite? Are the names spelled as on the Database Structure tab? Did you undo a change that a step asked you to make only for a moment? Finally, Save SQL file saves the script, while Ctrl+S is Write Changes, which saves the database.

9.6 Calculated Columns and Functions

A column alias gives a column a different name in the result set. You write it after the column, with the keyword AS:

SELECT AccountNumber AS Number, AccountName AS Name
FROM Account;

The alias changes only the heading of the result. The table keeps its own column names. An alias that contains a space must be enclosed in double quotes, as in AS "Account Name", which is one reason to prefer aliases without spaces.

9.6.1 Arithmetic

A column in the result set does not have to exist in the table. A calculated column applies arithmetic to the columns of each row, with +, -, *, and /, and it usually has an alias so that its heading says what it is. Every closed work order in the WorkOrderClose table records its variance in parts: a material part, and a conversion part for the cost of turning materials into products, which is itself made up of direct labor and overhead. The query below adds the material part and the conversion part and compares the sum with the recorded total:

SELECT WorkOrderCloseID,
    MaterialVarianceAmount + ConversionVarianceAmount AS PartsTotal,
    TotalVarianceAmount AS RecordedTotal
FROM WorkOrderClose;

The result is calculated when the query runs and is not stored anywhere. Amounts are stored as REAL numbers, which a computer holds in binary, so adding or multiplying two amounts can leave a tiny fraction of a cent. ROUND(x, 2) rounds x to two decimal places, the cents in which this dataset records its dollar amounts. The rounded result is still a REAL number held in binary, so ROUND sets the precision you report rather than making the arithmetic exact.

Division needs one more precaution. When both numbers are INTEGER values, SQLite divides them as integers and drops the remainder, so 7 / 2 returns 3. When either one is a REAL value, even one written as 7.0, the result keeps its fraction, so 7 / 2.0 and 7.0 / 2 both return 3.5. Amounts stored as REAL keep their decimals, but a ratio of two counts, or of two INTEGER columns, loses them unless you multiply one of them by 1.0 or convert it with CAST, described below. The % operator returns the remainder of a division, so 7 % 2 returns 1. Given a number with decimals, it drops the decimals first, which makes it unsuitable for amounts.

9.6.2 Functions

A function takes one or more values and returns a result. A scalar function returns one result for each row it is applied to, as ROUND does. Table 9.2 lists the scalar functions you will use most often in this Part, with CAST, an expression that works like one, and the SQLite documentation describes them all (SQLite, n.d.-a, n.d.-d).

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
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

Text functions take codes apart and standardize text. Charles River’s document numbers carry a year in a fixed place, counting positions from 1: in WO-2026-011512, the four characters from position 4 are the year the work order was released, and SUBSTR(WorkOrderNumber, 4, 4) extracts them. The result is text, so CAST(SUBSTR(WorkOrderNumber, 4, 4) AS INTEGER) turns it into a number when a calculation needs one. UPPER removes differences of case in the letters of the English alphabet, and TRIM removes stray spaces, so UPPER(TRIM(x)) puts text that may have been entered inconsistently into one form before it is compared. REPLACE removes characters such as hyphens. Chapter 2 showed why such differences break matching. LOWER is the counterpart of UPPER, and the || operator joins text, so AccountNumber || ' ' || AccountName produces a single label such as 5080 Manufacturing Variance.

Two differences from Excel are worth knowing. SQLite’s ROUND treats a negative number of decimal places as zero, so ROUND(1234.567, -2) returns 1235.0, not the 1200 that Excel’s ROUND would give. And a SELECT statement does not need a table at all: SELECT 7 / 2, 7 / 2.0, SUBSTR('WO-2026', 4, 4); returns one row of results. That makes it a quick way to test a function or an expression before you use it in a query.

9.6.3 Distinct Values

The keyword DISTINCT removes duplicate rows from the result. SELECT DISTINCT SourceDocumentType FROM GLEntry returns each kind of source document that posts to the ledger once, however many rows carry it. DISTINCT is how you discover the values a column actually holds, and you should use it before you filter on a value. A filter written for a value that does not exist, such as a status the data never uses, returns no rows and no error. It is easy to mistake that empty result for a finding.

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

Context and objective. In this tutorial, you will set up the environment that you will use for every SQL tutorial in Part III. You will make a working copy of the Charles River database, open it in DB Browser for SQLite, and read the structure of the tables that record the manufacturing variance. You will then start the script that holds the chapter’s queries and write your first queries in it. You will also make a deliberate error, read its message, and calculate a check on the variance records.

Prerequisites. You need DB Browser for SQLite, which is free, installed on your computer. Download it from https://sqlitebrowser.org/dl/. The tutorials use version 3.13.1; an older version may lack features that Part III uses. The About window, under the Help menu (under DB Browser for SQLite on a Mac), shows the version you have, and packages for Linux can be older. You also need the CharlesRiver.sqlite file from the Downloads page. The file is large, several hundred megabytes, so allow time for the download.

Step-by-step instructions.

Step 1. Make a working copy of the database. You will work in the copy, so that the file you downloaded is never changed.

1. In File Explorer (Finder on a Mac), open the folder that holds CharlesRiver.sqlite.
2. Copy the file and paste the copy into the same folder.
3. Rename the copy CharlesRiver_Work.sqlite.

The folder now holds two identical files. If File Explorer hides file extensions, type the name without .sqlite, or the file becomes CharlesRiver_Work.sqlite.sqlite. Every tutorial in Part III uses CharlesRiver_Work.sqlite.

Step 2. Open the working copy and read the structure of the ledger. The Database Structure tab tells you the exact names of the tables and columns before you write a query.

1. Start DB Browser for SQLite.
2. Click Open Database on the toolbar, select CharlesRiver_Work.sqlite, and click Open.
3. Check the title bar, which shows the path of the open file, ending in CharlesRiver_Work.sqlite.
4. On the Database Structure tab, scroll down the Tables group to GLEntry, and click the arrow beside it to show its columns.
5. Read the type of each column: AccountID and FiscalYear are INTEGER, Debit and Credit are REAL, and PostingDate and SourceDocumentType are TEXT.
6. Expand the WorkOrderClose table in the same way, and note its columns for the parts of the variance: MaterialVarianceAmount, DirectLaborVarianceAmount, OverheadVarianceAmount, ConversionVarianceAmount, and TotalVarianceAmount.

The tab should look like Figure 9.2. The Write Changes and Revert Changes buttons are grayed out, because nothing in the database has changed. If they ever become available while you are only reading data, something was edited, and Revert Changes undoes it.

Step 3. Start the chapter’s script. A script keeps the chapter’s queries together, so that you or a reviewer can run any of them again.

1. Click the Execute SQL tab. Its editor is empty, in a tab named SQL 1.

2. Type the header comment below, with your own name and the date.

   /* Chapter 9: manufacturing variance, first look
      Database: CharlesRiver_Work.sqlite (a copy of CharlesRiver.sqlite)
      Prepared by: your name, date
      Checks: listed at the end of Tutorial 9.3 */

3. Click Save SQL file on the toolbar of the Execute SQL tab, and save the script as Chapter09.sql in the folder that holds your working copy.

The editor’s tab is renamed Chapter09.sql. The text between /* and */ is a comment, which DB Browser ignores when it runs a script. Each query you write in this chapter goes at the end of the script, under a one-line comment that begins with two dashes. Click Save SQL file again whenever you add to the script. Ctrl+S is the shortcut for Write Changes, which saves changes to the database, not the script.

Step 4. Run your first query. The Execute SQL tab has an editor for queries, a grid for the result, and a pane for messages, which Figure 9.3, in the next step, labels.

1. Below the header, leave a blank line, and type the comment and query below.

   -- Tutorial 9.1: every column of the chart of accounts
   SELECT *
   FROM Account;

2. Select the two lines of the query, from SELECT to the semicolon.

3. Click Execute all/selected SQL, the button with a triangle pointing right, or press Ctrl+Return (Cmd+Return on a Mac). F5 and Ctrl+R also run it.

4. Read the message pane below the result.

The grid shows every column of the Account table, one row for each account in the chart of accounts. The message pane reports that the query ran without errors, how many rows it returned, a little over a hundred, and how long it took. Scroll through the rows and notice the header accounts, such as 1000 Assets, which organize the chart. They receive no postings.

Step 5. Choose the columns you need. Most analyses need only a few columns, listed in the order you want them.

1. Leave a blank line after the first query, and type the comment and query below.

   -- Tutorial 9.1: the columns that identify each account
   SELECT AccountID, AccountNumber, AccountName,
       AccountType, AccountSubType
   FROM Account;

2. Select the new query and run it.

3. Scroll down to account 5080 Manufacturing Variance and read its AccountID.

4. Record the AccountID of account 1090 Manufacturing Cost Clearing as well.

The result, shown in Figure 9.3, has five columns. AccountID and AccountNumber are different numbers: 5080 Manufacturing Variance has AccountID 93, and 1090 has AccountID 92. As Chapter 3 explained, AccountID is the surrogate key that the GLEntry table uses, so any query of the ledger must use it. Both accounts have the subtype you would expect: 5080 is part of cost of goods sold, and 1090 is a current asset.

Figure 9.3: Choosing Columns from the Account Table.

Step 6. Make and correct a deliberate error. Every query writer meets error messages, and reading one is quicker than hunting for the mistake.

1. In the query from Step 5, change AccountNumber on its first line to AccountNumbr.
2. Select the query and run it.
3. Read the message pane.
4. Correct the spelling, and run the query again.

The message pane turns red, as in Figure 9.4. It reports that execution finished with errors, names the column it cannot find, and gives the line of the editor at which the statement starts. After the correction, the query runs as before. Table 9.1 lists the other messages you are likely to meet.

Figure 9.4: A Misspelled Column in the Message Pane.

Step 7. Calculate a check on the variance records. A calculated column can test whether the parts of each variance add up to its total.

1. At the end of the script, type the comment and query below, which give each column a short alias.

   -- Tutorial 9.1: the parts of each variance and their check
   SELECT WorkOrderCloseID, CloseDate,
       MaterialVarianceAmount AS Material,
       ConversionVarianceAmount AS Conversion,
       TotalVarianceAmount AS Total,
       ROUND(MaterialVarianceAmount + ConversionVarianceAmount
           - TotalVarianceAmount, 2) AS CheckDifference
   FROM WorkOrderClose;

2. Select the query, run it, and read the number of rows in the message pane.

3. Scroll through the first rows and look at the CheckDifference column.

4. Click Save SQL file to save the script.

The result, shown in Figure 9.5, has one row for each closed work order, many thousands of them, and the headings use the aliases. In every row you can see, CheckDifference is zero: the material part and the conversion part of each variance add up to its total. You cannot scroll through thousands of rows to confirm the check for all of them, but in Tutorial 9.2 you will filter for the rows where it fails.

Figure 9.5: A Calculated Check on the Variance Records.

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.

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

9.8 Filtering Rows with WHERE

The WHERE clause keeps only the rows that meet a condition. It comes after FROM, and the condition compares a column with a value:

SELECT AccountID, AccountNumber, AccountName
FROM Account
WHERE AccountNumber = 5080;

Table 9.3 lists the comparison and logical operators you will use most. Numbers are written without quotes. Text values are written in single quotes, and with SQLite’s default comparison they must match the data exactly, including capitals: the condition SourceDocumentType = 'WorkOrderClose' finds the postings of the work order closes, but SourceDocumentType = 'workorderclose' finds nothing. When the capitals or spacing of stored text cannot be trusted, compare standardized values instead, as in UPPER(TRIM(CustomerName)) = 'BROWN LLC'.

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'

The type of a value matters as well. A column’s declared type gives it what SQLite calls an affinity, a preference for one kind of value, and a value compared with the column is converted to that kind where it can be, so AccountNumber = '5080' finds account 5080 even though the number is written as text (SQLite, n.d.-b). A value typed into the query, or returned by a function such as SUBSTR, has no affinity of its own. SUBSTR returns text, so SUBSTR(WorkOrderNumber, 4, 4) = 2026 compares text with a number, nothing converts either side, and the query finds no rows at all, without any error. Write the value as text, = '2026', or convert the text with CAST, which gives the result the affinity of the type it names, before you compare it with a number.

Dates need particular care in SQLite, which has no separate date type. The Charles River dates are stored as text in the form year-month-day, such as 2026-12-31, and text in that form sorts in the same order as the dates it represents. A date condition therefore compares the column with a date written the same way, in quotes: PostingDate >= '2026-01-01'. A date written in any other form, such as 12/31/2026, does not compare correctly, and SQLite gives no warning. Charles River’s fiscal year is the calendar year, so the postings of fiscal 2026 are those dated from 2026-01-01 to 2026-12-31, and GLEntry also records the year in its FiscalYear column.

9.8.1 Combining Conditions

AND requires both conditions to be true, OR requires at least one, and NOT reverses a condition. When a WHERE clause mixes AND and OR, SQL evaluates the ANDs first, the same way that arithmetic multiplies before it adds. This order is called operator precedence, and it can change the answer completely. Suppose you want the ledger rows of the manufacturing variance account, AccountID 93, in fiscal 2025 and 2026. This query looks right but is not:

SELECT GLEntryID, AccountID, FiscalYear, Debit, Credit
FROM GLEntry
WHERE AccountID = 93 AND FiscalYear = 2025 OR FiscalYear = 2026;

Because AND is evaluated first, the condition means the variance rows of 2025, or any ledger row of 2026. The query returns hundreds of thousands of rows, from every account with a posting in 2026. Parentheses make the intended grouping explicit:

SELECT GLEntryID, AccountID, FiscalYear, Debit, Credit
FROM GLEntry
WHERE AccountID = 93 AND (FiscalYear = 2025 OR FiscalYear = 2026);

The second query returns only the variance postings of the two years, a small fraction of the first result. Comparing the row counts of the two is a quick way to see the difference that the parentheses make.

Whenever a condition combines AND with OR, add parentheses, even when you believe the default order gives the right answer. They make the logic clear to a reviewer as well as to the database.

9.8.2 BETWEEN, IN, and LIKE

Three operators shorten conditions that would otherwise take several comparisons. BETWEEN selects a range and includes both ends, so CloseDate BETWEEN '2026-01-01' AND '2026-12-31' selects every close dated in fiscal 2026. Take care with a column that stores a time as well as a date, such as the CreatedDate of a journal entry, which reads like 2024-01-06 10:00:00. Such a value on December 31 sorts after the text 2026-12-31, so BETWEEN leaves it out. For those columns, end the range with < '2027-01-01'.

IN selects any value in a list, so AccountNumber IN (1090, 5080) is shorter than two conditions joined by OR. LIKE compares text with a pattern. In the pattern, a wildcard stands for other characters: % stands for any run of characters, including none, and _ for exactly one. The pattern 'JE-2026-%' matches every voucher number that begins with JE-2026-. In SQLite, LIKE ignores the difference between capital and small letters of the English alphabet, unlike = (SQLite, n.d.-d). NOT reverses any of the three, as in NOT IN and NOT LIKE.

9.9 Missing Values and NULL

A column with no value holds NULL. NULL is not zero, and it is not an empty piece of text. It means that the value is unknown or does not exist, as with the ClosedDate of a work order that has not yet closed. Chapter 2 discussed why missing values matter. SQL treats them in a way that often surprises new users. Any ordinary comparison with NULL, such as = or <, is itself unknown, so a condition such as ClosedDate = NULL is never true, and a query that uses it returns no rows at all.

To find missing values, use IS NULL, and to exclude them, use IS NOT NULL (SQLite, n.d.-d). The condition ClosedDate IS NULL returns every work order that has not yet closed, as Figure 9.6 shows. NULL also spreads through calculations: an expression such as ListPrice - StandardCost is NULL for any row whose ListPrice is NULL. When a result has unexpected blank cells, look for a NULL in one of the columns it was calculated from.

Figure 9.6: How WHERE Treats a Missing Value.

COALESCE replaces a missing value with one you choose. COALESCE(ClosedDate, 'open') returns the ClosedDate where there is one and the text open where there is not, and COALESCE(Amount, 0) lets a calculation treat a missing amount as zero. IFNULL does the same with exactly two values. Replacing a NULL is a decision, not a correction, so a zero in place of a missing amount should be one you can justify.

TipIn Practice

A missing value is often the most important value in an audit test. A shipment with no tracking number, a journal entry with no approver, or a time record with no clock-out time may each point to a step in a process that did not happen, or to one that happened without being recorded, and completeness tests look for exactly these gaps. Writing them as IS NULL conditions makes the test explicit, repeatable, and easy for a reviewer to check.

9.10 Guided Tutorial 9.2: Filtering the Ledger to the Variance Postings

Context and objective. The general ledger records every posting to the manufacturing variance account, but it records them among more than 800,000 rows. In this tutorial, you will filter the ledger down to the variance postings of fiscal 2026, find out which source documents produced them, and identify the one posting that must be left out when you measure the year’s variance. You will also confirm the check from Tutorial 9.1 for every row, list the work orders that have not yet closed, whose variance, if any, has not yet been recorded, and label them with functions.

Prerequisites. Complete Tutorial 9.1, and open CharlesRiver_Work.sqlite in DB Browser for SQLite on the Execute SQL tab, with Chapter09.sql in the editor. If you closed it, click Open SQL file(s) and choose it. Type each query of this tutorial at the end of the script, under its comment, and run it by selecting it. If you start here, make the working copy as Step 1 of Tutorial 9.1 describes, and download Chapter09.sql as it stands at the end of Tutorial 9.1 from Companion Files.

Step-by-step instructions.

Step 1. Find the accounts by their numbers. In Tutorial 9.1 you scrolled to find the two accounts. A WHERE clause finds them directly.

1. Type and run the query below.

   -- Tutorial 9.2: the two manufacturing accounts by number
   SELECT AccountID, AccountNumber, AccountName
   FROM Account
   WHERE AccountNumber IN (1090, 5080);

2. Confirm that the result holds two rows, with the AccountIDs you recorded.

The result lists account 1090 with AccountID 92 and account 5080 with AccountID 93. The account numbers are written without quotes, because AccountNumber is an INTEGER column.

Step 2. Filter the ledger to the variance account for fiscal 2026. GLEntry stores the fiscal year of each posting in its FiscalYear column. Charles River’s fiscal year is the calendar year, so FiscalYear 2026 holds the postings dated in 2026.

1. Type and run the query below.

   -- Tutorial 9.2: the variance postings of fiscal 2026
   SELECT GLEntryID, PostingDate, Debit, Credit,
       SourceDocumentType, VoucherNumber, Description
   FROM GLEntry
   WHERE AccountID = 93 AND FiscalYear = 2026;

2. Read the number of rows in the message pane.

3. Look at the first rows of the result.

The query returns several thousand rows, each a posting to manufacturing variance during fiscal 2026. Most rows carry a debit, which is an unfavorable variance, but some carry a credit, which is a favorable one. The Description column shows that a work order close usually posts two rows, one for the material part of its variance and one for the conversion part. Each row also names its source document type and voucher number, the trace back to the document that produced it.

Step 3. List the source document types. Before you total or filter the rows of a table, find out what kinds of rows it contains.

1. Type and run the query below.

   -- Tutorial 9.2: the source document types of those postings
   SELECT DISTINCT SourceDocumentType
   FROM GLEntry
   WHERE AccountID = 93 AND FiscalYear = 2026;

2. Read the values in the result.

The result, shown in Figure 9.7, holds two values. WorkOrderClose rows are the variances recorded when work orders close. JournalEntry is the other value, and it needs a closer look, because a journal entry to this account can adjust the variance or, at year-end, close the account, and only an adjustment belongs in the variance.

Figure 9.7: The Source Document Types Behind the Variance Account.

Step 4. Examine the journal entry and exclude it. A closing entry clears every revenue and expense account at year-end, as you found in the Part I case.

1. Type and run the query below.

   -- Tutorial 9.2: the closing entry, kept apart from the variance
   SELECT GLEntryID, PostingDate, Debit, Credit,
       VoucherNumber, Description
   FROM GLEntry
   WHERE AccountID = 93 AND FiscalYear = 2026
       AND SourceDocumentType = 'JournalEntry';

2. Read the posting date, the credit, and the description of the single row.

3. Change the last condition to VoucherNumber LIKE 'JE-2026-%', run the query again, and compare the result.

4. Change the last condition to SourceDocumentType = 'WorkOrderClose', run the query again, and compare the number of rows with Step 2.

5. Change the last condition back to SourceDocumentType = 'JournalEntry', so that the script keeps the query that finds the closing entry.

The journal entry is JE-2026-000296, dated December 31, 2026, with the description Close Manufacturing Variance. Its credit equals the year’s net variance, so it brings the account back to zero for the new year. A total of every row in Step 2 would therefore be zero, which is the same trap the closing entry set for the Furniture margin in the Part I case. The LIKE pattern finds the same single row, because it is the only posting to the account in 2026 whose voucher number begins with JE-2026-. The query with WorkOrderClose returns one row fewer than Step 2: every posting except the close. Neither condition identifies a closing entry by itself, so in another year or account read the description of each journal entry before you leave it out: one that adjusts the variance belongs in it.

Step 5. Confirm the check for every work order close. In Tutorial 9.1 you saw a zero CheckDifference in the rows on the screen. A filter can test all of them at once.

1. Type and run the query below.

   -- Tutorial 9.2: closes whose parts differ from their total
   SELECT WorkOrderCloseID, TotalVarianceAmount
   FROM WorkOrderClose
   WHERE ROUND(MaterialVarianceAmount + ConversionVarianceAmount
       - TotalVarianceAmount, 2) <> 0;

2. Read the message pane.

The query runs without errors and returns no rows. An empty result is a finding here: no work order close records a total that differs from the sum of its parts, so the parts account for the whole variance of every close. A close with a missing part would not appear either, because its calculation would be NULL; none of the three amounts is NULL in the Charles River data. The check shows that the records are consistent, not that each amount is right.

Step 6. Find the work orders that have not yet closed. A work order’s variance is recorded only when it closes, so the variance of a work order still open at year-end, if it has one, is not in the ledger.

1. Type and run the query below.

   -- Tutorial 9.2: work orders not yet closed
   SELECT WorkOrderNumber, Status, ReleasedDate, DueDate,
       CompletedDate
   FROM WorkOrder
   WHERE ClosedDate IS NULL;

2. Read the number of rows and the Status values in the result.

3. Change IS NULL to = NULL, run the query again, and read the message pane.

4. Change = NULL back to IS NULL.

The query with IS NULL returns several hundred work orders that were released, in progress, or completed, but have no ClosedDate in the records. A variance would be recorded for one of them only if and when it closes, and the open status does not say whether it will close, or how large its variance would be or in which direction. The query with = NULL returns no rows at all, because an ordinary comparison with NULL is never true.

Step 7. Label the open work orders with functions. Functions can replace a missing value with a label and take a code apart.

1. In the query from Step 6, replace the line that reads CompletedDate with two new columns, so that the query reads as below.

   -- Tutorial 9.2: work orders not yet closed
   SELECT WorkOrderNumber, Status, ReleasedDate, DueDate,
       COALESCE(CompletedDate, 'not completed') AS Completed,
       CAST(SUBSTR(WorkOrderNumber, 4, 4) AS INTEGER) AS ReleaseYear
   FROM WorkOrder
   WHERE ClosedDate IS NULL;

2. Run the query, and read the Completed and ReleaseYear columns.

The Completed column shows not completed for the work orders that were released or in progress, and the date for those that were completed but not yet closed. ReleaseYear takes the year from the work order number, which carries the year in which the work order was released, and it is 2026 for every open work order.

Step 8. Test a year taken from the text. A condition on the year that SUBSTR takes from the number looks as if it should keep every open work order.

1. Add the condition AND SUBSTR(WorkOrderNumber, 4, 4) = 2026 at the end of the WHERE clause, run the query, and read the number of rows.
2. Change 2026 in that condition to '2026', with quotes, and run the query again.
3. Remove the condition you added, so that the script keeps the query of Step 7.

The condition in item a returns no rows, although every open work order was released in 2026. SUBSTR returns text, and neither its result nor the number 2026 has the affinity of a column, so SQLite converts neither of them and compares text with a number, which are never equal. With the quotes, the same condition finds every open work order. The empty result in Step 5 was a finding, but this one is a logical error, and knowing what the result should be is how you tell the two apart.

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.

You have separated the variance postings from the entry that closes the account, and you have confirmed that the variance records add up. The postings are ready to be analyzed. The open work orders, all released during fiscal 2026, show that the variance of the year covers only the work orders that closed during it.

9.11 Sorting and Limiting Results

Rows in a result set have no guaranteed order unless you ask for one. ORDER BY sorts the result by one or more columns. ASC sorts in ascending order, which is the default, and DESC sorts in descending order:

SELECT WorkOrderCloseID, CloseDate, TotalVarianceAmount
FROM WorkOrderClose
ORDER BY TotalVarianceAmount DESC;

To sort on several columns, list them in order of priority, as in ORDER BY CloseDate, TotalVarianceAmount DESC. The first column decides the overall order, and each later column only breaks ties. You can sort by a calculated column or its alias, and by a column that the SELECT clause does not show. You can also sort by an expression: ORDER BY ABS(TotalVarianceAmount) DESC ranks variances by their size, whatever their sign. In an ascending sort, SQLite places NULLs first.

NoteConnecting the Dots

ORDER BY works like the sort levels of Excel’s Sort dialog: the first column is the first level, and each later column breaks the ties that the levels before it leave. WHERE plays the part of a filter, with one difference that matters in audit work. The condition is written in the query, so a reviewer can see exactly which rows were kept.

LIMIT keeps only the first rows of the result. Combined with ORDER BY, it answers ranking questions, such as the ten largest variances of the year or the five oldest open work orders. Without ORDER BY, LIMIT returns whichever rows the database happens to reach first. That is fine for a quick look at a table, but it should never be used to select rows for an analysis.

WarningWatch out

LIMIT is one of the places where SQL dialects differ. SQLite, PostgreSQL, and MySQL use LIMIT, Microsoft SQL Server uses SELECT TOP 10, and the SQL standard and Oracle use FETCH FIRST 10 ROWS ONLY. If a query from this book fails in another system, a dialect difference such as this one is the first thing to look for.

9.12 Saving, Documenting, and Sharing Queries

A query is only useful as evidence if it can be found, understood, and run again. DB Browser saves the contents of an Execute SQL tab as a SQL script, a plain text file with the extension .sql, which you can reopen later or send to someone else. Save a script with the Save SQL file button. Ctrl+S is the shortcut for Write Changes, which saves changes to the database, not the script. A script can hold many queries, each ending with a semicolon. To run one of them, select it and click Execute all/selected SQL. Execute current line (Shift+F5) runs the statement on the line where the cursor sits.

Comments explain a script to its reader and are ignored when it runs. Two dashes start a comment that runs to the end of the line, and /* and */ enclose a comment of several lines. Begin every script with a header comment that records the purpose of the queries, the database file they read, who prepared them and when, and what checks the results should pass. Then put a one-line comment above each query saying what it answers. A reviewer who opens the script can see what each query was meant to do, run it, and compare the result with what you reported.

NoteConnecting the Dots

A script’s header comment is the SQL counterpart of the Documentation worksheet you built in Chapter 4. Both record the purpose, the source, the preparer and date, and the checks, so that a reviewer can follow the work without having to ask.

TipIn Practice

Audit teams and finance departments keep libraries of saved queries for tests they repeat, such as extracting a period’s journal entries or listing unapproved documents. A saved, commented query turns a one-off extraction into a procedure. The next person, or the next year’s engagement, can rerun it on new data, with its dates moved to the new period, and know that it applies the same logic as before.

9.12.1 Taking a Result to Excel

A query result often ends up in a workbook, where Excel can chart it, model it, or format it for a report. In the Execute SQL tab, select the cells of the result, or click a cell and press Ctrl+A to select all of them. Then right-click the selection and choose Copy with Headers (Ctrl+Shift+C). The rows paste into Excel with their column headings, one value in each cell. DB Browser loads a large result in blocks, of 50,000 rows by default, so selecting all of a larger one asks whether to load the rest first. For a result that large, or one you want to keep as a file, the Save the results view button on the Execute SQL toolbar offers Export to CSV, which writes a file that Excel opens. The query stays in the script, so the extraction can be repeated when the data changes.

9.13 Reading and Correcting Queries

The clauses of a query must be written in a fixed order: SELECT, FROM, WHERE, ORDER BY, and LIMIT, as Figure 9.8 shows. Only SELECT is always needed, and the others appear when a query uses them. A clause out of order stops the query with a syntax error, such as near "WHERE": syntax error.

Figure 9.8: The Anatomy of a SQL Query.

Errors that stop a query name the problem. A logical error is more dangerous. It lets the query run and return a result, but the result answers a different question from the one you asked. In an analysis of more than 33,000 queries written by database students, Taipalus et al. (2018) found that such errors recur in similar ways from student to student. Nothing flags them, so a query that runs without an error has not yet shown that it answers the question.

You have already met several. A missing pair of parentheses around an OR condition, a comparison with NULL, a text value in the wrong case, and a text value compared with a number each return a wrong result, or none, without complaint. A missing comma is another. SELECT AccountNumber AccountName FROM Account runs, because SQL reads AccountName as an alias for AccountNumber and returns a single column.

The defense against logical errors is the same as in a workbook: know what the result should look like before you run the query. Check the row count against what you expect, and look at the first rows. When a total exists elsewhere, compare the result with it, as you did with the control totals of Chapter 4.

WarningWatch out

SQLite is more forgiving than most database systems, and its tolerance can hide mistakes. It accepts a text value written in double quotes when no column has that name, a habit that some builds of SQLite turn off, so SourceDocumentType = "JournalEntry" works here but fails in most other systems (SQLite, n.d.-c). In ordinary tables like these, which are not declared STRICT, it treats a column’s declared type as a preference rather than a rule, so a column declared INTEGER can hold text. And, as the section on WHERE explained, it converts values in some comparisons but not in others. A query that relies on any of these habits may run in SQLite and fail, or return a different result, elsewhere. Write text values in single quotes, compare numbers with numbers and dates with dates written as text, and your queries will travel.

9.14 Guided Tutorial 9.3: Ranking the Largest Work-Order Variances

Context and objective. The ledger postings tell you how much variance was recorded, but not which work orders produced it or which part of the cost went over standard. In this tutorial, you will rank the work order closes of fiscal 2026 by their variance, look at the parts of the largest ones, and examine the favorable end of the ranking. You will then list the closes above a review threshold in either direction, copy a result into Excel, and complete the chapter’s script.

Prerequisites. Complete Tutorials 9.1 and 9.2, and open CharlesRiver_Work.sqlite in DB Browser for SQLite on the Execute SQL tab, with Chapter09.sql in the editor. As before, type each query at the end of the script and run it by selecting it. You also need Excel for Step 5. If you start here, make the working copy as Step 1 of Tutorial 9.1 describes, and download Chapter09.sql as it stands at the end of Tutorial 9.2 from Companion Files.

Step-by-step instructions.

Step 1. Rank the largest variances of fiscal 2026. ORDER BY with DESC puts the largest values first, and LIMIT keeps the top of the ranking.

1. Type and run the query below.

   -- Tutorial 9.3: the ten largest variances of fiscal 2026
   SELECT WorkOrderCloseID, WorkOrderID, CloseDate,
       MaterialVarianceAmount AS Material,
       DirectLaborVarianceAmount AS Labor,
       OverheadVarianceAmount AS Overhead,
       TotalVarianceAmount AS Total
   FROM WorkOrderClose
   WHERE CloseDate BETWEEN '2026-01-01' AND '2026-12-31'
   ORDER BY TotalVarianceAmount DESC
   LIMIT 10;

2. Read the Total column from the top down.

3. Compare the Overhead column with the Material and Labor columns in each row.

The result, shown in Figure 9.9, lists the ten work order closes of fiscal 2026 with the largest unfavorable variances, each a few thousand dollars. In every one of them, the overhead part is far larger than the material and labor parts together.

Figure 9.9: The Ten Largest Work-Order Variances of Fiscal 2026.

Step 2. Measure the share of overhead. A calculated column turns the comparison in Step 1 into a number.

1. Add a comma after AS Total in the query from Step 1, and add a new column after it, so that the query reads as below.

   -- Tutorial 9.3: the ten largest variances of fiscal 2026
   SELECT WorkOrderCloseID, WorkOrderID, CloseDate,
       MaterialVarianceAmount AS Material,
       DirectLaborVarianceAmount AS Labor,
       OverheadVarianceAmount AS Overhead,
       TotalVarianceAmount AS Total,
       ROUND(OverheadVarianceAmount / TotalVarianceAmount, 2)
           AS OverheadShare
   FROM WorkOrderClose
   WHERE CloseDate BETWEEN '2026-01-01' AND '2026-12-31'
   ORDER BY TotalVarianceAmount DESC
   LIMIT 10;

2. Run the query and read the OverheadShare column.

Overhead makes up most of each of the ten largest variances, in most of them more than four-fifths. The variance amounts are stored as REAL numbers, so the division keeps its decimals. Without the comma after AS Total, the query would fail with a syntax error, because a new column must be separated from the one before it.

Step 3. Look at the favorable end of the ranking. A variance can be favorable as well as unfavorable, and the net variance of the year is the balance of the two.

1. In the query from Step 2, remove DESC from the ORDER BY clause, and run the query again.
2. Read the Total column of the ten rows.
3. Add DESC back, so that the script keeps the ranking of Step 2.

Sorted in ascending order, the ranking starts with negative totals, which are favorable variances of up to about two thousand dollars. They are much smaller than the largest unfavorable ones.

Step 4. List the closes above a review threshold. ABS removes the sign of a number, so one condition can find large variances in either direction.

1. Type and run the query below.

   -- Tutorial 9.3: closes of 2026 above $1,000 either way
   SELECT WorkOrderCloseID, CloseDate, TotalVarianceAmount
   FROM WorkOrderClose
   WHERE CloseDate BETWEEN '2026-01-01' AND '2026-12-31'
       AND ABS(TotalVarianceAmount) > 1000
   ORDER BY TotalVarianceAmount;

2. Read the number of rows in the message pane.

3. Scroll through the first rows, which the ascending sort puts at the favorable end.

The query keeps every close whose variance is more than a thousand dollars in either direction, the kind of threshold an analyst might set for review. It returns several hundred closes, about a quarter of the year’s closes. Only about a tenth of them are favorable, at the top of the result. Among the closes above this threshold, unfavorable variances are far more numerous. The mix of the whole year and its net total take every close into account, which Chapter 10 summarizes.

Step 5. Copy a result into Excel. A query result can move into a workbook to be charted or formatted for a report.

1. Select the query from Step 2 and run it again, so that the grid shows the ten largest variances.
2. Click any cell of the result grid, and press Ctrl+A to select all of its cells.
3. Right-click the selection and choose Copy with Headers.
4. Open a blank Excel workbook, click cell A1, and paste.

The ten rows paste into the worksheet with their column headings, one value in each cell, ready to be formatted or charted. For a result too large to copy comfortably, Save the results view > Export to CSV, on the Execute SQL toolbar, writes the result to a file that Excel opens. Either way, the query remains in the script, so the same extraction can be produced again.

Step 6. Complete and save the script. The header comment should record the checks that the queries passed.

1. Replace the last line of the header comment, so that the header reads as below.

   /* Chapter 9: manufacturing variance, first look
      Database: CharlesRiver_Work.sqlite (a copy of CharlesRiver.sqlite)
      Prepared by: your name, date
      Checks: every close adds up; the closing entry is JE-2026-000296 */

2. Click Save SQL file.

3. Select any query in the script, from SELECT to its semicolon, and run it again.

The script now looks like Figure 9.10. It holds every query of the chapter, each under a comment that says what it answers, and a header that records the purpose, source, preparer, and checks. When text is selected, DB Browser runs only the selection, so one script can hold every query and you can still run each one on its own. Running the whole script at once would run every query and show only the last result.

Figure 9.10: The Chapter’s Script After Tutorial 9.3.

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, and those questions need the tables to be joined and summarized.

9.15 Looking Ahead

This chapter has introduced SQL through the Charles River database. You set up DB Browser for SQLite with a working copy of the database and read the structure of its tables. You wrote SELECT statements that choose columns, rename them, calculate new ones with arithmetic and functions, and list the distinct values a column holds. You filtered rows with WHERE, combined conditions with AND and OR, used BETWEEN, IN, and LIKE, compared values of the right type, and found and replaced missing values with IS NULL and COALESCE. You sorted and limited results to rank transactions, copied a result into Excel, and saved your queries in a documented script. Along the way, you read the messages of errors that stop a query and learned to check a result against what it should be, the defense against logical errors. Most of this work was descriptive analytics, in the terms of Table 1.1: finding, listing, and ranking what the records hold.

The queries have also given the controller a first look at her question. The manufacturing variance is recorded when a work order closes, and each close records the variance in parts. In the largest closes of fiscal 2026, most of the variance is overhead, not material. The ledger holds the same amounts, together with a closing entry that clears the account at year-end and must be left out when the year’s variance is measured. A single table cannot say more. Which products carry the variance, how much of it arose in each year, and what drives the overhead are questions that span the work orders, the items, the payroll, and the ledger. Answering them requires joining tables and summarizing their rows, which is the work of Chapter 10.

9.16 Chapter Summary

A query is a written request for data that the database answers where the data is stored. Querying suits an accounting analysis when the data is large, when it spans related tables, and when the procedure must be repeated. The query text itself documents what was extracted, so it can be reviewed and run again. SQL is the standard language of relational databases, and it is declarative: you state the result you want, and the database decides how to produce it. This book uses SQLite, which keeps a whole database in one file, and DB Browser for SQLite, whose Database Structure tab shows every table and column and whose Execute SQL tab runs queries. Because DB Browser can change a database as well as read it, you work in a copy of the download, CharlesRiver_Work.sqlite. Write Changes stays grayed out while you only read.

A SELECT statement is built from clauses that name the columns it wants and the table they come from. An error in the wording or the names of a query, such as clauses out of order or a misspelled column, stops the statement, and the red message pane names the problem. Aliases rename columns in the result, and calculated columns apply arithmetic to each row, with ROUND rounding amounts to the cent. Two INTEGER values divide as integers, so a ratio of counts needs a REAL operand. Scalar functions return one value for each row. ABS removes a sign, CAST converts a value to another type, SUBSTR takes a code apart, and UPPER, TRIM, and REPLACE standardize text. DISTINCT lists the values a column actually holds, which is the way to learn the values in use before you filter on one.

The WHERE clause keeps the rows that meet a condition, built from comparison operators, BETWEEN, IN, and LIKE, and combined with AND, OR, and NOT. Text values sit in single quotes and must match the data exactly. SQLite converts a value to the type of the column it is compared with where it can, but the text that SUBSTR returns has no affinity, so compared with a number it finds nothing until the number is quoted or the text converted with CAST. Dates stored as text are compared with dates written the same way, and a column that also stores a time needs a range that ends before the next day. Parentheses make the order of AND and OR explicit. A missing value is NULL, which no ordinary comparison can match, so IS NULL and IS NOT NULL are the tests for it, and COALESCE replaces it with a value you choose.

ORDER BY sorts a result by one or more columns or expressions, and LIMIT keeps its first rows. Together they rank transactions, such as the largest variances of a year. A script saved from the Execute SQL tab keeps a chapter’s queries together, with a header comment that records their purpose, source, preparer, and checks. Copy with Headers moves a result into Excel, and Export to CSV writes it to a file. Logical errors, such as a missing comma or a text value compared with a number, return a result that answers the wrong question. The defense against them is to know what the result should look like before running the query.

Applied to the controller’s question, the queries showed that the manufacturing variance is recorded when work orders close, and that the variance parts of every close add up to its total. They also showed that the ledger postings of fiscal 2026 must be separated from the closing entry that clears the account. In the largest closes of the year, overhead makes up most of the variance, and only a small share of the closes above a thousand dollars either way are favorable. Which products carry the variance and how it grew from year to year are questions for Chapter 10.

9.17 Key Terms

Calculated column. A column of an Excel Table whose formula Excel applies by default to every row, including rows added later. In a SQL query, a calculated column is an expression in the SELECT list, such as Debit - Credit AS Net, calculated for each row when the query runs and not stored in the table.

Clause. A part of a SQL statement that begins with a keyword and performs one task, such as SELECT, FROM, WHERE, ORDER BY, or LIMIT. The clauses of a query must be written in a fixed order.

Column alias. A name given to a column in the result of a query with the keyword AS, such as TotalVarianceAmount AS Total. An alias changes only the heading of the result, not the column in the table.

Comment. Text in a SQL script that explains the queries and is ignored when they run. Two dashes start a comment that runs to the end of the line, and /* and */ enclose a comment of several lines.

Declarative language. A language in which you state the result you want rather than the steps for producing it. SQL is declarative: a query describes the rows and columns required, and the database decides how to find them.

DISTINCT. A keyword that removes duplicate rows from the result of a query, so that each combination of the selected values appears once. It is the way to list the values a column actually holds before filtering on one of them.

Logical error. A mistake that lets a query run without an error message but makes it answer a different question from the one intended, such as a missing comma, a missing pair of parentheses, or a text value compared with a number. Logical errors are found by comparing a result with what it should look like.

Manufacturing variance. The difference between what production actually cost and the standard cost of the items made. Charles River records it in account 5080 when a work order closes, in a material part and a conversion part, and reports it with cost of goods sold.

NULL. The marker SQL uses for a missing value: a value that is unknown or does not exist. NULL is not zero or empty text, no ordinary comparison with it, such as = or <, is true, and it is found with IS NULL and IS NOT NULL and replaced with COALESCE.

Operator precedence. The order in which SQL evaluates the operators of an expression. AND is evaluated before OR, so a condition that mixes them needs parentheses to express the intended grouping.

Query. A written request for data from a database. A query describes the data it wants, and the database returns the result without changing the tables it reads.

Result set. The rows and columns that a query returns. It looks like a table but exists only as the answer to the query.

Scalar function. A function that returns one value for each row it is applied to, such as ROUND, ABS, SUBSTR, or COALESCE. CAST is a conversion expression rather than a function, but it is used the same way, row by row.

SQL (Structured Query Language). The standard language for accessing, querying, and manipulating data stored in relational databases. It is a declarative language, and although database systems differ in details of their dialects, its core of SELECT, FROM, WHERE, and ORDER BY works the same way across them.

SQL dialect. The version of SQL that a particular database system understands. Dialects share a common core but differ in details, such as LIMIT in SQLite and TOP in Microsoft SQL Server.

SQL script. A plain text file, usually with the extension .sql, that holds one or more SQL statements and their comments. A documented script records what was asked of the database, so that the queries can be reviewed and run again.

SQLite. A lightweight relational database system that stores the entire database in a single file. SQLite has no separate date type, so the Charles River dates are stored as text, and it treats column types as advisory, so queries must compare values of the same kind.

Standard cost. The cost that one unit of an item should require in materials, direct labor, and overhead, at standard prices and rates. A standard cost system values inventory and cost of goods sold at standard cost and records the difference from actual cost as variances.

Syntax error. A mistake in the wording of a query, such as clauses out of order or a quotation mark left open, that stops the query from running. A misspelled table or column name stops it too, with a message such as no such column. SQLite reports either with a message that names the problem or points near it.

WHERE clause. The clause of a query that keeps only the rows meeting a condition, built from comparison operators such as = and >, from BETWEEN, IN, LIKE, and IS NULL, and from the logical operators AND, OR, and NOT.

Wildcard. A character that stands for other characters in a LIKE pattern. The percent sign matches any run of characters, including none, and the underscore matches exactly one.

Working copy. A copy of a database file made for analysis, so that the original is never changed. The Part III tutorials work in CharlesRiver_Work.sqlite, a copy of the downloaded CharlesRiver.sqlite.

9.18 Multiple Choice Questions

1. An analyst needs the account number and account name of every account, and nothing else. Which query returns exactly that?

1. SELECT * FROM Account;
2. SELECT AccountNumber, AccountName FROM Account;
3. SELECT AccountNumber, AccountName WHERE Account;
4. SELECT Account FROM AccountNumber, AccountName;

2. To list the ledger postings to account 5080 Manufacturing Variance, an analyst writes a condition on GLEntry.AccountID. Why does the condition use the value 93 instead of 5080?

1. SQLite stores account numbers as text, so the number 5080 cannot be compared with them
2. The value 5080 would select the postings of every account in the 5000 series
3. AccountID 93 is the only account that has postings in fiscal 2026
4. GLEntry stores the Account table’s surrogate key, and account 5080 has AccountID 93

3. The condition SourceDocumentType = 'workorderclose' returns no rows, although the ledger holds thousands of WorkOrderClose postings. What is the most likely reason?

1. = compares text exactly, so the capitals must match the stored value
2. Text values must be written in double quotes, not in single quotes as here
3. WHERE cannot filter on a column that is not also listed in the SELECT clause
4. SourceDocumentType is declared as INTEGER, so no text value can match it

4. PostingDate is stored as text in the form year-month-day. Which condition selects every posting dated in fiscal 2026?

1. PostingDate = 2026
2. PostingDate BETWEEN '01/01/2026' AND '12/31/2026'
3. PostingDate BETWEEN '2026-01-01' AND '2026-12-31'
4. PostingDate LIKE '12/31/2026'

5. An analyst wants the postings to AccountID 93 in fiscal 2025 and 2026 and writes WHERE AccountID = 93 AND FiscalYear = 2025 OR FiscalYear = 2026. What does the query return?

1. Only the postings to AccountID 93 in fiscal 2025 and 2026, as intended
2. No rows, because a single posting cannot belong to two fiscal years
3. The postings to AccountID 93 in 2025, plus every posting made in 2026
4. An error, because AND and OR cannot be combined in one WHERE clause

6. The query SELECT * FROM WorkOrder WHERE ClosedDate = NULL; returns no rows, although hundreds of work orders are still open. Why?

1. A comparison with NULL is never true; open orders need IS NULL
2. NULL must be written in quotes, as 'NULL', to be compared with a date
3. Open work orders store the date 1900-01-01 in ClosedDate, not a missing value
4. WHERE cannot test whether a date column holds a missing value

7. Before filtering the ledger on a source document type, an analyst runs SELECT DISTINCT SourceDocumentType FROM GLEntry;. What is the main benefit of this step?

1. It counts how many ledger rows carry each source document type
2. It removes the duplicate postings from the GLEntry table for good
3. It sorts the whole ledger by source document type before the filter
4. It shows which values occur, so a filter uses a value that exists

8. A query ends with ORDER BY TotalVarianceAmount DESC LIMIT 10;. What does it return?

1. The ten smallest variances, from the smallest up
2. The ten largest variances, from the largest down
3. Ten variances chosen at random from the table
4. Every variance above the tenth percentile of the table

9. An analyst selects “the first 25 journal entries” for review with SELECT * FROM JournalEntry LIMIT 25;. What is the problem with this selection?

1. Without ORDER BY, the rows are whichever the database reaches first
2. LIMIT can only be used together with a WHERE clause that filters the rows
3. The query returns the first 25 columns of the table rather than 25 rows
4. The query deletes all but 25 rows from the JournalEntry table

10. Why do the Part III tutorials work in CharlesRiver_Work.sqlite rather than the downloaded CharlesRiver.sqlite?

1. DB Browser opens a downloaded file read-only, so no query can run on it
2. The copy is smaller, because DB Browser leaves out the indexes when copying
3. Write Changes saves every query result into the file it was run on
4. DB Browser can change a database, so the download stays safe

11. An analyst runs SELECT AccountNumber AccountName FROM Account;, leaving out a comma. What happens?

1. The query fails with a syntax error that points to the missing comma
2. It returns one column, headed AccountName, holding the account numbers
3. It returns the AccountNumber and AccountName columns, exactly as intended
4. It returns only the account names, because the second column replaces the first

12. A query written as SELECT * FROM Account ORDER BY AccountNumber WHERE AccountType = 'Asset'; stops with the message near “WHERE”: syntax error. What is the cause?

1. AccountType is not a column of the Account table, so it cannot be tested
2. Text values cannot be compared in a query that also sorts its rows
3. The clauses are out of order: WHERE must come before ORDER BY
4. The semicolon at the end of the statement is not allowed in SQLite

13. Which condition finds every voucher number that begins with JE-2026-?

1. VoucherNumber LIKE 'JE-2026-%'
2. VoucherNumber = 'JE-2026-%'
3. VoucherNumber LIKE 'JE-2026-_'
4. VoucherNumber IN ('JE-2026-')

14. A SQL script for an audit test begins with a comment that records its purpose, the database file, the preparer and date, and the checks its results should pass. What is the main purpose of this header?

1. It makes the queries in the script run faster when the file is opened again
2. It prevents the script from making any change to the database
3. SQLite requires such a comment before a script can be saved to a file
4. A reviewer can understand, rerun, and check the work without help

15. What does SELECT 7 / 2, 7 / 2.0, '2026' = 2026; return in SQLite?

1. 3.5, 3.5, and 1
2. 3, 3.5, and 0
3. 3, 3, and 1
4. An error, because the statement has no FROM clause

9.19 Applied Exercises

Work on these exercises in CharlesRiver_Work.sqlite, the working copy you made in Tutorial 9.1. Save the queries of each exercise in a script of their own, named after the exercise (such as Ex 9.1.sql), with a header comment like the one in Tutorial 9.3 and a comment above each query. The queries in this chapter only read data, so the exercises cannot change the database, but never type into the Browse Data tab while you work.

9.19.1 Financial Accounting Exercises

Exercise 9.1: Reviewing the Chart of Accounts

Table group: Accounting Core

Scenario. Before the fiscal 2026 financial statements are finalized, the controller asks you to review the chart of accounts in the database. She wants to know how it is organized, which accounts have a normal balance opposite to their type, and which accounts are no longer in use.

Requirements.

1. List every account with its AccountID, AccountNumber, AccountName, AccountType, AccountSubType, ParentAccountID, NormalBalance, and IsActive, sorted by AccountNumber.

2. Use DISTINCT to list the account types and, in a second query, the combinations of AccountType and AccountSubType. Describe how the subtypes divide each type.

3. List the header accounts, whose AccountSubType is Header. Identify the ones that have no ParentAccountID and the ones that do, and explain what the difference means for the structure of the chart.

4. List the accounts whose NormalBalance is Credit although their type is Asset or Expense, or Debit although their type is Liability, Equity, or Revenue. Use parentheses to group the conditions. Explain what kind of account each one is.

5. List the inactive accounts (IsActive is 0). Two of them are named for manufacturing salaries and factory overhead. Use LIKE to find the active accounts whose names mention manufacturing, and suggest which of them records those costs instead.

6. Write a short memo (one half page) to the controller summarizing the structure of the chart and anything that should be reviewed before year-end.

Deliverable. A script with the queries of requirements (1) to (5) and a half-page memo.

Exercise 9.2: The Purchase Price Variance in the Ledger

Table group: Accounting Core, Procure-to-Pay

Scenario. Cost of goods sold includes a second variance account, 5060 Purchase Price Variance. The controller asks for a first look at its postings in fiscal 2026, to learn what the account records and how it compares with the manufacturing variance.

Requirements.

1. Find the AccountID of account 5060.

2. List the distinct source document types of the account’s postings in fiscal 2026, and identify the posting that must be excluded from an analysis of the year’s purchase variances. Explain why.

3. Use DISTINCT to list the descriptions of the remaining postings. Explain the two kinds of cost the account records.

4. List the postings of fiscal 2026 whose description records an unfavorable purchase variance, sorted by Debit from the largest down, and show the ten largest with their posting date and voucher number.

5. Using the message pane, count the postings of each description. Explain what a credit to this account means.

6. Write two or three sentences comparing how purchase price variance and manufacturing variance arise, based on the source documents that post to each account.

Deliverable. A script with the queries and a short comparison.

9.19.2 Managerial Accounting Exercises

Exercise 9.3: Standard Cost Components of Manufactured Items

Table group: Master Data, Manufacturing

Scenario. The manufacturing variance compares actual cost with standard cost, so the controller wants to understand how the standards are built before the variance is analyzed. She asks you for a review of the standard cost components of the items Charles River makes.

Requirements.

1. List the items whose SupplyMode is Manufactured, with ItemCode, ItemName, ItemGroup, StandardCost, StandardLaborHoursPerUnit, StandardDirectLaborCost, StandardVariableOverheadCost, StandardFixedOverheadCost, and StandardConversionCost.

2. Add a calculated column for the standard material cost, the part of StandardCost that is not conversion cost, and round it to two decimals.

3. Write a query that returns any manufactured item whose direct labor, variable overhead, and fixed overhead do not add up to its standard conversion cost, after rounding to two decimals. Report the result.

4. Add a calculated column for the standard labor rate per hour, StandardDirectLaborCost divided by StandardLaborHoursPerUnit, rounded to two decimals, and sort the items by it. Describe the range of rates.

5. List the ten items with the highest standard conversion cost, and state which item groups they belong to.

Deliverable. A script with the queries and a short description of how the standards are built.

Exercise 9.4: Open Work Orders at Year-End

Table group: Manufacturing

Scenario. A work order’s variance is recorded only when it closes, so the variance of a work order still open at the end of fiscal 2026, if it has one, is not in the ledger. The production manager and the controller want to know how many work orders are open, in what state, and how late they are.

Requirements.

1. List the work orders with no ClosedDate, with their WorkOrderNumber, Status, ReleasedDate, DueDate, CompletedDate, and PlannedQuantity.

2. Add two calculated columns: the year in each WorkOrderNumber, extracted with SUBSTR and converted with CAST, and the CompletedDate with the text not completed in place of a missing value, using COALESCE. Describe what the two columns show about the open work orders.

3. Use DISTINCT to list the statuses of the open work orders. For each status, count the work orders with a separate query and the message pane.

4. List the open work orders whose DueDate is on or before 2026-12-31, sorted by DueDate. Identify the oldest.

5. Among them, list the work orders whose Status is still Released, a status meant for work orders not yet started. Describe what you find.

6. Write a short memo (one half page) to the production manager on the open work orders, and explain why any variance on them is not in the fiscal 2026 results.

Deliverable. A script with the queries and a half-page memo.

9.19.3 Auditing Exercises

Exercise 9.5: Shipments That Need Follow-Up

Table group: Order-to-Cash

Scenario. In Tutorial 2.1 you found shipments with no tracking number and shipments still marked In Transit after their delivery date, working with filters in Excel. The audit senior asks you to repeat both tests with SQL, so that they can be saved and rerun at the next year-end.

Requirements.

1. Use DISTINCT to list the values of Status and of ShippedBy in the Shipment table.

2. List the shipments with no TrackingNumber, with their ShipmentNumber, ShipmentDate, ShippedBy, and Status, sorted by ShipmentDate. Report the number of rows and the range of dates.

3. List the shipments whose Status is In Transit and whose DeliveryDate is before 2026-12-31, the last shipment date in the data, sorted by DeliveryDate. Report the number of rows and the oldest DeliveryDate.

4. Compare your results with your findings from Tutorial 2.1, and explain why the SQL version of the tests is easier to rerun.

5. For each test, name the data quality dimension it addresses and suggest one question to ask the shipping team.

Deliverable. A script with the queries and a short comparison with Tutorial 2.1.

Exercise 9.6: Missing Values in Time and Labor Records

Table group: Manufacturing, Payroll and Time

Scenario. The manufacturing variance depends on the labor time recorded against work orders. Before the audit team relies on those records, the audit senior asks you to test them for missing values that could signal incomplete or unreliable time data.

Requirements.

1. List the LaborTimeEntry rows whose LaborType is Direct Manufacturing and whose WorkOrderOperationID is missing. Show the EmployeeID, WorkOrderID, work date, and hours.

2. Use DISTINCT to list the values of ClockStatus in the TimeClockEntry table, and list the time clock entries with no ClockOutTime, showing COALESCE(ClockOutTime, 'missing') in place of the empty value.

3. List the employees with a TerminationDate, with their EmploymentStatus and IsActive flag, and check whether the three columns agree.

4. For each finding, explain why it matters for the reliability of the labor or payroll records, and whether these tests show an effect on manufacturing cost or only a possible one. Say what you would ask the payroll or production team to verify.

Deliverable. A script with the queries and a short list of findings with follow-up questions.

9.20 Further Reading

Beaulieu, A. (2020). Learning SQL: Generate, manipulate, and retrieve data (3rd ed.). O’Reilly Media. An introduction to SQL that starts with single-table queries and builds to joins, grouping, and analytic functions. Its examples use MySQL, so it is also a practical way to see where another dialect differs from the SQLite you use in this book.

Borthick, A. F., Schneider, G. P., & Viscelli, T. R. (2017). Analyzing data for decision making: Integrating spreadsheet modeling and database querying. Issues in Accounting Education, 32(1), 59–66. https://doi.org/10.2308/iace-51385 In this accounting case, students begin an analysis in a spreadsheet and find it unwieldy as the analysis draws on more attributes from more sheets. They then import the data into a database, join the tables, and complete the analysis with queries. The case mirrors the move from Part II to Part III of this book.

Chamberlin, D. D. (2012). Early history of SQL. IEEE Annals of the History of Computing, 34(4), 78–82. https://doi.org/10.1109/MAHC.2012.61 A short account by one of the language’s designers of how SQL was created at IBM in the 1970s to query relational databases, and of the design choices that shaped it. It explains why SQL reads more like a description of the result than a program.

Lee, L. S., & Casterella, G. (2023). A mental model approach to teaching database querying skills with SQL and Alteryx. Journal of Accounting Education, 64, Article 100858. https://doi.org/10.1016/j.jaccedu.2023.100858 The authors position SQL as the mental model for the data preparation features of a newer analytics tool, and they have students solve the same problems in both. The paired tasks illustrate how the SQL concepts of this Part can help you understand the data preparation steps of another tool.

Taipalus, T., & Seppänen, V. (2020). SQL education: A systematic mapping study and future research agenda. ACM Transactions on Computing Education, 20(3), Article 20. https://doi.org/10.1145/3398377 A review of three decades of research on teaching SQL, which identifies recurring topics such as the errors students make when writing queries and the design of the practice database. It is a useful starting point for anyone interested in how SQL is learned.

Taipalus, T., Siponen, M., & Vartiainen, T. (2018). Errors and complications in SQL query formulation. ACM Transactions on Computing Education, 18(3), Article 15. https://doi.org/10.1145/3231712 Based on more than 33,000 queries written by students, this study classifies the errors people make in SQL, including logical errors that recur in similar ways from student to student. Reading its categories is a good way to learn which mistakes to look for in your own queries.

9.21 References

Datar, S. M., & Rajan, M. V. (2021). Horngren’s cost accounting: A managerial emphasis (17th ed.). Pearson. https://www.pearson.com/en-us/pearsonplus/p/9780136713845

Borthick, A. F., Schneider, G. P., & Viscelli, T. R. (2017). Analyzing data for decision making: Integrating spreadsheet modeling and database querying. Issues in Accounting Education, 32(1), 59–66. https://doi.org/10.2308/iace-51385

Chamberlin, D. D. (2012). Early history of SQL. IEEE Annals of the History of Computing, 34(4), 78–82. https://doi.org/10.1109/MAHC.2012.61

Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377–387. https://doi.org/10.1145/362384.362685

SQLite. (n.d.-a). Built-in scalar SQL functions. https://www.sqlite.org/lang_corefunc.html

SQLite. (n.d.-b). Datatypes in SQLite. https://www.sqlite.org/datatype3.html

SQLite. (n.d.-c). Quirks, caveats, and gotchas in SQLite. https://www.sqlite.org/quirks.html

SQLite. (n.d.-d). SQL language expressions. https://www.sqlite.org/lang_expr.html

Taipalus, T., Siponen, M., & Vartiainen, T. (2018). Errors and complications in SQL query formulation. ACM Transactions on Computing Education, 18(3), Article 15. https://doi.org/10.1145/3231712