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

Create a Simple SQL Table

📖 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 SQL table named Books with columns for BookID, Title, and Author.

📋 What You'll Learn

Create a table named Books

Add a column BookID as an integer and primary key

Add a column Title as text and not null

Add a column Author as text and not null

💡 Why This Matters

🌍 Real World

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

💼 Career

Knowing how to create tables with proper columns and constraints is essential for database administrators, backend developers, and data analysts.

1

Create the table named Books

Write a SQL statement to create a table called Books.

SQL

-- Write the SQL command to create the table named Books
-- Your code here

Hint

Use CREATE TABLE Books to start your statement.

2

Add the BookID column

Add a column named BookID as an integer and set it as the primary key inside the Books table.

SQL

CREATE TABLE Books (
  -- Add BookID column here
  -- Your code here
);

Hint

Use BookID INTEGER PRIMARY KEY to define the column.

3

Add the Title and Author columns

Add two columns inside the Books table: Title and Author. Both should be text and not allow null values.

SQL

CREATE TABLE Books (
  BookID INTEGER PRIMARY KEY,
  -- Add Title and Author columns here
  -- Your code here
);

Hint

Use Title TEXT NOT NULL and Author TEXT NOT NULL to add these columns.

4

Complete the CREATE TABLE statement

Make sure the CREATE TABLE Books statement is complete with all columns and proper syntax including commas and parentheses.

SQL

-- Review and finalize the CREATE TABLE statement
-- Your code here

Hint

Check that all columns are separated by commas and the statement ends with a closing parenthesis and semicolon.

Practice

1. What does the CREATE TABLE statement do in SQL?

easy

A. It deletes an existing table.

B. It selects data from a table.

C. It updates data inside a table.

D. It creates a new table in the database.

Solution

1. Step 1: Understand the purpose of CREATE TABLE

   The CREATE TABLE command is used to make a new table to store data in a database.

2. Step 2: Compare with other SQL commands

   Deleting, updating, or selecting data are done by other commands like DROP, UPDATE, and SELECT.

3. Final Answer:

   It creates a new table in the database. -> Option D

4. Quick Check:

   CREATE TABLE = create new table [OK]

Hint: Remember: CREATE TABLE makes new tables [OK]

Common Mistakes:

• Confusing CREATE with DROP or SELECT
• Thinking CREATE modifies data inside tables
• Mixing CREATE TABLE with UPDATE command

2. Which of the following is the correct syntax to create a table named Students with columns id (integer) and name (text)?

easy

A. CREATE TABLE Students (id INT, name TEXT);

B. CREATE Students TABLE (id INT, name TEXT);

C. CREATE TABLE Students id INT, name TEXT;

D. CREATE TABLE Students {id INT, name TEXT};

Solution

1. Step 1: Check the correct order of keywords

   The correct order is CREATE TABLE table_name (columns);. CREATE TABLE Students (id INT, name TEXT); follows this.

2. Step 2: Verify column definitions and punctuation

   Columns must be inside parentheses and separated by commas. CREATE TABLE Students (id INT, name TEXT); uses parentheses and commas correctly.

3. Final Answer:

   CREATE TABLE Students (id INT, name TEXT); -> Option A

4. Quick Check:

   CREATE TABLE + (columns) syntax = CREATE TABLE Students (id INT, name TEXT); [OK]

Hint: Use parentheses around columns after table name [OK]

Common Mistakes:

• Placing TABLE keyword after table name
• Missing parentheses around columns
• Using curly braces instead of parentheses

3. What will happen if you run this SQL statement?

CREATE TABLE Employees (emp_id INT PRIMARY KEY, emp_name VARCHAR(50));

medium

A. Creates a table Employees with emp_id as unique key and emp_name as text.

B. Creates a table Employees but emp_id is not unique.

C. Syntax error because VARCHAR is not allowed.

D. Creates a table Employees but emp_name cannot store text.

Solution

1. Step 1: Understand column definitions

   The statement defines emp_id as an integer and sets it as PRIMARY KEY, which means it must be unique.

2. Step 2: Check data types and constraints

   VARCHAR(50) is a valid type for text up to 50 characters. So emp_name can store text.

3. Final Answer:

   Creates a table Employees with emp_id as unique key and emp_name as text. -> Option A

4. Quick Check:

   PRIMARY KEY = unique key, VARCHAR = text [OK]

Hint: PRIMARY KEY means unique; VARCHAR stores text [OK]

Common Mistakes:

• Thinking VARCHAR is invalid
• Assuming PRIMARY KEY allows duplicates
• Confusing data types with constraints

4. Identify the error in this SQL statement:

CREATE TABLE Orders order_id INT, order_date DATE;

medium

A. Incorrect data type for order_date.

B. Missing parentheses around column definitions.

C. Table name cannot be Orders.

D. Missing semicolon at the end.

Solution

1. Step 1: Check syntax for column definitions

   Column names and types must be inside parentheses after the table name.

2. Step 2: Verify other parts

   Data types and table name are valid, and semicolon is present.

3. Final Answer:

   Missing parentheses around column definitions. -> Option B

4. Quick Check:

   Columns must be inside ( ) [OK]

Hint: Always use parentheses around columns in CREATE TABLE [OK]

Common Mistakes:

• Omitting parentheses around columns
• Using wrong data types without error
• Confusing table name rules

5. You want to create a table Products with columns product_id (integer, unique), product_name (text), and price (decimal). Which SQL statement correctly enforces uniqueness on product_id using PRIMARY KEY?

hard

A. CREATE TABLE Products (product_id INT UNIQUE, product_name TEXT, price DECIMAL);

B. CREATE TABLE Products (product_id INT, product_name TEXT, price DECIMAL UNIQUE);

C. CREATE TABLE Products (product_id INT PRIMARY KEY, product_name TEXT, price DECIMAL);

D. CREATE TABLE Products (product_id INT, product_name TEXT UNIQUE, price DECIMAL);

Solution

1. Step 1: Understand uniqueness constraints

   Both UNIQUE and PRIMARY KEY enforce uniqueness, but PRIMARY KEY also disallows NULLs and is preferred for IDs.

2. Step 2: Analyze options for correct uniqueness on product_id

   CREATE TABLE Products (product_id INT UNIQUE, product_name TEXT, price DECIMAL); uses UNIQUE on product_id, which works but PRIMARY KEY is better. CREATE TABLE Products (product_id INT PRIMARY KEY, product_name TEXT, price DECIMAL); uses PRIMARY KEY on product_id, which is the best practice.

3. Final Answer:

   CREATE TABLE Products (product_id INT PRIMARY KEY, product_name TEXT, price DECIMAL); -> Option C

4. Quick Check:

   PRIMARY KEY = unique + not null [OK]

Hint: Use PRIMARY KEY for unique IDs [OK]

Common Mistakes:

• Putting UNIQUE on wrong column
• Confusing UNIQUE with PRIMARY KEY
• Not enforcing uniqueness on ID column