DBMS

SQL Fundamentals

DDL: Defining Schema

In many databases (notably MySQL and Oracle),

JrCodex·6 min read

Jr Codex DBMS Notes

Level: Beginner Prerequisites: Module 2, Chapter 4: From Algebra to SQL Time to complete: ~20 minutes

Table of Contents

  1. The Three Sublanguages
  2. Data Types
  3. CREATE TABLE
  4. The Working Dataset
  5. ALTER and DROP
  6. Summary & Next Steps

1. The Three Sublanguages

SQL Splits Into Three
─────────────────────────────────────────
  DDL   Data Definition Language
        CREATE, ALTER, DROP, TRUNCATE
        Defines and changes STRUCTURE.
        ── this chapter

  DML   Data Manipulation Language
        SELECT, INSERT, UPDATE, DELETE
        Works with the DATA.
        ── Chapters 2-6

  DCL   Data Control Language
        GRANT, REVOKE
        Controls PERMISSIONS.
─────────────────────────────────────────

A Trap Worth Knowing Now
─────────────────────────────────────────
  In many databases (notably MySQL and Oracle),
  DDL statements cause an IMPLICIT COMMIT — they
  end any transaction in progress and cannot be
  rolled back.

  PostgreSQL is the notable exception: DDL is
  transactional there, so a failed migration rolls
  back cleanly.

  This is why migration tooling differs so much
  between databases, and why you test migrations
  on a copy first.
─────────────────────────────────────────

2. Data Types

The Families
─────────────────────────────────────────
  NUMERIC
    INTEGER / BIGINT      whole numbers
    NUMERIC(p,s)          EXACT decimal — use for
                          MONEY
    REAL / DOUBLE         floating point —
                          NEVER for money

  TEXT
    VARCHAR(n)            variable, capped length
    TEXT                  unbounded
    CHAR(n)               fixed, space-padded

  TEMPORAL
    DATE, TIME, TIMESTAMP
    TIMESTAMPTZ           with time zone — prefer
                          this one

  OTHER
    BOOLEAN, BLOB/BYTEA, UUID, JSON/JSONB, ARRAY
─────────────────────────────────────────

The Money Rule
─────────────────────────────────────────
  Use NUMERIC(10,2), never REAL or DOUBLE.

  Floating point cannot represent 0.1 exactly, so
  sums drift. After a million transactions your
  ledger disagrees with itself by a few cents and
  nobody can find where.

  NUMERIC is slower and exact. For money, exact
  wins every time.
─────────────────────────────────────────

Choosing Well
─────────────────────────────────────────
  - Smallest type that fits: INTEGER over BIGINT
    unless you truly need it. Every row and every
    index entry pays the difference (Module 5).
  - TIMESTAMPTZ over TIMESTAMP — store an instant,
    not a wall-clock reading with no time zone.
  - VARCHAR(n) where n is a real business rule;
    TEXT otherwise. An arbitrary VARCHAR(255) is
    cargo cult.
  - A constrained TEXT with CHECK beats an
    unconstrained one for status-like columns.
─────────────────────────────────────────

3. CREATE TABLE

CREATE TABLE students (
    id         INTEGER PRIMARY KEY,                 -- surrogate key (Module 2, Ch.2)
    email      TEXT    NOT NULL UNIQUE,             -- natural key, still enforced
    name       TEXT    NOT NULL,
    city       TEXT,                                -- nullable: genuinely optional
    marks      INTEGER CHECK (marks BETWEEN 0 AND 100),
    enrolled_on TEXT   NOT NULL DEFAULT (date('now'))
);
Column vs Table Constraints
─────────────────────────────────────────
  COLUMN constraint — attached to one column,
  can only reference that column.

  TABLE constraint — declared separately, CAN
  reference several columns. Required for
  composite keys and cross-column CHECKs.
─────────────────────────────────────────

CREATE TABLE enrollments (
    student_id INTEGER NOT NULL REFERENCES students(id) ON DELETE CASCADE,
    course_id  INTEGER NOT NULL REFERENCES courses(id)  ON DELETE RESTRICT,
    grade      TEXT    CHECK (grade IN ('A','B','C','D','F')),
    graded_on  TEXT,
    PRIMARY KEY (student_id, course_id),                    -- TABLE constraint: composite
    CHECK (grade IS NULL OR graded_on IS NOT NULL)          -- TABLE constraint: 2 columns
);
Name Your Constraints
─────────────────────────────────────────
  CONSTRAINT chk_marks_range CHECK (marks BETWEEN 0 AND 100)

  Unnamed constraints get generated names like
  `students_marks_check1`. Named ones produce
  readable error messages and can be dropped by
  name in a migration.

  Worth the extra words on anything non-trivial.
─────────────────────────────────────────

4. The Working Dataset

Every remaining chapter in this module uses this. Run it once.

