Examples of the CREATE TABLE SQL Statement


Now that we've seen the basic syntax for the CREATE TABLE statement and have studied some simple examples of the CREATE TABLE statement's use, let's take a look at some more complex CREATE TABLE statement examples and examine the characteristics of the resulting tables that would be created if each statement shown were executed.

Example 1:

Suppose the following CREATE TABLE statement is executed:

 CREATE TABLE project   (projno  CHAR(6) NOT NULL,   projname VARCHAR(24) NOT NULL,   deptno  SMALLINT,   budget  DECIMAL(6,2),   startdate DATE,   enddate DATE) 

A table named PROJECT will be created that has the following characteristics:

  • The first column will be assigned the name PROJNO and can be used to store fixed-length character string data that is six characters in length (for example, "PROJ01" or "PROJ02").

  • The second column will be assigned the name PROJNAME and can be used to store variable-length character string data that can be up to 24 characters in length (for example, "DB2 Benchmarks Tool" or "Auto-Configuration Tool").

  • The third column will be assigned the name DEPTNO and can be used to store numeric values in the range of -32,768 to +32,767.

  • The fourth column will be assigned the name BUDGET and can be used to store numerical values that contain both whole and fractional parts. Up to six numbers can be specified-four for the whole number part and two for the fractional part (for example, 1500.00, 2000.50, etc.).

  • The fifth column will be assigned the name STARTDATE and can be used to store date values.

  • The sixth column will be assigned the name ENDDATE and can also be used to store date values.

  • Whenever data is added to the PROJECT table, values must be provided for the PROJNO column and the PROJNAME column. (Null values are not allowed because the NOT NULL constraint was defined for both of these columns.)

  • The PROJECT table will be created in the table space USERSPACE1 (because no table space was specified, this is the default table space used).

Example 2:

Suppose the following CREATE TABLE statement is executed:

 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 

A table named SALES will be created that has the following characteristics:

  • The first column will be assigned the name PO_NUMBER (for Purchase Order Number) and can be used to store numeric values in the range of -2,147,483,648 to 2,147,483,647.

  • The second column will be assigned the name DATE and can be used to store date values.

  • The third column will be assigned the name OFFICE and can be used to store fixed-length character string data that can be up to 128 characters in length (for example, "Kansas City" or "Dallas").

  • The fourth column will be assigned the name AMT (for Amount) and can be used to store numerical values that contain both whole and fractional parts. Up to 10 numbers can be specified-eight for the whole number part and two for the fractional part (for example, 20,000,000.50).

  • Whenever data is added to the SALES table, values must be provided for the PO_NUMBER and the AMT columns. (Null values are not allowed in any column because the NOT NULL constraint was defined for each column; however, default values are provided for two columns.)

  • Every value provided for the PO_NUMBER column must be unique. (Because a UNIQUE constraint named UC1 was created for the PO_NUMBER column.)

  • An index was automatically created for the PO_NUMBER column. (Because a UNIQUE constraint named UC1 was created for the PO_NUMBER column.) As data is added to the table, the values provided for the PO_NUMBER column will be added to the index, and the index will be sorted in ascending order.

  • If no value is provided for the DATE column when a row is inserted into the SALES table, the system date at the time the row is inserted will be written to the column by default (because a default constraint was created for the DATE column).

  • If no value is provided for the OFFICE column, the value "Dallas" will be written to the column by default (because a default constraint was created for the OFFICE column).

  • Every value provided for the AMT column must be greater than or equal to 100.00 (because a CHECK constraint was created for the AMT column).

  • The SALES table will be created in a schema named CENTRAL.

  • The SALES table will be created in a table space named MY_SPACE.

Example 3:

Suppose the following CREATE TABLE statements are executed in the order shown:

 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)) 

