Thank You!

You are attempting to access subscriber-restricted content.

Are You Ready to Experience Everything Internal Auditor (Ia) Has to Offer?

​Stop Clicking, Start Coding

SQL queries can enable internal auditors to uncover greater insights from organizational data.

Comments Views

​As data grows in volume and complexity, the effective use of it is critical for making better, faster, and more informed decisions. Organizations increasingly are seeking internal auditors who can analyze data and generate insights that bring new value to the business.

While internal auditors typically perform data analysis using specialized audit software packages or a general spreadsheet application, there is a growing need for auditors to develop technical skills beyond those tools. For example, Fortune 500 firms such as Google and Verizon have made proficiency in structured query language (SQL) part of their job requirements for hiring internal auditors.

SQL is a special-purpose programming language designed for managing data held in database management systems that support widely used enterprise resource planning systems. Designing SQL procedures for transforming data into useful information requires a good understanding of data structure and the logic of how a system works. Such understanding is particularly important for internal auditors when they work with large volumes of data in today’s complex business environment. From the learning perspective, logical thinking and reasoning inherent in the SQL coding process helps internal auditors develop the critical thinking and problem-solving skills desired by the profession.

Moreover, SQL-based analysis has gained increasing importance with the advent of big data. SQL tools enable fast access to relational databases that store vast amounts of data, offer flexibility in developing ad hoc queries on an as-needed basis, and can be tailored to the specific needs of auditing. Furthermore, because SQL is an international standard, internal auditors are not constrained to using a specific software tool.

Asking Questions of Data

Internal auditors can write and refine SQL codes in a relational database to arrive at incrementally better solutions until the desired outcome is achieved. Consider the example of an Employees table that contains data such as employee ID, first name, last name, birth date, and hire date. Auditors can ask many interesting questions about this data, such as whether the company has complied with all employment regulations. In the context of The Committee of Sponsoring Organizations of the Treadway Commission’s Enterprise Risk Management–Integrated Framework, this inquiry addresses the company’s conformance with its compliance objectives.
To check compliance with child labor laws, internal auditors can query the data to determine whether any employees were underage at the time of their hiring. For example, the minimum age for employment in the U.S. is 14; and there are specific requirements for the age group between 14 and 18. Auditors can begin answering this question using this code:

SELECT EmployeeID, FirstName, LastName,
FROM Employees;

The SELECT statement in the code retrieves all of the values in the EmployeeID, FirstName, and LastName columns, and calculates the age of the employee at the time of hiring as the difference between the HireDate and BirthDate divided by 365 days. The FROM clause specifies the tables from which the data are selected.
The query returns a total of 11 employees. Of these employees, the results identify four questionable employees: two are under 18 and the other two have no age information. At first glance, the design of the query seems to answer the question, but this solution only works well for small organizations. Imagine a large company that has thousands of employees. In such a situation, auditors would have to sift through a long list of employees to identify those with age problems. An additional issue is that the system-generated title of the column for the age data, “Expr1003,” is not descriptive, and the data, itself, has 10 decimal places. To address these drawbacks, internal auditors can improve the SQL statement:

EmployeeID, FirstName, LastName
ROUND((HireDate-BirthDate)/365, 1)
AS AgeAtHire
FROM Employees
WHERE (HireDate-BirthDate)/365 < 18;

This revision aims to filter out unnecessary data and improve the readability of the report. Adding the WHERE clause restricts the result to employees under age 18. The ROUND function rounds the age number off to one decimal place. The heading of the column containing the age data is also renamed to AgeAtHire. The query result now contains only two suspicious employees who were under 18 at the time of their hiring.
However, there is something missing from the report. The first query uncovered two additional suspicious employees without any age information. Further examination of the Employees table reveals that birth and hiring dates are not available for these two employees. While only a conjecture, these two individuals may have been “ghost employees” as the result of payroll frauds. Internal auditors should include these two suspicious employees in the report, as well.
To find this information, internal auditors can amend the SQL query:

EmployeeID, FirstName, LastName
ROUND((HireDate-BirthDate)/365, 1)
AS AgeAtHire
FROM Employees
WHERE (HireDate-BirthDate)/365 < 18
OR (HireDate-BirthDate) IS NULL;

In this solution, auditors add another condition “(HireDate-BirthDate ) IS NULL” in the WHERE clause with the OR operator. The OR operator performs a logical comparison and specifies that an employee should be included in the report if either of the two conditions is met: age at the time of hiring is less than 18, or age data for this employee is NULL (i.e., left blank). Now the report shows all four suspicious employees.

This is not the end of the data analysis, however. Based on this result, internal auditors would need to investigate further to determine why the age information is missing for two employees and how the two underage employees were hired in the first place.

Powerful Analytical Tools

The underage employee example demonstrates how SQL can be a useful database tool for solving audit-related problems. However, it has only scratched the surface of the capabilities of SQL-based data analysis. Indeed, SQL and other audit software can form a powerful set of analytical tools for internal auditors, particularly in the context of ever-growing volumes of data available for business use.

Ken Guo
Wei Jiang
Internal Auditor is pleased to provide you an opportunity to share your thoughts about the articles posted on this site. Some comments may be reprinted elsewhere, online or offline. We encourage lively, open discussion and only ask that you refrain from personal comments and remarks that are off topic. Internal Auditor reserves the right to remove comments.

About the Authors



Ken GuoKen Guo<p>Ken Guo, PHD, CPA/CMA (Canada), is an assistant professor at California State University, Fullerton.​</p>



Wei JiangWei Jiang<p>​Wei Jiang, PHD, is a professor of accounting at California State University, Fullerton.</p>


Comment on this article

comments powered by Disqus
  • TeamMate_Aug2017_Prem 1
  • SCCE_Aug2017_Prem 2
  • IIA CRMA_Aug2017_Prem 3