Which statement is TRUE about associative arrays?

• A. Associative array cannot be a type of a table column.
• B. The index data type for an associated array must be an integer.
• C. The index values for an associative array must be a continuous set of integer values.
• D. Associative array values can be stored in table columns.

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_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
• C. 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 SET quantity_on_hand=? WHERE ?=?';
  PREPARE v_stmt1 FROM v_dynSQL;
  EXECUTE v_stmt1 USING new_count, v_col_name, item_code;
  END
• D. 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

Which two statements are true about SQL procedures? (Choose 2)

• A. SQL procedures cannot be called from triggers.
• B. SQL procedures support recursion.
• C. SQL procedures support the PRINT statement.
• D. SQL procedures reside outside of the database for easy portability.
• E. SQL procedures may return multiple result sets to the caller or to a client application.

A developer needs to create a user-defined function that 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 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
• B. CREATE FUNCTION dept_employees (deptno CHAR(3)) RETURNS TABLE (empno CHAR(6), l_name VARCHAR(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
• C. 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
• D. 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