IBM C2090-543 - DB2 9.7 Application Development Exam
Page: 2 / 20
Total 100 questions
Question #6 (Topic: Topic 1)
The statement below was used to create a cursor named CSR1:
DECLARE csr1 DYNAMIC SCROLL CURSOR FOR
SELECT * FROM employee;
After fetching all of the rows in the result set associated with cursor CSR1, which two
statements can be used to fetch the data starting at the end of the result set? (Choose
two.)
DECLARE csr1 DYNAMIC SCROLL CURSOR FOR
SELECT * FROM employee;
After fetching all of the rows in the result set associated with cursor CSR1, which two
statements can be used to fetch the data starting at the end of the result set? (Choose
two.)
A. FETCH LAST
B. FETCH PRIOR
C. FETCH RELATIVE
D. FETCH OFFSET
Answer: B,C
Question #7 (Topic: Topic 1)
Which combination of statements can be used to delete qualifying rows from a table named
EMP?
EMP?
A. DECLARE cursor1 CURSOR FOR SELECT empno, workdept FROM emp FOR UPDATE; DELETE FROM emp WHERE CURRENT OF cursor1;
B. DECLARE cursor1 CURSOR FOR SELECT empno, workdept FROM emp FOR DELETE; DELETE FROM emp WHERE CURRENT OF cursor1;
C. DECLARE cursor1 CURSOR FOR SELECT empno, workdept FROM emp FOR UPDATE; DELETE FROM emp;
D. DECLARE cursor1 CURSOR FOR SELECT empno, workdept FROM emp FOR DELETE; DELETE FROM emp;
Answer: A
Question #8 (Topic: Topic 1)
Click the Exhibit button.
CREATE FUNCTION deptemployees (idept VARCHAR(3))
RETURNS TABLE (empno CHAR(6),
firstname VARCHAR(12),
lastname VARCHAR(15))
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
SELECT empno, firstnme, lastname
FROM employee
WHERE workdept = deptemployees.idept;
Referring to the exhibit, what contains the proper way to invoke the DEPTEMPLOYEES()
user-defined function to return all of the employees that work in department D11?
CREATE FUNCTION deptemployees (idept VARCHAR(3))
RETURNS TABLE (empno CHAR(6),
firstname VARCHAR(12),
lastname VARCHAR(15))
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
SELECT empno, firstnme, lastname
FROM employee
WHERE workdept = deptemployees.idept;
Referring to the exhibit, what contains the proper way to invoke the DEPTEMPLOYEES()
user-defined function to return all of the employees that work in department D11?
A. SELECT * FROM TABLE(deptemployees('D11'))
B. SELECT * FROM deptemployees('D11')
C. SELECT * FROM deptemployees('D11') AS D11dept
D. SELECT * FROM TABLE(deptemployees('D11')) AS D11dept
Answer: D
Question #9 (Topic: Topic 1)
The scope of a cursor is limited to ____.
A. the source program in which it is defined
B. the transaction in which the cursor is declared
C. all remotely called routines
D. applications local to the DB2 server
Answer: A
Question #10 (Topic: Topic 1)
Click the Exhibit button.
CONNECT TO test;
CREATE TABLE parent (empno CHAR(6), firstname VARCAHR(25), lastname
VARCHAR(25));
CREATE TABLE child (childno CHAR(6), firstname VARCHAR(25), lastname
VARCHAR(25), empno
CHAR(6));
CREATE VIEW employee AS SELECT * FROM parent;
CREATE VIEW employee_children AS SELECT * FROM child;
COMMIT;
CONNECT RESET;
A DB2 Command Line Processor script file containing the commands and statements
shown in the exhibit was executed successfully.
Which technique can be used to produce a list of all employees and their children, including
employees who have no children?
CONNECT TO test;
CREATE TABLE parent (empno CHAR(6), firstname VARCAHR(25), lastname
VARCHAR(25));
CREATE TABLE child (childno CHAR(6), firstname VARCHAR(25), lastname
VARCHAR(25), empno
CHAR(6));
CREATE VIEW employee AS SELECT * FROM parent;
CREATE VIEW employee_children AS SELECT * FROM child;
COMMIT;
CONNECT RESET;
A DB2 Command Line Processor script file containing the commands and statements
shown in the exhibit was executed successfully.
Which technique can be used to produce a list of all employees and their children, including
employees who have no children?
A. FULL OUTER JOIN
B. LEFT or RIGHT OUTER JOIN
C. MERGE JOIN
D. INNER JOIN
Answer: B
