Auditor's Toolbox-SQL #1

Dr.K.Paul Jayakar & CA. Deephika

SQL stands for Structured Query Language. A query language is a variant of a programming language that's designed to retrieve specific information from databases, so SQL is the language of databases.

This fact is important because most companies store their data in databases. And while there are many types of databases (like MySQL, PostgreSQL, Microsoft SQL Server), most of them work with SQL, so once the SQL basics are mastered, it’s easy to work with any of them.

Even if the analysis must be performed with another language like Python, most of the time SQL will be used to retrieve the required data from the company's database.

As data grows in volume and complexity, it’s effective use 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.

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.

SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987.

SQL supports full-fledged coding of scripts to do the following:

• execute queries against a database
• retrieve data from a database
• insert records in a database
• update records in a database
• delete records from a database
• create new databases
• create new tables in a database
• create stored procedures in a database
• create views in a database
• set permissions on tables, procedures, and views

However, for extraction of data from databases all these features are not required.

The data is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows. It's easier to understand how this works by using an example. Assume a business wants to keep track of sales information. The data could be set up a spreadsheet in Excel with all the information as separate columns.

There are many commands in SQL using which the data can be manipulated. Some of the important SQL commands are:

SELECT - extracts data from a database

UPDATE - updates data in a database

DELETE - deletes data from a database

INSERT INTO - inserts new data into a database

CREATE DATABASE - creates a new database

ALTER DATABASE - modifies a database

CREATE TABLE - creates a new table

ALTER TABLE - modifies a table

DROP TABLE - deletes a table

CREATE INDEX - creates an index (search key)

DROP INDEX - deletes an index

 To illustrate the syntax, a query to display all the orders from the orders table issued by the salesman “Ashok” is given below:

Pictorial representation of the query process

As shown in the above diagram the inner query is executed first, returning the salesman_id for “Ashok” from the table Salesman, which is the value “5007”. Next the entire inner query is replaced with the value “5007” in the outer query and the rows with the salesman_id value “5007” are extracted and returned from the orders table.

SQL consists of simple and easy commands and syntax which can be mastered easily by anyone wanting to extract data from relational databases for analysis. Its application extends to stored procedures (SQL scripts), embedded queries in programming languages, to interactive commands at the Database prompt.

A knowledge of SQL is an invaluable tool in the hands of the internal auditor who needs to perform independent queries and audit tests on large amounts of data for data analytics and data visualization.