← SQL roadmap

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

TypeUse forNote
INT / BIGINTIDs, countsBIGINT for > 2.1 billion
DECIMAL(10,2)MoneyExact, no rounding errors
VARCHAR(n)Names, emailsVariable length
TEXTLong textNot fully indexable
DATE / DATETIMEDates, timesTIMESTAMP 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.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “CREATE TABLE & Data Types” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.