Creating Tables: CREATE TABLE and Data Types

Design your own tables and pick the right box for every piece of data

Big Idea: So far you have read data that already lived in tables. Now you become the builder. CREATE TABLE lets you design a brand-new table by naming its columns and choosing a data type for each one. A data type is a promise about what kind of value a column will hold.

By the end you will be able to:

write a CREATE TABLE statement with several columns,
choose the right data type: INT, DECIMAL, VARCHAR, TEXT, DATE, DATETIME, BOOLEAN,
make a column required with NOT NULL and give it a fallback with DEFAULT,
change a table later with ALTER TABLE,
remove a table with DROP TABLE.

1. What CREATE TABLE does

Think of a table as an empty spreadsheet you design before anyone types data in. You give the table a name, then you list its columns. For each column you write two things: the column's name and its data type.

Here is the smallest possible shape of the command:

CREATE TABLE table_name (
    column1  TYPE,
    column2  TYPE,
    column3  TYPE
);

The column definitions live inside round brackets, separated by commas, and the whole statement ends with a semicolon. Running it does not add any rows yet - it only builds the empty structure, ready for the data you will add in the next topic.

2. Data types: the right box for every value

A data type tells the database what kind of value a column may store. Picking the right type is like choosing the right container in your kitchen: you keep milk in a bottle, not in a paper bag. The type protects your data and saves space.

These are the everyday types you will use again and again:

Type	Holds	Example value
`INT`	Whole numbers (no decimal point)	`7`, `-40`, `2026`
`DECIMAL(5,2)`	Exact numbers with decimals	`19.99`, `4.50`
`VARCHAR(50)`	Short text up to a length you choose	`'Sara'`, `'Blue'`
`TEXT`	Long text (paragraphs, notes)	a whole review
`DATE`	A calendar day	`'2026-09-01'`
`DATETIME`	A day and a time	`'2026-09-01 08:30:00'`
`BOOLEAN`	True or false (yes/no)	`TRUE`, `FALSE`

A closer look:

INT is for counting things: an age, a grade, how many books. No fractions allowed.
DECIMAL(5,2) means "up to 5 digits in total, 2 of them after the dot". So it can store numbers up to 999.99. Use it for money and exact measurements, because it never rounds in surprising ways.
VARCHAR(n) holds short text and you pick the biggest length n. VARCHAR(50) means "up to 50 characters". Names, colours and cities fit here.
TEXT is for long writing where you cannot guess the length - a comment, a description, a story.
DATE stores a day as YYYY-MM-DD. DATETIME also keeps the clock time, which is perfect for "when did this happen".
BOOLEAN answers a yes/no question, like "is this student active?".

Dialect note In MySQL, BOOLEAN is really stored as a tiny number: TRUE is 1 and FALSE is 0. PostgreSQL has a real boolean type, and SQLite is flexible about types, but the idea is the same everywhere.

Creating Tables: CREATE TABLE and Data Types