DB2 9.7 Application Development (C2090-543) Free Practice Test

Question 1

The table shown below contains a large number of financial transactions: CREATE TABLE webstore.transactions (transaction_id INTEGER NOT NULL PRIMARY KEY, order_date TIMESTAMP NOT NULL, shipped_date TIMESTAMP, customer_id INTEGER NOT NULL, shipping_info XML NOT NULL, billing_info XML NOT NULL, invoice XML NOT NULL ) Only members of the AUDIT_TEAM group have SELECT privilege on the WEBSTORE.TRANSACTIONS table. For appropriate supply-chain management, members of the INVENTORY_CONTROL group need to see the INVOICE document for each transaction that has a NULL SHIPPED_DATE, but are restricted from seeing any shipping or billing information. Which database object can a member of the AUDIT_TEAM group create to enable the INVENTORY_CONTROL group to access the information needed from WEBSTORE.TRANSACTIONS?

A. view

B. trigger

C. alias

D. sequence

Correct Answer: A

Question 2

Click the Exhibit button.
UPDATE DBM CFG USING FEDERATED YES;
db2stop;
db2start;
CONNECT TO test;
CREATE WRAPPER oledb LIBRARY 'db2oledb';
CREATE SERVER nwind
WRAPPER oledb
OPTIONS (CONNECTSTRING 'Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=f:Northwind.mdb;', COLLATING_SEQUENCE 'Y');
CREATE FUNCTION nwind.customers()
RETURNS TABLE (customerid CHAR(5),
companynameVARCHAR(40),
contactnameVARCHAR(30),
contacttitleVARCHAR(30),
address VARCHAR(60),
city VARCHAR(15),
region VARCHAR(15),
postalcodeVARCHAR(15),
country VARCHAR(24),
phone VARCHAR(24),
fax VARCHAR(24))
LANGUAGE OLEDB
EXTERNAL NAME 'nwind!customers';
COMMIT;
CONNECT RESET;
A DB2 Command Line Processor script file containing the commands and statements shown in
the exhibit was executed successfully.
Which statement can be used to verify that an OLE DB table function designed to retrieve data
from the CUSTOMERS table of the NORTHWIND Microsoft Access database was created
successfully?

A. SELECTcompanyname FROM TABLE (nwind.customers()) AS t

B. SELECTcompanyname FROM oledb.nwind.customers()

C. SELECTcompanyname FROM nwind.customers()

D. SELECTcompanyname FROM TABLE (oledb.nwind.customers()) AS t

Correct Answer: A

Question 3

Which SQL procedure will retrieve all the rows from table T1 and make those rows available to the invoker as a result set?

A. CREATE PROCEDURE proc1()
DYNAMIC RESULTS SET 1
LANGUAGE SQL
RETURN (SELECT * FROM t1)

B. CREATE PROCEDURE proc1()
DYNAMIC RESULTS SET 1
LANGUAGE SQL
BEGIN
DECLARE c1 CURSOR FOR SELECT * FROM t1;
RETURN c1;
END

C. CREATE PROCEDURE proc1()
DYNAMIC RESULTS SET 1
LANGUAGE SQL
BEGIN
DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM t1;
OPEN c1;
END

D. CREATE PROCEDURE proc1() DYNAMIC RESULTS SET 1 LANGUAGE SQL SELECT * FROM t1

Correct Answer: C

Question 4

