INSERT and auto-generated keys in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SQL INSERT statement do?

The INSERT statement adds new rows (records) into a database table.

beginner

What is an auto-generated key in a database?

An auto-generated key is a unique value created automatically by the database, often used as a primary key to identify each row uniquely.

intermediate

How do you insert a row and get the auto-generated key in SQL?

You use INSERT to add the row, then use RETURNING (PostgreSQL) or LAST_INSERT_ID() (MySQL) to get the new key.

beginner

Why are auto-generated keys useful?

They ensure each row has a unique ID without manual input, making it easier to reference and manage data.

beginner

Write a simple SQL INSERT statement for a table 'users' with columns 'name' and 'email'.

INSERT INTO users (name, email) VALUES ('Alice', '[e-mail]');

What SQL clause can you use in PostgreSQL to get the auto-generated key after an INSERT?

A LAST_INSERT_ID()

B RETURNING

C FETCH

D OUTPUT

In PostgreSQL, RETURNING lets you get the auto-generated key immediately after an INSERT.

Which SQL function retrieves the last auto-generated ID in MySQL?

A LAST_INSERT_ID()

B GET_ID()

C RETURNING

D IDENTITY()

MySQL uses LAST_INSERT_ID() to get the last auto-generated key.

What happens if you try to insert a row without specifying a value for an auto-generated key column?

A The database generates a unique key automatically

B The insert fails with an error

C The key is set to NULL

D The key is set to zero

Auto-generated keys are created automatically if you don't provide a value.

Which of these is NOT a reason to use auto-generated keys?

A To avoid key duplication errors

B To ensure unique row identification

C To simplify referencing rows

D To manually assign IDs for each row

Auto-generated keys remove the need to manually assign IDs.

What is the correct syntax to insert a new user named 'Bob' with email '[e-mail]' into a 'users' table?

A ADD INTO users (name, email) VALUES ('Bob', '[e-mail]');

B INSERT users VALUES ('Bob', '[e-mail]');

C INSERT INTO users (name, email) VALUES ('Bob', '[e-mail]');

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

Option C uses correct INSERT syntax specifying columns and values.

Explain how to insert a new row into a table and retrieve its auto-generated key.

Think about how databases create unique IDs automatically and how you can get that ID after adding data.

Why are auto-generated keys important in database tables? Give an example.

Consider how you would keep track of many records without repeating or missing IDs.