CREATE TABLE syntax in MySQL - Mini Project: Build & Apply

Create a Simple Table with CREATE TABLE Syntax

📖 Scenario: You are setting up a small database for a local bookstore. You need to create a table to store information about books.

🎯 Goal: Build a table named books with columns for id, title, and author using the CREATE TABLE statement.

📋 What You'll Learn

Create a table named books

Add a column id as an integer and primary key

Add a column title as a variable character string with a maximum length of 100

Add a column author as a variable character string with a maximum length of 50

💡 Why This Matters

🌍 Real World

Creating tables is the first step in building a database to store organized data for applications like bookstores, libraries, or any business.

💼 Career

Database developers and administrators frequently write CREATE TABLE statements to design and set up databases.

1

Create the books table with id column

Write a CREATE TABLE statement to create a table named books with one column called id of type INT.

MySQL

-- Your code here

Hint

Start with CREATE TABLE books ( and add the id INT column inside the parentheses.

2

Add title column to the books table

Add a column named title to the books table with type VARCHAR(100) after the id column.

MySQL

CREATE TABLE books (
    id INT,
    -- Your code here
);

Hint

Add title VARCHAR(100) after id INT separated by a comma.

3

Add author column to the books table

Add a column named author to the books table with type VARCHAR(50) after the title column.

MySQL

CREATE TABLE books (
    id INT,
    title VARCHAR(100),
    -- Your code here
);

Hint

Add author VARCHAR(50) after title VARCHAR(100) separated by a comma.

4

Make id the primary key

Modify the id column to be the primary key by adding PRIMARY KEY after INT in the CREATE TABLE statement.

MySQL

CREATE TABLE books (
    id INT,
    title VARCHAR(100),
    author VARCHAR(50)
    -- Your code here
);

Hint

Add PRIMARY KEY right after INT for the id column.

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