← SQL roadmap

Topic 2 of 52 · Beginner

CREATE TABLE

Define a new table: its name, columns, data types and constraints.

01CREATE TABLE

Module 02 – Database & Table Management (DDL)

02What is it?

CREATE TABLE is the DDL command that builds a brand-new, empty table. You give the table a name and list every column with its data type and any rules (constraints) it must follow.

After it runs, the table exists in the database with zero rows. INSERT is then used to add data.

03Why do we need it?

Data in a relational database can only live inside tables. Before a company can store customers, orders or payments, someone has to design and create the tables that will hold them.

Choosing the right types and constraints up front stops bad data (text in a salary column, missing names, duplicate IDs) from ever getting in.

04Syntax

CREATE TABLE [IF NOT EXISTS] table_name (
  column1 datatype [constraints],
  column2 datatype [constraints],
  ...
  [table_constraints]
);

MySQL 8.0 enforces CHECK constraints from 8.0.16. Older versions parsed but ignored them.

05Parameter explanation

IF NOT EXISTS
Optional. Skips creation silently if a table with that name already exists instead of raising an error.
table_name
Name of the new table. Must be unique within the database.
column datatype
Each column needs a name and a type such as INT, VARCHAR(50), DECIMAL(10,2) or DATE.
constraints
Column rules: PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT value, CHECK (condition), REFERENCES other_table(col).
table_constraints
Rules that span columns, e.g. PRIMARY KEY (a, b) or FOREIGN KEY (dept_id) REFERENCES departments(dept_id).

06Sample database

Every example and exercise below starts from a fresh copy of this data.

CREATE TABLE departments (
  dept_id   INT PRIMARY KEY,
  dept_name VARCHAR(50) NOT NULL
);
CREATE TABLE employees (
  emp_id    INT PRIMARY KEY,
  name      VARCHAR(60) NOT NULL,
  dept_id   INT,
  salary    DECIMAL(10,2),
  hire_date DATE
);
INSERT INTO departments VALUES (10,'Engineering'),(20,'Sales'),(30,'HR');
INSERT INTO employees VALUES
 (1,'Asha',10,85000,'2021-04-12'),
 (2,'Ravi',20,52000,'2022-01-03'),
 (3,'Meena',10,97000,'2019-09-20'),
 (4,'Karan',30,46000,'2023-06-15'),
 (5,'Divya',20,61000,'2020-11-30');

07Basic coding example

Basic example · runs on the real engine

08Step-by-step execution

  1. 1.The engine checks that no table called projects already exists.
  2. 2.It records three columns with their types: project_id (INT), title (VARCHAR(80)) and budget (DECIMAL(12,2)).
  3. 3.project_id is marked as the primary key, so it must be unique and not NULL.
  4. 4.title gets a NOT NULL rule; budget gets a default of 0 when no value is supplied.
  5. 5.The empty table is saved in the schema. No rows are returned — only a success message.

09Before and after output

Generated live by running the basic example against the sample database.

Running on the SQL engine…

10Intermediate coding example

Intermediate example

11Real-world business scenario

A data engineer onboarding a new source system (say, a CRM) first creates a raw/staging table whose columns mirror the source file, e.g. CREATE TABLE stg_crm_contacts (...).

Production tables are usually created through version-controlled migration scripts, so the same CREATE TABLE runs identically in dev, test and production.

12Common mistakes

CREATE TABLE projects (project_id INT, title VARCHAR(80),);

Trailing comma after the last column — the parser expects another column.

CREATE TABLE projects (project_id INT, title VARCHAR(80));

CREATE TABLE projects (project_id, title VARCHAR(80));

In MySQL every column must have a data type.

CREATE TABLE projects (project_id INT, title VARCHAR(80));

CREATE TABLE employees (emp_id INT);

employees already exists, so this fails with 'table already exists'.

CREATE TABLE IF NOT EXISTS employees (emp_id INT); -- or choose a new name

13Important comparisons

CommandWhat it doesData copied?
CREATE TABLE t (...)New empty table with columns you defineNo
CREATE TABLE t AS SELECT ...New table shaped by a queryYes, the query rows
CREATE TABLE t LIKE src (MySQL)Empty copy of src's structure incl. indexesNo
ALTER TABLEChanges an existing tableKeeps existing data

14Practical exercises

Beginner1. Create a table clients with client_id INT primary key and client_name VARCHAR(100) NOT NULL.

Reveal solution (15)
Solution · run it

Two columns; the PK makes client_id unique, NOT NULL forces a name.

Beginner2. Create a table cities with city VARCHAR(50) and state VARCHAR(50), then insert ('Hyderabad','Telangana').

Reveal solution (15)
Solution · run it

A table without constraints accepts any values of the right type.

Beginner3. Create skills(skill_id INT PRIMARY KEY, skill VARCHAR(40)) only if it does not already exist.

Reveal solution (15)
Solution · run it

Running it twice proves IF NOT EXISTS prevents the 'already exists' error.

Intermediate4. Create leaves(leave_id INT PK, emp_id INT, days INT) where days must be between 1 and 30.

Reveal solution (15)
Solution · run it

CHECK constraints are enforced in MySQL 8.0.16+.

Intermediate5. Create attendance with a status column that defaults to 'Present'. Insert a row without status and show it.

Reveal solution (15)
Solution · run it

Omitted columns take their DEFAULT value.

Intermediate6. Create salaries_history with a composite primary key (emp_id, effective_date).

Reveal solution (15)
Solution · run it

The same emp_id can appear many times, but never twice for the same date.

Advanced7. Create assets(asset_id PK, emp_id INT referencing employees, serial VARCHAR(30) UNIQUE NOT NULL).

Reveal solution (15)
Solution · run it

Combines FK, UNIQUE and NOT NULL in one design.

Advanced8. Design a staging table stg_orders for a CSV with order_id, customer, amount, order_ts plus an audit column loaded_at defaulting to the current time.

Reveal solution (15)
Solution · run it

Staging tables usually avoid strict constraints but add audit columns.

16Key takeaways

  • CREATE TABLE defines the structure; it creates no rows.
  • Every column needs a data type; constraints guard data quality.
  • Use IF NOT EXISTS for re-runnable scripts.
  • Composite keys and foreign keys are declared as table constraints at the end.

17Knowledge check

1. What does CREATE TABLE return when it succeeds?

2. Which clause avoids an error if the table already exists?

3. How do you declare a primary key on two columns?

Interview questions

What's the difference between a column constraint and a table constraint?

A column constraint is written next to one column (salary INT NOT NULL). A table constraint is written after all columns and can cover several columns, e.g. PRIMARY KEY (emp_id, effective_date).

Does CREATE TABLE commit an open transaction in MySQL?

Yes. DDL statements cause an implicit commit in MySQL, so they can't be rolled back with ROLLBACK.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “CREATE TABLE” — 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.