Structured Query Language

BAs need to understand the purpose of basic SQL statements

Structured query language (SQL) is a query language used to access and manipulate data in a relational database. It was originally designed using English language statements to allow business people to query their data for ad hoc reporting. The hope was that IT developers would be free to focus on developing new complex software while business users could develop their own reports. Unfortunately, the complexity of most databases has prevented all but the most sophisticated users from becoming independent of IT support. SQL is used for almost every database management system based on the relational model. There are hundreds of books/publications available from which you can learn SQL. There are two primary statements with which the BA should be familiar: SELECT and JOIN.

Like relational database concepts, the basic building blocks of SQL are relatively simple concepts. The statement or code that tells the computer to retrieve information out of one of the database tables is a SELECT statement:

SELECT CUSTOMER NAME FROM CUST_TABLE

This statement tells the database management system to look at the table called CUST_TABLE and retrieve the customer names. You will often hear developers talk about the SELECT statement because it is the most used statement in all of SQL. If you find incorrect data in a report, the developer might say something like: “My SELECT statement only pulled customers who are active.” The SELECT statement for a complex request may be several lines or even a page long. They are very complex to write and change. A simple change in a SELECT statement can result in a completely different result. See Figure 5.3 for an example of a complex SELECT statement.

Figure 5.3: Sample Complex SELECT Statement

The other statement with which you need to be familiar is JOIN. In SQL, the word JOIN refers to the task of bringing together information that is stored in two or more different tables. It is not critical that you understand the intricacies of the JOIN statement, but you should understand its basic purpose so that you can better communicate with developers.

Since most software applications manage large amounts of data, there are usually many tables. Almost any search of a database requires that at least two tables be joined together to find the requested information. See Figure 5.4 for a sample JOIN statement.

Figure 5.4: Sample JOIN Statement

For example, if a business area wanted to see a list of all of the orders placed by a particular customer, the SQL code would JOIN the two tables together to provide a list (printed or on screen) showing the customer name and all of the order numbers, with any other details needed. Like the SELECT statement, a JOIN can become very complex and may be at the root of an erroneous report. Be prepared to listen to developers talk about the complexity of their JOIN when trying to diagnose problems. To learn more about SQL, refer to a book like Sams Teach Yourself SQL in 10 Minutes (Forta, 2004).

In cases where you are asking a developer to make a change to a data access, be aware that if the developer did not write the original SQL, he or she is going to need time to become familiar with the existing code before attempting to change it. As stated earlier, a simple one-word coding change can result in a significant change in the result. This points out one reason why understanding the current or as is system is important. A developer may make a change that answers the new requirement but negatively impacts an old requirement that still exists. The BA must understand what pieces of the existing system need to remain, along with the new functionality that is required.