Why INSERT matters in SQL - See It in Action

Why INSERT Matters

📖 Scenario: You are managing a small library database. You need to add new books to the collection so readers can find them.

🎯 Goal: Build a simple table for books and practice adding new book records using the INSERT statement.

📋 What You'll Learn

Create a table called books with columns id (integer), title (text), and author (text).

Insert a new book record with specific values into the books table.

Add a configuration variable to hold the next book id.

Use the INSERT statement to add a new book using the id variable.

💡 Why This Matters

🌍 Real World

Adding new records to a database is essential for keeping data current, like adding new books to a library system.

💼 Career

Database administrators and developers often write INSERT statements to add data to tables in real applications.

1

Create the books table

Write a SQL statement to create a table called books with columns id as INTEGER, title as TEXT, and author as TEXT.

SQL

-- Your code here to create the books table

Need a hint?

Use CREATE TABLE books (id INTEGER, title TEXT, author TEXT); to create the table.

2

Set the next book id

Create a variable called next_id and set it to 1 to hold the next book's id.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT
);
-- Your code here to set next_id

Need a hint?

Use SET @next_id = 1; to create the variable.

3

Insert a new book record

Write an INSERT statement to add a new book with id as @next_id, title as 'The Great Gatsby', and author as 'F. Scott Fitzgerald' into the books table.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT
);

-- Set the next book id
SET @next_id = 1;
-- Your code here to insert the new book

Need a hint?

Use INSERT INTO books (id, title, author) VALUES (@next_id, 'The Great Gatsby', 'F. Scott Fitzgerald');

4

Update the next book id

Write a statement to increase @next_id by 1 to prepare for the next book insertion.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT
);

-- Set the next book id
SET @next_id = 1;

INSERT INTO books (id, title, author) VALUES (@next_id, 'The Great Gatsby', 'F. Scott Fitzgerald');
-- Your code here to update next_id

Need a hint?

Use SET @next_id = @next_id + 1; to increase the id.