IBM IBM Certified Solution Developer C2090-545 Questions & Answers

• Question 51:

  Given the function shown below:

  

  Which statement will execute successfully?

  A. SELECT to_upper('Seize the day!')

  B. SELECT * FROM to_upper_fcn('Seize the day!')

  C. SELECT to_upper('Seize the day!') FROM SYSIBM.SYSDUMMY1;

  D. SELECT * FROM TABLE(to_upper_fcn('Seize the day!')) AS T

  Correct Answer: C

• Question 52:

  Click the Exhibit button.

  

  An SQL function was created using the statement shown in the exhibit. Assume this function is invoked by executing the SQL statementshown below:

  VALUES e_degree(SMALLINT(17))

  What will be returned?

  A. SQL0440N No authorized routine named "E_DEGREE"of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884

  B. 1

  BACHELOR

  1 record(s) selected.

  C. 1

  MASTER

  1 record(s) selected.

  D. 1 UNKNOWN

  1 record(s) selected.

  Correct Answer: D

• Question 53:

  Given the function:

  

  What is the output of the statement: VALUES LENGTH(fcn1(' one good day '))?

  A. 'one good day'

  B. 12

  C. ' one good day '

  D. 14

  Correct Answer: B

• Question 54:

  Click the Exhibit button.

  

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

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

  A. NO EXTERNAL ACTION

  B. NOT FENCED

  C. NOT DETERMINISTIC

  D. STATICDISPATCH

  Correct Answer: C

• Question 55:

  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.

  Correct Answer: C

• Question 56:

  Which SQL statement is allowed in a BEFORE trigger?

  A. INSERT

  B. CALL

  C. MERGE

  D. UPDATE

  Correct Answer: B

• Question 57:

  A trigger was created using the CREATE TRIGGER statement shown below.

  

  Which statement is true about the INCRS_COMM trigger?

  A. The trigger will be executed ifthe STAFF table is populated by a load operation.

  B. The trigger will be executed before a row is inserted into the STAFF table.

  C. This trigger will be executed after a row is inserted into the STAFF table.

  D. This trigger will increase the COMM value ofa newly inserted row by 10 percent.

  Correct Answer: C

• Question 58:

  Given the SQL statement shown below:

  

  Which privilege allows a user to execute the CREATE TRIGGER statement shown?

  A. SYSCTRL authority

  B. SELECT and UPDATE privilege on the EMPLOYEE table

  C. SYSMAINTauthority

  D. SELECT and ALTER privilege on the EMPLOYEE table

  Correct Answer: B

• Question 59:

  Which CREATE TRIGGER statement is valid?

  A. CREATE TRIGGER test1 NO CASCADE AFTER INSERT ON employee REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL WHEN (n.bonus ISNULL) SET n.salary = n.salary + 2000

  B. CREATE TRIGGER test1 NO CASCADE BEFORE INSERT ON employee REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL WHEN (n.bonus IS NULL) SET n.salary = n.salary + 2000

  C. CREATE TRIGGER test1 AFTER DELETE ON employee REFERENCING OLD_TABLE AS oldemployee FOR EACH STATEMENT MODE DB2SQL DELETE FROM staff WHERE id IN (SELECT empno FROM oldemployee)

  D. CREATE TRIGGER test1 NO CASCASE BEFORE DELETE ON employee FOR EACH STATEMENT MODE DB2SQL DELETE FROM staff WHERE id IN (SELECT empnoFROM oldemployee)

  Correct Answer: B

• Question 60:

  Click the Exhibit button.

  

  The table and triggers in the exhibit have been createdsuccessfully, when the statements shown below are executed.

  INSERT INTO emp_hours VALUES('KAY', '9:00', '17:00');

  INSERT INTO emp_hours (emp_name, starting) VALUES('BILL', '9:00');

  What is the result?

  A. Table EMP_HOURS will contain these rows: KAY 9:00:00 AM 5:00:00 PM BILL 9:00:00 AM 10:00:00 AM

  B. Table EMP_HOURS will contain this row: BILL 9:00:00 AM

  C. Table EMP_HOURS will contain these rows: KAY 9:00:00 AM 5:00:00 PM BILL 9:00:00 AM

  D. Table EMP_HOURS will contain this row: BILL 9:00:00 AM 17:00:00 PM

  Correct Answer: A