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