C2090-545 Online Practice Questions and Answers

Given the following SQL:

Which statement is incorrect?

A. The procedure declarationrequires the DYNAMIC RESULT SETS 1 clause in order to return a result set.

B. The cursor declaration requires the WITH RETURN TO CLIENT clause in order to return a result set.

C. The cursor declaration requires the WITH RETURN TO CALLER clause in order toreturn a result set.

D. The cursor declaration requires the WITH RETURN clause in order to return a result set.

Which statement is TRUE about associative arrays?

A. Associative array values can be stored in table columns.

B. The index values for an associative array must be a continuous set of integer values.

C. Associative array cannot be atype of a table column.

D. The index data type for an associated array must be an integer.

Which statement will successfully create an SQL procedure that returns the name of the current month?

A. CREATE PROCEDURE proc.current_month(OUT month VARCHAR(20)) BEGIN DECLARE today DATE; SET (today = CURRENT_DATE); SET month = MONTHNAME(today); END

B. CREATE PROCEDURE proc.current_month(OUT month VARCHAR(20)) BEGIN DECLARE today DATE; SELECT (CURRENT_DATE) INTO today; SET month = MONTHNAME(today); END

C. CREATE PROCEDURE proc.current_month(OUT month VARCHAR(20)) BEGIN DECLARE today DATE; VALUES (CURRENT_DATE) INTO today; SET month = MONTHNAME(today); END

D. CREATE PROCEDURE proc.current_month(OUT month VARCHAR(20)) BEGIN SET month = MONTHNAME(SELECT (CURRENT_DATE)) END

If table TAB1 were created as follows:CREATE TABLE tab1 (col1 INT NOT NULL); Which statement illustrates the proper way to define an anchor variable that references column COL1 in table TAB1?

A. DECLARE var1 REFERENCES tab1.col1

B. DECLARE var1 tab1.col1%TYPE

C. DECLARE var1 ANCHOR DATA TYPETO tab1.col1

D. DECLARE var1 ANCHOR DATA TYPE TO tab1.col1%TYPE

Assuming C1 and C2 are cursor type variables, which of the following operations cannot be performed?

A. IF (c1 = c2)THEN ?END;

B. SET c2 = c1;

C. SET c1 = CURSOR FOR SELECT * FROM t;

D. SET c1 = CURSOR FOR VALUES ('a', 'b', 'c');

Which statement will create a scalar function named FCN1?

A. CREATE SCALAR FUNCTION fcn1(v1 CHAR(3)) RETURNS CHAR(3) ?CREATE SCALAR FUNCTION fcn1(v1 CHAR(3)) RETURNS CHAR(3)

B. CREATE FUNCTION fcn1(v1 CHAR(3)) RETURNS ROW CONTAINING CHAR(3) ?CREATE FUNCTION fcn1(v1 CHAR(3)) RETURNS ROW CONTAINING CHAR(3)

C. CREATE SCALAR FUNCTION fcn1(v1 CHAR(3)) RETURNS ROW CONTAINING CHAR(3) ?CREATE SCALAR FUNCTION fcn1(v1 CHAR(3)) RETURNS ROW CONTAINING CHAR(3)

D. CREATE FUNCTION fcn1(v1 CHAR(3)) RETURNS CHAR(3) ?CREATE FUNCTIONfcn1(v1 CHAR(3)) RETURNS CHAR(3)

Which statementdescribes what must be done to create an SQL procedure that returns a result set?

A. Specify the clause DYNAMIC RESULT SETS 1 in the CREATE PROCEDURE statement; declare a cursor within the procedure body; open the cursor; exit the procedure without closing the cursor.

B. Specify the clause DYNAMIC RESULT SETS 1 in the CREATE PROCEDURE statement; code a SELECT statement in the procedure body.

C. Execute the CREATE PROCEDURE statement using the defaults; declare a cursor within the procedure body; open the cursor; exit the procedure without closing the cursor.

D. Execute the CREATE PROCEDURE statement using the defaults; declare a cursor within the procedure body; open the cursor; retrieve each row into output variables; close the cursor before exiting the procedure.

Which statement is true about the NUMBER data type?

A. In DB2, theNUMBER data type has maximum precision of 31; in Oracle, it has a maximum precision of 38.

B. In DB2, the NUMBER data type is the same as in Oracle, provided the DB2 database was created in Oracle compatibility mode.

C. Declaring a column as NUMBER(10,-2)is valid both in Oracle and in DB2 provided the DB2 database was created in Oracle compatibility mode.

D. Declaring a column as NUMBER(6,8) is valid both in Oracle and in DB2 provided the DB2 database was created in Oracle compatibility mode.

Which of the following is the simplest way to define a recordwith fields corresponding to all of the columns that are fetched from a cursor or cursor variable?

A. Using the VARRAY attribute

B. Using the READ FROM CURSOR attribute

C. Using the %ROWTYPE attribute

D. Using the %CURSOR attribute

Which two statements describe a CASE statement? (Choose two.)

A. CASE statements are used to enter into some logic based on a literal value.

B. CASE statements are used to enter into some logicbased on the value of an expression.

C. CASE statements are used to return control to the beginning of an expression.

D. CASE statements are used to enter into some condition and loop until the condition is met.

E. CASE statements are used to iterate intosome logic based on a literal value.