First, a table named EMPLOYEES will be created that has the following characteristics:

  • The first column will be assigned the name EMPID (for Employee ID) and can be used to store numeric values in the range of -2,147,483,648 to 2,147,483,647.

  • The second column will be assigned the name EMP_FNAME (for Employee First Name) and can be used to store fixed-length character string data that can be up to 30 characters in length (for example, "Bob" or "Mark").

  • The third column will be assigned the name EMP_LNAME (for Employee Last Name) and can be used to store fixed-length character string data that can be up to 30 characters in length (for example, "Jancer" or "Hayakawa").

  • Whenever data is added to the EMPLOYEES table, values must be provided for the EMPID column. (Null values are not allowed because the NOT NULL constraint was defined for this column.)

  • Every value provided for the EMPID column must be unique (because a primary key constraint was created for the EMPID column).

  • An index was automatically created for the EMPID column. (Because a primary key constraint was created for the EMPID column.) As data is added to the table, the values provided for the EMPID column will be added to the index, and the index will be sorted in ascending order.

  • The EMPLOYEES table will be created in a schema named PAYROLL.

  • The EMPLOYEES table will be created in the table space USERSPACE1 (because no table space was specified, this is the default table space used).

Then a table named PAYCHECKS will be created that has the following characteristics:

  • The first column will be assigned the name EMPID and can be used to store numeric values in the range of -2,147,483,648 to 2,147,483,647.

  • The second column will be assigned the name WEEKNUMBER and can be used to store fixed-length character string data that can be up to three characters in length (for example, "1" or "35").

  • The third column will be assigned the name PAY_AMT and can be used to store numerical values that contain both whole and fractional parts. Up to six numbers can be specified-four for the whole number part and two for the fractional part (for example, 2,000.50).

  • Every value entered in the EMPID column must have a matching value in the EMPID column of the EMPLOYEES table created earlier (because a referential constraint has been created in which the EMPID column of the EMPLOYEES table is the parent key and the EMPID column of the PAYCHECKS table is the foreign key-this referential constraint is assigned the name FKCONST).

  • Whenever a row is deleted from the EMPLOYEES table created earlier, all rows in the PAYCHECKS table that have a value in the EMPID column that matches the primary key of the row being deleted will also be deleted.

  • Every value provided for the PAY_AMT column must be greater than 0 (because a CHECK constraint named CHK1 was created for the PAY_AMT and WEEKNUMBER columns).

  • Every value provided for the WEEKNUMBER column must be greater than or equal to 1 and less than or equal to 52 (again, because a CHECK constraint named CHK1 was created for the PAY_AMT and WEEKNUMBER columns.)

  • The PAYCHECKS table will be created in a schema named PAYROLL.

  • The PAYCHECKS table will be created in the table space USERSPACE1 (because no table space was specified, this is the default table space used).

Example 4:

Suppose the following CREATE TABLE statement is executed:

 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)) 

A table named EMPLOYEE will be created that has the following characteristics:

  • The first column will be assigned the name EMPID (for Employee ID) and can be used to store numeric values in the range of -32,768 to +32,767.

  • The second column will be assigned the name FIRSTNAME and can be used to store variable length character string data that can be up to 30 characters in length (for example, "Mark" or "Antonio").

  • The third column will be assigned the name LASTNAME and can be used to store variable length character string data that can be up to 30 characters in length (for example, "Hayakawa" or "Robinson").

  • The fourth column will be assigned the name DEPTID and can be used to store fixed-length character string data that can be up to three characters in length (for example, "1", "35", etc.).

  • The fifth column will be assigned the name EDLEVEL and can be used to store fixed-length character string data that can only be one character in length (for example, 'C', or 'H').

  • The EMPID column is an identity column. Therefore a unique numeric value will automatically be assigned to this column whenever data is added to the EMPLOYEE table; this value can be overridden by specifying a value for the EMPID column in the INSERT statement used to add data. (Null values are not allowed because the NOT NULL constraint was defined for this column.)

  • Whenever data is added to the EMPLOYEE table, values must be provided for the FIRSTNAME and the LASTNAME columns. (Null values are not allowed because the NOT NULL constraint was defined for these columns.)

  • The only values that can be inserted into the EDLEVEL column are 'C', 'H', and 'N' (because a CHECK constraint was created for the EDLEVEL column).

  • Every value provided for the EMPID column must be unique (because a primary key constraint named EMP_PK was created for the EMPID column).

  • An index was automatically created for the EMPID column (again, because a primary key constraint named EMP_PK was created for the EMPID column). As data is added to the table, the values generated/provided for the EMPID column will be added to the index, and the index will be sorted in ascending order.

  • Every value entered in the DEPTID column must have a matching value in the DEPTNO column of a table named DEPARTMENT (because a referential constraint has been created in which the DEPTNO column of the DEPARTMENT table is the parent key and the DEPTID column of the EMPLOYEE table is the foreign key-this referential constraint was assigned the name EMP_DEPT_FK).

  • The EMPLOYEE table will be created in the table space USERSPACE1 (because no table space was specified, this is the default table space used).

