CREATE, DROP & ALTER TABLE

CREATE TABLE lists column names and data types. Add constraints like PRIMARY KEY when you create the table or later with ALTER.

CREATE TABLE — define structure

CREATE TABLE lists column names and data types. Add constraints like PRIMARY KEY when you create the table or later with ALTER.

Real-life example: CREATE TABLE is designing a blank form — name box, date box, score box — before anyone fills it in.

Teachers table for the school DB

CREATE TABLE teachers (
  teacher_id INT PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL,
  department VARCHAR(60),
  hire_date DATE
);

DROP TABLE — remove a table

DROP TABLE deletes the table and all its data. Use TRUNCATE to empty a table but keep its structure.

Real-life example: DROP TABLE is throwing away the whole form template and every filled copy — not just erasing one row.

Drop or truncate

-- Remove table completely
DROP TABLE teachers;

-- Empty rows but keep columns (syntax varies slightly)
TRUNCATE TABLE enrollments;

ALTER TABLE — change existing structure

ALTER TABLE adds, drops, or modifies columns. Use it when requirements change — new phone column, rename field, etc.

Real-life example: ALTER TABLE is adding a new 'email' line to every printed form without reprinting old entries from scratch.

Add, modify, and drop columns

ALTER TABLE students ADD email VARCHAR(120);

ALTER TABLE students MODIFY grade_level INT;  -- MySQL
-- ALTER TABLE students ALTER COLUMN grade_level TYPE INT;  -- PostgreSQL

ALTER TABLE students DROP COLUMN email;