Structured Query Language (SQL)

Structured Query Language (SQL) is a standardized language used to work with database objects and the data they contain. Using SQL, you can define, alter, and delete database objects, as well as insert, update, delete, and retrieve data values stored in database tables. One of the strengths of SQL is that it can be used in a variety of ways: SQL statements can be executed interactively using tools such as the Command Editor and the Command Line Processor, they can be placed directly in UNIX shell scripts or Windows batch files, and they can be embedded in high-level programming language source code files. (Because SQL is nonprocedural by design, it is not an actual programming language; therefore, most embedded SQL applications are built by combining the decision and sequence control of a high-level programming language with the data storage, manipulation, and retrieval capabilities of SQL.)

Like most other languages, SQL has a defined syntax and a set of language elements. Most SQL statements can be categorized according to the function they have been designed to perform; SQL statements typically fall under one of the following categories:

• Embedded SQL Application Construct Statements are SQL statements used for the sole purpose of constructing embedded SQL applications.

• Data Control Language (DCL) Statements are SQL statements used to grant and revoke authorities and privileges.

• Data Definition Language (DDL) Statements are SQL statements used to create, alter, and delete database objects.

• Data Manipulation Language (DML) Statements are SQL statements used to store data in, manipulate data in, retrieve data from, and remove data from select database objects.

• Transaction Management Statements are SQL statements used to establish and terminate database connections and active transactions.

In Chapter 3, "Security," we examined the DCL statements available when we looked at the GRANT and REVOKE statements. In Chapter 4, "Working with Databases and Database Objects," we encountered several DDL statements as we explored the various database objects to be had and examined how they are created and destroyed. In this chapter, we'll start out by examining the DML statements that are available; then we'll turn our attention to transactions and to the SQL statements that are designed to support them. You do not have to be familiar with the Embedded SQL Application Construct Statements to pass the DB2 9 Fundamentals certification exam (Exam 730), so they are not covered.

Tip 

Although basic syntax is presented for most of the SQL statements covered in this chapter, the actual syntax supported may be much more complex. To view the complete syntax for a specific SQL statement or to obtain more information about a particular statement, refer to the IBM DB2 SQL Reference, Volume 1 and IBM DB2 SQL Reference, Volume 2 product documentation.