Create Table Examples

1. Create the employee table with columns eno, ename, age, job, salary, and dept, with journaling enabled.

CREATE TABLE employee (
    eno SMALLINT,
    ename VARCHAR(20) NOT NULL WITH DEFAULT,
    age INTEGER1,
    job SMALLINT,
    salary FLOAT4,
    dept SMALLINT)
   WITH JOURNALING;

CREATE TABLE debts (
    acct VARCHAR(20) NOT NULL NOT DEFAULT,
    owes MONEY,
    LOGICAL_KEY OBJECT_KEY WITH SYSTEM_MAINTAINED,
    due DATE NOT NULL WITH DEFAULT);

3. Create a table listing employee numbers for employees who make more than the average salary.

CREATE TABLE highincome AS
    SELECT eno
    FROM EMPLOYEE
    WHERE salary >
    (SELECT AVG (salary)
     FROM employee);

4. Create a table that spans two locations. Specify number of pages to be allocated for the table.

CREATE TABLE emp AS
    SELECT eno FROM employee
    WITH LOCATION = (location1, location2),
    ALLOCATION = 1000;

CREATE TABLE dept (
    dname CHAR(10),
    location CHAR(10) DEFAULT 'LA',
    creation_date DATE DEFAULT '1/1/93',
    budget MONEY DEFAULT 100000,
    expenses MONEY DEFAULT 0);

6. Create a table specifying check constraints. In the following example, department budgets default to $100,000, expenses to $0. The check constraint insures that expenses do not exceed the budget.

CREATE TABLE dept (
    dname CHAR(10),
    budget MONEY DEFAULT 100000,
    expenses MONEY DEFAULT 0,
    CHECK (budget >= expenses));

CREATE TABLE dept (
    deptno CHAR(5) PRIMARY KEY,
    dname CHAR(10) NOT NULL,
    dlocation CHAR(10) NOT NULL,
UNIQUE (dname, dlocation));

CREATE TABLE emp (
    salary MONEY WITH NULL NOT DEFAULT,
    hiredate DATE WITH NULL NOT DEFAULT,
    sickdays FLOAT WITH NULL WITH DEFAULT 5.0);

CREATE TABLE department (dept_id CHAR(6) NOT NULL PRIMARY KEY WITH STRUCTURE = HASH, dept_name CHAR(20));

CREATE TABLE department (dept_id CHAR(6) NOT NULL, dept_name CHAR(20));
MODIFY department TO HASH UNIQUE ON dept_id;

MODIFY department TO UNIQUE_SCOPE = STATEMENT;

ALTER TABLE department ADD PRIMARY KEY (dept_id)
WITH INDEX = BASE TABLE STRUCTURE;

13. Unique constraints generate index in non-default location. First referential constraint generates no index at all.

CREATE TABLE employee (empl_no INT NOT NULL
    UNIQUE WITH LOCATION = (ixloc1),
    emp_name CHAR(20) NOT NULL,
    dept_id CHAR(6) REFERENCES department (dept_id) WITH NO INDEX,
    mgrno INT REFERENCES employee (empl_no));

CREATE TABLE assignment (empl_no INT NOT NULL
    REFERENCES employee (empl_no) WITH (INDEX = assnpkix,
    LOCATION = (ixloc2)),
    proj_id INT NOT NULL REFERENCES project (proj_id),
    task CHAR(20),
    PRIMARY KEY (empl_no, proj_id) WITH INDEX = assnpkix);

CREATE TABLE employee (empl_no INT NOT NULL
    UNIQUE WITH LOCATION = (ixloc1),
    emp_name CHAR(20) NOT NULL,
    dept_id CHAR(6) REFERENCES department (dept_id)
    ON DELETE CASCADE ON UPDATE CASCADE WITH NO INDEX,
    mgrno INT REFERENCES employee (empl_no) ON UPDATE CASCADE
    ON DELETE SET NULL);

15. Create an automatically-distributed, partitioned table with four partitions. The last partition is placed in the database's default data location.

CREATE TABLE foo (
    i      INTEGER NOT NULL WITH DEFAULT,
    str    VARCHAR(10) NOT NULL WITH DEFAULT
) WITH JOURNALING,
    PARTITION = (AUTOMATIC
        PARTITION p1 WITH LOCATION=(ii_database),
        2 PARTITIONS WITH LOCATION=(loc1, loc2),
        PARTITION p4);

16. Create a list-distributed, partitioned table. Partition p1 is stored in location ii_database. Partition p2 is stored in locations loc1 and loc2. Partition p3 is stored in the database default data location.

CREATE TABLE table1 (
    i INTEGER NOT NULL WITH DEFAULT,
    str VARCHAR(10) NOT NULL WITH DEFAULT
) WITH PARTITION = (LIST ON i,str
   PARTITION p1 VALUES ((1,'one'))
        WITH LOCATION=(ii_database),
   PARTITION p2 VALUES ((2,'two'),(3,'three'))
        WITH LOCATION=(loc1, loc2),
   PARTITION p3 VALUES (DEFAULT));

