DB2 Universal Database V8.1 Certification Exam 700 Study Guide

Which of the following is not a valid DB2 data type?

• A. BIGINT

• B. SMALLINT

• C. IDENTITY

• D. INTEGER

Which type of key is defined on the parent table to implement referential constraints?

• A. Unique key

• B. Primary key

• C. Foreign key

• D. Composite key

Given the following tables:

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. 0, 0

• B. 0, 1

• C. 1, 0

• D. 1, 1

Which of the following DB2 UDB data types is used to store 1000 MB of single-byte character data as a single value?

• A. BLOB

• B. CLOB

• C. DBCLOB

• D. GRAPHIC

Given the following CREATE TABLE statement:

CREATE TABLE newtable LIKE table1

Which two of the following would NOT occur as a result of the statement execution?

• A. NEWTABLE would have the same column names and column data types as TABLE1

• B. NEWTABLE would have the same column defaults as TABLE1

• C. NEWTABLE would have the same indexes as TABLE1.

• D. NEWTABLE would have the same nullability characteristics as TABLE1

• E. NEWTABLE would have the same referential constraints as TABLE1

Which of the following DB2 data types should NOT be used to store double-byte character data?

• A. DBCLOB

• B. GRAPHIC

• C. VARCHAR

• D. VARGRAPHIC

Given the statement:

CREATE  TABLE  tablea  (  col1    INTEGER  NOT  NULL,

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

Which of the following can be inserted into TABLEA?

• A. 0

• B. NULL

• C. 1

• D. '1'

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

Given the following scenario:

Table TABLE1 needs to hold specific numeric values up to 9999999.999 in column COL1. COL1 is also used to perform arithmetic operations.

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)

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

Which of the following CANNOT be used to restrict specific values from being inserted into a column in a particular table?

• A. Index

• B. Check constraint

• C. Referential constraint

• D. Default constraint

Which of the following is NOT a difference between a unique index and a primary key?

• A. A primary key is a special form of a unique constraint; both use a unique index.

• B. Unique indexes can be defined over one or more columns ; primary keys can only be defined on a single column.

• C. A table can have many unique indexes but only one primary key.

• D. Unique indexes can be defined over one or more columns that allow null values; primary keys cannot contain null values.

Which of the following DB2 data types CANNOT be used to store the time a scheduled flight left the departure gate?

• A. CLOB

• B. VARCHAR

• C. DATE

• D. TIMESTAMP

Given the following statements:

CREATE  TABLE  table1  (col1    CHAR(3)  PRIMARY  KEY,

       CONSTRAINT  const1  CHECK  (col1  IN  'A01',  'B02',  'C03'))

INSERT  INTO  table1  VALUES('A02');

COMMIT

Which of the following occurs if the statements are executed in the order shown?

• A. The insert operation is rejected.

• B. The row is inserted with COL1 having a NULL value.

• C. The row is inserted with COL1 having a value of A02.

• D. A user -defined function called CONST1 is activated to validate the data.