IBM DB2 9 Fundamentals certification (Exam 730): Sample questions about tables, Part 6

The following sample exam questions are taken from DB2 9 Fundamentals Certification Study Guide, by Roger E. Sanders (copyright 2007) and are reprinted here with permission from MC Press.

Test your knowledge of DB2 9 by answering the questions below, which are from a real, previous DB2 9 Fundamentals certification exam (Exam 730). When you make your answer selection, click on "Get Answer" to check your answers and read the explanations. If you haven't done so already, be sure to take part 1, part 2, part 3, part 4 and part 5 of this DB2 9 quiz series about tables.

Sample Questions
Question 1

Which of the following tools can be used to automate table reorganization operations?

A. Control Center
B. Command Center
C. Command Line Processor
D. Task Center

Get answer

Question 2

Which of the following can NOT be used to restrict specific values from being inserted into a column in a particular table?

A. Index
B. Check Constraint
C. Refential Constraint
D. Default Constraint

Get answer

Question 3

A transaction using the Read Stability isolation level scans the same table multiple times before it terminates. Which of the following can occur within this transaction's processing?

A. Uncommitted changes made by other transactions can be seen from one scan to the next.
B. Rows removed by other transactions that appeared in one scan will no longer appear in subsequent scans.
C. Rows added by other transactions that did not appear in one scan can be seen in subsequent scans.
D. Rows that have been updated can be changed by other transactions from one scan to the next.

Get answer

Question 4

Which of the following will provide user USER1 and all members of the group GROUP1 with the ability to perform DML, but no other operations on table TABLE1?

A. GRANT INSERT, UPDATE, DELETE, SELECT ON TABLE table1 TO user1 AND group1
B. GRANT INSERT, UPDATE, DELETE, SELECT ON TABLE table1 TO USER user1, GROUP group1
C. GRANT ALL PRIVILEGES EXCEPT ALTER, INDEX, REFERENCES ON TABLE table1 TO USER user1, GROUP group1
D. GRANT CONTROL ON TABLE table1 TO user1 AND group1

Get answer

Question 5

Given the following table definitions:

TABLE1
¯¯¯¯¯¯¯¯¯¯¯

ID	INT
NAME	CHAR(30)
PERSON	INT
CITIES	INT

TABLE2
¯¯¯¯¯¯¯¯¯¯¯

ID	INT
LASTNAME	CHAR(30)

Which of the following statements will remove all rows in table TABLE1 that have matching PERSONs in table TABLE2?

A. DELETE FROM table1 WHERE id IN (SELECT id FROM table2)
B. DELETE FROM table1 WHERE id IN (SELECT person FROM table2)
C. DELETE FROM table1 WHERE person IN (SELECT id FROM table2)
D. DELETE FROM table1 WHERE person IN (SELECT person FROM table2)

Get answer

Question 6

If the following SQL statements are executed:

CREATE TABLE tab1 (id SMALLINT NOT NULL PRIMARY KEY,
                   name  VARCHAR(25));

CREATE TABLE tab2 (empid   SMALLINT,
                   weekno  SMALLINT,
                   payamt  DECIMAL(6,2),
    CONSTRAINT const1 FOREIGN KEY (empid) 
        REFERENCES taba(id) ON UPDATE NO ACTION);

Which of the following statements is true?

A. Only values that exist in the ID column of table TAB1 are allowed to be in-serted in the EMPID column of table TAB2
B. The updating of values in the ID column of table TAB1 is not allowed
C. Only values that do not already exist in the ID column of table TAB1 are al-lowed to be inserted in the EMPID column of table TAB2
D. When values that exist in the ID column of table TAB1 are updated, corre-sponding values in the EMPID column of table TAB2 are updated as well

Get answer

Question 7

Application A holds an Exclusive lock on table TAB1 and needs to acquire an Exclusive lock on table TAB2. Application B holds an Exclusive lock on table TAB2 and needs to acquire an Exclusive lock on table TAB1. If lock timeout is set to -1 and both applications are using the Read Stability isolation level, which of the following will occur?

A. Applications A and B will cause a deadlock situation
B. Application B will read the copy of table TAB1 that was loaded into memory when Application A first read it
C. Application B will read the data in table TAB1 and see uncommitted changes made by Application A
D. Application B will be placed in a lock-wait state until Application A releases its lock

Get answer

Question 8

A declared temporary table is used for which of the following purposes?

A. Backup purposes
B. Storing intermediate results
C. Staging area for load operations
D. Sharing result data sets between applications

Get answer

Question 9

Given the following data:

TAB1

C1  C2
--- ----
200 abc
250 abc
150 def
300 ghi
175 def

If the following query is executed:

WITH subset (col1, col2) AS
    (SELECT c1, c2 FROM tab1 WHERE c1 > 150)
SELECT col2, SUM(col1) AS col1_sum 
  FROM subset 
  GROUP BY col2
  ORDER BY col2

Which of the following result data sets will be produced?

A.

COL2	COL1_SUM
--------	--------
abc	200
abc	250
def	175
ghi	300


4 record(s) selected

B.

COL2	COL1_SUM
--------	--------
abc	450
def	175
ghi	300


3 record(s) selected

C.

COL2	COL1_SUM
--------	--------
abc	450
def	375
ghi	300


3 record(s) selected

D.

COL2	COL1_SUM
--------	--------
abc	450
abc	450
def	175
def	175
ghi	300


5 record(s) selected

Get answer

Question 10

Given the following CREATE TABLE statement:

CREATE TABLE table2 LIKE table1

Which two of the following will NOT occur when the statement is executed?

A. TABLE2 will have the same column names and column data types as TABLE1
B. TABLE2 will have the same column defaults as TABLE1
C. TABLE2 will have the same nullability characteristics as TABLE1
D. TABLE2 will have the same indexes as TABLE1
E. TABLE2 will have the same referential constraints as TABLE

Get answer

ANSWER KEY

Question 1

The correct answer is D -- Task Center

The Task Center allows users to schedule tasks, run tasks, and send notifications about completed tasks to other users. Users can create a task within the Task Center, generate a task by saving the results from a DB2 dialog or wizard, create a script within another tool and save it to the Task Center, or import an existing script. Thus, it is possible to create a script that calls the REORG command and have the Task Center to execute that script on a routine basis.

Question 2

The correct answer is D -- Default Constraint

A unique index, a check constraint, and a referential constraint place restrictions on what can and cannot be stored in the column(s) they are associated with. A default constraint, however, is used to provide a default value for a particular column if no data is provided for that column when data is inserted into a table; if a value is provided for the column, the default value is ignored.

Question 3

The correct answer is C -- Rows added by other transactions that did not appear in one scan can be seen in subsequent scans.

When the Read Stability isolation level is used by a transaction that executes a query, locks are acquired on all rows returned to the result data set produced, and other transactions cannot modify or delete the locked rows; however, they can add new rows to the table that meet the query's search criteria. If that happens, and the query is run again, these new rows will appear in the new result data set produced.

Question 4

The correct answer is B -- GRANT INSERT, UPDATE, DELETE, SELECT ON TABLE table1 TO USER user1, GROUP group1

The syntax used to grant table privileges is:

GRANT [ALL  |
       Privilege <( ColumnName, ... )> , ...] 
ON TABLE [TableName] 
TO [Recipient, ...] 

where:

Privilege	Identifies one or more table privileges that are to be given to one or more users and/or groups. The following values are valid for this parameter: CONTROL, ALTER, SELECT, INSERT, UPDATE, DELETE, INDEX, and REFERENCES. (CONTROL privilege is not recognized by DB2 for iSeries and DB2 for zSeries.)
ColumnName	Identifies by name one or more specific columns that UPDATE or REFERENCES privileges are to be associated with. This option is only used when Privilege contains the value UPDATE or REFERENCES
TableName	Identifies by name the table that all table privileges specified are to be associated with.
Recipient	Identifies the name of the user(s) and/or group(s) that are to receive the table privileges specified. The value specified for the Recipient parameter can be any combination of the following: <USER> [UserName], <GROUP> [GroupName], and PUBLIC. CONTROL privilege allows a user to remove (drop) a table from a database and gives the user the ability to grant and revoke one or more table privileges (except the CONTROL privilege) to/from other users and groups; granting ALL PRIVILEGES gives a user the right to perform other operations besides DML operations.

Question 5

The correct answer is C -- DELETE FROM table1 WHERE person IN (SELECT id FROM table2)

Since we are looking for values in the PERSON column of TABLE1 that have a matching value in the ID column of TABLE2, the statement shown in Answer C is the only statement that is correct. (The SQL statements shown in Answers B and D are incorrect because there is no PERSON column in TABLE2; the statement shown in Answer A is incorrect because it is looking for values that match those in the ID column in TABLE1, not the PERSON column.

Question 6

The correct answer is A -- Only values that exist in the ID column of table TAB1 are allowed to be in-serted in the EMPID column of table TAB2

The Insert Rule for a referential constraint guarantees that a value can never be inserted into the foreign key of a child table unless a matching value can be found in the corresponding parent key of the associated parent table. Any attempt to insert records into a child table that violates this rule will result in an error, and the insert operation will fail. The Insert Rule for a referential constraint is implicitly created when the referential constraint itself is created. In this example, the EMPID column of table TAB2 is a foreign key (in a child table) that references the ID column (the parent key) of table TAB1 (the parent table). Therefore, because of the Insert Rule, values cannot be added to the EMPID column of table TAB2 that do not already exist in the ID column of table TAB1.

Question 7

The correct answer is A -- Applications A and B will cause a deadlock situation If Application B did not already have an Exclusive lock on table TAB2, Application B would be placed in a lock-wait state until Application A released its locks. However, because Application B holds an Exclusive lock on table TAB2, when Application A tries to acquire an Exclusive lock on table TAB2 and Application B tries to acquire an Exclusive lock on table TAB1, a deadlock will occur – processing by both transactions will be suspended until their second lock request is granted. Because neither lock request can be granted until one of the owning transactions releases the lock it currently holds (by performing a commit or rollback operation), and because neither transaction can perform a commit or rollback operation because they both have been suspended (and are waiting on locks), a deadlock has occurred.

Question 8

The correct answer is B -- Storing intermediate results

Declared temporary tables are used to hold temporary data on behalf of a single application and are automatically destroyed when the application that declared them disconnects from the database. Declared temporary tables are not used for backup purposes, to sage data for load operations, or to share result data sets between applications.

Question 9

The correct answer is B --

Common table expressions are mechanisms that are used to construct local temporary tables that reside in memory and only exist for the life of the SQL statement that defines them. The syntax used to construct a common table expression is:

 WITH [TableName] <( [ColumnName] ,...] )>
 AS ( [SELECTStatement] )

where:

TableName	Specifies the name that is to be assigned to the temporary table to be created.
ColumnName	Specifies the name(s) to be assigned to one or more columns that are to be included in the temporary table to be created. Each column name specified must be unique and unqualified; if no column names are specified, the names derived from the result data set produced by the SELECT Statement specified will be used. If a list of column names is specified, the number of column names provided must match the number of columns that will be returned by the SELECT statement used to create the temporary table. If a common table expression is recursive, or if the result data set produced by the SELECT statement specified contains duplicate column names, column names must be specified.
SELECTStatement	Identifies a SELECT SQL statement that, when executed, will produce the data values to be added to the column(s) in the temporary table to be created.

So in this example, all of the data stored in table TAB1, with the exception of the re-cord "150 - def" is copied to a common table named SUBSET, and then a query is ran against this common table.

Question 10

The correct answers are D and E -- TABLE2 will have the same indexes as TABLE1 and TABLE2 will have the same referential constraints as TABLE1

When the CREATE TABLE … LIKE … statement is executed, each column of the table that is created will have exactly the same name, data type and nullability characteristic as the columns of the source table used to create the new table. Furthermore, if the EXCLUDING COLUMN DEFAULTS option is not specified (which is the case in this example), all column defaults will be copied as well. However, the new table will not contain any unique constraints, foreign key constraints, triggers, or indexes that exist in the original.