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.