CREATE TABLE syntax in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the result of this CREATE TABLE statement?

Consider this MySQL statement:

CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(50), age INT);

What will be the structure of the employees table after running this?

MySQL

CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(50), age INT);

A A table named 'employees' with columns: id (integer, primary key), name (string up to 50 chars), age (integer)

B A table named 'employees' with columns: id (string), name (integer), age (integer)

C Syntax error due to missing semicolon

D A table named 'employees' with columns: id (integer), name (string), age (string)

💡 Hint

Look at the data types and constraints defined for each column.

The statement creates a table 'employees' with three columns: 'id' as integer primary key, 'name' as VARCHAR(50), and 'age' as integer.

📝 Syntax

intermediate

Identify the syntax error in this CREATE TABLE statement

Which option correctly identifies the syntax error in this statement?

CREATE TABLE products (product_id INT, product_name VARCHAR(100), price DECIMAL(10,2) NOT NULL,);

MySQL

CREATE TABLE products (product_id INT, product_name VARCHAR(100), price DECIMAL(10,2) NOT NULL,);

A Missing PRIMARY KEY declaration

B VARCHAR length must be 255 or less

C Extra comma before closing parenthesis causes syntax error

D DECIMAL data type is invalid

💡 Hint

Check punctuation carefully inside the parentheses.

The trailing comma before the closing parenthesis is not allowed in MySQL CREATE TABLE syntax.

🧠 Conceptual

advanced

What does the following CREATE TABLE statement do?

Analyze this statement:

CREATE TABLE orders (order_id INT AUTO_INCREMENT PRIMARY KEY, order_date DATE DEFAULT CURRENT_DATE, customer_id INT, UNIQUE (customer_id, order_date));

What is the purpose of the UNIQUE constraint here?

MySQL

CREATE TABLE orders (order_id INT AUTO_INCREMENT PRIMARY KEY, order_date DATE DEFAULT CURRENT_DATE, customer_id INT, UNIQUE (customer_id, order_date));

A It automatically increments customer_id

B It makes customer_id the primary key

C It allows duplicate customer_id values but unique order_date values

D It ensures no two orders have the same customer_id and order_date combination

💡 Hint

Think about what UNIQUE means when applied to multiple columns.

The UNIQUE constraint on (customer_id, order_date) prevents duplicate pairs of these values, ensuring each customer can have only one order per date.

🔧 Debug

advanced

Why does this CREATE TABLE statement fail?

Given this statement:

CREATE TABLE inventory (item_id INT PRIMARY KEY, quantity INT DEFAULT 'ten');

Why will this cause an error?

MySQL

CREATE TABLE inventory (item_id INT PRIMARY KEY, quantity INT DEFAULT 'ten');

A DEFAULT value 'ten' is a string but quantity expects an integer

B PRIMARY KEY cannot be on item_id

C INT data type cannot have a DEFAULT value

D Missing comma between columns

💡 Hint

Check the data type compatibility of default values.

The DEFAULT value must match the column's data type. 'ten' is a string and cannot be used as default for an INT column.

❓ optimization

expert

Which CREATE TABLE option optimizes storage for a large table with many NULL values?

You want to create a table with many optional columns that often have NULL values. Which option best optimizes storage in MySQL?

A Define columns as NOT NULL with default values

B Define columns as NULLABLE without default values

C Use VARCHAR columns with fixed length instead of TEXT

D Use ENUM type for all columns

💡 Hint

Think about how NULL values are stored in MySQL.

Allowing columns to be NULL without default values saves space because NULLs do not store data, unlike default values which consume space.

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