IBM IBM Certified Database Associate C2090-730 Questions & Answers

• Question 111:

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

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

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

• Question 114:

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

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

  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?

  B. 1

  C. 2

  D. 3

  Correct Answer: A

• Question 117:

  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

• Question 118:

  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

• Question 119:

  If the following SQL statements are executed:

  CREATE TABLE tab1 (id SMALLINT NOT NULL PRIMARY KEY, name VARCHAR(25));

  CREATE TABLE tab2 (empid SMALLINT,

  weekno SMALLINT,

  payamt DECIMAL(6,2),

  CONSTRAINT const1 FOREIGN KEY (empid)

  REFERENCES taba(id) ON UPDATE NO ACTION);

  Which of the following statements is true?

  A. Only values that exist in the ID column of table TAB1 are allowed to be inserted in the EMPID column of table TAB2

  B. The updating of values in the ID column of table TAB1 is not allowed

  C. Only values that do not already exist in the ID column of table TAB1 are allowed to be inserted in the EMPID column of table TAB2

  D. When values that exist in the ID column of table TAB1 are updated, corresponding values in the EMPID column of table TAB2 are updated as well

  Correct Answer: A

• Question 120:

  Given the following CREATE TABLE statement: CREATE TABLE table2 LIKE table1

  Which two of the following will NOT occur when the statement is executed?

  A. TABLE2 will have the same column names and column data types as TABLE1

  B. TABLE2 will have the same column defaults as TABLE1

  C. TABLE2 will have the samenullability characteristics as TABLE1

  D. TABLE2 will have the same indexes as TABLE1.

  E. TABLE2 will have the same referential constraints as TABLE1

  Correct Answer: DE