It is hard to imagine a time when SQL was unknown and not the lingua franca it is today for accessing databases. That said, there are still folks out there who don’t know what SQL is… so for them, here is an introductory place to start…
SQL is an acronym for Structured Query Language. It is often procounced a sequel, but also spelled out as letters, like ess-cue-ell. SQL is a powerful tool for accessing and manipulating data. It is the de facto standard query language for relational database management systems, used by all of the leading RDBMS products including Oracle, SQL Server (natch), Db2, MySQL, Postgres, SAP Adaptive Server, and more.
Intereestingly enough, NoSQL database systems are increasingly being adapted to allow SQL access, too! So SQL is ubiquitous and it makes sense for anybody with an interest in data management to learn how to code SQL.
SQL is a high-level language that provides a greater degree of abstraction than do procedural languages. Most programming languages require that the programmer navigate data structures. This means that program logic needs to be coded to proceed record-by-record through data elements in an order determined by the application programmer or systems analyst. This information is encoded in programs and is difficult to change after it has been programmed.
SQL, on the other hand, is fashioned so that the programmer can specify what data is needed, instead of how to retrieve it. SQL is coded without embedded data-navigational instructions. The DBMS analyzes the SQL and formulates data-navigational instructions “behind the scenes.” These data-navigational instructions are called access paths.
By having the DBMS determine the optimal access path to the data, a heavy burden is removed from the programmer. In addition, the database can have a better understanding of the state of the data it stores, and thereby can produce a more efficient and dynamic access path to the data. The result is that SQL, used properly, can provide for quicker application development.
Another feature of SQL is that it is not merely a query language. The same language used to query data is used also to define data structures, control access to the data, and insert, modify, and delete occurrences of the data. This consolidation of functions into a single language eases communication between different types of users. DBAs, systems programmers, application programmers, systems analysts, and end users all speak a common language: SQL. When all the participants in a project are speaking the same language, a synergy is created that can reduce overall system-development time.
Arguably, though, the single most important feature of SQL that has solidified its success is its capability to retrieve data easily using English-like syntax. It is much easier to understand the following than it is to understand pages and pages of program source code.
SELECT LASTNAME
FROM EMP
WHERE EMPNO = '000010';
Think about it; when accessing data from a file the programmer would have to code instructions to open the file, start a loop, read a record, check to see if the EMPNO field equals the proper value, check for end of file, go back to the beginning of the loop, and so on.
SQL is, by nature, quite flexible. It uses a free-form structure that gives the user the ability to develop SQL statements in a way best suited to the given user. Each SQL request is parsed by the DBMS before execution to check for proper syntax and to optimize the request. Therefore, SQL statements do not need to start in any given column and can be strung together on one line or broken apart on several lines. For example, the following SQL statement is equivalent to the previously listed SQL statement:
SELECT LASTNAME FROM EMP WHERE EMPNO = '000010';
Another flexible feature of SQL is that a single request can be formulated in a number of different and functionally equivalent ways. One example of this SQL capability is that it can join tables or nest queries. A nested query always can be converted to an equivalent join. Other examples of this flexibility can be seen in the vast array of functions and predicates. Examples of features with equivalent functionality are:
- BETWEEN versus <= / >=
- IN versus a series of predicates tied together with AND
- INNER JOIN versus tables strung together in the FROM clause separated by commas
- OUTER JOIN versus a simple SELECT, with a UNION, and a correlated subselect
- CASE expressions versus complex UNION ALL statements
This flexibility exhibited by SQL is not always desirable as different but equivalent SQL formulations can result in extremely differing performance. The ramifications of this flexibility are discussed later in this paper with guidelines for developing efficient SQL.
As mentioned, SQL specifies what data to retrieve or manipulate, but does not specify how you accomplish these tasks. This keeps SQL intrinsically simple. If you can remember the set-at-a-time orientation of a relational database, you will begin to grasp the essence and nature of SQL. A single SQL statement can act upon multiple rows. The capability to act on a set of data coupled with the lack of need for establishing how to retrieve and manipulate data defines SQL as a non-procedural language.
Because SQL is a non-procedural language a single statement can take the place of a series of procedures. Again, this is possible because SQL uses set-level processing and DB2 optimizes the query to determine the data-navigation logic. Sometimes one or two SQL statements can accomplish tasks that otherwise would require entire procedural programs to do.
Summary
Of course, this brief introduction does not constitute an education in SQL and it will not make you a SQL programmer. For that, you will need education and practice. A good place to start is with a SQL book or two. I can recommend these:
After reading through some good books, practice writing some SQL and keep learning… move on to more advanced texts and if you can, attend a class on the SQL. Because learning SQL makes sense in this day and age of analytics!
