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.