Example 5:

Suppose the following CREATE TABLE statement is executed:

 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)) 

A table named ACTIVITY will be created that has the following characteristics:

  • The first column will be assigned the name ACTIVITYNO (for Activity Number) and can be used to store numeric values in the range of -32,768 to +32,767.

  • The second column will be assigned the name ACTKWD (for Activity Keyword) and can be used to store fixed-length character string data that can be up to 6 characters in length (for example, "Sale" or "Buy").

  • The third column will be assigned the name ACTDESC (for Activity Description) and can be used to store variable-length character string data that can be up to 20 characters in length (for example, "Sale of 1000 shares of EMC" or "Buy 250 shares IBM").

  • The ACTIVITYNO column is an identity column. Therefore a unique numeric value will automatically be assigned to this column whenever data is added to the ACTIVITY table; this value can be overridden by specifying a value for the ACTIVITY column in the INSERT statement used to add data. If no value is provided, the first value generated will be the number 10, and subsequent generated values will be incremented by 10. (Null values are not allowed because the NOT NULL constraint was defined for this column.)

  • Whenever data is added to the ACTIVITY table, values must be provided for the ACTKWD and the ACTDESC columns. (Null values are not allowed because the NOT NULL constraint was defined for these columns.)

  • Every value provided for the ACTIVITYNO column must be unique (because a UNIQUE constraint was created for the ACTIVITYNO column).

  • An index was automatically created for the AVTIVITYNO column (again, because a UNIQUE constraint was created for the ACTIVITYNO column). As data is added to the table, the values generated/provided for the ACTIVITY column will be added to the index, and the index will be sorted in ascending order.

  • The ACTIVITY table will be created in a schema named STOCK.

  • The ACTIVITY table will be created in the table space USERSPACE1 (because no table space was specified, this is the default table space used).

Example 6:

Suppose the following CREATE TABLE statement is executed:

 CREATE TABLE self_reference   (idcol1 SMALLINT NOT NULL PRIMARY KEY,   idcol2 SAMLLINT,   CONSTRAINT fkconst FOREIGN KEY (idcol2)     REFERENCES self_reference(idcol1)) 

A table named SELF_REFERENCE will be created that has the following characteristics:

  • The first column will be assigned the name IDCOL1 and can be used to store numeric values in the range of -32,768 to +32,767.

  • The second column will be assigned the name IDCOL2 and can be used to store numeric values in the range of -32,768 to +32,767.

  • Every value entered in the IDCOL2 column must have a matching value in the IDCOL1 column of the same table (because a referential constraint has been created in which the IDCOL1 column of the table is the parent key and the IDCOL2 column of the same table is the foreign key-this referential constraint was assigned the name FKCONST). Every value provided for the IDCOL1 column must be unique (because a primary key constraint was created for the IDCOL1 column).

  • An index was automatically created for the IDCOL1 column (again, because a primary key constraint was created for the IDCOL1 column). As data is added to the table, the values generated/provided for the IDCOL01 column will be added to the index, and the index will be sorted in ascending order.

  • The SELF_REFERENCE table will be created in the table space USERSPACE1 (because no table space was specified, this is the default table space used).




DB2 9 Fundamentals Certification Study Guide
DB2 9 Fundamentals: Certification Study Guide
ISBN: 1583470727
EAN: 2147483647
Year: 2007
Pages: 93

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net