Structured Query Language (SQL) is a programming language which allows you to define, access, and modify data stored in relational databases. Developed in the 1970s by IBM, SQL has become an extremely popular industry standard. An SQL standard was defined by the American National Standards Institute (ANSI) in 1986 and was accepted by the International Standards Organization (ISO) a year later. The most recent ANSI/ISO standard was defined in 1992. Because SQL is an international standard, you can learn one set of commands and use it to interface to a wide variety of database management systems. As the AS/400 becomes a more open system, it could benefit you as a programmer to capitalize on technologies that work across multiple platforms. SQL is one such technology.

In V3R1, IBM has made some enhancements to SQL. These enhancements cover three major issues-compliance with SQL standards, performance, and pricing. Many of the new database functions in V3R1, such as referential integrity, bring SQL closer to the ANSI 1992 standard. Recognizing that SQL performance on the AS/400 has been an issue since the system was first introduced, IBM delivered SQL performance improvements of up to 20 times for some types of record retrieval. IBM has also made SQL more affordable. With the new V3R1 pricing, you can now purchase SQL for as little as $750 for a single user license.

SQL's exploding popularity over the past few years is an important trend in the computer industry. More than 100 database management products-including Oracle, Access, SQL Server, DB2, and DB2/400-support SQL. These and other products run on computer systems from PCs to mainframes and everything in between. From its early beginnings as an IBM research project, SQL has risen to become one of today's most important database technologies. Anyone who works with databases, whether it's on a single system or in a client/server environment, needs to know SQL. In this article, I'll review some basic SQL concepts and show you some examples to help you understand some of the capabilities of this powerful language.

Overview

I'll start by introducing you to a few standard SQL concepts. First, SQL differs from most traditional languages in that it's a nonprocedural language. Procedural languages contain statements which can control the logic flow of an application. Nonprocedural languages don't contain these types of statements. Since SQL doesn't contain any "IF-THEN-ELSE" or "DO-WHILE" constructs, you can't use it to cause any branching or looping to occur. Next, there are two types of SQL: interactive and embedded. While these two types of SQL perform the same task, they're used quite differently.

Interactive SQL is probably the one that most SQL users are familiar with and the one that I'll concentrate on in this article. Interactive SQL allows you to enter an SQL statement into a prompt or text editor and then execute the statement immediately. Using this method, you can interact with the database by entering SQL statements one at a time and observing the results.

With embedded SQL, statements are coded into a program which is written in another language. It usually requires a precompile phase where the SQL statements are translated into the programming language used. Embedded SQL can be further broken down into two additional categories-static and dynamic.

Static SQL consists of an application program which contains hard-coded SQL statements. These statements are prepared before the program is executed (at compile-time) and generally can't be changed without recompiling the program.

With dynamic SQL, the statements are prepared during the execution of a program. This allows the program to dynamically generate SQL statements based on a user request. These types of applications can allow users to access the functionality of SQL while shielding them from SQL syntax.

Terms

SQL uses different terms for database components than you might be used to. In SQL, a file is called a table, a record is a row, and a field is a column. While the terms may be different, the concepts are the same.

An SQL table is an object that stores data. Tables are made up of rows and columns. A row is the horizontal portion of a table which consists of a sequence of values, one for each column. The rows in a table aren't necessarily in any particular order. A column is the vertical portion of a table. Each column has a name and a particular data type. A table always contains a fixed number of columns.

Two other terms you should be familiar with are views and indexes. A view is an alternate representation of data which is extracted from one or more tables. A view is the result of querying a database. Views may or may not contain all of the rows and columns in the tables they are extracted from.

An index is an object which logically orders the rows in a table by one or more columns. It provides quick access by allowing you to locate a particular row without having to read the entire table. An index can also be used to enforce uniqueness of rows within a table. An index is similar to an AS/400 logical file.

Syntax

The SQL language itself is not difficult to learn. It's made up of a small set of statements. Each of these statements has a very "English-like" syntax which can often be understood, even by someone who is unfamiliar with SQL. For example, the following is a typical example of an SQL statement.

 SELECT CUSTNAME FROM CUSTOMER WHERE ZIPCODE = 92008 

As you can tell just by looking at this statement, it selects customer names from a customer table where the zip code equals 92008. Like English statements, SQL statements can be broken down into various components. Learning to recognize these components can be helpful to an overall understanding of SQL, so I'll briefly describe them.

SQL statements always begin with a verb. Here are some of the more common SQL verbs.

o CREATE

o INSERT

o SELECT

o UPDATE

o DELETE

o DROP

Beyond the verbs, SQL statements can be further broken down into keywords, clauses, arguments, predicates, and objects.

In SQL, keywords are interpreted as instructions. Some keywords are optional while others are required. Each verb has its own set of keywords. For example, these are the keywords for a SELECT statement.

 o WHERE o FROM o GROUP BY o HAVING o ORDER BY o UNION 

These keywords are used to modify the actions of the SELECT verb.

Each keyword forms the basis for a clause. An example of a clause would be WHERE State = 'CA'. Another example would be ORDER BY ZipCode. Notice that in each of these examples the clause begins with a keyword.