Click the Exhibit button.
CREATE TABLE gradereport(sid INTEGER, info XML);
INSERT INTO gradereport VALUES (1,
'<studentinfo sid="1">
<name>John Smith</name>
<honours>No</honours>
<grades>
<course><name>ECE100</name><grade>80</grade></course>
<course><name>CSC100</name><grade>70</grade></course>
<course><name>MAT100</name><grade>60</grade></course>
</grades>
<phone type="mobile">416-333-8725</phone>
</studentinfo>');
Given the statements shown in the exhibit, which two queries can be used to return the semester
average of the student? (Choose two.)

A. XQUERY for $studinfo in db2-fn:xmlcolumn('GRADEREPORT.INFO')/studentinfo
let $average := fn:average($studinfo/grades/course/grade)
return $average

B. XQUERY for $studinfo in db2-fn:xmlcolumn('GRADEREPORT.INFO')/studentinfo
let $avg := fn:avg($studinfo/grades/course/grade)
return $avg

C. XQUERY for $studinfo in db2-fn:xmlcolumn('GRADEREPORT.INFO')/studentinfo
let $sum := fn:sum($studinfo/grades/course/grade),
$count := fn:count($studinfo/grades/course/grade)
return $sum div $count

D. XQUERY for $studinfo in db2-fn:xmlcolumn('GRADEREPORT.INFO')/studentinfo
let $avg := fn:avg($studinfo/grades/course)
return $avg

Correct Answer: B,C

Question 5

Using the default lock timeout and isolation level, user USER1 drops table TABLE1 however, the transaction is not committed. Subsequently, user USER2 attempts to access table TABLE1 using the default isolation level. What will occur?

A. User USER2 will wait until user USER1 issues a COMMIT or ROLLBACK statement.

B. User USER2 receives an error immediately.

C. User USER2 will be able to access the table successfully.

D. User USER1 will receive an error.

Correct Answer: A

Question 6

Click the Exhibit button.
CONNECT TO test;
CREATE TABLE tab_a (col1 INT);
CREATE TABLE tab_b (col1 INT);
INSERT INTO tab_a VALUES (1), (2), (3), (4);
INSERT INTO tab_b VALUES (1), (1), (2), (2);
CONNECT RESET;
A DB2 Command Line Processor script file containing the commands and statements shown in
the exhibit was executed successfully.
If the statement shown below is executed:
UPDATE tab_a SET col1 = 10 WHERE col1 IN (SELECT * FROM tab_b);
How many rows in table TAB_A will be modified?

A. 3

B. 0

C. 1

D. 2

Correct Answer: D

Question 7

.Click the Exhibit button. The database shown below exists on a remote server: Database name: MYDB Host IP: 12.34.56.78 DB2 Port: 56789 User: remote password: password This database has been cataloged on the local server with an alias of MYREMDB. You are developing a CLI/ODBC application that must establish a connection to the remote database shown in the exhibit. What are two ways by which you can connect to the database? (Choose two.)

A. SQLConnect(hdbc, "mydb", SQL_NTS, "remote", SQL_NTS, "password", SQL_NTS)

B. SQLDriverConnect(hdbc, (SQLHWND)NULL, "DSN=myremdb;UID=remote;PWD=password",
SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT)

C. SQLConnect(hdbc, "myremdb", SQL_NTS, "remote", SQL_NTS, "password", SQL_NTS)

D. SQLDriverConnect(hdbc, (SQLHWND)NULL, "DSN=mydb;UID=remote;PWD=password",
SQL_NTS,
NULL, 0,
NULL, SQL_DRIVER_NOPROMPT)

Correct Answer: B,C

Question 8

In a PHP script, which ibm_db2 functions can be used to retrieve the result set of a query executed against a database table?

A. db2_fetch_array()

B. db2_result()

C. db2_execute()

D. db2_next_result()

Correct Answer: A

Question 9

Which statement should be used to obtain the result set shown below?
<Root>
<First>Hello</First>
<Second>World !</Second>
<?Test Pass test?>
</Root>

A. VALUES XMLDOCUMENT(
XMLELEMENT (NAME "Root",
XMLFOREST(
'Hello' as "First",
'World !' as "Second"
),
XMLCOMMENT ('Test Pass test')
)
)

B. VALUES XMLDOCUMENT(
XMLELEMENT (NAME "Root",
XMLELEMENT(NAME "First", 'Hello'),
XMLELEMENT(NAME "Second", 'World !'),
XMLATTRIBUTES ( 'Pass test' as "test")
)
)

C. VALUES XMLDOCUMENT(
XMLELEMENT (NAME "Root",
XMLFOREST(
'Hello' as "First",
'World !' as "Second"
),
XMLPI (NAME "Test", 'Pass test')
)
)

D. VALUES XMLDOCUMENT(
XMLELEMENT (NAME "Root",
XMLELEMENT(NAME "First", 'Hello'),
XMLELEMENT(NAME "Second", 'World !'),
XMLTEXT ('Test Pass test')
)
)

Correct Answer: C

Question 10

For which two database objects can an ALIAS be created? (Choose two.)

A. module

B. schema

C. trigger

D. sequence

Correct Answer: A,D

Welcome to TestSimulate

IBM DB2 9.7 Application Development (C2090-543) Free Practice Test