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

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 answer the sample questions in Part 2 of this DB2 quiz series about tables.

Sample Questions
Question 1

User USER1 holds CONTROL privilege on table TABLE1. Which two of the following statements is user USER1 allowed to execute?

A. GRANT CONTROL ON table1 TO user2
B. GRANT LOAD ON table1 TO user2
C. GRANT INSERT, UPDATE ON table1 TO user2 WITH GRANT OPTION
D. GRANT BINDADD ON table1 TO PUBLIC
E. GRANT ALL PRIVILEGES ON table1 TO PUBLIC

Get answer

Question 2

Given the following statements:

CREATE TABLE t1 (c1 INTEGER, c2 CHAR(5));
CREATE TABLE t1audit (user VARCHAR(20), date DATE, action VARCHAR(20));

CREATE TRIGGER trig1 AFTER INSERT ON t1
  FOR EACH ROW
  MODE DB2SQL
  INSERT INTO t1audit VALUES (CURRENT USER, CURRENT DATE, 'Insert');

If user USER1 executes the following statements:

INSERT INTO t1 VALUES (1, 'abc');
INSERT INTO t1 (c1) VALUES (2);
UPDATE t1 SET c2 = 'ghi' WHERE c1 = 1;

How many new records will be written to the database?

A. 0
B. 2
C. 3
D. 4

Get answer

Question 3

Given the requirements to store customer names, billing addresses, and telephone numbers, which of the following would be the best way to define the telephone number column for a table if all customers were located in the same country?

A. PHONE    CHAR(15)
B. PHONE   VARCHAR(15)
C. PHONE   LONG VARCHAR
D. PHONE   CLOB(1K)

Get answer

Question 4

Which of the following is NOT a difference between a unique index and a primary key?

A. A primary key is a special form of a unique constraint; both use a unique index.
B. Unique indexes can be defined over one or more columns; primary keys can only be defined on a single column.
C. A table can have many unique indexes but only one primary key.
D. Unique indexes can be defined over one or more columns that allow null values; primary keys cannot contain null values.

Get answer

Question 5

User USER1 wants to utilize an alias to remove rows from a table. Assuming USER1 has no authorities or privileges, which of the following privileges are needed?

A. DELETE privilege on the table
B. DELETE privilege on the alias
C. DELETE privilege on the alias; REFERENCES privilege on the table
D. REFERENCES privilege on the alias; DELETE privilege on the table

Get answer

Question 6

Given the following statements:

CREATE TABLE tab1 (c1 INTEGER, c2 CHAR(5));
CREATE VIEW view1 AS SELECT c1, c2 FROM tab1 WHERE c1 < 100;
CREATE VIEW view2 AS SELECT c1, c2 FROM view1
  WITH CASCADED CHECK OPTION;

Which of the following INSERT statements will fail to execute?

A. INSERT INTO view2 VALUES(50, 'abc')
B. INSERT INTO view1 VALUES (100, 'abc')
C. INSERT INTO view2 VALUES(150, 'abc')
D. INSERT INTO view1 VALUES(100, 'abc')

Get answer

Question 7

Given the following table:

CURRENT_EMPLOYEES
–––––––––––––––––
EMPID      INTEGER NOT NULL
NAME       CHAR(20)
SALARY    DECIMAL(10,2)

PAST_EMPLOYEES
–––––––––––––––––
EMPID      INTEGER NOT NULL
NAME       CHAR(20)
SALARY    DECIMAL(10,2)

Assuming both tables contain data, which of the following statements will NOT successfully add data to table CURRENT_EMPLOYEES?

A. INSERT INTO current_employees (empid) VALUES (10)
B. INSERT INTO current_employees VALUES (10, 'JAGGER', 85000.00)
C. INSERT INTO current_employees SELECT empid, name, salary FROM past_employees WHERE empid = 20
D. INSERT INTO current_employees (name, salary) VALUES (SELECT name, salary FROM past_employees WHERE empid = 20)

Get answer

Question 8

The following triggers were defined for a table named SALES in the order shown:

CREATE TRIGGER trigger_a
NO CASCADE BEFORE UPDATE ON sales
REFERENCING NEW AS new
FOR EACH ROW
SET new.commission = sale_amt * .05
   WHERE invoice = n.invoice;

CREATE TRIGGER trigger_b
AFTER INSERT ON sales
REFERENCING NEW AS new
FOR EACH ROW
UPDATE sales SET bill_date = CURRENT DATE + 30 DAYS
   WHERE invoice = n.invoice;

CREATE TRIGGER trigger_c
NO CASCADE BEFORE DELETE ON sales
FOR EACH ROW
SIGNAL SQLSTATE '75005'
   SET MESSAGE_TEXT = 'Deletes not allowed!';

Which of the following statements is NOT true?

A. Once a row has been added to the SALES table, it cannot be removed
B. Whenever a row is inserted into the SALES table, the value in the BILL_DATE column is automatically set to 30 days from today
C. Each time a row is inserted into the SALES table, trigger TRIGGER_A is fired first, followed by trigger
D. Whenever a row in the SALES table is updated, all three triggers are fired but nothing happens because none of the triggers have been coded to trap update operations

Get answer

Question 9

If the following SQL statements are executed in the order shown:

CREATE TABLE orders
   (order_num   INTEGER NOT NULL,
   buyer_name   VARCHAR(35),
   amount      NUMERIC(5,2));

CREATE UNIQUE INDEX idx_orderno ON orders(order_num);

Which of the following describes the resulting behavior?

A. Every ORDER_NUM value entered must be unique; whenever the ORDERS table is queried rows should be displayed in order of increasing ORDER_NUM values
B. Every ORDER_NUM value entered must be unique; whenever the ORDERS table is queried rows will be displayed in no particular order
C. Duplicate ORDER_NUM values are allowed; no other index can be created for the ORDERS table that reference the ORDER_NUM column
D. Every ORDER_NUM value entered must be unique; no other index can be created for the ORDERS table that reference the ORDER_NUM column

