All posts
SQLNovember 11, 20268 min read

SQL CREATE TABLE Explained for Beginners (Examples)

Learn SQL CREATE TABLE step by step: columns, data types, primary keys and NOT NULL, with copy-paste examples. A friendly guide for absolute beginners.

F
Fepiq Team
Fepiq

How do you create a table in SQL? You use the CREATE TABLE statement. You give the table a name, then list its columns, and for each column you say what kind of data it holds. That is the whole idea. In this guide you will learn it step by step, with small examples you can copy and run.

No experience is needed. Every new word is explained the first time it appears.

What is a table in SQL?

A database is a place where a computer stores information. Inside a database, information lives in tables. A table looks like a spreadsheet. It has columns (the headings, like name or email) and rows (one entry each, like one customer).

SQL stands for Structured Query Language. It is the language you use to talk to a database. Before you can save any data, you must first build the table that will hold it. That is what CREATE TABLE does.

The basic CREATE TABLE syntax

Syntax means the shape a command must follow. Here is the shape of CREATE TABLE:

sql
CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  column3 data_type
);

Line 1 says you want to create a table and gives it a name. The round brackets hold the list of columns. Each column has a name and a data type. A data type tells the database what kind of value goes in that column, such as a number or text. Columns are separated by commas, and the whole command ends with a semicolon.

Your first table: customers

Let us build a simple table to store customers.

sql
CREATE TABLE customers (
  id INT,
  name VARCHAR(100),
  email VARCHAR(255),
  age INT
);

Line 1 creates a table called customers. Line 2 adds a column named id that stores whole numbers (INT). Line 3 adds name, which stores text up to 100 characters (VARCHAR(100)). Line 4 adds email, text up to 255 characters. Line 5 adds age, another whole number. The table is empty for now, but its shape is ready.

Common SQL data types

Choosing the right data type keeps your data clean. A number column will not accept the word hello. These are the types you will use most. Names vary a little between databases such as MySQL, PostgreSQL and SQL Server, but these work almost everywhere.

Data typeWhat it storesExample value
INTWhole numbers42
DECIMAL(10,2)Exact numbers with decimals, good for money19.99
VARCHAR(n)Text up to n characters'Maria'
TEXTLong text with no small limitA blog post
DATEA calendar date'2026-11-11'
BOOLEANTrue or falseTRUE

Add a primary key

A primary key is a column whose value is different for every row. It works like an ID card number: it lets you find exactly one row. A table should almost always have one.

sql
CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(255),
  age INT
);

The only change is PRIMARY KEY after the id column. Now the database refuses to store two customers with the same id. It also refuses an empty id.

Make the id fill in by itself

Typing a new id every time is boring and easy to get wrong. Databases can count for you. The keyword is different in each database. MySQL uses AUTO_INCREMENT. PostgreSQL uses GENERATED ALWAYS AS IDENTITY.

sql
-- MySQL
CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100)
);

-- PostgreSQL
CREATE TABLE customers (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(100)
);

Lines starting with two dashes are comments; the database ignores them. In both versions the id becomes 1 for the first row, 2 for the second, and so on. You never type it yourself. Use the version that matches the database you installed.

Rules for columns: NOT NULL, UNIQUE and DEFAULT

You can add rules, called constraints, to a column. They stop bad data from getting in. Here are the three most useful ones:

  • NOT NULL: the column can never be empty. NULL is SQL's word for no value.
  • UNIQUE: no two rows may have the same value, for example two people with the same email.
  • DEFAULT: if you give no value, the database fills in the default for you.
sql
CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  age INT,
  country VARCHAR(50) DEFAULT 'Unknown',
  created_on DATE
);

The name column must always be filled in. The email column must be filled in and must be different for every customer. The age column is optional, so it may be empty. If you add a customer without a country, the database saves the word Unknown. Because we gave created_on no rules, it simply accepts a date or nothing.

Check that your table exists

After you create a table, add one row and read it back to prove it works.

sql
INSERT INTO customers (id, name, email, age)
VALUES (1, 'Maria', 'maria@example.com', 30);

SELECT * FROM customers;

The INSERT command adds one row. It lists the columns first, then the matching values in the same order. The SELECT command reads data back. The star means all columns. You should see Maria with the country set to Unknown, because of the DEFAULT rule.

What if the table already exists?

If you run CREATE TABLE twice with the same name, you get an error. To avoid that, add IF NOT EXISTS.

sql
CREATE TABLE IF NOT EXISTS customers (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

Now the database creates the table only if it is missing. If it is already there, it quietly does nothing. This is handy in scripts you run many times.

Common beginner mistakes

  • Forgetting a comma between columns, or adding one after the last column.
  • Forgetting the closing bracket or the semicolon.
  • Using a space in a name, like first name. Use first_name instead.
  • Using a reserved word like order or user as a table name. Pick a clearer name such as orders.
  • Storing money in a text column. Use DECIMAL so you can do maths with it.

Next steps

Now you can build a table. The next skill is filling it with data and changing it. Read our guide on SQL INSERT, UPDATE and DELETE to learn how. If you use PostgreSQL, the PostgreSQL create table guide covers the extra details of that database.

Next, learn how to add, change and remove rows in the table you just made.

Read SQL INSERT, UPDATE, DELETE

Using PostgreSQL? See the PostgreSQL version of this lesson.

PostgreSQL create table guide

Frequently asked questions

What is the syntax of CREATE TABLE in SQL?+

You write CREATE TABLE, then the table name, then round brackets holding a list of columns. Each column has a name and a data type, separated by commas. End the command with a semicolon.

Can I create a table without a primary key?+

Yes, SQL allows it. But it is not a good idea. Without a primary key you cannot reliably tell rows apart, which makes updating or deleting one specific row hard.

What is the difference between INT and VARCHAR?+

INT stores whole numbers like 5 or 100 and can be used in maths. VARCHAR stores text such as names or emails, with a maximum length you choose.

How do I delete a table I created by mistake?+

Use DROP TABLE table_name;. Be careful, because this removes the table and all its data for good. If you only want to remove the rows, delete them instead.

Can I change a table after I create it?+

Yes. The ALTER TABLE command lets you add, rename or remove columns later. So a small mistake in your first design is easy to fix.

Need help designing a database for your website or app? The Fepiq team can help you plan and build it.

Talk to Fepiq

Get new posts in your inbox

Occasional, no-fluff notes on shipping modern software — startups, automation, Laravel, Shopify and more. No spam, unsubscribe anytime.

Keep reading

Related posts

All posts

Let's build something

Ready to ship your next product with Fepiq?

Book a free discovery call. We'll listen, ask sharp questions, and send you a proposal within 3 business days.