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

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

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

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

Hint

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

Practice

1. What is the main purpose of the INSERT statement in SQL?

easy

A. To add new rows of data into a table

B. To delete rows from a table

C. To update existing data in a table

D. To create a new table

Solution

1. Step 1: Understand the role of INSERT

   The INSERT statement is used to add new data rows into an existing table.

2. Step 2: Compare with other SQL commands

   DELETE removes rows, UPDATE changes existing data, and CREATE makes new tables, so these are different from INSERT.

3. Final Answer:

   To add new rows of data into a table -> Option A

4. Quick Check:

   INSERT adds rows = D [OK]

Hint: INSERT always adds new rows, not changes or deletes [OK]

Common Mistakes:

• Confusing INSERT with UPDATE or DELETE
• Thinking INSERT creates tables
• Assuming INSERT modifies existing rows

2. Which of the following is the correct syntax to insert a new row into a table named users with columns id and name?

easy

A. INSERT INTO users VALUES id=1, name='Alice';

B. INSERT users INTO (id, name) VALUES (1, 'Alice');

C. INSERT INTO users (id, name) VALUES (1, 'Alice');

D. INSERT (id, name) INTO users VALUES (1, 'Alice');

Solution

1. Step 1: Recall correct INSERT syntax

   The correct syntax is: INSERT INTO table_name (columns) VALUES (values);

2. Step 2: Check each option

   INSERT INTO users (id, name) VALUES (1, 'Alice'); matches the correct syntax exactly. Options A, B, and D have incorrect order or missing keywords.

3. Final Answer:

   INSERT INTO users (id, name) VALUES (1, 'Alice'); -> Option C

4. Quick Check:

   Correct INSERT syntax = C [OK]

Hint: Remember: INSERT INTO table (cols) VALUES (vals); [OK]

Common Mistakes:

• Swapping INTO and table name
• Omitting INTO keyword
• Using assignment inside VALUES

3. Given the table products with columns product_id and product_name, what will be the result of this query?

INSERT INTO products (product_id, product_name) VALUES (101, 'Pen');
SELECT * FROM products WHERE product_id = 101;

medium

A. No rows returned

B. One row with product_id 101 and product_name 'Pen'

C. Syntax error in INSERT statement

D. Multiple rows with product_id 101

Solution

1. Step 1: Understand the INSERT effect

   The INSERT adds a new row with product_id 101 and product_name 'Pen' to the products table.

2. Step 2: Analyze the SELECT query

   The SELECT searches for rows with product_id 101, which now includes the newly inserted row.

3. Final Answer:

   One row with product_id 101 and product_name 'Pen' -> Option B

4. Quick Check:

   INSERT adds row, SELECT finds it = B [OK]

Hint: INSERT adds row, SELECT finds it immediately after [OK]

Common Mistakes:

• Assuming INSERT does not add data immediately
• Thinking SELECT returns no rows
• Confusing syntax errors with correct queries

4. What is wrong with this SQL statement?

INSERT INTO employees (id, name) VALUES 1, 'John';

medium

A. Missing parentheses around VALUES

B. Incorrect table name

C. Missing comma between columns

D. VALUES keyword is misspelled

Solution

1. Step 1: Check VALUES syntax

   The VALUES keyword must be followed by parentheses enclosing the values, like VALUES (1, 'John').

2. Step 2: Identify the error in the statement

   The statement lacks parentheses around the values, causing a syntax error.

3. Final Answer:

   Missing parentheses around VALUES -> Option A

4. Quick Check:

   VALUES needs parentheses = A [OK]

Hint: Always put values inside parentheses after VALUES [OK]

Common Mistakes:

• Omitting parentheses around values
• Confusing column list commas
• Misspelling keywords

5. You have a table orders with columns order_id, customer, and amount. You want to insert multiple new orders in one statement. Which SQL command correctly inserts two rows at once?

hard

A. INSERT INTO orders (order_id, customer, amount) VALUES (1, 'Anna', 100) OR (2, 'Ben', 150);

B. INSERT INTO orders VALUES (1, 'Anna', 100); INSERT INTO orders VALUES (2, 'Ben', 150);

C. INSERT INTO orders (order_id, customer, amount) VALUES (1, 'Anna', 100) AND (2, 'Ben', 150);

D. INSERT INTO orders (order_id, customer, amount) VALUES (1, 'Anna', 100), (2, 'Ben', 150);

Solution

1. Step 1: Understand multi-row INSERT syntax

   To insert multiple rows in one statement, list each row's values separated by commas inside VALUES.

2. Step 2: Evaluate each option

   INSERT INTO orders (order_id, customer, amount) VALUES (1, 'Anna', 100), (2, 'Ben', 150); correctly uses comma-separated tuples. INSERT INTO orders VALUES (1, 'Anna', 100); INSERT INTO orders VALUES (2, 'Ben', 150); uses two separate statements, which is valid but not one statement. Options C and D use invalid keywords AND/OR instead of commas.

3. Final Answer:

   INSERT INTO orders (order_id, customer, amount) VALUES (1, 'Anna', 100), (2, 'Ben', 150); -> Option D

4. Quick Check:

   Multiple rows use commas in VALUES = A [OK]

Hint: Use commas between value sets to insert multiple rows [OK]

Common Mistakes:

• Using AND/OR instead of commas between rows
• Writing multiple separate INSERT statements
• Omitting parentheses around each row