INSERT INTO multiple rows in MySQL - Mini Project: Build & Apply

Insert Multiple Rows into a MySQL Table

📖 Scenario: You are managing a small bookstore database. You need to add several new books to the books table at once to keep the inventory updated.

🎯 Goal: Learn how to insert multiple rows into a MySQL table using a single INSERT INTO statement.

📋 What You'll Learn

Create a table named books with columns id, title, and author

Insert multiple rows into the books table in one query

Use exact values for the books as specified

💡 Why This Matters

🌍 Real World

In real life, inserting multiple rows at once is faster and more efficient than inserting one row at a time, especially when adding new data to a database.

💼 Career

Database administrators and developers often need to bulk insert data into tables quickly and correctly, making this skill essential for managing real-world databases.

1

Create the books table

Write a SQL statement to create a table called books with three columns: id as an integer primary key, title as a variable character string of length 100, and author as a variable character string of length 100.

MySQL

-- Your code here

Need a hint?

Use CREATE TABLE with column definitions and specify PRIMARY KEY for id.

2

Prepare the list of books to insert

Define the exact three books to insert: (1, 'The Great Gatsby', 'F. Scott Fitzgerald'), (2, '1984', 'George Orwell'), and (3, 'To Kill a Mockingbird', 'Harper Lee'). You will use these values in the next step.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100)
);
-- Prepare the list of books to insert
-- Your code here

Need a hint?

Just add comments listing the exact book tuples for clarity before inserting.

3

Insert multiple rows into the books table

Write a single INSERT INTO books (id, title, author) statement that inserts all three books: (1, 'The Great Gatsby', 'F. Scott Fitzgerald'), (2, '1984', 'George Orwell'), and (3, 'To Kill a Mockingbird', 'Harper Lee').

MySQL

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

-- Books to insert:
-- (1, 'The Great Gatsby', 'F. Scott Fitzgerald')
-- (2, '1984', 'George Orwell')
-- (3, 'To Kill a Mockingbird', 'Harper Lee')

-- Insert all three books in one statement
-- Your code here

Need a hint?

Use one INSERT INTO statement with multiple sets of values separated by commas.

4

Verify the inserted rows

Write a SELECT * FROM books; statement to retrieve all rows from the books table and confirm the insertion.

MySQL

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

-- Books to insert:
-- (1, 'The Great Gatsby', 'F. Scott Fitzgerald')
-- (2, '1984', 'George Orwell')
-- (3, 'To Kill a Mockingbird', 'Harper Lee')

INSERT INTO books (id, title, author) VALUES
  (1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
  (2, '1984', 'George Orwell'),
  (3, 'To Kill a Mockingbird', 'Harper Lee');

-- Select all rows to verify
-- Your code here

Need a hint?

Use SELECT * FROM books; to see all rows in the table.