IBM IBM Certified Database Associate C2090-730 Questions & Answers

• Question 101:

  Which of the following are all valid DB2 data types?

  A. LONG VARCHAR, SMALLINT, NUMBER, BLOB

  B. DECIMAL, DATE, DBCLOB, INTERVAL

  C. NUMERIC, TIMESTAMP, BYTE, FLOAT

  D. NUM, TIME, XML, DOUBLE

  Correct Answer: D

• Question 102:

  A view is created with the following statement: CREATE VIEW v1 AS SELECT col1, col2, col3, col4 FROM t1 WHERE col4 > 1000 WITH CHECK OPTION

  What is the effect of the CHECK OPTION clause?

  A. Any row inserted or updated through view V1 must meet the condition that col4 > 1000.

  B. From now on, any row inserted or updated in table T1 must meet the condition that col4 > 1000, but existing rows in the table are not checked.

  C. At view creation, DB2 will check the data in table T1, and if in any row doesn't meet the condition col4 > 1000, the view creation will be rejected.

  D. Any row inserted or updated through view V1 must meet the condition that col4 > 1000 and no row in table T1 can be updated such that col4 <= 1000, but new rows in the table can be inserted with col4 <= 1000.

  Correct Answer: A

• Question 103:

  What type of constraint can be used to ensure that, in any given row in a table, the value of one column never exceeds the value of another column?

  A. Check

  B. Range

  C. Referential

  D. Informational

  Correct Answer: A

• Question 104:

  Which of the following will be a consequence of defining the column IDCOL2 in TABLE2 as a foreign key referencing the primary key (IDCOL1) of TABLE1?

  A. DB2 will no longer allow updating the value of IDCOL1 in TABLE1.

  B. When inserting a row in TABLE2, the only values that DB2 will allow for IDCOL2 are the existing values of IDCOL1.

  C. When inserting a row in TABLE2, DB2 will only allow foreign values forIDCOL2, that is values which do not exist in IDCOL1.

  D. When a SELECT statement joins TABLE1 with TABLE2, DB2 will automatically add the condition TABLE1.IDCOL1=TABLE2.IDCOL2 if not specified in the statement.

  Correct Answer: B

• Question 105:

  Which of the following is NOT a characteristic of a declared temporary table?

  A. Declared temporary tables are not persistent and can only be used by the application that creates them

  B. It is possible for many applications to create declared temporary tables that have the same name

  C. Declared temporary tables are created by executing a CREATE TABLE statement with the DECLARED GLOBAL TEMPORARY clause specified

  D. Once the application that created a global temporary table is terminated, any records in the table are deleted and the table is automatically destroyed

  Correct Answer: C

• Question 106:

  If the following SQL statement is executed:

  CREATE TABLE sales

  (invoice_no NOT NULL PRIMARY KEY,

  sales_date DATE,

  sales_amt NUMERIC(7,2))

  IN tbsp0, tbsp1, tbsp2, tbsp3

  PARTITION BY RANGE (sales_date NULLS FIRST)

  (STARTING '1/1/2007' ENDING '12/31/2007'

  EVERY 3 MONTHS)

  Which of the following statements is true?

  A. Administrative tasks such as backing up, restoring, and reorganizing data stored in the SALES table must be done at the table level; not at the partition level

  B. Data can be quickly rolled in and out of the SALES table by using the ATTACH PARTITION and DETACH PARTITION clauses of the ALTER TABLE statement

  C. If an index is created for the SALES table, its data must be stored in table space TBSP0

  D. When resolving queries against the SALES table, each partition used is scanned asynchronously and the results of each partition scan are merged to produce the result data set returned

  Correct Answer: B

• Question 107:

  Which of the following CREATE TABLE statements will NOT be successful?

  A. CREATE TABLE t1 (c1 XML NOT NULL UNIQUE, c2 INT)

  B. CREATE TABLE t1 (c1 XML NOT NULL, c2CHAR(20))

  C. CREATE TABLE t1 (c1 XML CHECK (c1 IS VALIDATED), c2 INT)

  D. CREATE TABLE t1 (c1 XML, c2 XML)

  Correct Answer: A

• Question 108:

  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 109:

  Which of the following actions will NOT cause a trigger to be fired?

  A. INSERT

  B. DELETE

  C. ALTER

  D. UPDATE

  Correct Answer: C

• Question 110:

  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