CREATE TABLE syntax in MySQL - Time & Space Complexity

Time Complexity: CREATE TABLE syntax

O(n)

Understanding Time Complexity

Let's see how the time it takes to create a table changes as the table design grows.

We want to know how the work grows when we add more columns or constraints.

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

CREATE TABLE Employees (
  ID INT PRIMARY KEY,
  Name VARCHAR(100),
  Age INT,
  Department VARCHAR(50)
);

This code creates a table named Employees with four columns and a primary key.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Processing each column and constraint to set up the table structure.
• How many times: Once for each column and constraint listed in the CREATE TABLE statement.

How Execution Grows With Input

As you add more columns or constraints, the work grows in a straight line.

Input Size (number of columns)	Approx. Operations
10	10 operations
100	100 operations
1000	1000 operations

Pattern observation: The work grows directly with the number of columns and constraints.

Final Time Complexity

Time Complexity: O(n)

This means the time to create the table grows linearly with the number of columns and constraints.

Common Mistake

[X] Wrong: "Creating a table always takes the same time no matter how many columns it has."

[OK] Correct: More columns and constraints mean more work to set up, so the time grows with the table size.

Interview Connect

Understanding how table creation time grows helps you design databases efficiently and shows you think about performance.

Self-Check

"What if we added indexes on multiple columns? How would the time complexity change?"

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