IBM 000-543 問題集

質問 1：
Which set of SQL statements must be run prior to the EXECUTE statement shown below so that
the
INSERT statement can be executed successfully?
EXECUTE s1 USING :v1;
A. SET :stmt = 'INSERT INTO tab1 VALUES(:v1)'; PREPARE s1 FROM :stmt;
B. SET :stmt = 'INSERT INTO tab1 VALUES(?)'; PREPARE s1 FROM :stmt
C. SET :stmt = 'INSERT INTO tab1 VALUES(?)'; PREPARE s1 FROM :stmt DESCRIBE :v1;
D. SET :stmt = 'INSERT INTO tab1 VALUES(?v1)'; PREPARE s1 FROM :stmt;
正解：B

質問 2：
While developing a Java JDBC application, you use the set of statements shown below:
Connection con;
PreparedStatementpstmt;
intnumUpd;
pstmt= con.prepareStatement("UPDATE EMPLOYEE SET PHONENO='4657' WHERE
EMPNO=?");
pstmt.setString(0, "000010");
numUpd= pstmt.executeUpdate();
pstmt.close();
Assuming a connection has already been established, which statement is true?
A. Statements will be executed successfully.
B. Statements will give a syntax error.
C. Statements will give a runtime exception.
D. Statements will execute successfully, but will not update rows in the database.
正解：C

質問 3：
Click the Exhibit button. PRODUCTS ID NAME QUANTITY
1 Orange juice 30
2 Milk 25 Given the table shown in the exhibit.
Which values will be returned by query shown below?
SELECT XMLGROUP(
id as "Id",name as "Name",quantity as "Quantity"
OPTION ROW "Product") as Products
FROM products
A.
1Orange
juice30 2Milk25
B.
1Orange juice30
2Milk25

C.
1Orange juice30
2Milk25

D. 1Orange juice30
2MIlk25
正解：B

質問 4：
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 USER1 will receive an error.
C. User USER2 receives an error immediately.
D. User USER2 will be able to access the table successfully.
正解：A

質問 5：
Given the table shown below:
COURSES
ID CODE NAME
1 ECE100 Operating Systems
2 ECE101 Programming Languages
User USER1 executes the following statements:
DECLARE GLOBAL TEMPORARY TABLE tempcourses
LIKE db2user.courses
ON COMMIT PRESERVE ROWS;
INSERT INTO session.tempcourses SELECT * FROM db2user.courses;
INSERT INTO session.tempcourses VALUES (3, 'ECE102', 'Databases');
SELECT max(ID) FROM db2user.courses;
User USER2 executes the following statements:
DECLARE GLOBAL TEMPORARY TABLE tempcourses
LIKE db2user.courses;
ON COMMIT PRESERVE ROWS;
INSERT INTO session.tempcourses SELECT * FROM db2user.courses;
INSERT INTO session.tempcourses VALUES (4, 'ECE103', 'Algorithms');
SELECT max(ID) FROM session.tempcourses;
Which results do USER1 and USER2 obtain?
A. User USER1 obtains the value 3; User USER2 obtains the value 4
B. User USER1 obtains the value 2; User USER2 obtains the value 2
C. User USER1 obtains the value 2; User USER2 obtains the value 4
D. User USER1 obtains the value 2; User USER2 obtains the value 3
E. User USER1 obtains no value; User USER2 obtains no value
正解：C

質問 6：
Click the Exhibit button. VEMPLOYEE
EMPNO CHAR(6) LASTNAME VARCHAR(30) SALARY DEC(9,2) DEPTNO CHAR(3) VDEPARTMENT
DEPTNO CHAR(3) DEPTNAME VARCHAR(20) MGRNO CHAR(6) VPROJECT
PROJNO CHAR(6) PROJNAME VARCHAR(20) DEPTNO CHAR(6)
RESPEMP CHAR(6)
A JOIN operation references three views in the exhibit; the SELECT statement used does not
contain a WHERE clause.
When the SELECT statement is executed, what will the result set contain?
A. unordered rows from one of three tables
B. one row from each table
C. the Cartesian product
D. no rows
正解：C

質問 7：
.Click the Exhibit button.
<personinfo>
<name>John Wayne</name>
<address type="private">
<city>New York</city>
<country>US</country>
<phone>850-734-6672</phone>
</address>
<phone type="mobile">796-858-1272 </phone>
<phone type="security">646-252-1053</phone>
</personinfo>
<personinfo>
<name>Barbara Wayne</name>
<address type="private">
<city>New York</city>
<country>US</country>
<phone>850-734-6672</phone>
</address>
<phone type="mobile">796-858-1231 </phone>
<phone type="security">646-252-1153</phone>
</personinfo>
The table PERSON is declared as shown below:
CREATE TABLE person (id BIGINT, info XML)
The documents shown in the exhibit are successfully inserted into the table.
How many phone numbers will be affected by the statement shown below?
UPDATE xmlapp.person
SET info = xmlquery( 'copy $new := $INFO
modify for $j in $new/personinfo//phone return
do replace value of $j with "444-444-4444"
return $new' )
WHERE XMLEXISTS('$INFO/personinfo[name="John Wayne"]')
A. 0
B. 3
C. 4
D. 6
正解：B