INSERT with specific columns in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What rows are inserted by this query?

Given the table Employees with columns id, name, department, and salary, what rows will be added after running this query?

INSERT INTO Employees (id, name) VALUES (101, 'Alice'), (102, 'Bob');

SQL

INSERT INTO Employees (id, name) VALUES (101, 'Alice'), (102, 'Bob');

A Two rows inserted: (101, 'Alice', 'Unknown', 0) and (102, 'Bob', 'Unknown', 0)

B Two rows inserted: (101, 'Alice', '', 0) and (102, 'Bob', '', 0)

C Two rows inserted: (101, 'Alice', NULL, NULL) and (102, 'Bob', NULL, NULL)

D No rows inserted because department and salary are missing

💡 Hint

Columns not listed in the INSERT get default values or NULL if no default.

When you specify columns in INSERT, only those columns get values. Others get NULL if allowed or default values if defined. Here, department and salary get NULL.

📝 Syntax

intermediate

Which INSERT statement is syntactically correct?

Choose the correct INSERT statement that adds a row with id=201 and name='Charlie' into the Users table with columns id, name, and email.

A INSERT INTO Users (id, name) VALUES (201, 'Charlie');

B INSERT INTO Users (id, name) VALUE (201, 'Charlie');

C INSERT INTO Users (id, name, email) VALUES (201, 'Charlie');

D INSERT INTO Users VALUES (201, 'Charlie');

💡 Hint

Check if the number of columns matches the number of values and syntax keywords.

Option A correctly specifies columns and matching values. Option A misses email column value. Option A has fewer values than columns. Option A uses wrong keyword VALUE instead of VALUES.

❓ optimization

advanced

Which INSERT is more efficient for adding multiple rows with partial columns?

You want to insert 1000 rows into the Products table with columns product_id, name, price, and stock. You only have product_id and name data. Which query is better for performance?

A INSERT INTO Products VALUES (1, 'Pen', NULL, NULL), (2, 'Pencil', NULL, NULL), ..., (1000, 'Eraser', NULL, NULL);

B INSERT INTO Products (product_id, name) VALUES (1, 'Pen'), (2, 'Pencil'), ..., (1000, 'Eraser');

C INSERT INTO Products (product_id, name, price, stock) SELECT product_id, name, NULL, NULL FROM TempProducts;

D INSERT INTO Products SELECT product_id, name FROM TempProducts;

💡 Hint

Consider specifying only needed columns and values directly.

Option B inserts only needed columns with values directly, which is efficient. Option B inserts NULLs explicitly, which is unnecessary. Option B uses SELECT but requires a TempProducts table. Option B misses columns and values mismatch.

🔧 Debug

advanced

Why does this INSERT fail?

Given table Orders with columns order_id, customer, date, and status (status has no default and is NOT NULL), why does this query fail?

INSERT INTO Orders (order_id, customer, date) VALUES (5001, 'John Doe', '2024-06-01');

A Because the <code>customer</code> value is too long

B Because the <code>date</code> format is invalid

C Because <code>order_id</code> must be auto-generated and cannot be inserted

D Because the <code>status</code> column is NOT NULL and missing in the INSERT

💡 Hint

Check constraints on columns not included in the INSERT.

The status column is NOT NULL and has no default, so it must be included in the INSERT or the query fails.

🧠 Conceptual

expert

What happens if you insert with specific columns but omit a NOT NULL column with a default?

Consider a table Inventory with columns item_id (PK), name, quantity NOT NULL DEFAULT 0, and location. What will be the value of quantity after this query?

INSERT INTO Inventory (item_id, name) VALUES (3001, 'Notebook');

A The <code>quantity</code> will be 0 because of the default value

B The <code>quantity</code> will be NULL because it was omitted

C The INSERT will fail because <code>quantity</code> is NOT NULL and missing

D The <code>quantity</code> will be set to an empty string

💡 Hint

Think about how default values work with NOT NULL columns.

If a NOT NULL column has a default, omitting it in INSERT sets it to the default value automatically.

Practice

1. What does the SQL statement INSERT INTO employees (name, age) VALUES ('Alice', 30); do?

easy

A. Deletes rows where name is 'Alice' and age is 30.

B. Inserts a new row filling all columns with 'Alice' and 30.

C. Updates existing rows setting name to 'Alice' and age to 30.

D. Inserts a new row with only the name and age columns filled, others get default or NULL.

Solution

1. Step 1: Understand the INSERT with specific columns

   The statement specifies only the columns 'name' and 'age' to insert values into.

