Understanding DB2: Learning Visually with Examples (2nd Edition)

9.8. Summary

This chapter shows the power of SQL statements to manipulate the database data, including the SELECT, INSERT, UPDATE, and DELETE statements. In the SQL statements, you can use the DB2 special registers to obtain information about the environment such as the current date and current user connected to the database.

Besides the basic SQL statements to manipulate data, you can also use column and scalar functions, cast expressions, WHERE clauses, ORDER BY clauses, GROUP BY clauses, FETCH FIRST n ROWS ONLY options, and many other features to customize the result that you want to obtain.

It is very common that data requested is from more than one table. Inner and outer joins are used to combine data from two or more tables in a SELECT statement.

SQL enhancement returns selective data from data being deleted, updated, or inserted in a single SQL statement because it provides a simplified and optimized way to write SQLs. For example, rather than issuing two separate statements to query data you are going to delete and then perform the delete, DB2 lets you combine the two operations in a single statement. It definitely simplifies the application code as well as improves performance in most cases.

The MERGE statement is one of the SQL enhancements introduced in DB2 Version 8. It combines an INSERT with an UPDATE or DELETE statement.