IBM IBM Certified Database Associate C2090-730 Questions & Answers

• Question 51:

  The following triggers were defined for a table named SALES in the order shown:

  CREATE TRIGGER trigger_a NO CASCADE BEFORE UPDATE ON sales REFERENCING NEW AS new FOR EACH ROW SET new.commission = sale_amt * .05 WHERE invoice = n.invoice;

  CREATE TRIGGER trigger_b AFTER INSERT ON sales REFERENCING NEW AS new FOR EACH ROW UPDATE sales SET bill_date = CURRENT DATE + 30 DAYS WHERE invoice = n.invoice;

  CREATE TRIGGER trigger_c NO CASCADE BEFORE DELETE ON sales FOR EACH ROW SIGNAL SQLSTATE '75005' SET MESSAGE_TEXT = 'Deletes not allowed!';

  Which of the following statements is NOT true?

  A. Once a row has been added to the SALES table, it cannot be removed

  B. Whenever a row is inserted into the SALES table, the value in the BILL_DATE column is automatically set to 30 days from today

  C. Each time a row is inserted into the SALES table, trigger TRIGGER_A is fired first, followed by trigger TRIGGER_B

  D. Whenever a row in the SALES table is updated, all three triggers are fired but nothing happens because none of the triggers have been coded to trap update operations

  Correct Answer: D

• Question 52:

  Given the following tables: TABLEA EMPIDNAME

  1 USER1 2 USER2

  TABLEB

  EMPID WEEKNO PAYAMT

  1 1 1000.00

  1 2 1000.00

  2 1 2000.00

  and the fact that TABLEB was defined as follows:

  CREATE TABLE tableb (empid SMALLINT,

  weekno SMALLINT,

  payamt DECIMAL(6,2),

  CONSTRAINT const1 FOREIGN KEY (empid)

  REFERENCES tablea(empid)

  ON DELETE NO ACTION)

  If the following command is issued:

  DELETE FROM tablea WHERE empid=2

  How many rows will be deleted from TABLEA and TABLEB?

  A. TABLEA - 0, TABLEB - 0

  B. TABLEA - 0, TABLEB - 1

  C. TABLEA - 1, TABLEB - 0

  D. TABLEA - 1, TABLEB - 1

  Correct Answer: A

• Question 53:

  Given the following statements:

  CREATE TABLE table1 (col1 INTEGER, col2 CHAR(3)); CREATE VIEW view1 AS SELECT col1, col2 FROM table1 WHERE col1 < 100 WITH LOCAL CHECK OPTION;

  Which of the following INSERT statements will execute successfully?

  A. INSERT INTO view1 VALUES (50,abc)

  B. INSERT INTO view1VALUES(100, abc)

  C. INSERT INTO view1VALUES(50, 'abc')

  D. INSERT INTO view1VALUES(100, 'abc')

  Correct Answer: C

• Question 54:

  When does a view get populated?

  A. When it is created

  B. When it is referenced in an INSERT statement

  C. The first time any executable SQL statement references it

  D. Any time an executable SQL statement references it

  Correct Answer: D

• Question 55:

  If the following statement is executed:

  CREATE TABLE tab1 (col1 INTEGER NOT NULL, col2 INTEGER,

  CONSTRAINT const1 FOREIGN KEY (col2)

  How many unique indexes are defined for table TAB1?

  A. 0

  B. 1

  C. 2

  D. 3

  Correct Answer: A

• Question 56:

  Which type of key is defined on the child table to implement a referential constraint?

  A. Unique key

  B. Primary key

  C. Foreign key

  D. Composite key

  Correct Answer: C

• Question 57:

  Which of the following is NOT true about schemas?

  A. If a schema name is not specified, either by qualifying a database object name or by executing the SET CURRENT SCHEMA statement, the authorization ID of the current session user is used as the schema name by default

  B. The value assigned to the CURRENT SCHEMA special register is persistent across database restarts

  C. A schema enables the creation of multiple objects in a database without encountering namespace collisions

  D. When most database objects are created, they are either implicitly or explicitly assigned to a schema

  Correct Answer: B

• Question 58:

  Given the following statement:

  CREATE TABLE tab1

  (col1 SMALLINT NOT NULL PRIMARY KEY,

  col2 VARCHAR(200) NOT NULL WITH DEFAULT NONE,

  col3 DECIMAL(5,2) CHECK (col3 >= 100.00),

  col4 DATE NOT NULL WITH DEFAULT)

  Which of the following definitions will cause the CREATE TABLE statement to fail?

  A. COL1

  B. COL2

  C. COL3

  D. COL4

  Correct Answer: B

• Question 59:

  Given the following scenario:

  Table TABLE1 needs to hold specific numeric values up to 9999999.999 in column COL1. Once TABLE1 is

  populated, arithmetic operations will be performed on data stored in column COL1.

  Which of the following would be the most appropriate DB2 data type to use for column COL1?

  A. INTEGER

  B. REAL

  C. NUMERIC(7, 3)

  D. DECIMAL(10, 3)

  Correct Answer: D

• Question 60:

  Which of the following is used to indicate a column will not accept NULL values and can be referenced in another table's foreign key specification?

  A. Check constraint

  B. Unique constraint

  C. Default constraint

  D. Informational constraint

  Correct Answer: B