IBM IBM Certified Database Associate C2090-730 Questions & Answers

• Question 61:

  A UDT is a data type which:

  A. must contain unique values.

  B. cannot be CAST to other data types.

  C. is created using the CREATE DISTINCT command.

  D. contains a value that was automatically calculated from values in other columns.

  Correct Answer: C

• Question 62:

  Which of the following DB2 data types has a fixed length?

  A. XML

  B. BLOB

  C. DOUBLE

  D. DBCLOB

  Correct Answer: C

• Question 63:

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

  A. DROP

  B. ALTER

  C. DELETE

  D. ROLLBACK

  Correct Answer: C

• Question 64:

  If the following statement is executed:

  CREATE TABLE employee

  (empid INT NOT NULL GENERATED BY DEFAULT

  AS IDENTITY (START WITH 1, INCREMENT BY 5),

  name VARCHAR(20),

  dept INT CHECK (dept BETWEEN 1 AND 20),

  hiredate DATE WITH DEFAULT CURRENT DATE,

  salary DECIMAL(7,2),

  PRIMARY KEY(empid),

  CONSTRAINT cst1 CHECK (YEAR(hiredate) > 2006 OR

  Salary > 60500));

  Which of the following INSERT statements will fail?

  A. INSERT INTO employee VALUES (15, 'Smith', 5, '01/22/2004', 92500.00)

  B. INSERT INTO employee VALUES (DEFAULT, 'Smith', 2, '10/07/2002', 80250.00)

  C. INSERT INTO employee VALUES (20, 'Smith', 5, NULL, 65000.00)

  D. INSERT INTO employee VALUES (DEFAULT, 'Smith', 10, '11/18/2004', 60250.00)

  Correct Answer: D

• Question 65:

  Which of the following is NOT a characteristic of a unique index?

  A. Each column in a base table can only participate in one uniqueindex, regardless of how the columns are grouped (the same column cannot be used in multiple unique indexes)

  B. In order for an index to be used to support a unique constraint, it must have been defined with the UNIQUE attribute

  C. A unique index cannot be created for a populated table if the key column specified contains more than one NULL value

  D. A unique index can only be created for a non-nullable column

  Correct Answer: D

• Question 66:

  Which of the following deletion rules on CREATE TABLE will allow parent table rows to be deleted if a dependent row exists?

  A. ON DELETE RESTRICT

  B. ON DELETE NO ACTION

  C. ON DELETE SET NO VALUE

  D. ON DELETE CASCADE

  Correct Answer: D

• Question 67:

  Given the statement:

  CREATE TABLE tablea (col1 INTEGER NOT NULL, CONSTRAINT const1 CHECK (col1 in (100, 200, 300)) Which of the following can be inserted into TABLEA?

  A. 0

  B. NULL

  C. 100

  D. '100'

  Correct Answer: C

• Question 68:

  If the following SQL statements are executed:

  CREATE TABLE make (makeid SMALLINT NOT NULL PRIMARY KEY, make VARCHAR(25));

  CREATE TABLE model (modelid SMALLINT,

  model VARCHAR(25),

  makeid SMALLINT,

  CONSTRAINT const1 FOREIGN KEY (makeid)

  REFERENCES make(makeid) ON DELETE RESTRICT);

  And each table created is populated as follows: MAKE MAKEID MAKE 1 Ford

  2 Chevrolet 3 Toyota MODEL

  MODELID MODEL MAKEID 1 Mustang 1 2 Escort 1 3 Malibu 2 4 Camry 3

  If the following SQL statement is executed:

  DELETE FROM make WHERE makeid = 1

  What is the total number of rows that will be deleted?

  A. 0

  B. 1

  C. 2

  D. 3

  Correct Answer: A

• Question 69:

  Given the following table definition:

  EMPLOYEES

  EMPID INTEGER

  NAME CHAR(20)

  SALARY DECIMAL(10,2)

  If the following SQL statement is executed:

  CREATE UNIQUE INDEX empid_ui ON employees (empid)

  Which two of the following statements are true?

  A. Multiple null values are allowed in the EMPID column of the EMPLOYEES table.

  B. No null values are allowed in the EMPID column of the EMPLOYEES table.

  C. One (and only one) null value is allowed in the EMPID column of the EMPLOYEES table.

  D. No other unique indexes can be created on the EMPLOYEES table.

  E. Every value found in the EMPID column of the EMPLOYEES table will be different.

  Correct Answer: CE

• Question 70:

  If table TAB1 is created using the following statement:

  CREATE TABLE tab1 (col1 INTEGER NOT NULL,

  col2 CHAR(5),

  CONSTRAINT cst1 CHECK (col1 in (1, 2, 3)))

  Which of the following statements will successfully insert a record into table TAB1?

  A. INSERT INTO tab1 VALUES (0, 'abc')

  B. INSERT INTO tab1 VALUES (NULL, 'abc')

  C. INSERT INTO tab1 VALUES (ABS(2), 'abc')

  D. INSERT INTO tab1 VALUES (DEFAULT, 'abc')

  Correct Answer: C