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;
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,
dlocation CHAR(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 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 a partitioned table with rows distributed evenly based on a hash value:
CREATE TABLE employee (
emp_no INTEGER NOT NULL NOT DEFAULT,
emp_name CHAR(32) NOT NULL NOT DEFAULT,
dept_no INTEGER)
WITH JOURNALING,
PARTITION = (HASH ON emp_no
16 PARTITIONS WITH LOCATION = (ii_database));
Create a partitioned table with rows distributed automatically:
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 a partitioned table with rows distributed according to the listed column values:
CREATE TABLE employee (
emp_no INTEGER NOT NULL NOT DEFAULT,
emp_name CHAR(32) NOT NULL NOT DEFAULT,
dept_no INTEGER)
WITH JOURNALING,
PARTITION = (LIST ON dept_no
PARTITION p1 VALUES (1, 2),
PARTITION p2 VALUES (3, 4),
PARTITION p3 VALUES (DEFAULT));
Create a partitioned table with rows distributed according to the specified ranges:
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 a range-distributed, sub-partitioned table using hash:
CREATE TABLE employee (
emp_no INTEGER NOT NULL NOT DEFAULT,
emp_name CHAR(32) NOT NULL NOT DEFAULT,
dept_no INTEGER)
WITH PARTITION = ((RANGE ON dept_no
PARTITION p1 VALUES <= 10,
PARTITION p2 VALUES <= 20,
PARTITION p3 VALUES <= 30,
PARTITION p4 VALUES > 30)
SUBPARTITION
(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.
CREATE TABLE t1 (
c1 CHAR(20) NOT NULL,
c2 INTEGER GENERATED ALWAYS AS IDENTITY,
c3 FLOAT);
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.
CREATE TABLE t2 (
d1 DECIMAL(15) GENERATED BY DEFAULT AS IDENTITY (START WITH -1 INCREMENT BY ‑10),
d2 FLOAT, . . .);
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 (
fname CHAR(10),
lname CHAR(20),
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:
CREATE TABLE t1 (
c1 CHAR(20) NOT NULL,
c2 INTEGER GENERATED ALWAYS AS IDENTITY,
c3 MONEY ENCRYPT)
WITH ENCRYPTION=AES256,
PASSPHRASE='decoder ring';
24. Create a table with a long byte column in which the first 10,000 bytes of the blob are to be stored inline:
CREATE TABLE user_profile (
up_id INT,
up_last NVARCHAR(30),
up_ first NVARCHAR(30),
up_email NVARCHAR(100),
up_airport NCHAR(3) WITH NULL,
up_image LONG BYTE(10000) WITH NULL);