DDL, Domains & CREATE TABLE

Lecture 4 · SQL as a Data Definition Language — defining schema, choosing domain types, and enforcing integrity constraints.

SQL began as IBM's SEQUEL (System R, San Jose), was renamed SQL, and standardised as SQL-86 → 89 → 92 → SQL:1999 → SQL:2003. Most engines implement SQL-92 plus extras, so not every statement runs identically everywhere.

Domain (data) types

A domain = a base type + optional constraints. Learn these families:

Exact numeric

SMALLINT · INT · BIGINT · DECIMAL(p,s) · NUMERIC(p,s)

IDs, counts, money & GPA (exact — no rounding)

Approx numeric

REAL · FLOAT · DOUBLE

Scientific / sensor data — avoid for money

Character

CHAR(n) · VARCHAR(n) · TEXT / CLOB

Fixed codes · names & emails · long text

Date / Time

DATE · TIME · TIMESTAMP · INTERVAL

Birthdate · class time · created_at · durations

Boolean

BOOLEAN

TRUE / FALSE (fallback: TINYINT 0/1, CHAR(1))

Binary / LOB

BINARY · VARBINARY · BLOB · CLOB

Hashes, images, PDFs, long text

Other (DB-specific)

JSON / JSONB · UUID · XML · spatial

Semi-structured, distributed IDs, GIS

DECIMAL(p,s): p = total digits, s = digits after the point. DECIMAL(10,2) → up to 10 digits, 2 decimals — the correct choice for currency.

The CREATE TABLE construct

r = relation name · Ai = attribute · Di = its domain. A worked example — this statement builds the instructor table below:

create table

create table instructor (
  ID         char(5),
  name       varchar(20) not null,
  dept_name  varchar(20),
  salary     numeric(8,2),
  primary key (ID),
  foreign key (dept_name) references department
);

Resulting table (with sample rows)

ID	name	dept_name	salary
1	Abdur	CSE	10000
2	aman	CSE	12500
3	bilal	CSE	17000
4	morshed	EEE	12000
5	rashid	BBA	10000
6	irshad	BBA	7800

6 tuples · 4 attributes

Integrity constraints

not null

Column may never hold NULL.

primary key (…)

Unique + automatically NOT NULL.

foreign key … references r

Value must exist as a key in the referenced relation.

Exam gold: a primary key already implies not null — you don't write both. A composite PK primary key (ID, course_id, sec_id, semester, year) is how junction & weak-entity tables are keyed.

Next: INSERT · UPDATE · DELETE →