DB2 Universal Database V8.1 Certification Exam 700 Study Guide

The correct answer is D. The proper way to test for a missing value (or null) is by using the NULL predicate with a WHERE clause, and answer D shows the correct way to construct such a WHERE clause. Keep in mind that NULL, zero (0), and blank (" ") are not the same value. NULL is a special marker used to represent missing information, while zero and blank (empty string) are actual values that can be stored in a column to indicate a specific value (or lack thereof).

The correct answer is C. The UPDATE statement assigns a NULL value to column C1 and NULL values are displayed as a dash (-).

The correct answer is B. The CREATE TABLE LIKE statement is used to create a new table that has the same structure as an existing table. On the other hand, an alias (a synonym is another name for an alias) and a view can be used to access data stored in a table, using an alternate name .

The correct answer is D. A before trigger can be used to automatically generate a value for a column each time an insert operation is performed on a table.

The correct answer is C. If the database or the DB2 Database Manager are terminated (for example, because of a hard disk failure or a loss of power), the DB2 Database Manager will try to roll back all open transactions it finds in the transaction log file the next time the database is restarted (which will take place automatically the next time a user attempts to connect to the database if the database configuration parameter autorestart has been set accordingly ).

The correct answer is C. Certain properties of an existing table can be changed, additional columns and constraints can be added, existing constraints can be removed, and the length of varying-length character data type values allowed for a particular column can be increased by executing the ALTER TABLE statement. However, the ALTER TABLE statement cannot be used to change a column's name.

The correct answer is B. The FETCH FIRST clause is used to limit the number of rows that are returned to the result data set produced in response to a query. When used, the FETCH FIRST clause is followed by a positive integer value and the words ROWS ONLY. This tells the DB2 Database Manager that the user/application executing the query does not want to see more than n number of rows, regardless of how many rows might exist in the result data set that would be produced were the FETCH FIRST clause not specified.

The correct answers are B and C. The HAVING clause is used to apply further selection criteria to columns that are referenced in a GROUP BY clause. This clause behaves like the WHERE clause, except that it refers to data that has already been grouped by a GROUP BY clause (the HAVING clause is used to tell the DB2 Database Manager how to select the rows that are to be returned in a result data set from rows that have already been grouped). And like the WHERE clause, the HAVING clause is followed by a search condition that acts as a simple test that, when applied to a row of data, will evaluate to TRUE, FALSE, or Unknown.

The correct answer is A. The pattern of characters specified with the LIKE clause of a SELECT statement can consist of regular alphanumeric characters and/or special metacharacters that are interpreted as follows :

• The underscore character (_) is treated as a wild card character that stands for any single alphanumeric character.

• The percent character (%) is treated as a wild card character that stands for any sequence of alphanumeric characters.

The correct answer is C. Table TAB1 is created, two rows are inserted, and the first row is deleted because a COMMIT statement follows each of these operations. The next two rows that are inserted are removed because a ROLLBACK statement follows their insertion. And finally, the value for COL2 of all rows is set to null ”again because this operation is followed by a COMMIT statement.

The correct answer is D. When a full outer join operation is performed, rows that would have been returned by an inner join operation and all rows stored in both tables of the join operation that would have been eliminated by the inner join operation are returned in the result data set produced.

The correct answer is B. When the results of a query, or subselect, are used to provide values for one or more columns identified in the column name list provided for an UPDATE statement, the values retrieved from one base table or view are used to modify values stored in another. The number of values returned by the subselect must match the number of columns provided in the column name list specified, and only one record can be returned.

The correct answer is C. USERA has been given the privileges needed to add data to TABLE1, update data stored in TABLE1, and delete data from TABLE1.

The correct answer is B. The LCASE(' CharacterString ') or LOWER (' CharacterString' ) function returns a character string in which all of the characters in the character string value specified are converted to lowercase characters. By using this function to convert values before they are used in a WHERE clause, a table can be searched for a specific value, regardless of the case used to store it.

The correct answer is A. The ORDER BY clause is used to tell the DB2 Database Manager how to sort and order the rows that are to be returned in a result data set produced in response to a query. In this example, all rows containing the value "ADMIN" in the DEPT column would be listed first, followed by all rows containing the value "PRODUCTION", followed by all rows containing the value "SALES".

