| Thirty-one percent (31%) of the DB2 UDB V8.1 Family Fundamentals certification exam (Exam 700) is designed to test your knowledge of the various Structured Query Language (SQL) statements that are commonly used to create database objects and manipulate data. The questions that make up this portion of the exam are intended to evaluate the following: -
Your ability to identify the Data Control Language (DCL) statements available with DB2 UDB. -
Your ability to identify the Data Definition Language (DDL) statements available with DB2 UDB. -
Your ability to identify the Data Manipulation Language (DML) statements available with DB2 UDB. -
Your ability to perform insert, update, and delete operations against a database. -
Your ability to retrieve and format data using various forms of the SELECT statement. -
Your ability to create and invoke a stored procedure. -
Your knowledge of what transactions are, as well as how transactions are initiated and terminated . This chapter is designed to introduce you to the Structured Query Language (SQL) statements that you need to be familiar with in order to create data base objects, and access and manipulate data. In this chapter, you will also learn what transactions are and how to terminate a transaction in such a way that all operations performed within that transaction are either applied to the database and made permanent (committed) or backed out (rolled back). Terms you will learn: Structured Query Language (SQL) Embedded SQL Application Construct Statements Data Control Language (DCL) Statements Data Definition Language (DDL) Statements Data Manipulation Language (DML) Statements Transaction Management Statements The CONNECT Statement The GRANT Statement The REVOKE Statement The CREATE BUFFERPOOL Statement The CREATE TABLESPACE Statement System Managed Space (SMS) tablespace Database Managed Space (DMS) tablespace Prefetching The ALTER TABLESPACE Statement The CREATE TABLE Statement Data Types The ALTER TABLE Statement The CREATE INDEX Statement Clustered Index The CREATE VIEW Statement WITH CHECK OPTION The CREATE ALIAS Statement The CREATE SCHEMA Statement The CREATE TRIGGER Statement Trigger Subject Table Trigger Event Trigger Activation Time Set of Affected Rows Trigger Granularity Triggered Action Before Trigger After Trigger The DROP Statement The INSERT Statement Subselect The UPDATE Statement Cursor The DELETE Statement The SELECT Statement Query The DISTINCT Clause The FROM Clause The WHERE Clause Relational Predicates The BETWEEN Predicate The LIKE Predicate Wild Card Characters The IN Predicate Subquery The EXISTS Predicate The NULL Predicate The GROUP BY Clause GROUP BY ROLLUP GROUP BY CUBE The HAVING Clause The ORDER BY Clause The FETCH FIRST Clause Cartesian Product Inner Join Left Outer Join Right Outer Join Full Outer Join Set Operator The UNION Set Operator The UNION ALL Set Operator The EXCEPT Set Operator The EXCEPT ALL Set Operator The INTERSECT Set Operator The INTERSECT ALL Set Operator SQL Functions The DECLARE CURSOR Statement The OPEN Statement The FETCH Statement The CLOSE Statement Transaction The COMMIT Statement The ROLLBACK Statement Stored Procedure External Procedure SQL Procedure The CREATE PROCEDURE statement The CALL Statement Techniques you will master: Recognizing the various Data Control Language (DCL) statements available, and understanding how each is used. Recognizing the various Data Definition Language (DDL) statements available, and understanding how each is used. Recognizing the various Data Manipulation Language (DML) statements available, and understanding how each is used. Understanding how to add data to a table using the INSERT SQL statement. Understanding how to modify data stored in a table using the UPDATE SQL statement. Understanding how to remove data from a table using the DELETE SQL statement. Knowing how to construct simple and complex queries using the SELECT SQL statement and its clauses. Understanding how transactions are initiated and terminated. Understanding how stored procedures are created and invoked.  | 