IBM IBM Certified Solution Developer C2090-545 Questions & Answers

• Question 111:

  Click the Exhibit button.

  

  Referring to theexhibit, how many rows will be returned by the SQL query shown below?

  SELECT * FROM TABLE(getnumemployee(21?) AS dSELECT * FROM TABLE(getnumemployee(?21?) AS d

  A. 0

  B. 1

  C. 7

  D. 10

  Correct Answer: C

• Question 112:

  Which three optional clauses can be used whencreating an external function? (Choose three.)

  A. SCRATCHPAD

  B. NOTEPAD

  C. LANGUAGE

  D. EXTERNAL NAME

  E. DATABASEINFO

  Correct Answer: ACD

• Question 113:

  Which statement is permitted within a scalar user-defined function body?

  A. COMMIT

  B. INSERT

  C. SIGNAL

  D. LOOP

  Correct Answer: C

• Question 114:

  Click the Exhibit button.

  

  A user-defined function was created using the statement shown in the exhibit.

  Which additional option can be added to the CREATE FUNCTION statement to tell the optimizer that the function doesnot always return the same results for a given argument value?

  A. NO EXTERNAL ACTION

  B. NOT FENCED

  C. NOT DETERMINISTIC

  D. STATIC DISPATCH

  Correct Answer: C

• Question 115:

  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

  Correct Answer: C