The argument is the portion of the clause which follows the keyword. Together the keyword and the argument make up the clause. If the clause is WHERE Employee = 123 then WHERE is the keyword and Employee = 123 is the argument. This is an example of a simple argument which only contains one comparison, but an argument can also contain multiple comparisons. For example, the clause WHERE Employee = 123 AND Status = 'A' contains two comparisons. These types of comparisons are called predicates.

A predicate specifies a condition that is either true or false. Status = 'A' is an example of a relational predicate. Two or more predicates can be combined using Boolean operators such as AND, OR, or NOT. The phrase WHERE Status = 'A' OR Status = 'B' contains two predicates which are combined with the OR operator. Even though this clause contains two predicates, it still only has one argument since the argument is the portion of the clause which follows the keyword.

An object is a structure in a database which is given a name. Some common types of SQL objects which I've already discussed are tables and columns. When a table or a column name is used in an SQL statement, it's referred to as an object. For example, in the statement SELECT PayRate FROM PayRoll, the objects are the PayRate column and the PayRoll table.

Examples

Now that I've covered some of the terms and syntax used in SQL, I'll show you some examples. I'll start by using the CREATE TABLE statement to create a table. Then I'll use a few INSERT statements to put some data into the table. Next, I'll use the SELECT statement to extract the data out of the table. I'll follow that by using an UPDATE statement to modify the data in the table. Then I'll use the DELETE statement to delete data from the table. Finally, I'll use the DROP TABLE statement to delete the table.

If you want, you can try executing these statements as you read the remainder of this article. I ran these statements in both SQL/400 and Microsoft Access with similar results. However, you should be able to use just about any SQL product.

I'll begin by creating a small category table to store employee types for a payroll application. This table will have two columns-a category code and category description. The statement below creates this table. (On the AS/400, you may need to qualify the table name with a library.)

 CREATE TABLE CATEGORY (CTCODE CHAR (1), CTDESC CHAR (10)) 

In this example, the name of the table is CATEGORY, the name of the category code column is CTCODE, and the name of the category description column is CTDESC. CTCODE is defined with a length of one byte, and CTDESC is defined with a length of ten bytes.

Once the table is created, three SQL statements can be used to put data into it. The statements below place three rows of data into the category table.

 INSERT INTO CATEGORY VALUES('H', 'Hourly') INSERT INTO CATEGORY VALUES('S', 'Salary') INSERT INTO CATEGORY VALUES('E', 'Exempt') 

The VALUES keyword allows you to specify a list of values which are mapped to the columns in the table. For example, the first INSERT statement assigns a value of H to the CTCODE column and a value of 'Hourly' to the CTDESC column. The category table now contains three rows of data. You can extract these rows with the following SELECT statement:

 SELECT * FROM CATEGORY ORDER BY CTCODE 

In this example, an asterisk (*) is used following the SELECT verb. This specifies that all columns in the table are to appear in the view. (Alternatively, you can specify the individual column names that you want.) The ORDER BY CTCODE clause sorts the rows by category code. When you run this statement, you see these results.

 CTCODE CTDESC E Exempt H Hourly S Salary 

Suppose that, after viewing the data in the category table, you decide that the code for an exempt employee should be X instead of E. You can easily change the value.

 UPDATE CATEGORY SET CTCODE = 'X' WHERE CTCODE = 'E' 

The SET keyword allows you to specify the name of one or more columns you want to modify along with their values. In this case, I'm setting the category code to X. The WHERE keyword lets you specify the condition under which the row gets updated. Here I'm only updating rows in which the category code is equal to an E.

If you now rerun the select statement you ran earlier, you'll see a different view.

 CTCODE CTDESC H Hourly S Salary X Exempt 

The exempt row now has a code value of X and, because the view is sorted by category code, the exempt row now shows up last instead of first.

Now suppose you decide you want to delete the exempt row altogether. You can use the following statement to accomplish this:

 DELETE FROM CATEGORY WHERE CTCODE = 'X' 

The WHERE keyword lets you specify the condition under which the row gets deleted. Here I'm only deleting rows in which the category code equals an X. If you again rerun the select statement, you'll see that the exempt row is now gone.

 CTCODE CTDESC H Hourly S Salary 

You've just seen how the DELETE statement deletes one or more rows from a table. Now I'll show you how to use the DROP statement to delete the entire table. Enter this statement to delete the category table.

DROP TABLE CATEGORY

To Sum It Up

In this article, I've tried to show you some of the basics of SQL, but there's a lot more to learn about the subject beyond what I've covered here. I would encourage you to learn all you can about this important technology. When you think about it, it's easy to see why SQL has become so popular. Its simplicity, power, and cross-platform portability make it a language well worth pursuing.

Robin Klima is a senior technical editor for Midrange Computing.

Also Read

TechTip: Prevent Unintentional Deletion of Objects with RESTRICT ON DROP

SQL 101: DDL Recap—Improving Our Basic SQL Tables, Part 2

TechTip: Automate Complex Tasks Using SQL and User-Defined Table Functions