INSERT INTO single row in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SQL command INSERT INTO do?

It adds a new row of data into a table in the database.

beginner

Write the basic syntax to insert a single row into a table named users with columns name and age.

INSERT INTO users (name, age) VALUES ('Alice', 30);

intermediate

Can you insert a row without specifying column names? When is it allowed?

Yes, if you provide values for all columns in the exact order they appear in the table. Otherwise, you must specify column names.

intermediate

What happens if you try to insert a row with a missing value for a column that does not allow NULL?

The database will return an error because that column requires a value and cannot be empty.

beginner

Why is it a good idea to specify column names in an INSERT INTO statement?

It makes your query clearer and safer, especially if the table structure changes later. It also avoids mistakes with column order.

Which SQL keyword is used to add a single row to a table?

A UPDATE

B SELECT

C INSERT INTO

D DELETE

INSERT INTO is used to add new rows to a table.

What must you include in an INSERT INTO statement to add data correctly?

A Table name and values

B Only values

C Only table name

D Column names and values

You should include column names and their corresponding values to avoid errors.

What happens if you omit column names but provide fewer values than columns in the table?

A An error occurs

B The database guesses the columns

C The row is inserted with NULLs

D The row is inserted with default values

Omitting column names requires values for all columns. Otherwise, an error happens.

Which of these is a correct INSERT INTO statement for table products(id, name)?

A INSERT INTO products (id, name) VALUES (1, 'Pen');

B INSERT INTO products VALUES (1, 'Pen');

C INSERT products (id, name) VALUES (1, 'Pen');

D INSERT INTO products (name, id) VALUES ('Pen', 1);

Options A, B, and D are correct. Option C is missing "INTO".

Why might you get an error inserting a row with a missing value?

A Because the value is too large

B Because the column is set to NOT NULL

C Because the database is offline

D Because the table is empty

If a column is NOT NULL, it must have a value when inserting a row.

Explain how to insert a single row into a MySQL table and why specifying column names is important.

Think about matching data to the right place in the table.

What errors might occur when inserting a single row and how can you prevent them?

Consider the rules the database enforces on data.

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