17. Create a range distribution table partition. Partition p1 contains all values less than A and is stored in location ii_database. Partition p2 contains all values between A and Z (inclusive) and is stored in locations loc1 and loc2. Partition p4 contains all values greater than Z and is stored in the database default location.

CREATE TABLE range (
    i    INTEGER NOT NULL WITH DEFAULT,
    str    VARCHAR(10) NOT NULL WITH DEFAULT
) WITH PARTITION = (RANGE ON str
        PARTITION p1 VALUES < 'A'
WITH LOCATION=(ii_database),
        PARTITION p2 VALUES <= 'Z'
WITH LOCATION=(loc1, loc2),
        PARTITION p4 VALUES > 'Z');

18. Create a range distributed, sub-partitioned table using hash. In this example, the physical partitions are all stored in location loc2. There are 32 hash partitions, 8 for each range.

CREATE TABLE lineitems (
    shipdate INGRESDATE NOT NULL WITH DEFAULT,
    partno INTEGER NOT NULL WITH DEFAULT,
    stuff VARCHAR(10) NOT NULL WITH DEFAULT
) WITH PARTITION = (
    (RANGE ON shipdate
     PARTITION p1 VALUES <= '31-Dec-2001',
     PARTITION p2 VALUES <= '31-Dec-2002',
     PARTITION p3 VALUES <= '31-Dec-2003',
     PARTITION p4 VALUES > '31-Dec-2003')
    SUBPARTITION
(HASH ON partno 8 PARTITIONS WITH LOCATION = (loc2)));

19. The following examples create the same partitioned table, but each using a different distribution type (HASH, AUTOMATIC, LIST, RANGE).

CREATE TABLE employee (

emp_no INTEGER NOT NULL NOT DEFAULT,

emp_name CHAR(32) NOT NULL NOT DEFAULT,

PARTITION = (HASH ON emp_no

16 PARTITIONS WITH LOCATION = (ii_database));

CREATE TABLE employee (

emp_no INTEGER NOT NULL NOT DEFAULT,

emp_name CHAR(32) NOT NULL NOT DEFAULT,

emp_date DATE NOT NULL NOT DEFAULT)

WITH PARTITION = (AUTOMATIC

1 PARTITION WITH LOCATION=(loc1),

1 PARTITION p1 WITH LOCATION=(loc2),

2 PARTITIONS (p2, p3));

CREATE TABLE employee (

emp_no INTEGER NOT NULL NOT DEFAULT,

emp_name CHAR(32) NOT NULL NOT DEFAULT,

PARTITION = (LIST ON dept_no

PARTITION p1 VALUES (1, 2),

PARTITION p2 VALUES (3, 4),

PARTITION p3 VALUES (DEFAULT));

CREATE TABLE employee (

emp_no INTEGER NOT NULL NOT DEFAULT,

emp_name CHAR(32) NOT NULL NOT DEFAULT,

emp_date DATE NOT NULL NOT DEFAULT)

WITH PARTITION=((RANGE ON emp_date

PARTITION VALUES <= ‘31-Dec-2000’,

PARTITION VALUES <= ‘31-Dec-2002’,

PARTITION VALUES <= ‘31-Dec-2004’,

PARTITION VALUES > ‘31-Dec-2004’));

CREATE TABLE employee (

emp_no INTEGER NOT NULL NOT DEFAULT,

emp_name CHAR(32) NOT NULL NOT DEFAULT,

WITH PARTITION = ((RANGE ON dept_no

PARTITION p1 VALUES <= 10,

PARTITION p2 VALUES <= 20,

PARTITION p3 VALUES <= 30,

PARTITION p4 VALUES > 30)

(HASH ON emp_no 16 PARTITIONS));

20. Define a table with an integer identity column. Because it is an ALWAYS identity column, INSERT statements cannot explicitly specify values for column c2.

c1 CHAR(20) NOT NULL,

c2 INTEGER GENERATED ALWAYS AS IDENTITY,

21. Define a table with a BY DEFAULT identity column that maps to a negative incrementing sequence. INSERT statements can override the sequence by explicitly defining values for column d1, but in the absence of an explicit value, the INSERT will generate the next value from the underlying sequence.

d1 DECIMAL(15) GENERATED BY DEFAULT AS IDENTITY (START WITH -1 INCREMENT BY ‑10),

22. Create a table in which the social security number is encrypted using AES 128-bit encryption. Do not add 16 bytes of random bits to the field to further obfuscate the encrypted value (NOSALT):

CREATE TABLE socsectab (

socsec CHAR(11) ENCRYPT NOSALT )

WITH ENCRYPTION=AES128,

PASSPHRASE='this is a secret';

23. Create a table in which data for column c3, which contains salary data, is encrypted using AES 256-bit encryption. Salt is added to the field by default: