CREATE TABLE syntax in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of the CREATE TABLE statement in SQL?

The CREATE TABLE statement is used to make a new table in a database where you can store data in rows and columns.

beginner

Which keyword defines the name of the table in a CREATE TABLE statement?

The table name comes right after the CREATE TABLE keywords. For example, in CREATE TABLE students, students is the table name.

beginner

How do you specify columns and their data types when creating a table?

Inside parentheses after the table name, list each column name followed by its data type, separated by commas. For example: (id INT, name VARCHAR(50)).

intermediate

What does PRIMARY KEY mean in a CREATE TABLE statement?

A PRIMARY KEY is a column (or set of columns) that uniquely identifies each row in the table. It helps keep data organized and prevents duplicates.

beginner

Write a simple CREATE TABLE statement for a table named books with columns id (integer primary key) and title (text up to 100 characters).

Example:
CREATE TABLE books (id INT PRIMARY KEY, title VARCHAR(100));

What keyword starts the command to create a new table in SQL?

A MAKE TABLE

B NEW TABLE

C CREATE TABLE

D ADD TABLE

The correct syntax to create a table starts with CREATE TABLE.

In the statement CREATE TABLE users (id INT, name VARCHAR(50));, what does VARCHAR(50) mean?

A A text column with max 50 characters

B A number column with max 50 digits

C A date column with 50 days

D A boolean column

VARCHAR(50) means a text column that can hold up to 50 characters.

Which of these is a valid data type for a column in MySQL?

A TEXTUAL

B INT

C NUMBERIC

D CHARACTERSTRING

INT is a valid data type for integers in MySQL.

What does the PRIMARY KEY constraint do?

A Creates a foreign key

B Allows duplicate values

C Stores large text

D Uniquely identifies each row

PRIMARY KEY ensures each row is unique and identifiable.

Which symbol is used to separate columns in a CREATE TABLE statement?

A ,

B .

C :

D ;

Columns are separated by commas , inside the parentheses.

Explain the basic structure of a CREATE TABLE statement and what each part does.

Think about how you tell the database to make a new box (table) with labeled sections (columns).

Describe why defining a PRIMARY KEY is important when creating a table.

Imagine a student ID that is unique for each student.

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