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

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, part 5 and part 6 of this DB2 9 quiz series about tables.

Sample Questions
Question 1

Which of the following isolation levels will lock all rows scanned to build a result data set?

A. Uncommitted Read
B. Cursor Stability
C. Read Stability
D. Repeatable Read

Get answer

Question 2

Which of the following is a NOT a valid reason for defining a view on a table?

A. Restrict users' access to a subset of table data
B. Ensure that rows inserted remain within the scope of a definition
C. Produce an action as a result of a change to a table
D. Provide users with an alternate view of table data

Get answer

Question 3

Which of the following is NOT a characteristic of a declared temporary table?

A. Declared temporary tables are not persistent and can only be used by the application that creates them
B. It is possible for many applications to create declared temporary tables that have the same name
C. Declared temporary tables are created by executing a CREATE TABLE statement with the DECLARED GLOBAL TEMPORARY clause specified
D. Once the application that created a global temporary table is terminated, any records in the table are deleted and the table is automatically destroyed

Get answer

Question 4

After the following SQL statement is executed:

GRANT ALL PRIVILEGES ON TABLE employee TO USER user1

Assuming user USER1 has no other authorities or privileges, which of the following actions is user USER1 allowed to perform?

A. Drop an index on the EMPLOYEE table
B. Grant all privileges on the EMPLOYEE table to other users
C. Alter the table definition
D. Drop the EMPLOYEE table

Get answer

Question 5

Given the following table definition:

EMPLOYEES
¯¯¯¯¯¯¯¯¯¯¯

EMPID	INTEGER
NAME	CHAR(20)
DEPT	CHAR(10)
SALARY	DECIMAL(10,2)
COMMISSION	DECIMAL(8,2)

Assuming the DEPT column contains the values 'ADMIN', 'PRODUCTION', and 'SALES', which of the following statements will produce a result data set in which all ADMIN department employees are grouped together, all PRODUCTION department employees are grouped together, and all SALES department employees are grouped together?

A. SELECT name, dept FROM employees ORDER BY dept
B. SELECT name, dept FROM employees GROUP BY dept
C. SELECT name, dept FROM employees GROUP BY ROLLUP (dept)
D. SELECT name, dept FROM employees GROUP BY CUBE (dept))

Get answer

Question 6

Which two of the following are optional and do not have to be specified when creating a table?

A. Table name
B. Column name
C. Default constraint
D. Column data type
E. NOT NULL constraint

Get answer

Question 7

If the following SQL statement is executed:

CREATE TABLE sales
    (invoice_no   NOT NULL PRIMARY KEY,
     sales_date   DATE,
     sales_amt    NUMERIC(7,2))
    IN tbsp0, tbsp1, tbsp2, tbsp3
    PARTITION BY RANGE (sales_date NULLS FIRST)
        (STARTING '1/1/2007' ENDING '12/31/2007'
         EVERY 3 MONTHS)

Which of the following statements is true?

A. Administrative tasks such as backing up, restoring, and reorganizing data stored in the SALES table must be done at the table level; not at the partition level
B. Data can be quickly rolled in and out of the SALES table by using the ATTACH PARTITION and DETACH PARTITION clauses of the ALTER TABLE statement
C. If an index is created for the SALES table, its data must be stored in table space TBSP0
D. When resolving queries against the SALES table, each partition used is scanned asynchronously and the results of each partition scan are merged to produce the result data set returned

Get answer

Question 8

A user wishing to invoke an SQL stored procedure that queries a table must have which of the following privileges?

A. CALL privilege on the procedure; SELECT privilege on the table
B. CALL privilege on the procedure; REFERENCES privilege on the table
C. EXECUTE privilege on the procedure; SELECT privilege on the table
D. EXECUTE privilege on the procedure; REFERENCES privilege on the table

Get answer

Question 9

Which of the following is NOT an attribute of Declared Global Temporary Tables (DGTTs)?

A. Each application that defines a DGTT has its own instance of the DGTT
B. Two different applications cannot create DGTTs that have the same name
C. DGTTs can only be used by the application that creates them, and only for the life of the application
D. Data stored in a DGTT can exist across transaction boundaries

Get answer

Question 10

Given the following table definition:

SALES
¯¯¯¯¯¯¯¯¯¯¯

SALES_DATE	DATE
SALES_PERSON	CHAR(20)
REGION	CHAR(20)
SALES	INTEGER

Which of the following SQL statements will remove all rows that had a SALES_DATE in the year 1995?

A. DELETE * FROM sales WHERE YEAR(sales_date) = 1995
B. DELETE FROM sales WHERE YEAR(sales_date) = 1995
C. DROP * FROM sales WHERE YEAR(sales_date) = 1995
D. DROP FROM sales WHERE YEAR(sales_date) = 1995

Get answer

ANSWER KEY

Question 1

The correct answer is D -- Repeatable Read

The Repeatable Read isolation level will lock all rows scanned in response to a query. (The Read Stability isolation level will only lock the rows returned in the result data set; the Cursor Stability isolation level will only lock the row in the result data set that the cursor is currently pointing to; and the Uncommitted Read isolation level will not lock any rows during normal read processing.)

