IBM Assessment: DB2 9 Family Fundamentals (A2090-730) Free Practice Test
Question 1
57 Given table T1 has column I1 containing the following data: I1
1
2
3
4
If the following sequence of SQL statements is applied within a single unit of work:
UPDATE t1 SET i1 = 3 WHERE i1 = 2; S
AVEPOINT s1 ON ROLLBACK RETAIN CURSORS;
UPDATE t1 SET i1 = 5 WHERE i1 = 3;
SAVEPOINT s2 ON ROLLBACK RETAIN CURSORS;
INSERT INTO t1 (i1) VALUES (6);
ROLLBACK TO SAVEPOINT s1;
UPDATE t1 SET i1 = 2 WHERE i1 = 4;
COMMIT;
What is the expected sequence of values returned from?
SELECT i1 FROM t1 ORDER BY i1
1
2
3
4
If the following sequence of SQL statements is applied within a single unit of work:
UPDATE t1 SET i1 = 3 WHERE i1 = 2; S
AVEPOINT s1 ON ROLLBACK RETAIN CURSORS;
UPDATE t1 SET i1 = 5 WHERE i1 = 3;
SAVEPOINT s2 ON ROLLBACK RETAIN CURSORS;
INSERT INTO t1 (i1) VALUES (6);
ROLLBACK TO SAVEPOINT s1;
UPDATE t1 SET i1 = 2 WHERE i1 = 4;
COMMIT;
What is the expected sequence of values returned from?
SELECT i1 FROM t1 ORDER BY i1
Correct Answer: B
Question 2
A stored procedure object is created into which DB2 object?
Correct Answer: C
Question 3
Which of the following is a NOT a valid reason for defining a view on a table?
Correct Answer: D
Question 4
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?
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?
Correct Answer: D
Question 5
What type of constraint can be used to ensure that, in any given row in a table, the value of one column never exceeds the value of another column?
Correct Answer: A
Question 6
A sequence was created with the DDL statement shown below:
CREATE SEQUENCE my_seq START WITH 10 INCREMENT BY 10 CACHE 10
User USER1 successfully executes the following statements in the order shown:
VALUES NEXT VALUE FOR my_seq INTO :hvar; VALUES NEXT VALUE FOR my_seq INTO :hvar;
User USER2 successfully executes the following statements in the order shown:
ALTER SEQUENCE my_seq RESTART WITH 5 INCREMENT BY 5 CACHE 5; VALUES NEXT VALUE FOR my_seq INTO :hvar;
After users USER1 and USER2 are finished, user USER3 executes the following query:
SELECT NEXT VALUE FOR my_seq FROM sysibm.sysdummy1
What value will be returned by the query?
CREATE SEQUENCE my_seq START WITH 10 INCREMENT BY 10 CACHE 10
User USER1 successfully executes the following statements in the order shown:
VALUES NEXT VALUE FOR my_seq INTO :hvar; VALUES NEXT VALUE FOR my_seq INTO :hvar;
User USER2 successfully executes the following statements in the order shown:
ALTER SEQUENCE my_seq RESTART WITH 5 INCREMENT BY 5 CACHE 5; VALUES NEXT VALUE FOR my_seq INTO :hvar;
After users USER1 and USER2 are finished, user USER3 executes the following query:
SELECT NEXT VALUE FOR my_seq FROM sysibm.sysdummy1
What value will be returned by the query?
Correct Answer: A
Question 7
Which of the following is NOT a characteristic of a unique index?
Correct Answer: C
Question 8
Given the following tables:
YEAR_2006
EMPID NAME
1 Jagger, Mick 2 Richards, Keith 3 Wood, Ronnie 4 Watts, Charlie 5 Jones, Darryl 6 Leavell, Chuck
YEAR_1962
EMPID NAME
1 Jagger, Mick 2 Richards, Keith 3 Jones, Brian 4 Wyman, Bill 5 Watts, Charlie 6 Stewart, Ian
If the following SQL statement is executed, how many rows will be returned?
SELECT name FROM year_2007 UNION ALL SELECT name FROM year_1962
YEAR_2006
EMPID NAME
1 Jagger, Mick 2 Richards, Keith 3 Wood, Ronnie 4 Watts, Charlie 5 Jones, Darryl 6 Leavell, Chuck
YEAR_1962
EMPID NAME
1 Jagger, Mick 2 Richards, Keith 3 Jones, Brian 4 Wyman, Bill 5 Watts, Charlie 6 Stewart, Ian
If the following SQL statement is executed, how many rows will be returned?
SELECT name FROM year_2007 UNION ALL SELECT name FROM year_1962
Correct Answer: D
Question 9
Given the following CREATE TABLE statement:
CREATE TABLE table2 LIKE table1
Which two of the following will NOT occur when the statement is executed?
CREATE TABLE table2 LIKE table1
Which two of the following will NOT occur when the statement is executed?
Correct Answer: B,E
Question 10
Which of the following is a valid DB2 data type?
Correct Answer: C
Question 11
Within the application MY_APP the following PREVIOUS VALUE expression references the sequence MY_SEQ: PREVIOUS VALUE FOR my_seq After which of the following events will the most recently generated value of MY_SEQ persist so that it can be returned to the active MY_APP application?
Correct Answer: C
Question 12
A number of applications issue the following SQL statement: SELECT d.deptno, e.empno, e.salary FROM department d INNER JOIN employee e ON d.deptno = e.deptno A database administrator wishes to store this query within the database. Which of the following database objects can be used to accomplish this?
Correct Answer: A
Question 13
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
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
Correct Answer: C
Question 14
Given the following statements:
CREATE TABLE tab1 (col1 INT);
CREATE TABLE tab2 (col1 INT);
CREATE TRIGGER trig1 AFTER UPDATE ON tab1
REFERENCING NEW AS new1
FOR EACH ROW MODE DB2SQL
INSERT INTO tab2 VALUES(new1.col1);
INSERT INTO tab1 VALUES(2),(3);
What is the result of the following query?
SELECT count(*) FROM tab2;
CREATE TABLE tab1 (col1 INT);
CREATE TABLE tab2 (col1 INT);
CREATE TRIGGER trig1 AFTER UPDATE ON tab1
REFERENCING NEW AS new1
FOR EACH ROW MODE DB2SQL
INSERT INTO tab2 VALUES(new1.col1);
INSERT INTO tab1 VALUES(2),(3);
What is the result of the following query?
SELECT count(*) FROM tab2;
Correct Answer: B
Question 15
Which of the following is used to create and debug user-defined functions?
Correct Answer: C