CREATE TABLE & Data Types

So far you've queried tables someone else built. Now you become the architect. CREATE TABLE defines a new table's columns and the data type each one holds — the foundation every other SQL command stands on.

Learn CREATE TABLE & Data Types in our free SQL course — a beginner-friendly interactive lesson with runnable queries, a practice exercise and a quick recall.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

By the end you'll know the everyday data types, how to pick the right one (especially for money), and how to add NOT NULL , DEFAULT , and a PRIMARY KEY as you create a table.

A column's type decides what it can store and how much space it takes. Keep this cheat sheet handy.

The pattern is CREATE TABLE name (column type, column type, …) . Each line names a column and the type of data it will hold. The table starts empty — you add rows later with INSERT .

Creating a table is like printing a blank spreadsheet with labelled, typed columns: "this column only accepts dates, this one only whole numbers". The rules are set before any data arrives.

Choosing the right type matters for correctness and storage. The golden rule: use DECIMAL for money . REAL / FLOAT are approximate and introduce rounding errors that are unacceptable for currency.

Two column options you'll use constantly: NOT NULL forces every row to supply a value, and DEFAULT fills in a value when you don't. Together they keep your data tidy from day one.

Every table should have a PRIMARY KEY — a column whose value uniquely identifies each row. It's automatically NOT NULL and unique. Auto-incrementing ids are the most common choice, though the keyword differs by engine.

Fill in the blanks to create a small tasks table with a primary key, a required title, and a default flag.

Re-running a setup script shouldn't crash. CREATE TABLE IF NOT EXISTS creates the table only if it's missing, turning a duplicate run into a harmless no-op.

You can build a table directly from a query's results — handy for snapshots and quick backups. The new table inherits the columns and data, though usually not the keys or constraints.

These lines of a CREATE TABLE statement are scrambled. Put them in the correct order:

1) Which type should store a product price like 19.99?

DECIMAL(p, s) — exact base-10. Never FLOAT / REAL , which round and would corrupt money totals.

2) You insert a row but omit a DEFAULT TRUE column. What is stored?

TRUE . DEFAULT supplies the value when you don't provide one.

3) What does PRIMARY KEY imply about a column besides uniqueness?

It's automatically NOT NULL — a primary key can never be empty, since it identifies the row.

Design a real table with the right type for each column. Write the CREATE TABLE , then run it to confirm it's valid.

Practice quiz

What is the basic shape of a CREATE TABLE statement?

  • CREATE TABLE name SELECT ...
  • CREATE name TABLE (columns)
  • CREATE TABLE name (column type, column type, ...)
  • TABLE CREATE name (columns)

Answer: CREATE TABLE name (column type, column type, ...). The pattern is CREATE TABLE name (column type, column type, ...), naming each column and its type.

Which data type should you use to store money like 19.99?

  • DECIMAL
  • FLOAT
  • REAL
  • INTEGER

Answer: DECIMAL. DECIMAL stores exact base-10 values; FLOAT/REAL are approximate and cause rounding errors for money.

What does the NOT NULL constraint do?

  • Fills the column with zeros
  • Makes the column unique
  • Sets a default value
  • Forbids the column from holding NULL — a value must always be provided

Answer: Forbids the column from holding NULL — a value must always be provided. NOT NULL requires every row to supply a value for that column.

What does DEFAULT TRUE do for a BOOLEAN column?

  • Forces every row to be true
  • Supplies true when the column is omitted on insert
  • Makes the column required
  • Deletes false rows

Answer: Supplies true when the column is omitted on insert. DEFAULT supplies the given value when you don't provide one on INSERT.

Besides being unique, what does PRIMARY KEY imply about a column?

  • It is automatically NOT NULL
  • It is automatically a foreign key
  • It allows duplicate values
  • It defaults to zero

Answer: It is automatically NOT NULL. A PRIMARY KEY uniquely identifies each row and is automatically NOT NULL.

What does VARCHAR(120) store?

  • Exactly 120 characters always
  • 120 rows
  • Variable-length text up to 120 characters
  • A number up to 120

Answer: Variable-length text up to 120 characters. VARCHAR(n) holds variable-length text up to n characters, storing only what you put in.

How does CHAR(8) differ from VARCHAR(8)?

  • CHAR is for numbers
  • CHAR is fixed-length and pads to 8 characters; VARCHAR is variable-length
  • They are identical
  • CHAR holds longer text

Answer: CHAR is fixed-length and pads to 8 characters; VARCHAR is variable-length. CHAR(n) is fixed-length and pads shorter values; VARCHAR(n) is variable up to the limit.

What does CREATE TABLE IF NOT EXISTS settings (...) do when the table already exists?

  • Drops and recreates it
  • Raises an error
  • Empties the table
  • Does nothing (a harmless no-op)

Answer: Does nothing (a harmless no-op). IF NOT EXISTS creates the table only when missing, turning a duplicate run into a no-op.

What does CREATE TABLE electronics AS SELECT ... do?

  • Renames an existing table
  • Builds a new table from a query's result columns and data
  • Deletes rows from a table
  • Adds a column

Answer: Builds a new table from a query's result columns and data. CREATE TABLE AS SELECT builds a new table from a query, copying its columns and data (usually not keys).

What common mistake is a comma after the last column before the closing parenthesis?

  • It is required
  • It creates an extra column
  • It is a trailing-comma syntax error
  • It defaults the column

Answer: It is a trailing-comma syntax error. A trailing comma after the final column before ) is a syntax error and must be removed.