import sqlite3
 
conn = sqlite3.connect("school.db")
conn.execute("PRAGMA foreign_keys = ON")
 
conn.executescript("""
DROP TABLE IF EXISTS enrollments;
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS courses;
 
CREATE TABLE students (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    city  TEXT,
    marks INTEGER CHECK (marks BETWEEN 0 AND 100)
);
 
CREATE TABLE courses (
    id      INTEGER PRIMARY KEY,
    title   TEXT NOT NULL,
    credits INTEGER NOT NULL CHECK (credits > 0),
    dept    TEXT NOT NULL
);
 
CREATE TABLE enrollments (
    student_id INTEGER NOT NULL REFERENCES students(id) ON DELETE CASCADE,
    course_id  INTEGER NOT NULL REFERENCES courses(id)  ON DELETE RESTRICT,
    grade      TEXT CHECK (grade IN ('A','B','C','D','F')),
    PRIMARY KEY (student_id, course_id)
);
 
INSERT INTO students (id, name, city, marks) VALUES
    (1,'Asha','Pune',88),   (2,'Ravi','Delhi',74),
    (3,'Meera','Pune',91),  (4,'Karan','Mumbai',65),
    (5,'Divya','Delhi',82), (6,'Sam','Pune',NULL);   -- NULL marks: not yet assessed
 
INSERT INTO courses (id, title, credits, dept) VALUES
    (10,'Databases',4,'CS'),   (11,'Networks',3,'CS'),
    (12,'Statistics',4,'Math'),(13,'Ethics',2,'Phil');
 
INSERT INTO enrollments (student_id, course_id, grade) VALUES
    (1,10,'A'), (1,11,'B'), (1,12,'A'),
    (2,10,'C'), (2,12,'B'),
    (3,10,'A'), (3,11,'A'), (3,12,'A'), (3,13,'B'),
    (4,11,'D'),
    (5,10,'B'), (5,13,NULL);                          -- enrolled, not yet graded
-- NOTE: student 6 (Sam) is enrolled in NOTHING, and course 13 has few students.
-- Both are deliberate — they make outer joins and NULL handling visible later.
""")
conn.commit()
Why the Dataset Has Awkward Bits
─────────────────────────────────────────
  Sam has NULL marks and no enrollments.
  One enrollment has a NULL grade.

  A tidy dataset hides exactly the behaviour that
  causes real bugs. These rows make outer joins,
  aggregate NULL-skipping and IS NULL visible in
  every later chapter.
─────────────────────────────────────────

5. ALTER and DROP

ALTER TABLE students ADD COLUMN phone TEXT;
ALTER TABLE students RENAME COLUMN marks TO score;
ALTER TABLE students DROP COLUMN phone;
ALTER TABLE students ADD CONSTRAINT chk_score CHECK (score >= 0);   -- not in SQLite
Safe vs Unsafe Changes
─────────────────────────────────────────
  SAFE (additive)
    add a NULLABLE column
    add a column with a DEFAULT
    add an index (Module 5)
    relax a constraint

  UNSAFE (breaking)
    drop a column         — breaks SELECT *
    rename anything       — breaks every reference
    add NOT NULL without a default on a populated
      table  — fails immediately
    narrow a type         — may fail on existing
      rows

  This is Module 1, Chapter 3's LOGICAL DATA
  INDEPENDENCE being partial: additive changes are
  absorbed, removals are not.
─────────────────────────────────────────

DROP vs DELETE vs TRUNCATE
─────────────────────────────────────────
  DROP TABLE t      removes the TABLE and its data.
                    Structure gone.
  TRUNCATE TABLE t  removes all ROWS fast. Structure
                    stays. Usually not rollback-able,
                    and ignores row triggers.
  DELETE FROM t     removes rows one at a time,
                    logged, transactional,
                    rollback-able, fires triggers.

  DELETE when you may need to undo it. TRUNCATE
  when you are certain and the table is large.
─────────────────────────────────────────

6. Summary & Next Steps

Key Takeaways

  • SQL splits into DDL, DML and DCL; DDL implicitly commits in most databases, which is why migrations are tested on a copy first.
  • Use NUMERIC for money — floating point cannot represent decimal fractions exactly, and the drift is unfindable later.
  • Table-level constraints are required for composite keys and any rule spanning two columns; naming constraints buys readable errors and droppable migrations.
  • Additive schema changes are safe and removals are breaking, which is the practical face of partial logical data independence.

Concept Check

  1. Why is REAL the wrong type for a price column, and what goes wrong specifically?
  2. When must a constraint be declared at the table level rather than on a column?
  3. Which of DROP, TRUNCATE and DELETE can be rolled back, and when would you choose each?

Next Chapter

→ Chapter 2: Querying with SELECT

Jr Codex — 1-on-1 Personalized Coaching | Back to Module Index | Back to DBMS Index