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

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

Sample Questions
Question 1

If the following SQL statements are executed:

CREATE TABLE make (makeid SMALLINT NOT NULL PRIMARY KEY,
                   make   VARCHAR(25));

CREATE TABLE model (modelid  SMALLINT,
                    model    VARCHAR(25),
                    makeid   SMALLINT,
    CONSTRAINT const1 FOREIGN KEY (makeid) 
        REFERENCES make(makeid) ON DELETE RESTRICT);

And each table created is populated as follows:

MAKE

MAKEID	MAKE
¯¯¯¯¯	¯¯¯¯¯
1	Ford
2	Chevrolet
3	Toyota

MODEL

MODELID	MODEL	MAKEID
¯¯¯¯¯	¯¯¯¯¯	¯¯¯¯¯
1	Mustang	1
2	Escort	1
3	Malibu	2
4	Camry	3

If the following SQL statement is executed:

DELETE FROM make WHERE makeid = 1

What is the total number of rows that will be deleted?

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

Get answer

Question 2

Given the following two tables:

     NAMES
     ¯¯¯¯¯¯¯

NAME	NUMBER
¯¯¯¯¯¯¯¯¯¯¯¯¯¯	¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Wayne Gretzky	99
Jaromir Jagr	68
Bobby Orr	4
Bobby Hull	23
Mario Lemieux	66

     NAMES
     ¯¯¯¯¯¯¯

NAME	NUMBER
¯¯¯¯¯¯¯¯¯¯¯¯¯¯	¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Wayne Gretzky	244
Bobby Orr	129
Bobby Hull	121
Mario Lemieux	189
Joe Sakic	94

How many rows would be returned using the following statement?

SELECT name FROM names, points

A. 0
B. 5
C. 10
D. 25

Get answer

Question 3

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 4

Given the following table definition:

      EMPLOYEES
      ¯¯¯¯¯¯¯¯¯¯

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

If the following SQL statement is executed:

CREATE UNIQUE INDEX empid_ui ON employees (empid)

Which two of the following statements are true?

A. Multiple null values are allowed in the EMPID column of the EMPLOYEES table.
B. No null values are allowed in the EMPID column of the EMPLOYEES table.
C. One (and only one) null value is allowed in the EMPID column of the EMPLOYEES table.
D. No other unique indexes can be created on the EMPLOYEES table.
E. Every value found in the EMPID column of the EMPLOYEES table will be different.

Get answer

Question 5

Application A is running under the Repeatable Read isolation level and holds an Update lock on table TAB1. Application B wants to query table TAB1 and cannot wait for Application A to release its lock. Which isolation level should Application B run under to achieve this objective?

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

Get answer

Question 6

User USER1 is the owner of TABLE1. Assuming user USER1 only holds privileges for TABLE1, which of the following is the best way to remove all privileges user USER1 holds?

A. REVOKE CONTROL ON table1 FROM user1
B. REVOKE ALL PRIVILEGES ON table1 FROM user1
C. REVOKE CONTROL ON table1 FROM user1; REVOKE ALL PRIVILEGES ON table1 FROM user1;
D. REVOKE CONTROL, ALL PRIVILEGES ON table1 FROM user1

Get answer

Question 7

Given the following table definitions:

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

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

DEPARTMENTS
¯¯¯¯¯¯¯¯¯¯¯

DEPTNO	INTEGER
DEPTNAME	CHAR(20)

Which of the following statements will produce a result data set that satisfies all of these conditions:

Displays the total number of employees in each department
Displays the corresponding department name for each department ID
Sorted by department employee count, from greatest to least

A. SELECT *, COUNT(empno) FROM departments, employees
WHERE deptid = deptno GROUP BY deptname ORDER BY 2 DESC
B. SELECT deptname, COUNT(empno) FROM departments, employees
WHERE deptid = deptno GROUP BY deptname ORDER BY 2 DESC
C. SELECT deptname, COUNT(empno) FROM departments, employees
WHERE deptid = deptno GROUP BY deptname ORDER BY 2 ASC
D. SELECT deptname, COUNT(*) FROM departments, employees
WHERE deptid = deptno GROUP BY deptname ORDER BY 2

Get answer

Question 8

If table TAB1 is created using the following statement:

CREATE TABLE tab1 (col1  INTEGER NOT NULL,
                         col2  CHAR(5),
 CONSTRAINT cst1 CHECK (col1 in (1, 2, 3)))

Which of the following statements will successfully insert a record into table TAB1?

A. INSERT INTO tab1 VALUES (0, 'abc')
B. INSERT INTO tab1 VALUES (NULL, 'abc')
C. INSERT INTO tab1 VALUES (ABS(2), 'abc')
D. INSERT INTO tab1 VALUES (DEFAULT, 'abc')

Get answer

Question 9

What does the following statement do?

GRANT REFERENCES (col1, col2) ON TABLE table1 TO user1 WITH GRANT OPTION

A. Gives user USER1 the ability to refer to COL1 and COL2 of table TABLE1 in queries, along with the ability to give this authority to other users and groups.
B. Gives user USER1 the ability to refer to COL1 and COL2 of table TABLE1 in views, along with the ability to give this authority to other users and groups.
C. Gives user USER1 the ability to define a referential constraint on table TABLE1 using columns COL1 and COL2 as the parent key of the constraint.
D. Gives user USER1 the ability to define a referential constraint on table TABLE1 using columns COL1 and COL2 as the foreign key of the constraint.

