Assessment: DB2 9 Family Fundamentals - A2090-730 Exam Practice Test

Question 1

Which of the following causes a lock that is being held by an application using the Cursor Stability isolation level to be released?

A. The row the cursor is on is deleted by another application

B. The cursor is moved to another row

C. The row the cursor is on needs to be updated by another application

D. The row the cursor is on is deleted by the application

Correct Answer: B

Question 2

Given the following statement:
SELECT hyear, AVG(salary)
FROM (SELECT YEAR(hiredate) AS hyear, salary
FROM employee WHERE salary > 30000)
GROUP BY hyear
Which of the following describes the result if this statement is executed?

A. The statement will return the year and average salary for all years that any employee had a salary greater than $30,000.

B. The statement will return the year and average salary for all years that every employee hired had a salary greater than $30,000.

C. The statement will return the year and average salary for all employees that have a salary greater than $30,000, sorted by year.

D. The statement will return the year and average salary for all employees hired within a given year that have a salary greater than $30,000.

Correct Answer: D

Question 3

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

B. 0

C. 3

D. 4

Correct Answer: D

Question 4

Given the following query:
SELECT quantity,
CASE WHEN itemcode = '099' THEN 'SILVER'
WHEN itemcode = '788' THEN 'GOLD'
WHEN itemcode = '899' THEN 'PLATINUM'
ELSE 'ERROR'
END
FROM supplier
What will be the result of the query if the following data is evaluated by the CASE expression?
SUPPLIER
QUANTITY ITEMCODE 3 099 4 099 1 788 1 899 5 009 3 788 1 899

A. 3,SILVER 4,SILVER 1,PLATINUM 1,ERROR 5,SILVER 3,GOLD 1,PLATINUM

B. 3,SILVER 4,SILVER 1,GOLD 1,PLATINUM 5,ERROR 3,GOLD 1,PLATINUM

C. 3,SILVER 4,GOLD 1,SILVER 1,GOLD 5,PLATINUM 3,SILVER 1,GOLD

D. 3,SILVER 4,SILVER 1,GOLD 1,GOLD 5,SILVER 3,ERROR 1,PLATINUM

Correct Answer: B

Question 5

Which of the following best describes the lock protection provided by DB2 for the current row of a cursor?

A. The row is protected from updates and deletes by the current application and from positioned updates and deletes that reference another cursor of the same application

B. The row is only protected from positioned updates and deletes that reference another cursor of the same application.

C. The cursor is only protected from updates and deletes by concurrent applications.

D. The cursor is protected from positioned updates and deletes that reference another cursor of a different application.

Correct Answer: C

Question 6

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 RIGHT OUTER JOIN points p ON n.name = p.name

B. SELECT p.name,n.number, p.points FROM names n INNER JOIN points p ON n.name = p.name

C. SELECT p.name,n.number, p.points FROM names n FULL OUTER JOIN points p ON n.name = p.name

D. SELECT p.name,n.number, p.points FROM names n LEFT OUTER JOIN points p ON n.name = p.name

Correct Answer: A

Question 7

Which of the following would NOT provide access to data stored in table TABLE1 using the name T1?

A. A schema named T1 that references table TABLE1

B. A view named T1 that references table TABLE1

C. An alias named T1 that references a view named V1 that references table TABLE1

D. An alias named T1 that references table TABLE1

Correct Answer: A

Question 8

Which of the following SQL statements can be used to create a DB2 object to store numerical data as EURO data?

A. CREATE DISTINCT TYPE euro AS DECIMAL (9,3)

B. CREATE ALIAS euro FOR DECIMAL (9,3)

C. CREATE NICKNAME euro FOR DECIMAL (9,3)

D. CREATE DATA TYPE euro AS DECIMAL (9,3)

Correct Answer: A

Question 9

Which of the following is an accurate statement about packages?

A. Packages provide a logical grouping of database objects.

B. Packages contain control structures that are considered the bound form for SQL statements

C. Packages may be used during query optimization to improve the performance for a subset of SELECT queries

D. Packages describe the objects in a DB2 database and their relationship to each other

Correct Answer: B

Question 10

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

A. Declared temporary tables are created by executing a CREATE TABLE statement with the DECLARED GLOBAL TEMPORARY clause specified

B. Once the application that created a global temporary table is terminated, any records in the table are deleted and the table is automatically destroyed

C. It is possible for many applications to create declared temporary tables that have the same name

D. Declared temporary tables are not persistent and can only be used by the application that creates them

Correct Answer: A

Question 11

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. If an index is created for the SALES table, its data must be stored in table space TBSP0

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

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

Correct Answer: D

Question 12

The opening of cursor CSR01 produces the following result set:
STUDENT LASTNM FIRSTNM CLASSNO 123 Brown John T100 213 Bailey James T100 312 Carter Arlene T210 465 Chas Devon T305 546 Davis Steven T405
If this Fetch statement is executed:
FETCH csr01 INTO :studnum, :firstname, :lastname, :class
Which of the following DELETE statements will cause this row to be deleted?

A. DELETE FROM tab01 FOR CURRENT csr01 WITH RS

B. DELETE * FROM tab01 WHERE CURRENT csr01 WITH CS

C. DELETE ALL FROM tab01 FOR CURRENT OF csr01

D. DELETE FROM tab01 WHERE CURRENT OF csr01 WITH RR

Correct Answer: D

Question 13

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 INTOcurrent_employees SELECT empid, name, salary FROM past_employees WHERE empid = 20

B. INSERT INTOcurrent_employees (name, salary) VALUES (SELECT name, salary FROM past_employees WHERE empid = 20)

C. INSERT INTOcurrent_employees (empid) VALUES (10)

D. INSERT INTOcurrent_employees VALUES (10, 'JAGGER', 85000.00)

Correct Answer: B

Question 14

Which of the following actions will NOT cause a trigger to be fired?

A. UPDATE

B. ALTER

C. DELETE

D. INSERT

Correct Answer: B

Question 15

Given the following two tables:
NAMES
NAME NUMBER
Wayne Gretzky 99 Jaromir Jagr 68 Bobby Orr 4 Bobby Hull 23 Mario Lemieux 66
POINTS
NAME POINTS
Wayne Gretzky 244 Bobby Orr 129 Brett 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

Correct Answer: D

IBM Assessment: DB2 9 Family Fundamentals - A2090-730 Exam Practice Test

Latest Update

Useful Links

Contact Us