IBM C2090-543 - DB2 9.7 Application Development Exam
Page: 3 / 20
Total 100 questions
Question #11 (Topic: Topic 1)
Click the Exhibit button.
CREATE TABLE store(sid INTEGER, info XML);
INSERT INTO store VALUES (1,
'<storeinfo sid="1">
<name>Grocery A</name>
<items>
<fruit><name>Mango</name><price>1.20</price></fruit>
<fruit><name>Apple</name><price>0.50</price></fruit>
<dessert><name>Ice Cream</name><price>6.00</price></dessert>
</items>
</storeinfo>');
Given the statements shown in the exhibit, a user executes the query shown below:
XQUERY for $store in db2-fn:xmlcolumn('STORE.INFO')/storeinfolet $items :=
$store/items/fruit, $count := fn:count($items) return <itemcount>$count</itemcount>
What is the output?
CREATE TABLE store(sid INTEGER, info XML);
INSERT INTO store VALUES (1,
'<storeinfo sid="1">
<name>Grocery A</name>
<items>
<fruit><name>Mango</name><price>1.20</price></fruit>
<fruit><name>Apple</name><price>0.50</price></fruit>
<dessert><name>Ice Cream</name><price>6.00</price></dessert>
</items>
</storeinfo>');
Given the statements shown in the exhibit, a user executes the query shown below:
XQUERY for $store in db2-fn:xmlcolumn('STORE.INFO')/storeinfolet $items :=
$store/items/fruit, $count := fn:count($items) return <itemcount>$count</itemcount>
What is the output?
A. 2
B. <itemcount>2</itemcount>
C. <itemcount>1</itemcount>
D. <itemcount>$count</itemcount>
Answer: D
Question #12 (Topic: Topic 1)
Table T1 and view V1 were created by executing the statements shown below:
CREATE TABLE t1 (c1 INT CHECK (c1 < 275), c2 CHAR(3));
CREATE VIEW v1 AS SELECT c1, c2 FROM t1 WHERE c1 > 250
WITH LOCAL CHECK OPTION;
Which statement will execute successfully?
CREATE TABLE t1 (c1 INT CHECK (c1 < 275), c2 CHAR(3));
CREATE VIEW v1 AS SELECT c1, c2 FROM t1 WHERE c1 > 250
WITH LOCAL CHECK OPTION;
Which statement will execute successfully?
A. INSERT INTO t1 VALUES (275, 'abc')
B. INSERT INTO t1 VALUES (260, 'abc')
C. INSERT INTO v1 VALUES (250, 'abc')
D. INSERT INTO v1 VALUES (NULL, abc)
Answer: B
Question #13 (Topic: Topic 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?
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. alias
B. sequence
C. trigger
D. view
Answer: D
Question #14 (Topic: Topic 1)
Given the two tables shown below:
TAB1
LETTER GRADE
A 80
B 70
C 60
TAB2
LETTER GPA
A 4
C 2
D 1
and the successful execution of the following query:
SELECT * FROM tab1 RIGHT OUTER JOIN tab2 ON tab1.letter = tab2.letter;
How many rows will be returned?
TAB1
LETTER GRADE
A 80
B 70
C 60
TAB2
LETTER GPA
A 4
C 2
D 1
and the successful execution of the following query:
SELECT * FROM tab1 RIGHT OUTER JOIN tab2 ON tab1.letter = tab2.letter;
How many rows will be returned?
A. 1
B. 2
C. 3
D. 4
Answer: C
Question #15 (Topic: Topic 1)
Which condition will prevent a developer from using the DB2 Call Level Interface in an
application?
application?
A. The developer must control the cursor names to comply with company naming standards.
B. An SQL precompiler is not available to the developer.
C. The application must create an external scalar function with the CREATE FUNCTION statement.
D. The DECLARE CURSOR statement needs to be used.
Answer: D
