CREATE TABLE syntax in MySQL - Step-by-Step Execution

Concept Flow - CREATE TABLE syntax

Start CREATE TABLE

↓

Define Table Name

↓

Define Columns and Data Types

↓

Add Constraints (Optional)

↓

Execute Statement

↓

Table Created in Database

The flow shows how a CREATE TABLE statement is built step-by-step and executed to create a new table.

Execution Sample

MySQL

CREATE TABLE Students (
  ID INT PRIMARY KEY,
  Name VARCHAR(50),
  Age INT
);

This code creates a table named Students with three columns: ID, Name, and Age.

Execution Table

Step	Action	Details	Result
1	Start CREATE TABLE	Begin statement	Ready to define table
2	Define Table Name	Table name: Students	Table name set
3	Define Column	ID INT PRIMARY KEY	Column ID created with INT type and primary key
4	Define Column	Name VARCHAR(50)	Column Name created with VARCHAR(50) type
5	Define Column	Age INT	Column Age created with INT type
6	End Statement	Close with );	Statement complete
7	Execute Statement	Run CREATE TABLE	Table Students created in database

💡 All columns defined and statement executed successfully, table created.

Variable Tracker

Variable	Start	After Step 3	After Step 5	Final
Table Name	None	Students	Students	Students
Columns	None	[ID INT PRIMARY KEY]	[ID INT PRIMARY KEY, Name VARCHAR(50), Age INT]	[ID INT PRIMARY KEY, Name VARCHAR(50), Age INT]
Statement Status	Not started	Building	Building	Complete

Key Moments - 2 Insights

Why do we need to specify data types like INT or VARCHAR for each column?

Each column needs a data type so the database knows what kind of data to store and how much space to allocate. See execution_table rows 3-5 where each column is defined with its type.

What does PRIMARY KEY mean in the column definition?

PRIMARY KEY means this column uniquely identifies each row in the table. In the execution_table row 3, ID is set as PRIMARY KEY to ensure uniqueness.

Visual Quiz - 3 Questions

Look at the execution_table, what is the table name after step 2?

A Name

B ID

C Students

D Age

💡 Hint

Check the 'Details' column in execution_table row 2 for the table name.

At which step is the PRIMARY KEY constraint added?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Look at the 'Action' and 'Details' columns in execution_table row 3.

If we remove the semicolon at the end, what would happen?

A Syntax error, statement not executed

B The table is created anyway

C Table created with default columns

D Only first column created

💡 Hint

The semicolon marks the end of the statement; missing it causes syntax errors (see exit_note).

Concept Snapshot

CREATE TABLE syntax:
CREATE TABLE table_name (
  column1 datatype [constraints],
  column2 datatype [constraints],
  ...
);
Defines a new table with columns and data types.
PRIMARY KEY sets unique identifier column.
Statement ends with semicolon.

Full Transcript

The CREATE TABLE statement starts by naming the table, then defining each column with a data type and optional constraints like PRIMARY KEY. After all columns are defined, the statement ends with a semicolon and is executed to create the table in the database. Each step builds the table structure, ensuring the database knows how to store data correctly.

Practice

1. What is the main purpose of the CREATE TABLE statement in MySQL?

easy

A. To create a new table in the database

B. To insert data into an existing table

C. To delete a table from the database

D. To update data in a table

Solution

1. Step 1: Understand the role of CREATE TABLE

   The CREATE TABLE statement is used to make a new table structure in the database.

2. Step 2: Differentiate from other SQL commands

   Commands like INSERT, DELETE, and UPDATE modify data but do not create tables.

3. Final Answer:

   To create a new table in the database -> Option A

4. Quick Check:

   CREATE TABLE = create new table [OK]

Hint: CREATE TABLE always makes a new table [OK]

Common Mistakes:

• Confusing CREATE TABLE with INSERT or UPDATE
• Thinking CREATE TABLE modifies data
• Mixing CREATE TABLE with DROP TABLE

2. Which of the following is the correct syntax to create a table named users with an integer id column as primary key?

