CREATE TABLE SQL Statement Examples Now that we've seen the basic syntax for the CREATE TABLE statement and have examined some simple examples of the CREATE TABLE statement's use, let's take a look at a few more complex CREATE TABLE statement examples and identify the characteristics of the resulting tables that would be created if each statement shown were executed. Example 1 If 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 as follows : -
The first column will be assigned the name PROJNO and will be used to store fixed-length character string data that is six characters in length (for example, 'PROJ01', 'PROJ02', etc.). -
The second column will be assigned the name PROJNAME and it will be used to store variable-length character string data that can be up to 24 characters in length (for example, 'DB2 Benchmarks Tool', 'Auto-Configuration Tool', etc.). -
The third column will be assigned the name DEPTNO and will 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 will 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 will be used to store date values. -
The sixth column will be assigned the name ENDDATE and will 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 tablespace USERSPACE1 (which was not specified, but is the default). Example 2 If the following CREATE TABLE statement is executed: CREATE TABLE SALES (PO_NUMBER INTEGER NOT NULL CONSTRAINT UC1 UNIQUE, DATE DATE NOT NULL WITH DEFAULT), OFFICE CHAR(128) NOT NULL WITH DEFAULT 'HQ', AMT DECIMAL(10,2) NOT NULL CHECK (AMT > 99.99) IN MY_SPACE A table named SALES will be created as follows: -
The first column will be assigned the name PO_NUMBER (for Purchase Order Number) and will be used to store numeric values in the range of “32,768 to +32,767. -
The second column will be assigned the name DATE and will be used to store date values. -
The third column will be assigned the name OFFICE and will be used to store fixed-length character string data that can be up to 128 characters in length (for example, 'Baltimore/Washington', 'Dallas/Ft. Worth', etc.). -
The fourth column will be assigned the name AMT (for Amount) and will 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, 15000000.00, 20000000.50, etc.). -
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 will automatically be 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 AMT column, the system date at the time a row is inserted into the SALES table 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 HQ 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 the tablespace MY_SPACE. Example 3 If the following CREATE TABLE statements are executed: CREATE TABLE EMPLOYEE (EMPID INT NOT NULL PRIMARY KEY, EMP_FNAME CHAR(30), EMP_LNAME CHAR(30)) CREATE TABLE PAYROLL (EMPID INTEGER, WEEKNUMBER CHAR(3), PAYCHECK DECIMAL(6,2), CONSTRAINT FKCONST FOREIGN KEY (EMPID) REFERENCES EMPLOYEE(EMPID) ON DELETE CASCADE, CONSTRAINT CHK1 CHECK (PAYCHECK > 0 AND WEEKNUMBER BETWEEN 1 AND 52)) A table named EMPLOYEE will be created as follows: -
The first column will be assigned the name EMPID (for Employee ID) and will be used to store numeric values in the range of “32,768 to +32,767. -
The second column will be assigned the name EMP_FNAME (for Employee First Name) and will be used to store fixed-length character string data that can be up to 30 characters in length (for example, 'Mark', 'Bob', etc.). -
The third column will be assigned the name EMP_LNAME (for Employee Last Name) and will be used to store fixed-length character string data that can be up to 30 characters in length (for example, 'Hayakawa', 'Jancer', etc.). -
Whenever data is added to the EMPLOYEE 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 unique constraint was created for the EMPID column). -
An index will automatically be 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 EMPLOYEE table will be created in the tablespace USERSPACE1. A table named PAYROLL will also be created as follows: -
The first column will be assigned the name EMPID and will be used to store numeric values in the range of “32,768 to +32,767. -
The second column will be assigned the name WEEKNUMBER and will be used to store fixed-length character string data that can be up to three characters in length (for example, '1', '35', etc.). -
The third column will be assigned the name PAYCHECK and will 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.). -
An index will automatically be 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. -
Every value entered in the EMPID column must have a matching value in the EMPID column of the EMPLOYEE table created earlier (because a referential constraint in which the EMPID column of the EMPLOYEE table is the parent key and the EMPID column of the PAYROLL table is the foreign key has been created ”this referential constraint is assigned the name FKCONST). -
Whenever a row is deleted from the EMPLOYEE table created earlier, all rows in the PAYROLL table that have a value in the EMPID column matching the primary key of the row being deleted will also be deleted. -
Every value provided for the PAYCHECK column must be greater than 0 (because a check constraint named CHK1 was created for the PAYCHECK 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, a check constraint named CHK1 was created for the PAYCHECK and WEEKNUMBER columns.) -
The EMPLOYEE table will be created in the tablespace USERSPACE1. |