INSERT INTO single row in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the result of this INSERT statement?

Given the table employees with columns id (int), name (varchar), and age (int), what will be the result after running this query?

INSERT INTO employees (id, name, age) VALUES (1, 'Alice', 30);

Assume the table was empty before.

MySQL

INSERT INTO employees (id, name, age) VALUES (1, 'Alice', 30);

A One new row is added with id=1, name='Alice', age=30

B No rows are added because the table is empty

C An error occurs because the id column is missing

D Two rows are added with the same data

💡 Hint

Think about what INSERT INTO does when the table is empty.

The INSERT INTO statement adds exactly one row with the specified values into the table. Since the table was empty, now it has one row with the given data.

📝 Syntax

intermediate

Which INSERT statement is syntactically correct?

Choose the correct INSERT statement to add a single row into the products table with columns product_id, product_name, and price.

A INSERT INTO products (product_id, product_name, price) VALUES 101, 'Pen', 1.5;

B INSERT products INTO VALUES (101, 'Pen', 1.5);

C INSERT INTO products VALUES (101, 'Pen', 1.5);

D INSERT INTO products (product_id, product_name, price) VALUE (101, 'Pen', 1.5);

💡 Hint

Remember the correct order of keywords in an INSERT statement.

Option C uses the correct syntax: INSERT INTO table VALUES (...). Option C has wrong keyword order. Option C misses parentheses around VALUES. Option C uses VALUE instead of VALUES, which is invalid in MySQL.

❓ optimization

advanced

Which INSERT statement is more efficient for inserting a single row?

You want to insert a single row into the orders table with columns order_id, customer, and amount. Which option is more efficient and why?

A INSERT INTO orders SET order_id=5001, customer='John Doe', amount=250;

B INSERT INTO orders VALUES (5001, 'John Doe', 250);

C INSERT INTO orders (order_id, customer) VALUES (5001, 'John Doe');

D INSERT INTO orders (order_id, customer, amount) VALUES (5001, 'John Doe', 250);

💡 Hint

Consider clarity and explicitness in specifying columns.

Option D explicitly lists columns and values, which is clearer and safer if table structure changes. Option D relies on column order, which can cause errors. Option D uses SET syntax which is valid but less common and may be less optimized. Option D misses the amount column, causing an error if it is NOT NULL.

🔧 Debug

advanced

Why does this INSERT statement fail?

Given the table students with columns id (int, primary key), name (varchar), and age (int NOT NULL), why does this query fail?

INSERT INTO students (id, name) VALUES (10, 'Emma');

MySQL

INSERT INTO students (id, name) VALUES (10, 'Emma');

A Because the table students does not exist

B Because the age column is NOT NULL and no value was provided

C Because the name column cannot be inserted without age

D Because the id column is missing in the VALUES clause

💡 Hint

Check the NOT NULL constraints on columns.

The age column is NOT NULL, so it requires a value on insert. Omitting it causes an error.

🧠 Conceptual

expert

What happens if you insert a row with a duplicate primary key?

Consider a table inventory with item_id as the primary key. What happens if you run this query twice?

INSERT INTO inventory (item_id, item_name) VALUES (100, 'Notebook');

MySQL

INSERT INTO inventory (item_id, item_name) VALUES (100, 'Notebook');

A The first insert succeeds; the second insert fails with a duplicate key error

B Both inserts succeed and create two identical rows

C The second insert updates the existing row with new data

D Both inserts fail because primary keys cannot be inserted

💡 Hint

Think about primary key uniqueness constraints.

Primary keys must be unique. The first insert adds the row. The second insert tries to add a duplicate key and fails with an error.

Practice

1. What does the INSERT INTO statement do in MySQL?

easy

A. Creates a new table

B. Deletes a row from a table

C. Adds a new row to a table

D. Updates existing rows in a table

Solution

1. Step 1: Understand the purpose of INSERT INTO

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

2. Step 2: Compare with other SQL commands

   Deleting removes rows, updating changes existing rows, and creating makes new tables, so these are different commands.

3. Final Answer:

   Adds a new row to a table -> Option C

4. Quick Check:

   INSERT INTO = Add row [OK]

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

Common Mistakes:

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

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

easy

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

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

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

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

Solution

