Lecture 05: SQL DDL & Schemas

SQL Sublanguages, SQLite vs PostgreSQL Data Types, Table Creation, Constraints, and Alteration

Lecture Source Slide Deck

Original Slides: Lecture 05 (22 slides) · Amit Kumar Dhar (IIT Bhilai)
Download lecture05.pdf

1. The Four SQL Sublanguages

SQL is divided into four distinct sublanguages based on operational scope:

+--------------------------------------------------------------------------+
|                                    SQL                                   |
+-------------------+-------------------+------------------+---------------+
|        DDL        |        DML        |       DCL        |      TCL      |
|  Data Definition  | Data Manipulation |   Data Control   |  Transaction  |
+-------------------+-------------------+------------------+---------------+
| CREATE            | SELECT            | GRANT            | COMMIT        |
| ALTER             | INSERT            | REVOKE           | ROLLBACK      |
| DROP              | UPDATE            |                  | SAVEPOINT     |
| TRUNCATE          | DELETE            |                  |               |
+-------------------+-------------------+------------------+---------------+

2. Data Types: SQLite vs. PostgreSQL

Logical Type	SQLite Affinity	PostgreSQL Native Type	Practical Notes
Integer	`INTEGER`	`INT`, `BIGINT`, `SMALLINT`	SQLite uses variable-length 1 to 8-byte ints.
Floating Point	`REAL`	`REAL`, `DOUBLE PRECISION`	IEEE 754 floating point numbers.
Exact Decimal	`NUMERIC`	`NUMERIC(precision, scale)`	Crucial for currency/financial data (no float rounding errors).
Variable String	`TEXT`	`VARCHAR(n)`, `TEXT`	In SQLite, `VARCHAR(50)` is accepted but length is not enforced.
Boolean	`INTEGER` (0 / 1)	`BOOLEAN` (`TRUE` / `FALSE`)	SQLite stores Booleans internally as `0` and `1`.
Date & Time	`TEXT` (ISO 8601)	`DATE`, `TIMESTAMP WITH TIME ZONE`	SQLite stores as `'YYYY-MM-DD HH:MM:SS'`.

3. Schema Constraints & Integrity Rules

CREATE TABLE department (
    dept_name   VARCHAR(20) PRIMARY KEY,
    building    VARCHAR(15) NOT NULL,
    budget      NUMERIC(12, 2) CHECK (budget > 0)
);

CREATE TABLE instructor (
    id          CHAR(5) PRIMARY KEY,
    name        VARCHAR(20) NOT NULL,
    dept_name   VARCHAR(20),
    salary      NUMERIC(8, 2) DEFAULT 30000.00 CHECK (salary >= 20000),
    FOREIGN KEY (dept_name) REFERENCES department(dept_name)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

The 6 Fundamental Constraint Types

PRIMARY KEY:
- Enforces both UNIQUE and NOT NULL automatically.
- Creates an automatic primary index (usually B+ Tree).
- Only one primary key per table (can be composite across multiple columns).
FOREIGN KEY:
- Enforces referential integrity with a referenced table's primary key or unique column.
- SQLite note: Foreign key enforcement must be activated per session via:
```
PRAGMA foreign_keys = ON;
```
NOT NULL:
- Forbids inserting NULL into the attribute.
UNIQUE:
- Guarantees no two rows share the same non-null value. Multiple NULL values are permitted in standard SQL.
CHECK (condition):
- Validates that every inserted or modified tuple satisfies a boolean expression (e.g., CHECK (salary > 0 AND salary < 1000000)).
DEFAULT value:
- Automatically populates the attribute if omitted in an INSERT statement.

4. Modifying and Dropping Tables

-- Add a new column
ALTER TABLE instructor ADD COLUMN email VARCHAR(50);

-- Rename a table
ALTER TABLE instructor RENAME TO faculty;

-- Drop an existing table
DROP TABLE IF EXISTS faculty;

5. Topic-Specific SQL Practice Questions

Schema Requirements:

student_id (Integer referencing Students)
course_id (Text referencing Courses)
enrollment_date (Text default current date)
Composite Primary Key on (student_id, course_id)
On student deletion, cascade the delete.

Solution:

CREATE TABLE Enrollments (
    student_id      INTEGER,
    course_id       TEXT,
    enrollment_date TEXT DEFAULT (CURRENT_DATE),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES Students(student_id) 
        ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES Courses(course_id) 
        ON DELETE RESTRICT
);

Lecture 05: SQL DDL & Schemas

1. The Four SQL Sublanguages

2. Data Types: SQLite vs. PostgreSQL

3. Schema Constraints & Integrity Rules

The 6 Fundamental Constraint Types

4. Modifying and Dropping Tables

5. Topic-Specific SQL Practice Questions

Q1: Write the DDL to create an Enrollments table with a composite primary key and foreign key cascading.

Q2: Why does SQLite require executing 'PRAGMA foreign_keys = ON;'?

Q3: What is the semantic difference between DROP TABLE and TRUNCATE TABLE?

On this page