2. Step 2: Effect on other columns

   Columns not listed get their default values or NULL if allowed.

3. Final Answer:

   Inserts a new row with only the name and age columns filled, others get default or NULL. -> Option D

4. Quick Check:

   INSERT with columns fills only those columns [OK]

Hint: List columns to insert only those, others get defaults [OK]

Common Mistakes:

• Thinking all columns get values from VALUES list
• Confusing INSERT with UPDATE
• Assuming unlisted columns get random data

2. Which of the following is the correct syntax to insert only the columns id and email into a table users?

easy

A. INSERT INTO users (id, email) VALUES (1, '[e-mail]');

B. INSERT INTO users SET id=1, email='[e-mail]';

C. INSERT users (id, email) VALUES (1, '[e-mail]');

D. INSERT INTO users VALUES (1, '[e-mail]');

Solution

1. Step 1: Identify correct INSERT syntax with specific columns

   The correct syntax requires specifying the table, columns in parentheses, then VALUES with matching values.

2. Step 2: Check each option

   INSERT INTO users (id, email) VALUES (1, '[e-mail]'); matches the correct syntax: INSERT INTO table (columns) VALUES (values).

3. Final Answer:

   INSERT INTO users (id, email) VALUES (1, '[e-mail]'); -> Option A

4. Quick Check:

   INSERT INTO table (cols) VALUES (vals) is correct [OK]

Hint: Use INSERT INTO table (cols) VALUES (vals) format [OK]

Common Mistakes:

• Omitting INTO keyword
• Using SET instead of VALUES
• Not specifying columns when needed

3. Given the table products(id INT, name VARCHAR, price DECIMAL DEFAULT 0), what will be the result of this query?
INSERT INTO products (id, name) VALUES (101, 'Pen');
Then selecting all rows?

medium

A. [{id: 101, name: 'Pen', price: NULL}]

B. [{id: 101, name: 'Pen', price: 0}]

C. [{id: 101, name: 'Pen'}]

D. Syntax error

Solution

1. Step 1: Understand default values on missing columns

   The 'price' column has a default value of 0, so if not specified, it gets 0.

2. Step 2: Result of the INSERT and SELECT

   The inserted row will have id=101, name='Pen', and price=0 from default.

3. Final Answer:

   [{id: 101, name: 'Pen', price: 0}] -> Option B

4. Quick Check:

   Default values fill missing columns [OK]

Hint: Unlisted columns get default values if defined [OK]

Common Mistakes:

• Assuming missing columns become NULL despite default
• Expecting syntax error without all columns
• Ignoring default values

4. Identify the error in this SQL statement:
INSERT INTO orders (order_id, customer) VALUES (123);

medium

A. Missing value for 'customer' column

B. Table name is incorrect

C. VALUES keyword is missing

D. Extra parentheses around columns

Solution

1. Step 1: Check number of columns vs values

   The statement lists two columns but provides only one value.

2. Step 2: Identify mismatch error

   SQL requires the number of values to match the number of columns exactly.

3. Final Answer:

   Missing value for 'customer' column -> Option A

4. Quick Check:

   Columns and values count must match [OK]

Hint: Count columns and values to match [OK]

Common Mistakes:

• Providing fewer values than columns
• Assuming missing values default to NULL always
• Ignoring syntax error messages

5. You have a table students(id INT PRIMARY KEY, name VARCHAR, grade CHAR DEFAULT 'F'). You want to insert a new student with id=5 and name='John', but leave grade as default. Which query is correct?

hard

A. INSERT INTO students (id, name, grade) VALUES (5, 'John', 'F');

B. INSERT INTO students VALUES (5, 'John');

C. INSERT INTO students (id, name) VALUES (5, 'John');

D. INSERT INTO students (name, grade) VALUES ('John', 'F');

Solution

1. Step 1: Understand default column behavior

   Omitting 'grade' column in INSERT lets it use its default 'F'.

2. Step 2: Check each option

   INSERT INTO students (id, name) VALUES (5, 'John'); omits grade, using default. INSERT INTO students (id, name, grade) VALUES (5, 'John', 'F'); sets it explicitly. INSERT INTO students VALUES (5, 'John'); has value count mismatch. INSERT INTO students (name, grade) VALUES ('John', 'F'); misses id primary key.

3. Final Answer:

   INSERT INTO students (id, name) VALUES (5, 'John'); -> Option C

4. Quick Check:

   Omit columns to use default values [OK]

Hint: Omit columns to use their default values [OK]

Common Mistakes:

• Providing all columns when default is desired
• Omitting primary key column causing errors
• Assuming VALUES must match all columns always