easy

A. CREATE TABLE users (id PRIMARY KEY INT);

B. CREATE TABLE users (id INT PRIMARY KEY);

C. CREATE TABLE users id INT PRIMARY KEY;

D. CREATE users TABLE (id INT PRIMARY KEY);

Solution

1. Step 1: Check the basic CREATE TABLE syntax

   The correct syntax starts with CREATE TABLE table_name (column definitions);

2. Step 2: Verify column definition order

   Column name first, then data type, then constraints like PRIMARY KEY.

3. Final Answer:

   CREATE TABLE users (id INT PRIMARY KEY); -> Option B

4. Quick Check:

   CREATE TABLE + (column datatype constraints) [OK]

Hint: Table name follows CREATE TABLE, columns inside parentheses [OK]

Common Mistakes:

• Placing keywords in wrong order
• Missing parentheses around columns
• Using incorrect keyword order

3. Given the SQL statement:

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(5,2)
);

What will be the data type of the price column?

medium

A. Integer number

B. String of 50 characters

C. Decimal number with 5 digits total and 2 after decimal

D. Boolean value

Solution

1. Step 1: Identify the data type of price column

   The column price is declared as DECIMAL(5,2).

2. Step 2: Understand DECIMAL(5,2)

   This means total 5 digits, with 2 digits after the decimal point (e.g., 123.45).

3. Final Answer:

   Decimal number with 5 digits total and 2 after decimal -> Option C

4. Quick Check:

   DECIMAL(5,2) = decimal with 2 decimal places [OK]

Hint: DECIMAL(m,n) means m digits total, n after decimal [OK]

Common Mistakes:

• Confusing VARCHAR with DECIMAL
• Assuming DECIMAL is integer
• Ignoring precision and scale in DECIMAL

4. Identify the error in this CREATE TABLE statement:

CREATE TABLE employees (
  emp_id INT,
  name VARCHAR(100),
  PRIMARY KEY emp_id
);

medium

A. Incorrect data type for emp_id

B. No error, statement is correct

C. Table name is missing

D. Missing parentheses around PRIMARY KEY column

Solution

1. Step 1: Check PRIMARY KEY syntax

   The PRIMARY KEY constraint must list columns inside parentheses, like PRIMARY KEY (emp_id).

2. Step 2: Identify the error in the statement

   The statement uses PRIMARY KEY emp_id without parentheses, which is invalid syntax.

3. Final Answer:

   Missing parentheses around PRIMARY KEY column -> Option D

4. Quick Check:

   PRIMARY KEY columns must be in parentheses [OK]

Hint: PRIMARY KEY columns always inside parentheses [OK]

Common Mistakes:

• Omitting parentheses for PRIMARY KEY
• Using wrong data types for keys
• Forgetting commas between columns

5. You want to create a table orders with columns order_id (integer, primary key), customer_id (integer), and order_date (date). Which statement correctly creates this table with order_id as the primary key and ensures customer_id cannot be empty?

hard

A. CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT NOT NULL, order_date DATE);

B. CREATE TABLE orders (order_id INT, customer_id INT, order_date DATE, PRIMARY KEY order_id, NOT NULL customer_id);

C. CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT, order_date DATE NOT NULL);

D. CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT NULL, order_date DATE);

Solution

1. Step 1: Define primary key correctly

   The order_id column must be declared as INT PRIMARY KEY inside the column definition.

2. Step 2: Ensure customer_id is NOT NULL

   To prevent empty values, add NOT NULL after customer_id INT.

3. Step 3: Check order_date data type

   order_date should be DATE without NOT NULL if empty dates allowed.

4. Final Answer:

   CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT NOT NULL, order_date DATE); -> Option A

5. Quick Check:

   Primary key + NOT NULL constraint syntax correct [OK]

Hint: Use NOT NULL to prevent empty columns [OK]

Common Mistakes:

• Placing NOT NULL outside column definition
• Incorrect PRIMARY KEY syntax
• Confusing NULL and NOT NULL