Common INSERT errors and fixes in SQL - Mini Project: Build & Apply

Common INSERT Errors and Fixes in SQL

📖 Scenario: You are managing a small bookstore database. You want to add new books to the books table, but sometimes your INSERT commands have errors. You will practice writing correct INSERT statements and fixing common mistakes.

🎯 Goal: Build a series of correct SQL INSERT statements to add new books to the books table, learning how to avoid and fix common INSERT errors.

📋 What You'll Learn

Create the books table with columns id, title, author, and year_published

Write an INSERT statement with all columns specified and correct values

Write an INSERT statement omitting the year_published column and providing only the other columns

Fix an INSERT statement with a missing value error

Fix an INSERT statement with a datatype mismatch error

💡 Why This Matters

🌍 Real World

In real databases, inserting data correctly is essential to keep data accurate and avoid errors that stop your application.

💼 Career

Database administrators and developers must write correct INSERT statements and fix common errors to maintain data integrity and application stability.

1

Create the books table

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

SQL

-- Your code here

Hint

Use CREATE TABLE with the exact column names and types.

2

Insert a book with all columns

Write an INSERT statement to add a book with id 1, title 'The Great Gatsby', author 'F. Scott Fitzgerald', and year_published 1925. Specify all columns in the INSERT statement.

SQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  author TEXT,
  year_published INTEGER
);
-- Your code here

Hint

Use INSERT INTO books (columns) VALUES (values) with all columns listed.

3

Insert a book omitting year_published

Write an INSERT statement to add a book with id 2, title '1984', and author 'George Orwell', but omit the year_published column. Specify only the columns you provide values for.

SQL

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

INSERT INTO books (id, title, author, year_published) VALUES (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 1925);
-- Your code here

Hint

List only the columns you provide values for in the INSERT statement.

4

Fix missing value error in INSERT

Fix this INSERT statement that causes a missing value error: INSERT INTO books (id, title, author, year_published) VALUES (3, 'To Kill a Mockingbird', 1960); Add the missing author value 'Harper Lee' in the correct position.

SQL

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

INSERT INTO books (id, title, author, year_published) VALUES (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 1925);

INSERT INTO books (id, title, author) VALUES (2, '1984', 'George Orwell');

-- Fix the INSERT statement below
INSERT INTO books (id, title, author, year_published) VALUES (3, 'To Kill a Mockingbird', 1960);

Hint

Make sure the number of values matches the number of columns listed.

Practice

1. What is a common cause of an INSERT statement failing in SQL?

easy

A. Adding extra spaces between keywords

B. Using SELECT instead of INSERT

C. Missing a value for a column that does not allow NULLs

D. Writing the table name in lowercase

Solution

1. Step 1: Understand column constraints

   Columns that do not allow NULL values must have a value provided during insert.

2. Step 2: Identify missing values impact

   If a required column is missing a value, the insert will fail with an error.

3. Final Answer:

   Missing a value for a column that does not allow NULLs -> Option C

4. Quick Check:

   Required columns need values [OK]

Hint: Always provide values for NOT NULL columns [OK]

Common Mistakes:

• Ignoring NOT NULL constraints
• Assuming case sensitivity causes errors
• Confusing SELECT with INSERT

2. Which of the following INSERT statements is syntactically correct for a table users(id, name)?

easy

A. INSERT INTO users VALUES (1, 'Alice');

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

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

D. INSERT INTO users VALUES 1, 'Alice';

Solution

1. Step 1: Review correct INSERT syntax

   The correct syntax is: INSERT INTO table_name VALUES (value1, value2, ...);

2. Step 2: Check each option

   INSERT INTO users VALUES (1, 'Alice'); matches the correct syntax exactly; others have misplaced keywords or missing parentheses.

3. Final Answer:

   INSERT INTO users VALUES (1, 'Alice'); -> Option A

4. Quick Check:

   INSERT INTO table VALUES (...) [OK]

Hint: Remember: INSERT INTO table VALUES (values) [OK]

Common Mistakes:

• Misplacing keywords order
• Omitting parentheses around values
• Using wrong keyword sequence

3. Given the table products(id INT PRIMARY KEY, name VARCHAR(20)), what happens when you run:
INSERT INTO products VALUES (1, 'Pen');
INSERT INTO products VALUES (1, 'Pencil');

medium

A. Syntax error on second insert

B. Both rows are inserted successfully

C. Second insert overwrites the first row

D. Second insert fails due to duplicate primary key

Solution

1. Step 1: Understand primary key constraint

   Primary keys must be unique; duplicate values cause insert failure.

2. Step 2: Analyze the inserts

   The first insert adds id=1. The second tries to add id=1 again, causing a duplicate key error.

3. Final Answer:

   Second insert fails due to duplicate primary key -> Option D

4. Quick Check:

   Primary key duplicates cause insert errors [OK]

Hint: Primary keys must be unique; duplicates fail [OK]

Common Mistakes:

• Assuming duplicates overwrite existing rows
• Ignoring primary key uniqueness
• Expecting syntax error instead of constraint error

4. You run this query:
INSERT INTO employees (id, name, age) VALUES (5, 'John');
What is the error and how to fix it?

medium

A. Syntax error: missing value for age; add the missing value

B. No error; query runs fine

C. Wrong table name; change employees to employee

D. Age column must be removed from column list

Solution

1. Step 1: Check column-value count

   Columns listed are 3, but only 2 values provided; this causes an error.

2. Step 2: Fix by adding missing value

   Provide a value for age or remove it from the column list to match values count.

3. Final Answer:

   Syntax error: missing value for age; add the missing value -> Option A

4. Quick Check:

   Columns and values count must match [OK]

Hint: Match columns count with values count [OK]

Common Mistakes:

• Providing fewer values than columns
• Assuming missing values default automatically
• Ignoring syntax errors from mismatch

5. You want to insert multiple rows into orders(order_id INT PRIMARY KEY, product VARCHAR(50), quantity INT). Which statement correctly inserts two rows without errors?

hard

A. INSERT INTO orders VALUES (101, 'Book', 3); INSERT INTO orders VALUES (101, 'Pen', 5);

B. INSERT INTO orders VALUES (101, 'Book', 3), (102, 'Pen', 5);

C. INSERT INTO orders (order_id, product) VALUES (101, 'Book'), (102, 'Pen');

D. INSERT INTO orders VALUES (101, 'Book', 'three'), (102, 'Pen', 5);

Solution

1. Step 1: Check multiple row insert syntax

   INSERT INTO orders VALUES (101, 'Book', 3), (102, 'Pen', 5); uses correct syntax to insert multiple rows in one statement.

2. Step 2: Validate data types and uniqueness

   INSERT INTO orders VALUES (101, 'Book', 3), (102, 'Pen', 5); provides unique order_id and correct data types; others have duplicates, missing columns, or wrong types.

3. Final Answer:

   INSERT INTO orders VALUES (101, 'Book', 3), (102, 'Pen', 5); -> Option B

4. Quick Check:

   Multiple rows insert with unique keys and correct types [OK]

Hint: Use comma-separated rows with matching columns [OK]

Common Mistakes:

• Duplicate primary keys in multiple inserts
• Missing required columns in multi-row insert
• Wrong data types causing errors