1. Step 1: Check the standard INSERT INTO syntax

   The correct syntax is INSERT INTO table_name (columns) VALUES (values); which matches INSERT INTO users (id, name) VALUES (1, 'Alice');.

2. Step 2: Identify errors in other options

   INSERT users INTO (id, name) VALUES (1, 'Alice'); has wrong order of keywords, C uses invalid assignment syntax, and D uses SET incorrectly.

3. Final Answer:

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

4. Quick Check:

   INSERT INTO ... VALUES (...) is correct syntax [OK]

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

Common Mistakes:

• Swapping keywords order
• Using SET instead of VALUES
• Incorrect assignment inside VALUES

3. Given the table products(id INT, name VARCHAR(20), price DECIMAL(5,2)), what will be the result of this query?
INSERT INTO products (id, name, price) VALUES (10, 'Pen', 1.50); SELECT * FROM products WHERE id = 10;

medium

A. [{"id":10, "name":"Pen", "price":1.50}]

B. Syntax error due to missing semicolon

C. Empty result, no rows inserted

D. [{"id":null, "name":"Pen", "price":1.50}]

Solution

1. Step 1: Analyze the INSERT statement

   The query inserts a row with id=10, name='Pen', price=1.50 into the products table.

2. Step 2: Check the SELECT query result

   The SELECT fetches the row with id=10, which was just inserted, so it returns that row.

3. Final Answer:

   [{"id":10, "name":"Pen", "price":1.50}] -> Option A

4. Quick Check:

   Inserted row is returned by SELECT [OK]

Hint: Inserted row appears in SELECT immediately after insert [OK]

Common Mistakes:

• Assuming insert fails without error
• Expecting empty result after insert
• Confusing syntax errors with correct queries

4. Identify the error in this query:
INSERT INTO employees (id, name, salary) VALUES (5, John, 3000);

medium

A. Salary should be a string

B. Missing parentheses around VALUES

C. Wrong table name

D. Missing quotes around the string 'John'

Solution

1. Step 1: Check string value syntax

   String values like names must be enclosed in single quotes in SQL, but 'John' is unquoted here.

2. Step 2: Verify other parts of the query

   Parentheses around VALUES are correct, table name is plausible, and salary is numeric so no quotes needed.

3. Final Answer:

   Missing quotes around the string 'John' -> Option D

4. Quick Check:

   Strings need quotes in VALUES [OK]

Hint: Always quote text values in INSERT [OK]

Common Mistakes:

• Forgetting quotes for text
• Confusing numeric and string types
• Misplacing parentheses

5. You have a table orders(order_id INT, customer VARCHAR(50), total DECIMAL(7,2)). You want to insert a new order with order_id 101, customer name "O'Reilly", and total 250.75. Which query correctly inserts this row?

hard

A. INSERT INTO orders (order_id, customer, total) VALUES (101, 'O''Reilly', 250.75);

B. INSERT INTO orders (order_id, customer, total) VALUES (101, 'O\'Reilly', 250.75);

C. INSERT INTO orders (order_id, customer, total) VALUES (101, 'O'Reilly', 250.75);

D. INSERT INTO orders (order_id, customer, total) VALUES (101, "O'Reilly", 250.75);

Solution

1. Step 1: Understand how to escape single quotes in SQL strings

   In SQL, single quotes inside strings are escaped by doubling them: 'O''Reilly'.

2. Step 2: Evaluate each option's string quoting

   INSERT INTO orders (order_id, customer, total) VALUES (101, 'O'Reilly', 250.75); breaks string due to unescaped single quote. INSERT INTO orders (order_id, customer, total) VALUES (101, 'O\'Reilly', 250.75); uses backslash which is not standard in MySQL. INSERT INTO orders (order_id, customer, total) VALUES (101, "O'Reilly", 250.75); uses double quotes which are not string delimiters in SQL. INSERT INTO orders (order_id, customer, total) VALUES (101, 'O''Reilly', 250.75); correctly doubles the single quote.

3. Final Answer:

   INSERT INTO orders (order_id, customer, total) VALUES (101, 'O''Reilly', 250.75); -> Option A

4. Quick Check:

   Escape single quote by doubling it [OK]

Hint: Escape single quote inside strings by doubling it '' [OK]

Common Mistakes:

• Using unescaped single quotes inside strings
• Using backslash escape in MySQL strings
• Using double quotes for string literals