A2090-543問題集、IBM実際の試験問題

質問 1

Click the Exhibit button.
CONNECT TO test;
CREATE TABLE project (projno CHAR(6), deptno CHAR(3), projname VARCHAR(20));
CREATE TABLE employee (empno CHAR(6), lastname VARCHAR(25), deptno CHAR(3));
COMMIT;
CONNECT RESET;
A DB2 Command Line Processor script file containing the commands and statements shown in
the exhibit was executed successfully.
Which query will produce a list of all projects and all employees assigned to projects and also
include employees that have not been assigned a project?

A. SELECTempno, lastname, workdept, projname FROM employee RIGHT OUTER JOIN project ON employee.deptno = project.deptno

B. SELECTempno, lastname, employee.deptno, projname FROM employee JOIN project ON employee.deptno = project.deptno

C. SELECTempno,lastname, employee.deptno, projname FROM employee FULL OUTER JOIN project ON employee.deptno = project.deptno

D. SELECTempno, lastname, employee.deptno, projname FROM employee LEFT OUTER JOIN project ON employee.deptno = project.deptno

正解: D

質問 2

Which type of join condition specifies that the result table will contain a row for each row from the Table to the left, concatenated with each row from the table to the right?

A. INNER JOIN

B. FULL OUTER JOIN

C. EXCEPTION JOIN

D. CROSS JOIN

正解: D

質問 3

A table and a sequence were created with the statements shown below: CREATE TABLE orders(oid INTEGER, info VARCHAR(200));
CREATE SEQUENCE order_num AS INTEGER
START WITH 1;
Autocommitis on and the statements shown are executed:
INSERT INTO orders VALUES (NEXT VALUE FOR order_num, 'Order A');
INSERT INTO orders VALUES (PREVIOUS VALUE FOR order_num, 'Order B');
SELECT * FROM orders;
What is the output?

A. OID INFO 1 Order A 2 Order B

B. OID INFO 0 Order A 1 Order B

C. OID INFO 1 Order A 1 Order B

D. OID INFO 2 Order A 2 Order B

正解: C

質問 4

The table shown below exists in the database:
CREATE TABLE team.workitems ( priority INTEGER, last_update TIMESTAMP, details XML )
User USER1 has CREATEIN authority on the TEAM schema and can SELECT from the
TEAM.WORKITEMS table. An SQL routine is created that performs a positioned update using a
cursor declared as shown below:
DECLARE c_workitem CURSOR FOR SELECT priority, last_update FROM team.workitems FOR
UPDATE;
Which additional privilege is required?

A. ALTER privilege on TEAM.WORKITEMS table

B. UPDATE privilege on TEAM.WORKITEMS table

C. BINDADD privilege on TEAM schema

D. EXECUTE privilege on TEAM schema

正解: B

質問 5

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

C. 0

D. 2

正解: D

質問 6

Click the Exhibit button. CREATE TABLE s1.mytab (col1 INTEGER GENERATED ALWAYS AS IDENTITY, col2 INTEGER, col3 INTEGER, CHECK (col1+col3 < 500) CREATE VARIABLE s1.var1 INTEGER DEFAULT (99) CREATE FUNCTION s1.add100 (p1 INT, p2 INT) RETURNS INTEGER LANGUAGE SQL DETERMINISTIC NOT FENCED BEGIN SET p1= p2+100; RETURN p1;END Given the table, variable, and function definitions shown in the exhibit, which two statements contain a valid invocation of the add100 function? (Choose two.)

A. SELECT col1,outcol=add100(col2,col3) FROM mytab

B. SELECT outcol.* FROM TABLE(add100(col2,col3)) ASoutcol

C. SET var1 = ABS(add100(1,99))

D. SELECT col1, add100(col2,col3) ASoutcol FROM mytab

正解: C,D

質問 7

Given the table COURSES shown below: COURSES
ID CODE NAME 1 ECE100 Operating Systems 2 ECE101 Programming Languages 3 ECE102 Intro to Databases 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; SELECT max(ID) FROM session.tempcourses; Assuming autocommit is on, which result will user USER1 obtain?

A. no value

B. value 2

C. value 1

D. value 3

正解: D

質問 8

The table PERSON is declared as shown below:
CREATE TABLE xmltest (id BIGINT, info XML)
What is the column type for the result of the following statement?
SELECT t.* FROM
xmltest,
XMLTABLE (
'$INFO/question'
) AS t

A. XML

B. CLOB

C. BLOB

D. VARCHAR

正解: A

質問 9

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), (2), (2), (3);
CONNECT RESET;
A DB2 Command Line Processor script file containing the commands and statements shown in
the exhibit executed successfully.
If the statement shown below is executed:
DELETE FROM tab_a WHERE col1 IN (SELECT DISTINCT * FROM tab_b);
How many rows will be removed from table TAB_A?

A. 3

B. 1

C. 0

D. 2

正解: A

質問 10

You have created a stored procedure (MULTIRESULTS()) on the server which will return multiple
result sets. Whiledeveloping a PHP application using the IBM_DB2 extension, you want to call the
stored procedure. The code shown below will execute the stored procedure:
$stmt = db2_exec($conn, 'CALL multiResults()');
How can you fetch all of the result sets from $stmt?

A. Call db2_next_result($stmt) for each result set; call db2_fetch_object($stmt) for each row in the result set.

B. Call db2_next_result($stmt) for each result set.

C. Call db2_fetch_object($stmt) for the first result set; call db2_next_result($stmt) for more result sets.

D. Call db2_fetch_object($stmt) for each result set.

正解: C

IBM Assessment: DB2 9.7 Application Development - A2090-543 模擬練習