SQL Series...3

What SQL Is and Isn’t

The first thing to understand about SQL is that SQL isn’t a procedural lan-guage, as are FORTRAN, BASIC, C, COBOL, Pascal, and Java. To solve a prob-lem in one of those procedural languages, you write a procedure that performs one specific operation after another until the task is complete. The procedure may be a linear sequence or may loop back on itself, but in either case, the programmer specifies the order of execution.

SQL, on the other hand, is nonprocedural. To solve a problem using SQL, simply tell SQL what you want (as if you were talking to Aladdin’s genie) instead of telling the system how to get you what you want. The database management system (DBMS) decides the best way to get you what you request.

All right. I just told you that SQL is not a procedural language. This is essen-tially true. However, millions of programmers out there (and you are proba-bly one of them) are accustomed to solving problems in a procedural manner. So, in recent years, there has been a lot of pressure to add some procedural functionality to SQL. Thus the latest version of the SQL specification, SQL:2003, incorporates procedural language facilities, such as BEGIN blocks, IF statements, functions, and procedures. These facilities have been added so you can store programs at the server, where multiple clients can use these programs repeatedly.

To illustrate what I mean by “tell the system what you want,” suppose that you have an EMPLOYEE table and you want to retrieve from that table the rows that correspond to all your senior people. You want to define a senior person as anyone older than age 40 or anyone earning more than $60,000 per year. You can make the desired retrieval by using the following query:

SELECT * FROM EMPLOYEE WHERE Age>40 OR Salary>60000 ;

This statement retrieves all rows from the EMPLOYEE table where either the value in the Age column is greater than 40 or the value in the Salary column is greater than 60,000. In SQL, you don’t need to specify how the information is retrieved. The database engine examines the database and decides for itself how to fulfill your request. You need only to specify what data you want to retrieve.

A quer y is a question you ask the database. If any of the data in the database satisfies the conditions of your query, SQL retrieves that data.

Current SQL implementations lack many of the basic programming constructs fundamental to most other languages. Real-world applications usually require at least some of these programming constructs, which is why SQL is actually a data sublanguage. Even with the extensions that were added in SQL:1999 and the additional extensions added in SQL:2003, you still need to use SQL in combination with a procedural language, such as C, to create a complete application.

You can extract information from a database in one of two ways:

Make an ad hoc query from a computer console by just typing an SQL statement and reading the results from the screen. Console is the tradi-tional term for the computer hardware that does the job of the keyboard and screen used in current PC-based systems. Queries from the console are appropriate when you want a quick answer to a specific question. To meet an immediate need, you may require information that you never needed before from a database. You’re likely never to need that informa-tion again either, but you need it now. Enter the appropriate SQL query statement from the keyboard, and in due time, the result appears on your screen.

Execute a program that collects information from the database and then reports on the information, either on-screen or in a printed report. You can use SQL either way. Incorporating an SQL query directly into a program is a good way to run a complex query that you’re likely to run again in the future. That way, you need to formulate the query only once. 

SQL originated in one of IBM’s research laboratories, as did relational database theory. In the early 1970s, as IBM researchers performed early development on relational DBMS (or RDBMS) systems, they created a data sublanguage to operate on these systems. They named the prerelease version of this sublan-guage SEQUEL (Structured English QUEry Language). However, when it came time to formally release their query language as a product, they wanted to make sure that people understood that the released product was different from and superior to the prerelease DBMS. Therefore, they decided to give the released product a name that was different from SEQUEL but still recog-nizable as a member of the same family. So they named it SQL.

IBM’s work with relational databases and SQL was well known in the industry even before IBM introduced its SQL/DS RDBMS in 1981. By that time, Relational Software, Inc. (now Oracle Corporation) had already released its first RDBMS. These early products immediately set the standard for a new class of database management systems. They incorporated SQL, which became the de facto standard for data sublanguages. Vendors of other rela-tional database management systems came out with their own versions of SQL. These other implementations typically contained all the core functional-ity of the IBM products but were extended in ways that took advantage of the particular strengths of the underlying RDBMS. As a result, although nearly all vendors used some form of SQL, compatibility across platforms was poor.

An implementation is a particular RDBMS running on a specific hardware platform.

Soon a movement began, to create a universally recognized SQL standard to which everyone could adhere. In 1986, ANSI released a formal standard it named SQL-86. ANSI updated that standard in 1989 to SQL-89 and again in 1992 to SQL-92. As DBMS vendors proceed through new releases of their products, they try to bring their implementations ever closer to this stan-dard. This effort has brought true SQL portability much closer to reality.

The most recent version of the SQL standard is SQL:2003 (ISO/IEC 9075-X:2003). I describe SQL as SQL:2003 defines the language. Any specific SQL implementation differs from the standard to a certain extent. Because the full SQL:2003 standard is very comprehensive, currently available implementa-tions are unlikely to support it fully. However, DBMS vendors are working to support a core subset of the standard SQL language. The full ISO/IEC stan-dards are available for purchase at webstore.ansi.org.