IST466: Lecture 2.

Creating Tables

Oracle Data Types

Number Types

When creating a table use the following syntax to declare a number:

column_name number(precision[, scale])

where precision is the total number of digits in the number (any integer between 1 and 38), and scale is the number of digits to the right of the decimal point. Examples:

price number(9,2)  -- valid numbers are 1, 234, 1234567.23, but not 12345678
id number(3) -- any integer with 3 digits of precision

Character Types

char for storing fixed-length character strings.
nchar for storing national characters in Unicode data type.
varchar2 is much the same as char data type, the difference between the two being that varchar2 has variable length. When columns are declared of this type, they will only be as long as the value that is assigned. To define a variable of this type use:
column_name varchar2(size char|byte)
The difference between char and byte in the declaration being that to keep Unicode characters you need more than 1 byte. (Max is 4000 bytes.)
nvarchar2 for storing variable-length unicode-based character data in database. The difference with varchar2 is the way the data is stored in the DB.
raw for storing variable-length binary data. (Max is 2000 bytes.)
blob for storing big binary data like video, audio, images, etc. (4Gb)
clob for storing large amount of character data.
nclob for storing large amount of national characters in the DB.
bfile contains pointers to large binary files stored in the file system of the database server.

Dates and Times

date stores date and time information.
timestamp is similar to date, but you can specify precision for the second field in a timestamp column (the date type can only store whole seconds).
timestamp with time zone is the timestamp type with an extension that contains an offset that represents the number of hours and minutes between the local time and the Universal Time Coordinate (UTC) aka Greenwich Mean Time (GMT).
timestamp with local time zone is much the same as the previous one, though in the case of the former the time zone is stored in the column according to your session time zone.
interval year to month
interval day to second

ANSI Data Types Conversion Charts

Numeric Data
Alternative Data Types	Oracle Equivalent Data Type
`numeric(precision, scale)`	`number(precision, scale)`
`decimal(precision, scale)`	`number(precision, scale)`
`integer`	`number(38)`
`int`	`number(38)`
`smallint`	`number(38)`
`float(b)`	`number`
`double precision`	`number`
`real`	`number`

Character Data
Alternative Data Types	Oracle Equivalent Data Type
`character(size)`	`char(size)`
`char(size)`	`char(size)`
`character varying(size)`	`varchar2(size)`
`char varying(size)`	`varchar2(size)`
`national character(size)`	`nchar(size)`
`national char(size)`	`nchar(size)`
`nchar(size)`	`nchar(size)`
`national character varying(size)`	`nvarchar2(size)`
`national char(size)`	`nvarchar2(size)`
`nchar varying(size)`	`nvarchar2(size)`

Constraints in `create table`

Please run all the commands below and try to forecast the output.

1. The Candidate Key

 
create table TROUBLE (
  City            varchar2(13) not null,
  SampleData      date not null,
  Noon            number(4,1),
  Midnight        number(4,1),
  constraint TROUBLE_UQ unique (City, SampleData)
);
insert into trouble (city, sampledata) values ('St-petersburg', to_date('08-22-2000', 'MM-DD-YYYY'));
insert into trouble (city, sampledata) values ('St-petersburg', to_date('08-22-2000', 'MM-DD-YYYY'));

2. The Primary Key

create table TROUBLE (
  City            varchar2(13) not null,
  SampleData      date not null,
  Noon            number(4,1),
  Midnight        number(4,1),
  constraint TROUBLE_PQ primary key (City, SampleData)
);
insert into trouble (city, sampledata) values ('St-petersburg', to_date('08-22-2000', 'MM-DD-YYYY'));
insert into trouble (city, sampledata) values ('St-petersburg', to_date('08-22-2000', 'MM-DD-YYYY'));

3. The Foreign Key

A. pure foreign key

create table lodging (
      lodg     varchar2(15) not null,
      somedata number,
      constraint LODGING_PK primary key (lodg)
);
create table worker (
  name    varchar2(25) not null,
  age     number,
  lodging varchar2(15),
  constraint worker_PK primary key (name),
  foreign key (lodging) references lodging(lodg)
);
insert into lodging values ('log15', 28);
insert into lodging values ('log17', 29);
insert into worker values ('Smith', 25, 'log15');
insert into worker values ('Jhons', 17, 'log17');
select * from lodging;
select * from worker;
delete from lodging where lodg='log15';

B. set null on delete

create table worker (
  name    varchar2(25) not null,
  age     number,
  lodging varchar2(15),
  constraint worker_PK primary key (name),
  foreign key (lodging) references lodging(lodg) on delete set null
);
insert into worker values ('Smith', 25, 'log15');
insert into worker values ('Jhons', 17, 'log17');
select * from worker;
delete from lodging where lodg='log15';
select * from worker;

C. cascade on delete

create table worker (
  name    varchar2(25) not null,
  age     number,
  lodging varchar2(15),
  constraint worker_PK primary key (name),
  constraint worker_FK foreign key (lodging) references lodging(lodg) on delete cascade
);
insert into worker values ('Smith', 25, 'log15');
insert into worker values ('Jhons', 17, 'log17');
delete from lodging where lodg='log15';
select * from worker;

4. The Check Constraint

A. Check constraints applied to one column

create table worker (
  name    varchar2(25) not null,
  age     number check (age between 18 and 65),
  lodging varchar2(15),
  constraint worker_PK primary key (name),
  constraint worker_FK foreign key (lodging) references lodging(lodg) on delete cascade 
);

create table worker (
  name    varchar2(25) not null,
  age     number,
  lodging varchar2(15),
  constraint worker_PK primary key (name),
  constraint worker_FK foreign key (lodging) references lodging(lodg) on delete cascade, 
  constraint worker_age check (age between 18 and 65)
);

insert into worker values ('Smith', 25, 'log15');
insert into worker values ('Jhons', 17, 'log17');
insert into worker values ('Jhons', 17, 'log17')

B. Check constraints applied to several columns

create table quantity (
   min number not null,
   max number not null,
   /* constraints */
   constraint Quantity_minmax check (max>min)
);
insert into quantity values (12, 15);
insert into quantity values (15, 12);