IBM IBM Certified Database Associate C2090-730 Questions & Answers

• Question 181:

  Given the following query: SELECT last_name, first_name, age, hire_date FROM employee WHERE age > 40 Which of the following clauses must be added to return the rows sorted by AGE, oldest first, and by LAST_NAME, from A to Z?

  A. SORT BY age ASC,last_name

  B. SORT BY age DESC,last_name

  C. ORDER BY age DESC,last_name

  D. ORDER BY age ASC,last_name

  Correct Answer: C

• Question 182:

  Which of the following will DELETE all of the rows from table T03?

  A. DELETE * FROM TABLE T03

  B. DELETE ALL FROM T03

  C. DELETE * FROM T03

  D. DELETE FROM T03

  Correct Answer: D

• Question 183:

  Given the following function: CREATE FUNCTION emplist () RETURNS TABLE ( id CHAR(6) , firstname VARCHAR(12) , lastname VARCHAR(15) ) LANGUAGE SQL BEGIN ATOMIC RETURN SELECT EMPNO, FIRSTNME, LASTNAME FROM EMPLOYEE WHERE WORKDEPT IN ('A00', 'B00'); END

  How can this function be used in an SQL statement?

  A. SELECTTABLE(EMPLIST()) FROM EMPLOYEE

  B. SELECTTABLE(EMPLIST()) AS t FROM EMPLOYEE

  C. SELECTEMPLIST(id, firstname, lastname) FROM EMPLOYEE

  D. SELECT id,firstname, lastname FROM TABLE(EMPLIST()) AS t

  Correct Answer: D

• Question 184:

  Given the following statements:

  CREATE TABLE tab1 (col1 INT);

  CREATE TABLE tab2 (col1 INT);

  CREATE TRIGGER trig1 AFTER UPDATE ON tab1

  REFERENCING NEW AS new1

  FOR EACH ROW MODE DB2SQL

  INSERT INTO tab2 VALUES(new1.col1);

  INSERT INTO tab1 VALUES(2),(3);

  What is the result of the following query?

  SELECT count(*) FROM tab2;

  A. 3

  B. 2

  C. 1

  D. 0

  Correct Answer: D

• Question 185:

  A number of applications issue the following SQL statement:

  SELECT d.deptno, e.empno, e.salary FROM department d INNER JOIN employee e ON d.deptno = e.deptno

  A database administrator wishes to store this query within the database. Which of the following database

  objects can be used to accomplish this?

  A. Alias

  B. View

  C. Schema

  D. Trigger

  Correct Answer: B

• Question 186:

  The following statements:

  CREATE TABLE t1 (col1 INT NOT NULL, PRIMARY KEY (col1)); CREATE TABLE t2 (col1 INT NOT NULL,

  col2 CHAR(1) NOT NULL, PRIMARY KEY (col1, col2), FOREIGN KEY (col1) REFERENCES t1 (col1)

  ON DELETE CASCADE ON UPDATE RESTRICT);

  CREATE TABLE t3 (col1 INT NOT NULL, col2 INT NOT NULL, PRIMARY KEY (col1, col2), FOREIGN KEY

  (col1) REFERENCES t1 (col1)

  ON DELETE NO ACTION ON UPDATE RESTRICT);

  INSERT INTO t1 VALUES (1), (2);

  INSERT INTO t2 VALUES (1, 'a'), (1, 'b'), (2,'c');

  INSERT INTO t3 VALUES (1, 100), (2, 200), (2,300);

  How many rows will be deleted by the following DELETE statement? DELETE FROM t1 WHERE col1 = 1;

  B. 3

  C. 1

  D. 0

  Correct Answer: D

• Question 187:

  Which of the following are types of routine objects?

  A. Package and function

  B. Function anduserexit

  C. Procedure and package

  D. Function and procedure

  Correct Answer: D

• Question 188:

  A stored procedure object is created into which DB2 object?

  A. Alias

  B. Schema

  C. Package

  D. Routine Space

  Correct Answer: B

• Question 189:

  An Alias can be an alternate name for which DB2 object?

  A. Sequence

  B. Trigger

  C. Schema

  D. View

  Correct Answer: D

• Question 190:

  Which of the following objects contains control structures that are considered the bound form of SQL statements?

  A. UDT

  B. Trigger

  C. Package

  D. Access plan

  Correct Answer: C