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

Concept Flow - CREATE TABLE syntax

Start CREATE TABLE

↓

Specify Table Name

↓

Define Columns

↓

Set Data Types

↓

Add Constraints (optional)

↓

Execute Statement

↓

Table Created in Database

The CREATE TABLE command starts by naming the table, then defining columns with data types and optional constraints, and finally creates the table in the database.

Execution Sample

SQL

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	Command begins	Preparing to create table Students
2	Specify Table Name	Table name: Students	Table name set to Students
3	Define Column	Column: ID, Type: INT, Constraint: PRIMARY KEY	Column ID defined
4	Define Column	Column: Name, Type: VARCHAR(50)	Column Name defined
5	Define Column	Column: Age, Type: INT	Column Age defined
6	Execute Statement	Run CREATE TABLE command	Table Students created successfully
7	End	No errors	Table ready for use

💡 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]
Table Status	Not created	Not created	Not created	Created

Key Moments - 2 Insights

Why do we specify data types like INT or VARCHAR when creating columns?

Data types tell the database what kind of data each column will hold, ensuring correct storage and operations. See execution_table rows 3-5 where each column is defined with a 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 Students

B ID

C None

D Age

💡 Hint

Check the 'Details' column in execution_table row 2.

At which step is the column 'Name' defined?

A Step 3

B Step 4

C Step 5

D Step 6

💡 Hint

Look for 'Column: Name' in the 'Details' column of execution_table.

If we remove PRIMARY KEY from ID, what changes in the execution_table?

A Table name would change

B Table would not be created

C Step 3 would not mention PRIMARY KEY

D No change at all

💡 Hint

PRIMARY KEY is part of the column definition in step 3.

Concept Snapshot

CREATE TABLE syntax:
CREATE TABLE table_name (
  column1 datatype [constraints],
  column2 datatype [constraints],
  ...
);
Defines a new table with columns and data types.
Constraints like PRIMARY KEY ensure data rules.

Full Transcript

The CREATE TABLE command starts by naming the table, then defining each column with its data type and optional constraints like PRIMARY KEY. The database uses this information to create a new table structure. For example, creating a Students table with ID as an integer primary key, Name as a string up to 50 characters, and Age as an integer. Each step defines part of the table until the command executes and the table is ready for use.

Practice

1. What does the CREATE TABLE statement do in SQL?

easy

A. It deletes an existing table.

B. It selects data from a table.

C. It updates data inside a table.

D. It creates a new table in the database.

Solution

1. Step 1: Understand the purpose of CREATE TABLE

   The CREATE TABLE command is used to make a new table to store data in a database.

2. Step 2: Compare with other SQL commands

   Deleting, updating, or selecting data are done by other commands like DROP, UPDATE, and SELECT.

3. Final Answer:

   It creates a new table in the database. -> Option D

4. Quick Check:

   CREATE TABLE = create new table [OK]

Hint: Remember: CREATE TABLE makes new tables [OK]

Common Mistakes:

• Confusing CREATE with DROP or SELECT
• Thinking CREATE modifies data inside tables
• Mixing CREATE TABLE with UPDATE command

2. Which of the following is the correct syntax to create a table named Students with columns id (integer) and name (text)?

easy

A. CREATE TABLE Students (id INT, name TEXT);

B. CREATE Students TABLE (id INT, name TEXT);

C. CREATE TABLE Students id INT, name TEXT;

D. CREATE TABLE Students {id INT, name TEXT};

Solution

1. Step 1: Check the correct order of keywords

   The correct order is CREATE TABLE table_name (columns);. CREATE TABLE Students (id INT, name TEXT); follows this.

2. Step 2: Verify column definitions and punctuation

   Columns must be inside parentheses and separated by commas. CREATE TABLE Students (id INT, name TEXT); uses parentheses and commas correctly.

3. Final Answer:

   CREATE TABLE Students (id INT, name TEXT); -> Option A

4. Quick Check:

   CREATE TABLE + (columns) syntax = CREATE TABLE Students (id INT, name TEXT); [OK]

Hint: Use parentheses around columns after table name [OK]

Common Mistakes:

• Placing TABLE keyword after table name
• Missing parentheses around columns
• Using curly braces instead of parentheses

3. What will happen if you run this SQL statement?

CREATE TABLE Employees (emp_id INT PRIMARY KEY, emp_name VARCHAR(50));

medium

A. Creates a table Employees with emp_id as unique key and emp_name as text.

B. Creates a table Employees but emp_id is not unique.

C. Syntax error because VARCHAR is not allowed.

D. Creates a table Employees but emp_name cannot store text.

Solution

1. Step 1: Understand column definitions

   The statement defines emp_id as an integer and sets it as PRIMARY KEY, which means it must be unique.

2. Step 2: Check data types and constraints

   VARCHAR(50) is a valid type for text up to 50 characters. So emp_name can store text.

3. Final Answer:

   Creates a table Employees with emp_id as unique key and emp_name as text. -> Option A

4. Quick Check:

   PRIMARY KEY = unique key, VARCHAR = text [OK]

Hint: PRIMARY KEY means unique; VARCHAR stores text [OK]

Common Mistakes:

• Thinking VARCHAR is invalid
• Assuming PRIMARY KEY allows duplicates
• Confusing data types with constraints

4. Identify the error in this SQL statement:

CREATE TABLE Orders order_id INT, order_date DATE;

medium

A. Incorrect data type for order_date.

B. Missing parentheses around column definitions.

C. Table name cannot be Orders.

D. Missing semicolon at the end.

Solution

1. Step 1: Check syntax for column definitions

   Column names and types must be inside parentheses after the table name.

2. Step 2: Verify other parts

   Data types and table name are valid, and semicolon is present.

3. Final Answer:

   Missing parentheses around column definitions. -> Option B

4. Quick Check:

   Columns must be inside ( ) [OK]

Hint: Always use parentheses around columns in CREATE TABLE [OK]

Common Mistakes:

• Omitting parentheses around columns
• Using wrong data types without error
• Confusing table name rules

5. You want to create a table Products with columns product_id (integer, unique), product_name (text), and price (decimal). Which SQL statement correctly enforces uniqueness on product_id using PRIMARY KEY?

hard

A. CREATE TABLE Products (product_id INT UNIQUE, product_name TEXT, price DECIMAL);

B. CREATE TABLE Products (product_id INT, product_name TEXT, price DECIMAL UNIQUE);

C. CREATE TABLE Products (product_id INT PRIMARY KEY, product_name TEXT, price DECIMAL);

D. CREATE TABLE Products (product_id INT, product_name TEXT UNIQUE, price DECIMAL);

Solution

1. Step 1: Understand uniqueness constraints

   Both UNIQUE and PRIMARY KEY enforce uniqueness, but PRIMARY KEY also disallows NULLs and is preferred for IDs.

2. Step 2: Analyze options for correct uniqueness on product_id

   CREATE TABLE Products (product_id INT UNIQUE, product_name TEXT, price DECIMAL); uses UNIQUE on product_id, which works but PRIMARY KEY is better. CREATE TABLE Products (product_id INT PRIMARY KEY, product_name TEXT, price DECIMAL); uses PRIMARY KEY on product_id, which is the best practice.

3. Final Answer:

   CREATE TABLE Products (product_id INT PRIMARY KEY, product_name TEXT, price DECIMAL); -> Option C

4. Quick Check:

   PRIMARY KEY = unique + not null [OK]

Hint: Use PRIMARY KEY for unique IDs [OK]

Common Mistakes:

• Putting UNIQUE on wrong column
• Confusing UNIQUE with PRIMARY KEY
• Not enforcing uniqueness on ID column