DDL with engine clause, defaults, and IF NOT EXISTS. Prefer explicit types and NOT NULL where business rules require values.
Table definition
CREATE TABLE IF NOT EXISTS orders (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
customer_id BIGINT UNSIGNED NOT NULL,
status ENUM('open','paid','shipped') NOT NULL DEFAULT 'open',
total DECIMAL(12,2) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;Practice: Run on practice.
ALTER preview
ALTER TABLE orders ADD COLUMN notes TEXT NULL AFTER status;
ALTER TABLE orders MODIFY COLUMN notes VARCHAR(500) NULL;ENUM caution
ENUM is convenient but awkward to evolve—VARCHAR + app validation or lookup table for volatile sets.
Important interview questions and answers
- Q: ENGINE=InnoDB?
A: Transactional storage with row locks and FK support. - Q: CURRENT_TIMESTAMP?
A: Default for created_at on insert—ON UPDATE for updated_at patterns.
Self-check
- Why ENGINE=InnoDB?
- When is ENUM risky?
Tip: Prefer TIMESTAMP/DATETIME defaults for audit columns.
Interview prep
- ENGINE=InnoDB?
Transactional storage with FK support.
- ENUM risk?
Hard to change allowed values in large tables.