Get answer

Question 10

Given the following two tables:

NAMES

NAME	NUMBER
¯¯¯¯¯¯	¯¯¯¯¯¯
Wayne Gretzky	99
Jaromir Jagr	68
Bobby Orr	4
Bobby Hull	23
Brett Hull	16
Mario Lemieux	66
Mark Messier	11

POINTS

NAME	POINTS
¯¯¯¯¯¯	¯¯¯¯¯¯
Wayne Gretzky	244
Jaromir Jagr	168
Bobby Orr	129
Brett Hull	121
Mario Lemieux	189
Joe Sakic	94

Which of the following statements will display the player name, number, and points for all players that have scored points?

A. SELECT p.name, n.number, p.points FROM names n INNER JOIN points p ON n.name = p.name
B. SELECT p.name, n.number, p.points FROM names n LEFT OUTER JOIN points p ON n.name = p.name
C. SELECT p.name, n.number, p.points FROM names n RIGHT OUTER JOIN points p ON n.name = p.name
D. SELECT p.name, n.number, p.points FROM names n FULL OUTER JOIN points p ON n.name = p.name

Get answer

ANSWER KEY

Question 1

The correct answer is A -- 0

The ON DELETE RESTRICT ensures that whenever a delete operation is performed on the parent table of a referential constraint, the value for the foreign key of each row in the child table will have the same matching value in the parent key of the parent table that it had before the delete operation was performed. Therefore, in this example no row will be deleted from the MAKE because two rows exists in the MODEL table that references the row the DELETE statement is trying to remove.

Had the ON DELETE CASCADE definition been used instead, the delete operation would have succeeded and the tables would have looked like this:

MAKE

MAKEID	MAKE
¯¯¯¯¯	¯¯¯¯¯
2	Chevrolet
3	Toyota

MODEL

MODELID	MODEL	MAKEID
¯¯¯¯¯	¯¯¯¯¯	¯¯¯¯¯
3	Malibu	2
4	Camry	3

On the other hand, if the ON DELETE SET NULL definition had been used, the delete operation would have succeeded and the tables would have looked like this:

MAKE

MAKEID	MAKE
¯¯¯¯¯	¯¯¯¯¯
2	Chevrolet
3	Toyota

MODEL

MODELID	MODEL	MAKEID
¯¯¯¯¯	¯¯¯¯¯	¯¯¯¯¯
1	Mustang	-
2	Escort	-
3	Malibu	2
4	Camry	3

Question 2

The correct answer is D -- 25

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.

Question 3

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 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 4

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.

Question 5

The correct answer is D -- Uncommitted Read

Typically, locks are not acquired during processing when the Uncommitted Read isolation level is used. Therefore, if Application B runs under this isolation level, it will be able to retrieve data from table TAB1 immediately – lock compatibility is not an issue that will cause Application B to wait for a lock.

Question 6

The correct answer is C -- REVOKE CONTROL ON table1 FROM user1; REVOKE ALL PRIVILEGES ON table1 FROM user1;

The owner of a table automatically receives CONTROL privilege, along with all other table privileges available for that table. If the CONTROL privilege is later revoked from the table owner, all other privileges that were automatically granted to the owner when the table was created are not automatically revoked. Instead, they must be explicitly revoked in one or more separate operations. Therefore, both REVOKE statements shown in answer C must be executed in order to completely remove all privileges user USER1 holds on table TABLE1. If an attempt is made to try to combine both operations in a single statement as shown in answer D, an error will be generated.

Question 7

The correct answer is B -- SELECT deptname, COUNT(empno) FROM departments, employees
WHERE deptid = deptno GROUP BY deptname ORDER BY 2 DESC

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.

Question 8

The correct answer is C -- INSERT INTO tab1 VALUES (ABS(2), 'abc')

A check constraint is used to ensure that a particular column in a base table is never assigned an unacceptable value—once a check constraint has been defined for a column, any operation that attempts to place a value in that column that does not meet specific criteria will fail. Check constraints are comprised of one or more predicates that collectively are known as the check condition. This check condition is compared with the data value provided and the result of this comparison is returned as the value "TRUE", "FALSE", or "Unknown". If the check constraint returns the value "TRUE", the value is acceptable, so it is added to the database. If, on the other hand, the check constraint returns the value "FALSE" or "Unknown", the operation attempting to place the value in the database fails, and all changes made by that operation are backed out.

In this example, the check constraint CST1 defined for tableTAB1 only allows the values 1, 2, or 3 to be entered into column COL1. The INSERT statement shown in Answer C is the only INSERT statement that has a valid value specified for column COL1.

Question 9

The correct answer is C -- Gives user USER1 the ability to define a referential constraint on table TABLE1 using columns COL1 and COL2 as the parent key of the constraint.

The REFERENCES table privilege allows a user to create and drop foreign key constraints that reference a table in a parent relationship. This privilege can be granted for the entire table or limited to one or more columns within the table, in which case only those columns can participate as a parent key in a referential constraint. (This particular GRANT statement also gives USER1 the ability the ability to give the REFERENCES privilege for columns COL1 and COL2 to other users and groups.)

Question 10

The correct answer is C -- SELECT p.name, n.number, p.points FROM names n RIGHT OUTER JOIN points p ON n.name = p.name

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.