Common INSERT errors and fixes in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this INSERT query?

Consider a table users with columns id INT PRIMARY KEY and name VARCHAR(50). What happens when you run this query?

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

SQL

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

A Syntax error due to multiple rows in VALUES clause.

B Both rows are inserted successfully.

C Only the first row is inserted; the second causes a primary key violation error.

D The second row overwrites the first row silently.

💡 Hint

Primary keys must be unique. What happens if you insert duplicate keys?

The primary key column 'id' must have unique values. Trying to insert two rows with the same 'id' value 1 causes a primary key violation error on the second row.

❓ query_result

intermediate

What error does this INSERT cause?

Given a table products with columns product_id INT, name VARCHAR(100), and price DECIMAL(5,2), what error occurs when running:

INSERT INTO products (product_id, name, price) VALUES (101, 'Pen', 'free');

SQL

INSERT INTO products (product_id, name, price) VALUES (101, 'Pen', 'free');

A Data type mismatch error because 'free' is not a valid decimal.

B The row is inserted with NULL price.

C Syntax error due to quotes around 'free'.

D No error; 'free' is converted to 0.00.

💡 Hint

Check the data type of the price column and the value provided.

The price column expects a decimal number. The string 'free' cannot be converted to a decimal, causing a data type mismatch error.

📝 Syntax

advanced

Which option fixes the syntax error in this INSERT?

This query causes a syntax error:

INSERT INTO orders order_id, customer_id VALUES (1001, 2002);

Which option fixes it?

SQL

INSERT INTO orders order_id, customer_id VALUES (1001, 2002);

A INSERT INTO orders VALUES (order_id, customer_id) (1001, 2002);

B INSERT INTO orders VALUES order_id, customer_id (1001, 2002);

C INSERT orders INTO (order_id, customer_id) VALUES (1001, 2002);

D INSERT INTO orders (order_id, customer_id) VALUES (1001, 2002);

💡 Hint

Check the correct syntax for specifying columns in INSERT.

The column list must be enclosed in parentheses immediately after the table name.

❓ query_result

advanced

What happens if you omit columns in INSERT?

Given a table employees with columns id INT PRIMARY KEY, name VARCHAR(50), and department VARCHAR(50) DEFAULT 'Sales', what is the result of:

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

SQL

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

A Row inserted with department set to 'Sales'.

B Error because department column is missing.

C Row inserted with department set to NULL.

D Row inserted but department column is empty string.

💡 Hint

What happens when a column has a default value and is not specified in INSERT?

If a column has a default value and is omitted in the INSERT, the default value is used.

🔧 Debug

expert

Why does this INSERT fail with a foreign key error?

Tables:

• departments: dept_id INT PRIMARY KEY, dept_name VARCHAR(50)
• employees: emp_id INT PRIMARY KEY, name VARCHAR(50), dept_id INT FOREIGN KEY REFERENCES departments(dept_id)

Query:

INSERT INTO employees (emp_id, name, dept_id) VALUES (1, 'Anna', 5);

Assuming no department with dept_id = 5 exists, what happens?

SQL

INSERT INTO employees (emp_id, name, dept_id) VALUES (1, 'Anna', 5);

A Row inserted with dept_id set to NULL automatically.

B Foreign key constraint violation error occurs.

C Syntax error due to missing department.

D Row inserted successfully; foreign key is ignored.

💡 Hint

Foreign keys require referenced values to exist.

The foreign key constraint requires that dept_id in employees must exist in departments. Since dept_id 5 does not exist, the insert fails.