SQL, creating a table

A database is made up of one or more tables.

You create a table in SQL with the CREATE TABLE command.

At creation time you specify the column names and the type of data each column will hold.

SQL defines several kinds of data.

The most important ones, and the ones you will see most often, are:

• CHAR
• TEXT
• VARCHAR
• DATE
• TIME
• DATETIME
• TIMESTAMP

Numeric types include:

• TINYINT 1 byte
• INT 4 bytes
• BIGINT 8 bytes
• SMALLINT 2 bytes
• DECIMAL
• FLOAT

They all hold numbers. What changes is the size that number can be.

A TINYINT goes from 0 to 255. An INT goes from -2^31 to +2^31.

The bigger the size in bytes, the more storage space the column needs.

This is the syntax to create a people table with two columns, one an integer and the other a variable-length string:

CREATE TABLE people (
  age INT,
  name CHAR(20)
);

Run that statement and the database creates an empty table ready for rows. You can confirm it exists with your database’s table-listing command before you insert data.

Pick types from how you will query the column, not from habit. Age as INT works. A phone number stored as INT loses leading zeroes. Money stored as FLOAT can surprise you with rounding.

You can add columns later with ALTER TABLE, but choosing sensible types at creation time saves migration work.

If you use an ORM, I built a free schema converter that turns CREATE TABLE statements into Prisma or Drizzle schemas (and back).