Db2 Basics, Part 3

Using the CREATE INDEX statement, secondary columns may be stored in the index along with the key.

• True

• False

Only one index in a table can be a clustering index.

• True

• False

A ________ defines a set of actions to be executed whenever an insert, update, or delete operation is performed against a table or updatable view.

________ let you place logic to enforce business rules within the database, rather than in applications.

A ________ column is a unique identifier that represents an individual table record.  Its value is a number sequentially incremented with each new record.

To create a table with the same definition as an existing table, use the statement     CREATE TABLE [TableName] ________ [SourceTable] ...

When using the LIKE clause to create a table with the same definition as an existing table, indicate which of the following are true:

• UNIQUE constraints are copied.

• Default constraints are copied (unless EXCLUDING COLUMN DEFAULTS is specified).

• Referential constraints are copied.

• Both tables have the same number of columns.

• Triggers are copied.

• Indexes are copied.

• The columns in the new table have the same names, data types, and nullability features.

CREATE TABLE stock.activity      (activityno   SMALLINT  NOT NULL  GENERATED BY DEFAULT AS IDENTITY                 (START WITH 10  INCREMENT BY 10),       actkwd       CHAR(6)      NOT NULL,       actdesc      VARCHAR(20)  NOT NULL,       UNIQUE (activityno))  'activityno' is a ________ column.  Values must be provided for columns ________ and ________.  An index is created for column ________.  In 'stock.activity', 'stock' identifies the ________.  

Which are the characteristics of base tables?

• Table name must be unique within a schema.

• Not persistent - only used by the application that created it.

• Only used for the life of the application.

• Descriptions and constraints stored in system catalog tables of the database.

• Many apps can create a table with the same name.

The GENERATED ... AS IDENTITY clause may be used when creating a declared temporary table.

• True

• False

The contents of a declared temporary table may be retained after a transaction is committed.

• True

• False

A ________ is an ordered set of ________ that refers to rows in a base table.

An index is based on one or more columns, known as the ________, in the base table.

To create an index, you must use the CREATE INDEX statement.

• True

• False

Data uniqueness is automatically enforced when an index is created with the CREATE INDEX statement.

• True

• False

The ________ option of the CREATE INDEX statement will cause these effects:  *  DB2 Database Manager tries to store records on a page that contains other records that have similar index key values.  *  If no space available on that page, it will try to store the record on a nearby page.  *  Increases performance by decreasing I/O required to access data.  

Which are components of a trigger?

• Subject table or view

• Trigger event

• Trigger sensitivity

• Trigger activation time

• Set of affected columns

• Set of affected rows

• Trigger granularity

• Triggered action

The ________ ________ specifies whether the trigger actions will be performed once for the entire insert, update, or delete operation (trigger event), or once for every row affected by the operation.

The ________ ________ is an optional search condition and a set of SQL statements to be executed when the trigger is activated.

Possible triggered actions - AFTER trigger:

• Signal SQL states

• Insert, update, and delete records

• Retrieve data

• Set transition variables

A ________ variable is a qualified column name that indicates whether the reference is to the original value (before an insert, update, or delete) or to the new value.

A ________ ________ uses subject table names, but allows the complete set of affected rows to be treated as a table.  Can only be used in AFTER triggers.

CREATE TRIGGER block_deletesNO CASCADE BEFORE DELETE ON activity_historyFOR EACH ROWSIGNAL SQLSTATE '75002'            SET MESSAGE_TEXT = 'Deletes not allowed!'  The subject table is ________.  The trigger event is ________.  The trigger activation time is ________.  

CREATE TRIGGER block_deletesNO CASCADE BEFORE DELETE ON activity_historyFOR EACH ROW  SIGNAL SQLSTATE '75002'            SET MESSAGE_TEXT = 'deletes not allowed!'  The line  FOR EACH ROW  is an example of ________ ________.  The SIGNAL and SET statements are examples of ________ ________.  

Constraints are ________ that govern     1.  how data values can be ________ to a table, and     2.  how data values in a table can be ________.

Constraints are usually defined during table ________; they can also be added to existing tables with the ________ ________ statement.

CREATE TABLE project    (projno         CHAR(6)  NOT NULL.     projname    VARCHAR(24)  NOT NULL,     deptno        SMALLINT,     budget        DECIMAL(6,2),     startdate     DATE,     enddate      DATE)          The table will be created in table space ________ (the default).     Values must be provided for fields ________ and ________.

CREATE TABLE self_reference      (idcol1     SMALLINT  NOT NULL  PRIMARY KEY,       idcol2     SMALLINT,       CONSTRAINT fkconst  FOREIGN KEY (idcol2)                 REFERENCES self_reference(idcol1))  Every value in column ________ must have a matching value in column ________, due to constraint ________.  ________ is the parent key, and ________ is the foreign key.  Every value of column ________ must be unique.  A ________ was created for column idcol1, due to the PRIMARY KEY constraint.  Table self_reference will be created in table space ________.  