The correct answer is C. When a right outer join operation is performed, rows that would have been returned by an inner join operation, together with all rows stored in the rightmost table of the join operation (i.e., the table listed last in the OUTER JOIN clause) that would have been eliminated by the inner join operation are returned in the result data set produced. In this case, we want to see all records found in the POINTS table, along with any corresponding records found in the NAMES table, so a right outer join is the appropriate join operation to use.

The correct answer is D. In order to read and modify data stored in a table, a user must have SELECT (to read) and UPDATE (to modify) privileges on that table. The GRANT statement shown in answer D is the appropriate statement to use to give USERA these privileges.

The correct answer is B. The DELETE statement is used to remove specific records from a table (the DROP statement completely destroys the table object), and the correct syntax for the DELETE statement is DELETE FROM [ TableName ]

The correct answer is A. The CALL statement is used to invoke a stored procedure, so answer D is wrong; because a stored procedure cannot be invoked using its specific name, answer B is wrong; and because SALES is a character string value that is being passed to the procedure, it must be enclosed in single quotes. Therefore, A is the correct answer.

The correct answer is B. COUNT(empno) together with GROUP BY deptname displays the total number of employees in each department; SELECT deptname displays the corresponding department name for each department ID, and ORDER BY 2 DESC sorts the data by employee count (which is column 2) from greatest to least.

The correct answers are C and E. When a unique index is created for a column, every value found in that column must be unique, and one of the column's unique values can be the null value.

The correct answer is C. Because VIEW1 was created using a SELECT statement that only references rows that have a value less than 100 in COL1, and because VIEW1 was created with the WITH CHECK OPTION specified, each value inserted into COL1 (using VIEW1) must be less than 100. In addition, because COL2 was defined using a character data type, all values inserted into COL2 must be enclosed in single quotes. The INSERT statements shown in answers B and D will fail because the value to be assigned to COL1 exceeds 100; the INSERT statement shown in answer A will fail because the value abc is not enclosed in single quotation marks.

The correct answer is D. Because the EMPID column was defined in such a way that it does not allow null values, a non-null value must be provided for this column anytime data is inserted into either table. The INSERT statement shown in answer D does not provide a value for the EMPID column of the CURRENT_EMPLOYEES table, so the statement will fail.

The correct answer is C. A trigger is used to produce an action as a result of a change to a table. Views provide users with alternate ways to see table data. And because a view can reference the data stored in any number of columns found in the base table it refers to, views can be used, together with view privileges, to control what data a user can and cannot see. Furthermore, if a view is created with the WITH CHECK OPTION specified, it can be used to ensure that all rows added to a table through it conform to its definition.

The correct answer is A. The IN predicate is used to define a comparison relationship in which a value is checked to see whether or not it matches a value in a finite set of values. This finite set of values can consist of one or more literal values coded directly in the SELECT statement, or it can be composed of the non-null values found in the result data set generated by a subquery. So in this example, the non-null values that appear in the result data set produced by the subquery are the values 1 and 2, and the only row in TAB1 that has a matching value in COL1 is the row with the value 1 in it.

The correct answer is C. When a cursor that has been declared with the WITH HOLD option specified (as in the example shown) is opened, it will remain open across transaction boundaries until it is explicitly closed; otherwise , it will be implicitly closed when the transaction that opens it is terminated. In this example, the cursor is opened, the first three rows are fetched from it, the transaction is committed (but the cursor is not closed), another row is fetched from it, and then it is closed. Thus, the last value obtained will be:

The correct answer is D. When a SELECT statement such as the one shown is executed, the result data set produced will contain all possible combinations of the rows found in each table specified (otherwise known as the Cartesian product). Every row in the result data set produced is a row from the first table referenced concatenated with a row from the second table referenced, concatenated in turn with a row from the third table referenced, and so on. The total number of rows found in the result data set produced is the product of the number of rows in all the individual table-references; in this case, 5 x 5 = 25.

The correct answer is D. Data is stored in a table in no particular order, and unless otherwise specified (with an ORDER BY clause), a query only returns data in the order in which it is found.

The correct answer is B. The DISTINCT clause of the SELECT statement must follow the word SELECT, and if used, duplicate records are removed from the result data set returned.

The correct answer is C. When the UNION set operator is used, the result data sets produced by each individual query are combined and all duplicate rows are eliminated. Thus with this example, the results of both tables are combined (6 rows + 6 rows = 12 rows) and the duplicate rows for Jagger, Mick and Richards, Keith are removed (12 “ 2 = 10 rows). So 10 rows are returned.