Get answer

Question 10

A table named DEPARTMENT has the following columns:

DEPT_ID
DEPT_NAME
MANAGER
AVG_SALARY

Which of the following is the best way to prevent most users from viewing AVG_SALARY data?

A. Encrypt the table's data
B. Create a view that does not contain the AVG_SALARY column
C. Revoke SELECT access for the AVG_SALARY column from users who should not see AVG_SALARY data
D. Store AVG_SALARY data in a separate table and grant SELECT privilege for that table to the appropriate users

Get answer

ANSWER KEY

Question 1

The correct answers are C and E -- GRANT INSERT, UPDATE ON table1 TO user2 WITH GRANT OPTION, and GRANT ALL PRIVILEGES ON table1 TO PUBLIC

The first GRANT statement (Answer A) is not valid be-cause only users with System Administrator (SYSADM) authority or Database Administra-tor (DBADM) authority are allowed to explicitly grant CONTROL privilege on any object; the second GRANT statement (Answer B) is not valid because LOAD is not a table privilege (LOAD is a database privilege); and the fourth GRANT statement (Answer C) is not valid because BINDADD is not a table privilege (BINDADD is a database privilege). However, a user with CONTROL privilege on a table can grant any table privilege (except the CONTROL privilege), along with the ability to give that privilege to other users and/or groups to anyone—including the group PUBLIC.

Question 2

The correct answer is D -- 4

Each time a record is inserted into table T1, trigger TRIG1 is fired and a record is written to the table T1AUDIT. If both tables were queried after the update operation completes, the results would look something like this:

SELECT * FROM t1

C1      C2
––––– –––––
1         ghi
2         -
   2 record(s) selected.

SELECT * FROM t1audit

USER            DATE          ACTION
––––––––– ––––––––– ––––––––
RSANDERS      01/20/2007   Insert
RSANDERS      01/20/2007   Insert
   2 record(s) selected.

In order to track update and delete operations performed against table T1, similar UPDATE and DELETE triggers would need to be created.

Question 3

The correct answer is A -- PHONE CHAR(15)

Although each data type specified is valid, the CHAR(15) data type will only require 16 bytes of storage whereas the VARCHAR(15) data type will need 20 bytes of storage, the LONG VARCHAR data type will need 40 bytes, and the CLOB(1K) data type will require over 1024 bytes of storage space.

Question 4

The correct answer is B -- Unique indexes can be defined over one or more columns; primary keys can only be defined on a single column.

Both primary keys and unique indexes can be defined over one or more columns in a table.

Question 5

The correct answer is A -- DELETE privilege on the table

The DELETE table privilege allows a user to remove rows of data from a table. Aliases are publicly-referenced names, so no special authority or privilege is required to use them. However, tables or views referred to by an alias have still have the authorization requirements that are associated with these types of objects.

Question 6

The correct answer is C -- INSERT INTO view2 VALUES(150, 'abc')

The statement "INSERT INTO view2 VALUES(150, 'abc')" will fail because the value 150 is greater than 100; because view VIEW2 was created with the WITH CASCADED CHECK OPTION specified, the "WHERE c1 < 100" clause used to create view VIEW1 became a constraint that is used to validate insert and update operations that are performed against view VIEW2 to ensure that all rows inserted into or updated in the base table the view refers to conform to the view's definition.

Question 7

The correct answer is D -- INSERT INTO current_employees (name, salary) VALUES (SELECT name, salary FROM past_employees WHERE empid = 20)

Because the EMPID column in each table 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.

Question 8

The correct answer is D -- Whenever a row in the SALES table is updated, all three triggers are fired but nothing happens because none of the triggers have been coded to trap update operations

Triggers are only fired if the trigger event they have been designed to watch for takes place against the subject table they are designed to interact with. In this example, no UPDATE trigger was defined; therefore, no triggers are fired when the sales table is updated.

Trigger TRIGGER_C will be fired anytime a delete operation is performed against the SALES table and triggers TRIGGER_A and TRIGGER_B will be fired in the order they were created whenever an insert operation is performed against the SALES table. Trigger TRIGGER_A is designed to calculate a sales commission for an invoice based on the sale amount; trigger TRIGGER_B is designed to assign a value to the BILL_DATE column that is 30 days from today; and trigger TRIGGER_C is designed to display an error message whenever anyone tries to delete records from the SALES table.

Question 9

The correct answer is A -- Every ORDER_NUM value entered must be unique; whenever the ORDERS table is queried rows should be displayed in order of increasing ORDER_NUM values

If the UNIQUE clause is specified when the CREATE INDEX statement is executed, rows in the table associated with the index will not have two or more occurrences of the same values in the set of columns that make up the index key. Further-more, the creation of an index provides a logical ordering of the rows of a table so in this example, rows inserted into the ORDERS table will be ordered ORDER_NUM values, in ascending order.

Question 10

The correct answer is B -- Create a view that does not contain the AVG_SALARY column

A view is a virtual table residing in memory that provides an alter-native way of working with data that resides in one or more base tables. For this reason, views can be used to prevent access to select columns in a table. While it is possible to en-crypt the data stored in the DEPARTMENT table or move the AVG_SALARY data to a separate table (you cannot revoke SELECT privilege for a column), the best solution is to create a view for the DEPARTMENT table that does not contain the AVG_SALARY col-umn, revoke SELECT privilege on the DEPARTMENT table from users who are not al-lowed to see AVG_SALARY data, and grant SELECT privilege on the new view to users who need to access the rest of the data stored in the DEPARTMENT table.