________ provides a way to cluster data along multiple dimensions automatically.

The three trigger activation time keywords are (in alpha order) ________, ________, and ________.

CREATE TRIGGER pay_raise  NO CASCADE BEFORE UPDATE ON employees  FOR EACH ROW  SET new.salary = salary * 1.1  The line  FOR EACH ROW  is an example of ________ ________.  The SET statement is an example of a ________ ________.  new.salary is an example of a ________ ________.   

The ________ data type stores data such as images, audio, and video.

CREATE TABLE central.sales    (po_number     INTEGER  NOT NULL  CONSTRAINT uc1 UNIQUE,     date                DATE       NOT NULL  WITH DEFAULT,     office              CHAR(128)  NOT NULL  WITH DEFAULT 'Dallas',     amt                DECIMAL(10,2)  NOT NULL  CHECK (amt > 99.99))   IN my_space     Must provide values for fields ________ and ________.  An index was provided for field ________.  The default for the date field is the ________ ________.  The sales table will be created in schema ________ and table space ________.  

Unique constraint versus unique index:  Which are the characteristics of a UNIQUE constraint?

• Generally cannot be used in a referential constraint

• Does not allow NULL values

• Enforces uniqueness

• Can be referenced in a referential constraint

• Allows NULL values

Unique constraint versus unique index:  Which are the characteristics of a unique index?

• Does not allow NULL values

• Generally cannot be used in a referential constraint

• Allows NULL values

• Can be referenced in a referential constraint

• Enforces uniqueness

CREATE TABLE payroll.employees      (empid             INTEGER  NOT NULL  PRIMARY KEY,       emp_fname    CHAR(30),       emp_lname    CHAR(30))  CREATE TABLE payroll.paychecks      (empid               INTEGER,       weeknumber    CHAR(3),       pay_amt           DECIMAL(6,2),       CONSTRAINT fkconst  FOREIGN KEY (empid)             REFERENCES employee(empid)  ON DELETE CASCADE,       CONSTRAINT chk1  CHECK (pay_amt > 0  AND  weeknumber BETWEEN 1 AND 52))  For table 'employees':       Values must be provided for ________.       Every value provided for ________ must be unique.       An index was provided for the ________ column.  For table 'paychecks':       pay_amt has a ________ of 6 and a ________ of 2.       Every value in paychecks.________ must have a matching value in employees.________, due to CONSTRAINT ________.       Whenever a row is ________ from table employees, all rows in table paychecks that have the same value will also be ________.   

CREATE TABLE employee      (empid            SMALLINT  NOT NULL  GENERATED BY DEFAULT AS IDENTITY,       firstname       VARCHAR(30)  NOT NULL,       lastname       VARCHAR(30)  NOT NULL,       deptid            CHAR(3),       edlevel          CHAR(1)  CHECK(edlevel IN ('C','H','N')),       CONSTRAINT  emp_pk  PRIMARY KEY (empid),       CONSTRAINT  emp_dept_fk  FOREIGN KEY (deptid)                 REFERENCES department(deptno))  'empid' is a ________ column.  Values must be supplied for fields ________ and ________.  Constraints are not named when they are part of the ________ definition, as for field ________.  Every value entered in field ________ must be unique, due to its constraint.  An index was created for field ________, due to its constraint.  Every value entered entered in field ________ must have a matching value in the ________ field of table ________, due to referential constraint ________.  

Triggers can be used to:

• Create a table.

• Enforce data integrity and business rules.

• Invoke functions: e.g. to issue errors or alerts.

• Generate/transform values for inserted or updated rows.

• Join tables or view.

• Update other tables.

A ________ ________ is an SQL operation that, when performed against the subject table or view, activates the trigger; must be an INSERT, UPDATE, or DELETE.

CREATE TRIGGER pay_raise  NO CASCADE BEFORE UPDATE ON employees  FOR EACH ROW  SET new.salary = salary * 1.1  The subject table is ________.  The trigger event is ________.  BEFORE is the ________ ________ ________.  

CREATE TRIGGER trigger_a  AFTER INSERT ON sales  REFERENCING NEW AS n  FOR EACH ROW  UPDATE sales SET sale_date = CURRENT_DATE            WHERE invoice = n.invoice  The subject table is ________.  INSERT is the ________ ________.      The trigger activation time is ________.    FOR EACH ROW indicates the ________ ________.    The triggered action is the ________ statement.    n.invoice is an example of a ________ ________.    

Possible triggered actions - BEFORE trigger:

• Signal SQL states

• Retrieve data

• Insert, update, and delete records

• Set transition variables

The CLOB data type stores single-byte character set (SBCS) or multibyte character set string values.

• True

• False