Examples: Create Table

The following are CREATE TABLE statement 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;

2. Create a table with some other data types.

   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;

5. Create a table specifying defaults.

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

7. Create a table specifying unique constraints and keys.

   create table dept (
       deptno char(5) primary key,
       dname char(10) not null,
       dlocationchar(10) not null,
   unique (dname, dlocation));

8. Create a table specifying null constraints.

   create table emp (
       salary money with null not default,
       hiredate date with null not default,
       sickdays float with null with default 5.0);

9. Primary key constraint uses hash index structure instead of B-tree.

   create table department (dept_id char(6) not null primary key with structure = hash, dept_name char(20));

10. Base table structure is hash unique on dept_id.

    create table department (dept_id char(6) not null, dept_name char(20));
    modify department to hash unique on dept_id;

11. Force ANSI uniqueness semantics.

    modify department to unique scope = statement;

12. Unique constraints use base table structure, not a generated index.

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

14. Referential and primary key constraints share the same named index.

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

    Referential action:

    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 that 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 distribution, 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 of the ranges.

    CREATE TABLE lineitems (
    shipdate DATE 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)));
        );