Question 2

The correct answer is C -- Produce an action as a result of a change to a table

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 [LOCAL | CASCADED] CHECK OPTION specified, it can be used to ensure that all rows added to a table through it conform to its definition.

Question 3

The correct answer is C -- Declared temporary tables are created by executing a CREATE TABLE state-ment with the DECLARED GLOBAL TEMPORARY clause specified

Unlike base tables, whose descriptions and constraints are stored in the system catalog tables of the database to which they belong, declared temporary tables are not persistent and can only be used by the application that creates them—and only for the life of the application. (Once the application that created the global temporary table is terminated, any records in the table are deleted and the table itself is destroyed.) When the application that creates a declared temporary table terminates, the rows of the table are deleted, and the definition of the table is dropped. (However, data stored in a temporary table can exist across transaction boundaries.) Another significant difference between the two centers around naming conventions: base table names must be unique within a schema, but because each application that defines a declared temporary table has its own instance of that table, it is possible for many applications to create declared temporary tables that have the same name. And where base tables are created with the CREATE TABLE SQL statement, declared temporary tables are created with the DECLARE GLOBAL TEMPORARY TABLE statement.

Question 4

The correct answer is C -- Alter the table definition

The GRANT ALL PRIVILEGES statement gives USER1 the following privileges for the EMPLOYEE table: ALTER, SELECT, INSERT, UPDATE, DELETE, INDEX, and REFERENCES. To drop an index, USER1 would need CONTROL privilege on the index – not the table the index is based on; USER1 cannot grant privileges to other users because the WITH GRANT OPTION clause was not specified with the GRANT ALL PRIVILEGES statement used to give USER1 table privileges; and in order to drop the EMPLOYEE table, USER1 would have to have CONTROL privilege on the table – CONTROL privilege is not granted with the GRANT ALL PRIVILEGES statement.

Question 5

The correct answer is A -- SELECT name, dept FROM employees ORDER BY dept

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".

Question 6

The correct answers are C and E -- Default constraint and NOT NULL constraint

At a minimum, when a new table is created, a table name, one or more column names, and corresponding column data types must be specified. Primary keys, constraints (NOT NULL, default, check, unique, referential integrity, and informational), and table space information is optional and is not required.

Question 7

The correct answer is B -- Data can be quickly rolled in and out of the SALES table by using the ATTACH PARTITION and DETACH PARTITION clauses of the ALTER TABLE statement

The SALES table in the example is partitioned such that each quarter's data is stored in a different data partition, and each partition resides in a different table space. Advantages of using table partitioning include:

Easy roll-in and roll-out of data. Rolling in partitioned table data allows a new range to be easily incorporated into a partitioned table as an additional data partition. Rolling out partitioned table data allows you to easily separate ranges of data from a partitioned table for subsequent purging or archiving. Data can be quickly rolled in and out by using the ATTACH PARTITION and DETACH PARTITION clauses of the ALTER TABLE statement.

Easier administration of large tables. Table level administration becomes more flexible because administrative tasks can be performed on individual data partitions. Such tasks include: detaching and reattaching of a data partition, backing up and restoring individual data partitions, and reorganizing individual indexes. In addition, time consuming maintenance operations can be shortened by breaking them down into a series of smaller operations. For example, backup operations can be performed at the data partition level when the each data partition is placed in separate table space. Thus, it is possible to backup one data partition of a partitioned table at a time.

Flexible index placement. With table partitioning, indexes can be placed in dif-ferent table spaces allowing for more granular control of index placement.

Better query processing. When resolving queries, one or more data partitions may be automatically eliminated, based on the query predicates used. This functionality, known as Data Partition Elimination, improves the performance of many decision support queries because less data has to be analyzed before a result data set can be returned.

Question 8

The correct answer is C -- EXECUTE privilege on the procedure; SELECT privilege on the table

The EXECUTE privilege, when granted, allows a user to invoke a routine (a routine can be a user-defined function, a stored procedure, or a method that can be invoked by several different users), create a function that is sourced from the routine (provided the routine is a function), and reference the routine in a Data Definition Language SQL statement (for example, CREATE VIEW and CREATE TRIGGER) statement. When the EXECUTE privilege is granted for a routine, any privileges needed by the routine must also be granted – in this case, the SELECT privilege is needed for the table the procedure will query.

Question 9

The correct answer is B -- Two different applications cannot create DGTTs that have the same name

Unlike base tables, whose descriptions and constraints are stored in the system catalog tables of the database to which they belong, declared temporary tables are not persistent and can only be used by the application that creates them—and only for the life of the application. When the application that creates a declared temporary table terminates, the rows of the table are deleted, and the description of the table is dropped. (However, data stored in a temporary table can exist across transaction boundaries.) Another significant difference focuses on naming conventions: base table names must be unique within a schema, but because each application that defines a declared temporary table has its own instance of that table, it is possible for many applications to create declared temporary tables that have the same name.

Question 10

The correct answer is B -- DELETE FROM sales WHERE YEAR(sales_date) = 1995

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] …