IBM Assessment: DB2 9.7 Application Development A2090-543 Prüfungsfragen mit Lösungen:

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

2. 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 FULL OUTER JOIN tab2 ON tab1.letter = tab2.letter; How many rows will be returned?

A) 6
B) 3
C) 2
D) 4

3. .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) SQLDriverConnect(hdbc, (SQLHWND)NULL, "DSN=mydb;UID=remote;PWD=password",
SQL_NTS,
NULL, 0,
NULL, SQL_DRIVER_NOPROMPT)
B) SQLDriverConnect(hdbc, (SQLHWND)NULL, "DSN=myremdb;UID=remote;PWD=password",
SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT)
C) SQLConnect(hdbc, "mydb", SQL_NTS, "remote", SQL_NTS, "password", SQL_NTS)
D) SQLConnect(hdbc, "myremdb", SQL_NTS, "remote", SQL_NTS, "password", SQL_NTS)

4. Click the Exhibit button.
CREATE TABLE prod_type (prod_type VARCHAR(10), description VARCHAR (30));
CREATE TABLE prod_descr(info XML);
INSERT INTO prod_descr VALUES
('<product id="1">
<name>Orange juice</name>
<company>Orange Garden</company>
<type>Drinks</type>
</product>'),
('<product id="2">
<name>Coke</name>
<company>Best drinks</company>
<type>Drinks</type>
<type>Premium</type>
</product>');
INSERT INTO prod_type VALUES ('Drinks','Drinks');
You execute the SQL statement shown in the exhibit.
Which two statements will return rows? (Choose two.)

A) SELECT name, company, description FROM
prod_type, prod_descr, XMLTABLE(
'$INFO/product'
COLUMNS
name VARCHAR(20) PATH './name',
company VARCHAR(20) PATH './company'
)
WHERE
prod_type= XMLCAST (XMLQUERY('$INFO/product/type') AS VARCHAR(10));
B) SELECT name, company, description FROM
prod_type, prod_descr, XMLTABLE(
'$INFO/product'
COLUMNS
name VARCHAR(20) PATH './name',
company VARCHAR(20) PATH './company'
)
WHERE
XMLEXISTS('$INFO/product[type = $PROD_TYPE]');
C) SELECT name, company, description FROM
prod_type, prod_descr, XMLTABLE(
'$INFO/product'
COLUMNS
type VARCHAR(10) PATH './type',
name VARCHAR(20) PATH './name',
company VARCHAR(20) PATH './company'
)
WHERE
type=prod_type;
D) SELECT name, company, description FROM
prod_type, prod_descr, XMLTABLE(
'$INFO/product[type=$PROD_TYPE]'
COLUMNS
name VARCHAR(20) PATH './name',
company VARCHAR(20) PATH './company'
);

5. Which three data types are supported as output of the XMLSERIALIZE function? (Choose three.)

A) CLOB
B) DOUBLE
C) GRAPHIC
D) CHAR
E) BLOB

Fragen und Antworten: