Chapter 8 of the text book gave a high level overview of relational database data structures and Entity Relationship Diagrams (ERD). The book only briefly discussed several Structured Query Language (SQL) commands, and what they did. When auditing relational databases, a basic understanding of SQL could make it much easier to pull appropriate data from a system in a format that makes it easier to import into a CAATTs Generalized Audit Software. This knowledge could save the auditor time and effort.
In a relational database, data is stored in tables. The rows of these tables are discrete collections of data, or records. The columns of these tables are attributes, or fields that describe the records.
SQL is the language that gives the user access to the data contained in a relational database’s tables. With the proper use of SQL commands, a user can build queries that either SELECT data from database tables, UPDATEs that data, INSERTs new data, or DELETEs it. (Systems 2005) This post will focus on the SELECT command, and will direct readers to a website where SQL commands can be practiced right from the user’s browser, without installing any software. (w3schools.com n.d.)
A basic knowledge of SQL can be very useful if you have to generate a report or export data from a relational database. The SQL SELECT statement has a number of elements, but we are going to focus on the most common:
- ORDER BY
The most basic SELECT query is the statement “SELECT * FROM [Employees];” where Employees is the name of a data table in the database, and the * character is shorthand for “select all fields in this table”. This statement will grab all the columns (field names) of a table, and all the rows of the table (records). (Systems 2005).
The user can be more selective. An example of this would be an Employees table, where the user wants to retrieve data from the EmployeeID, LastName, FirstName and BirthdDate columns, and return only employees who were born after January 1st, 1960, sorted by employee birthdate. We would use the following query:
SELECT EmployeeID, LastName, FirstName, BirthDate
WHERE BirthDate >= ’1960-01-01′
ORDER BY BirthDate;
The WHERE command limits the rows returned by the query, based on the criteria supplied in the WHERE command. In this case, only return records where the employee’s birthday is greater than or equal to January 1st, 1960.
The ORDER BY command sorts the rows returned based on the field name used with the ORDER BY command. In this case, we are sorting by Birthdate.
Readers of this post can try the bolded queries included in this post by going directly tohttp://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all, pasting the query into the “SQL Statement” text box, and clicking the “Run SQL” button.
The reader can try a number of other SQL commands, such as INSERT, UPDATE, or DELETE for herself by going to the website http://www.w3schools.com/sql/default.asp, selecting a command from the list on the left hand side, and clicking the “Try it yourself” button. (w3schools.com n.d.)
More and more companies are moving toward relational databases and enterprise resource planning (ERP) systems. Having a basic understanding of SQL can give auditors much finer control over retrieving and extracting the data to import into their CAATTs.
SQL SELECT Statement. n.d. http://www.w3schools.com/sql/sql_select.asp (accessed March 17, 2014).
Systems, Jackie Goldstein of Renaissance Computer. http://technet.microsoft.com/en-us/library/bb264565(v=sql.90).aspx.November 01, 2005. http://technet.microsoft.com/en-us/library/bb264565(v=sql.90).aspx (accessed March 17, 2014).