Was this helpful?
CREATE TABLE (extended) Examples
1. Create the employee table with columns eno, ename, age, job, salary, and dept.
create table employee
  (eno    smallint,
   ename  varchar(20) not null,
   age    smallint,
   job    smallint,
   salary float4,
   dept   smallint);
2. 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);
3. Create a table specifying defaults.
create table dept 
( dname         char(10)
  location      char(10) default 'LA'
  creation_date date default date('1/1/93'));
4. Create a table specifying referential constraints. When a department number is assigned to an employee, it will be checked against the entries in the dept table.
create table emps (
  empno   char(5),
  deptno  char(5) references dept),
  ...);
5. Create a table specifying check constraints. In this 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   decimal default 100000,
 expenses decimal default 0,
  check    (budget >= expenses));
6. 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));
7. Create a table specifying null constraints.
create table emp (
  salary    decimal not default with null ,
  hiredate  date not default with null,
  sickdays  float default 5.0 with null );
8. Unique constraint uses base table structure, not a generated index:
alter table department add primary key (dept_id)
    with index = base table structure;
9. Unique constraint generates 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));
10. 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);
11. 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);
Last modified date: 01/30/2023