Topic 15 of 52 · Beginner
CREATE TABLE & Data Types
Design tables with the right column types.
Concept
CREATE TABLE defines a table's columns and their types. Choosing the smallest correct type saves space and avoids bugs.
Use INT/BIGINT for whole numbers, DECIMAL(p,s) for money (never FLOAT), VARCHAR(n) for text, DATE/DATETIME/TIMESTAMP for time, and BOOLEAN (TINYINT(1)) for flags.
ALTER TABLE adds, changes or drops columns; DROP TABLE deletes the table; TRUNCATE empties it fast without logging every row.
Cheat sheet
| Type | Use for | Note |
|---|---|---|
| INT / BIGINT | IDs, counts | BIGINT for > 2.1 billion |
| DECIMAL(10,2) | Money | Exact, no rounding errors |
| VARCHAR(n) | Names, emails | Variable length |
| TEXT | Long text | Not fully indexable |
| DATE / DATETIME | Dates, times | TIMESTAMP is stored in UTC |
Code (SQL · MySQL 8)
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE,
city VARCHAR(50),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE customers ADD COLUMN phone VARCHAR(15);
ALTER TABLE customers MODIFY city VARCHAR(80);Remember
- Money → DECIMAL, never FLOAT.
- TRUNCATE resets AUTO_INCREMENT; DELETE does not.
- DROP removes structure and data; TRUNCATE keeps structure.
Interview questions
DELETE vs TRUNCATE vs DROP?
DELETE removes chosen rows, is logged and can be rolled back. TRUNCATE removes all rows quickly and resets identity. DROP removes the table itself.
CHAR vs VARCHAR?
CHAR(n) is fixed length and padded; VARCHAR(n) stores only the characters used plus a length byte. Use CHAR only for fixed codes like country codes.
Lesson test
Take an 8-question test on “CREATE TABLE & Data Types” — concepts, output prediction, bug spotting and interview questions.