INSERT and auto-generated keys in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output after inserting a row with auto-generated key?

Consider a table Users with columns id (auto-increment primary key) and name. After running the following SQL statements, what will be the value of id for the newly inserted row?

INSERT INTO Users (name) VALUES ('Alice');
SELECT id FROM Users WHERE name = 'Alice';

SQL

CREATE TABLE Users (id SERIAL PRIMARY KEY, name VARCHAR(100));
INSERT INTO Users (name) VALUES ('Alice');
SELECT id FROM Users WHERE name = 'Alice';

A The id will be 0 because auto-increment starts at zero.

B The id will be NULL because it is not specified in the INSERT.

C The id will be 1 if this is the first row inserted.

D The id will be the name 'Alice' because it is used as the key.

💡 Hint

Auto-increment keys start from 1 by default and increase by 1 for each new row.

When you insert a row without specifying the auto-increment column, the database automatically assigns the next available number starting at 1. So the first inserted row gets id = 1.

🧠 Conceptual

intermediate

Which SQL clause retrieves the auto-generated key after an INSERT?

You insert a new row into a table with an auto-generated primary key. Which SQL clause or statement lets you get the value of that new key immediately after insertion?

A GROUP BY clause after INSERT

B ORDER BY clause in the INSERT statement

C WHERE clause in the INSERT statement

D RETURNING clause (e.g., INSERT ... RETURNING id)

💡 Hint

Think about how to get data back from an INSERT in one step.

The RETURNING clause in SQL lets you get the values of columns, including auto-generated keys, right after inserting a row.

📝 Syntax

advanced

Identify the syntax error in this INSERT with auto-generated key retrieval

Which option contains a syntax error when trying to insert a row and get the auto-generated key in PostgreSQL?

SQL

INSERT INTO Orders (product) VALUES ('Book') RETURNING id;

A INSERT INTO Orders (product) RETURNING id VALUES ('Book');

B INSERT INTO Orders (product) VALUES ('Book') RETURNING id;

C INSERT INTO Orders (product) VALUES ('Book'); SELECT id FROM Orders;

D INSERT INTO Orders (product) VALUES ('Book') RETURNING *;

💡 Hint

Check the order of clauses in the INSERT statement.

The RETURNING clause must come after the VALUES clause. Option A places RETURNING before VALUES, which is invalid syntax.

❓ optimization

advanced

Best way to insert multiple rows and get all auto-generated keys efficiently

You want to insert multiple rows into a table with an auto-increment key and get all their generated keys in one query. Which option is the best approach?

A Use a single INSERT with multiple VALUES and RETURNING id

B Run multiple single-row INSERTs each with RETURNING id

C Insert rows without RETURNING and then query the table for max id

D Insert rows and use a trigger to store keys in another table

💡 Hint

Think about minimizing round trips to the database.

A single INSERT with multiple rows and RETURNING returns all generated keys at once, which is efficient and reduces database calls.

🔧 Debug

expert

Why does this INSERT fail to return the auto-generated key?

Given the table Products(id SERIAL PRIMARY KEY, name TEXT), the following query runs but returns no rows:

INSERT INTO Products (name) VALUES ('Pen');

Why does this happen if you want to get the new id?

A Because the SERIAL type does not auto-generate keys automatically

B Because the INSERT statement lacks a RETURNING clause to output the new id

C Because the name 'Pen' is already in the table causing a silent failure

D Because the id column must be included explicitly in the INSERT

💡 Hint

Think about how to get data back from an INSERT statement.

Without RETURNING, the INSERT does not output any rows. To get the new id, you must add RETURNING id.