C2090-545 Online Practice Questions and Answers

A developer needs to create a user-defined functionthat will return a list of employees who work in a particular department. Which statement will successfully create a function that meets this objective?

A. CREATE FUNCTION dept_employees (deptno CHAR(3)) RETURNS TABLE LANGUAGE SQL READS SQL DATA RETURN SELECT empno, lastname AS l_name, firstnme AS f_name FROM employee WHERE employee.workdept = dept_employees.deptno

B. CREATE FUNCTION dept_employees (deptno CHAR(3)) RETURNS TABLE DYNAMIC RESULT SETS 1 LANGUAGE SQL READS SQL DATA DECLARE emp_info CURSOR WITHRETURN FOR SELECT empno, lastname AS l_name, firstnme AS f_name FROM employee WHERE employee.workdept = dept_employees.deptno OPEN emp_info; RETURN

C. CREATE FUNCTION dept_employees (deptno CHAR(3)) RETURNS TABLE (empno CHAR(6), l_name VARCHAR(15), f_name VARCHAR(12)) LANGUAGE SQL READS SQL DATA RETURN SELECT empno, lastname AS l_name, firstnme AS f_name FROM employee WHERE employee.workdept = dept_employees.deptno

D. CREATE FUNCTION dept_employees (deptno CHAR(3)) RETURNS TABLE (empno CHAR(6), l_nameVARCHAR(15), f_name VARCHAR(12)) DYNAMIC RESULT SETS 1 LANGUAGE SQL READS SQL DATA DECLARE emp_info CURSOR WITH RETURN FOR SELECT empno, lastname AS l_name, firstnme AS f_name FROM employee WHERE employee.workdept = dept_employees.deptno OPEN emp_info; RETURN

Which procedure demonstrates the correct use of dynamic SQL?

A. CREATE PROCEDURE update_count1 (IN new_count INTEGER, IN item_code INTEGER) BEGIN DECLARE v_dynSQL VARCHAR(200); SET v_dynSQL = 'UPDATE stock SET quantity_on_hand=? WHERE item_number=?'; PREPARE v_stmt1 FROM v_dynSQL; EXECUTE v_stmt1 USING new_count, item_code; END

B. CREATE PROCEDURE update_count2 (IN tab_name VARCHAR(128), IN new_count INTEGER, IN item_code INTEGER) BEGIN DECLARE v_dynSQL VARCHAR(200); SET v_dynSQL = 'UPDATE ? SET quantity_on_hand=? WHERE item_number=?'; PREPARE v_stmt1 FROM v_dynSQL; EXECUTE v_stmt1 USING tab_name, new_count, item_code; END

C. CREATE PROCEDURE update_count4 (IN tab_name VARCHAR(128), IN col_name1 VARCHAR(128), IN col_name2 VARCHAR(128), IN new_count INTEGER, IN item_code INTEGER) BEGIN DECLARE v_dynSQL VARCHAR(200); SET v_dynSQL = 'UPDATE ? SET ?=? WHERE ?=?'; PREPARE v_stmt1 FROM v_dynSQL; EXECUTE v_stmt1 USING tab_name, col_name1,new_count, col_name2, item_code; END

D. CREATE PROCEDURE update_count5 (IN new_count INTEGER, IN item_code INTEGER) BEGIN DECLARE v_dynSQL VARCHAR(200); DECLARE v_col_name VARCHAR(128); SET v_col_name = 'item_number'; SET v_dynSQL = 'UPDATE stock SETquantity_on_hand=? WHERE ?=?'; PREPARE v_stmt1 FROM v_dynSQL; EXECUTE v_stmt1 USING new_count, v_col_name, item_code; END

The CREATE OR REPLACE PROCEDURE statement is similar semantically to which of the following combined statements?

A. DROP andCREATE PROCEDURE

B. ALTER and CREATE PROCEDURE

C. UPDATE and CREATE PROCEDURE

D. DROP and ALTER PROCEDURE

Which statement is true about the data types VARCHAR and VARCHAR2?

A. The VARCHAR data type cannot be used with a database thathas been created in Oracle compatibility mode.

B. The VARCHAR2 data type treats empty strings as null; the VARCHAR data type treats empty strings as not null with zero length.

C. The VARCHAR2 data type length is limited to 2000 bytes; the VARCHAR data type length can be up to 32K.

D. The VARCHAR2 data type cannot be used in a database that has not been created in Oracle compatibility mode.

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

Given the statement shown below:

What happens toNEWPROD if 'http://posample.org/product.xsd' is dropped?

A. NEWPROD will validate against a new schema.

B. NEWPROD will raise an exception.

C. NEWPROD is marked as inoperative.

D. NEWPROD is dropped.

Which statement will let you use the result set from the nested procedure CALLEE?

A. ASSOCIATE RESULT SET LOCATOR( loc1) WITH PROCEDURE callee;

B. BIND RESULT SET WITH PARAMETERS FOR PROCEDUREcallee;

C. INSERT RESULT SET FROM callee INTO CURSOR c1;

D. SELECT * FROM callee;

Given the following CREATE PROCEDURE statement:

In what order must declarations occur within the lines ap: BEGIN and END ap?

A. Variables, cursors, and condition handlers

B. Cursors, variables, condition handlers

C. Condition handlers, variables, cursors

D. Condition handlers, cursors, variables

In the code segment shown below:

Which statement accurately describes the result?

A. For activities whose description has not changed, update the description in the ARCHIVE table. For new activities, insert into the ARCHIVE table. The ARCHIVE and ACTIVITIES table both have ACTIVITY as a primary key.

B. For activities whose description has changed, update the description in the ARCHIVE table. For new activities, insert into the ARCHIVE table. The ARCHIVE and ACTIVITIES tableboth have ACTIVITY as a primary key.

C. For activities whose description has changed, update the description in the ACTIVITY table. For new activities, insert into the ACTIVITIES table. The ARCHIVE and ACTIVITIES table both have ACTIVITY as a primary key.

D. The statement will fail since MERGE is not a supported operator.

Given the procedure show below:

What will be the value of P2?

A. 50

B. 51

C. 100

D. 102