IBM IBM Certified Advanced Database Administrator C2090-610 Questions & Answers

• Question 21:

  Which statement about foreign keys is true?

  A. A table can have only one foreign key.

  B. Foreign keys can only be defined when a table is created.

  C. A foreign key can be defined on a column or a set of columns in a table.

  D. Foreign keys are enforced only during the execution of INSERT statements.

  Correct Answer: C

• Question 22:

  Which type of table is best suited for recording results from application data manipulation that is not required to be persistent?

  A. Base Table

  B. Temporal Table

  C. Transition Table

  D. Temporary Table

  Correct Answer: D

• Question 23:

  Which statement regarding referential integrity constraints is true?

  A. Multiple primary keys can be defined for a table.

  B. A foreign key can reference multiple primary keys.

  C. A primary key can be referenced by only one foreign key.

  D. Primary keys and foreign keys are used to define relationships.

  Correct Answer: D

• Question 24:

  Given the following statements:

  

  What will be the result?

  A. Joe Smith

  B. Joe Smith

  C. Joe Smith>DB2

  D. Joe Smith

  Correct Answer: B

• Question 25:

  A table named STATES has the following columns:

  STATE_CD, REGION_CD, and STATE_NAME.

  Which SQL statement will return the number of states in each region, ordered by number of states?

  A. SELECT state_cd, COUNT(*) FROM states ORDER BY COUNT(*);

  B. SELECT state_cd, COUNT(*) FROM states GROUP BY state_cd ORDER BY state_cd;

  C. SELECT region_cd, COUNT(*) FROM states GROUP BY state_cd ORDER BY COUNT(*);

  D. SELECT region_cd, COUNT(*) FROM states GROUP BY region_cd ORDER BY COUNT(*);

  Correct Answer: D

• Question 26:

  When a user-defined function (UDF) is created and no schema is specified, what schema is used to store the UDF?

  A. The SYSIBM schema.

  B. The SYSFUN schema.

  C. A schema with the name of the UDF.

  D. A schema with the userid of the UDF definer.

  Correct Answer: D

• Question 27:

  Which statement about UPDATE processing is FALSE?

  A. The UPDATE statement logs the before and after value of every column it modifies.

  B. A searched UPDATE is used to update one or more rows and a positioned UPDATE is used to update exactly one row.

  C. When the UPDATE statement modifies parent key columns, the value of corresponding foreign key columns are also modified.

  D. The UPDATE statement modifies the values of specified columns in the rows of a table, view, or underlying table(s) of a specified fullselect.

  Correct Answer: C

• Question 28:

  Which type of stored procedure is not associated with an external application program?

  A. A Java stored procedure.

  B. A REXX stored procedure.

  C. A native SQL stored procedure.

  D. An external SQL stored procedure.

  Correct Answer: C

• Question 29:

  Which SQL statement will retrieve the department number (WORKDEPT) and average departmental salary (SALARY) for all departments in a table named EMPLOYEE and arrange the result set in ascending order by average departmental salary?

  A. SELECT workdept, AVG(salary) FROM employee ORDER BY workdept GROUP BY 2

  B. SELECT workdept, AVG(salary) FROM employee ORDER BY workdept ARRANGE BY 2

  C. SELECT workdept, AVERAGE(salary) FROM employee GROUP BY workdept ORDER BY 2

  D. SELECT workdept, AVG(salary) FROM employee GROUP BY workdept ORDER BY 2

  Correct Answer: D

• Question 30:

  Which statement about system-period temporal tables is true?

  A. They store user-based period information.

  B. They do not have a separate history table.

  C. They can be queried without a time period specification.

  D. They manage data based on time criteria specified by users or applications.

  Correct Answer: C