Real-life analogy: The file cabinet system

DDL is the office manager who designs the filing system (CREATE/ALTER/DROP TABLE). DML is the receptionist who files, retrieves, and updates documents (INSERT, SELECT, UPDATE, DELETE). DCL is the security officer who decides who can access which cabinet (GRANT, REVOKE). TCL is the auditor who approves or rejects batches of changes (COMMIT, ROLLBACK).

DDL — Data Definition Language

-- CREATE TABLE with all constraint types
CREATE TABLE Department (
    DeptID   INT           PRIMARY KEY,
    DeptName VARCHAR(100)  NOT NULL UNIQUE,
    Budget   DECIMAL(12,2) CHECK (Budget > 0)
);

CREATE TABLE Student (
    StudentID  INT          PRIMARY KEY,
    FirstName  VARCHAR(50)  NOT NULL,
    LastName   VARCHAR(50)  NOT NULL,
    Email      VARCHAR(100) UNIQUE NOT NULL,
    DOB        DATE,
    GPA        DECIMAL(3,2) CHECK (GPA BETWEEN 0.00 AND 4.00),
    DeptID     INT          REFERENCES Department(DeptID)
                            ON DELETE SET NULL
                            ON UPDATE CASCADE,
    EnrollDate DATE         DEFAULT CURRENT_DATE,
    Status     VARCHAR(20)  DEFAULT 'Active'
                            CHECK (Status IN ('Active','Graduated','Suspended'))
);

-- ALTER TABLE: modify existing structure
ALTER TABLE Student ADD COLUMN PhoneNumber VARCHAR(15);
ALTER TABLE Student DROP COLUMN PhoneNumber;
ALTER TABLE Student ADD CONSTRAINT chk_dob CHECK (DOB < '2010-01-01');

-- DROP vs TRUNCATE vs DELETE
DROP TABLE Student;          -- Remove table (structure + data)
TRUNCATE TABLE Student;      -- Remove all rows, keep structure, fast, less logged
DELETE FROM Student;         -- Remove all rows, fully logged, rollback-able

-- CREATE INDEX
CREATE INDEX idx_student_dept ON Student(DeptID);
CREATE UNIQUE INDEX idx_email ON Student(Email);
CREATE INDEX idx_name ON Student(LastName, FirstName);  -- Composite

DML — INSERT, UPDATE, DELETE

-- INSERT: single row
INSERT INTO Department VALUES (1, 'Computer Science', 500000);
INSERT INTO Department (DeptID, DeptName) VALUES (2, 'Mathematics');

-- INSERT multiple rows
INSERT INTO Student (StudentID, FirstName, LastName, Email, DeptID) VALUES
    (101, 'Ravi',  'Kumar',  'ravi@univ.edu',  1),
    (102, 'Priya', 'Sharma', 'priya@univ.edu', 2),
    (103, 'Ahmed', 'Khan',   'ahmed@univ.edu', 1);

-- INSERT from SELECT (copy data)
INSERT INTO ArchivedStudents SELECT * FROM Student WHERE Status = 'Graduated';

-- UPDATE rows
UPDATE Student SET GPA = 3.9 WHERE StudentID = 101;
UPDATE Student SET GPA = GPA + 0.1 WHERE DeptID = 1;  -- Computed

-- DELETE rows
DELETE FROM Student WHERE StudentID = 103;
DELETE FROM Student WHERE DeptID NOT IN (SELECT DeptID FROM Department);

-- UPSERT (PostgreSQL ON CONFLICT)
INSERT INTO Student (StudentID, FirstName, LastName, Email, GPA)
VALUES (101, 'Ravi', 'Kumar', 'ravi@univ.edu', 3.95)
ON CONFLICT (StudentID) DO UPDATE SET GPA = EXCLUDED.GPA;

SQL Data Types

CHAR vs VARCHAR: CHAR(n) always stores exactly n characters (pads with spaces). VARCHAR(n) stores only what you put in. CHAR(50) storing "Ravi" uses 50 bytes; VARCHAR(50) uses 4 bytes. Always use VARCHAR unless you need fixed-width (e.g., country codes). DECIMAL(p,s): p = total digits, s = digits after decimal point.

Never store money in FLOAT: FLOAT and DOUBLE are binary floating-point: 0.1 has no exact binary representation, so 0.1 + 0.2 = 0.30000000000000004 and rounding errors accumulate silently across millions of transactions until a ledger fails to balance. Use DECIMAL(p,s) (exact base-10 arithmetic) for money, or store integer paise/cents. Reserve FLOAT for genuinely approximate quantities — sensor readings, ML features, coordinates — where a tiny relative error is acceptable.

DCL and TCL — the two categories everyone forgets

Exams ask you to classify SQL statements into five categories, and candidates reliably know DDL, DML, and DQL while blanking on the last two. The distinguishing question is what does the statement act on? — structure, data, results, permissions, or the transaction itself.

-- ── DCL: who is allowed to do what ──
GRANT SELECT, INSERT ON Orders TO analyst_role;
GRANT SELECT ON Orders TO reporting_user WITH GRANT OPTION;  -- may re-grant
REVOKE INSERT ON Orders FROM analyst_role;

-- ── TCL: all-or-nothing units of work ──
BEGIN;
    UPDATE Account SET Balance = Balance - 1000 WHERE AccountID = 'A';
    SAVEPOINT after_debit;          -- a partial rollback marker

    UPDATE Account SET Balance = Balance + 1000 WHERE AccountID = 'B';
    -- Something went wrong with only the credit:
    -- ROLLBACK TO SAVEPOINT after_debit;   -- undo credit, KEEP the debit
COMMIT;                              -- make both permanent

-- ⚠️ The DDL trap inside a transaction:
BEGIN;
    INSERT INTO Orders VALUES (1, 'pending');
    TRUNCATE TABLE Audit;   -- DDL → implicit COMMIT in MySQL/Oracle!
    ROLLBACK;               -- Too late: the INSERT was already committed.
-- PostgreSQL is the exception: its DDL is transactional and this rolls back.

DELETE vs TRUNCATE vs DROP — the exam trio: DELETE (DML): removes rows one at a time, fires triggers, logs each row, honours WHERE, and can be rolled back. TRUNCATE (DDL): deallocates whole data pages, no WHERE, no row triggers, resets identity/auto-increment counters, far faster on big tables — and auto-commits in most engines. DROP (DDL): removes the rows, the table definition, its indexes, and its constraints entirely. Memory hook: DELETE edits the contents, TRUNCATE empties the container, DROP throws the container away.

Practice questions

1. Difference between DROP, DELETE, and TRUNCATE? (Answer: DROP removes entire table structure and data. DELETE removes specific rows — logged, rollback-able, can use WHERE. TRUNCATE removes all rows without per-row logging — faster, cannot use WHERE, difficult to rollback.)
2. DECIMAL(7,3) — what is the maximum value it can store? (Answer: 9999.999 — 4 digits before decimal, 3 after.)
3. Difference between NULL and empty string in SQL? (Answer: NULL = unknown/missing data, not a value. Empty string = valid zero-length string. NULL comparisons use IS NULL / IS NOT NULL, not = NULL.)
4. ON DELETE CASCADE vs ON DELETE SET NULL: when to use each? (Answer: CASCADE: child rows have no meaning without parent (OrderItems when Order deleted). SET NULL: child can exist independently (Employee.DeptID can be NULL if department closed).)
5. Can a table have no primary key? (Answer: Technically yes (heap table) but bad practice — duplicate rows possible, UPDATE/DELETE may affect unintended rows, FK references impossible. Every table should have a PK.)
6. Classify: GRANT, COMMIT, TRUNCATE, MERGE, SELECT. (Answer: GRANT = DCL (permissions). COMMIT = TCL (transaction control). TRUNCATE = DDL (structure — it deallocates pages, not rows). MERGE = DML (row manipulation). SELECT = DQL (querying). The two most-missed: TRUNCATE is DDL despite feeling like DELETE, and GRANT/REVOKE form their own DCL category.)
7. Why should a Price column be DECIMAL(10,2) rather than FLOAT? (Answer: FLOAT is binary floating-point and cannot represent decimal fractions like 0.1 exactly, so 0.1 + 0.2 = 0.30000000000000004. Errors accumulate across many transactions and a financial ledger silently stops balancing. DECIMAL stores exact base-10 values with fixed scale — the correct type for money. Alternative: store integer paise/cents.)
8. Inside a transaction you INSERT a row, then TRUNCATE another table, then ROLLBACK. Is the INSERT undone? (Answer: In MySQL and Oracle, no — TRUNCATE is DDL and triggers an implicit COMMIT, permanently saving the INSERT before the ROLLBACK runs. PostgreSQL is the notable exception: its DDL is transactional, so the ROLLBACK undoes both. This is why mixing DDL into transactional logic is dangerous and engine-specific.)