INSERT and auto-generated keys in SQL - Mini Project: Build & Apply

Insert Records with Auto-Generated Keys

📖 Scenario: You are managing a small library database. You want to add new books to the books table. Each book has a unique ID that the database creates automatically when you add a new record.

🎯 Goal: Learn how to insert new records into a table where the primary key is automatically generated by the database.

📋 What You'll Learn

Create a table called books with columns id (auto-increment primary key), title, and author.

Insert a new book record without specifying the id (let the database generate it).

Insert a second book record similarly.

Retrieve all records from the books table to see the auto-generated id values.

💡 Why This Matters

🌍 Real World

Auto-generated keys are used in many real-world databases to uniquely identify records without manual input.

💼 Career

Understanding how to insert records with auto-generated keys is essential for database management and backend development roles.

1

Create the books table

Write a SQL statement to create a table called books with three columns: id as an auto-incrementing primary key, title as text, and author as text.

SQL

-- Your code here to create the books table

Need a hint?

Use SERIAL for auto-incrementing id in PostgreSQL or AUTO_INCREMENT in MySQL.

2

Insert the first book record

Write a SQL INSERT statement to add a book with title 'The Great Gatsby' and author 'F. Scott Fitzgerald' into the books table. Do not specify the id column.

SQL

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    author TEXT NOT NULL
);

-- Your code here to insert the first book

Need a hint?

Only include title and author in the INSERT statement.

3

Insert the second book record

Write a SQL INSERT statement to add a book with title '1984' and author 'George Orwell' into the books table. Again, do not specify the id column.

SQL

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    author TEXT NOT NULL
);

INSERT INTO books (title, author) VALUES ('The Great Gatsby', 'F. Scott Fitzgerald');

-- Your code here to insert the second book

Need a hint?

Use the same pattern as the first insert but with the new book details.

4

Retrieve all books to see auto-generated IDs

Write a SQL SELECT statement to get all columns from the books table to see the records with their auto-generated id values.

SQL

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    author TEXT NOT NULL
);

INSERT INTO books (title, author) VALUES ('The Great Gatsby', 'F. Scott Fitzgerald');

INSERT INTO books (title, author) VALUES ('1984', 'George Orwell');

-- Your code here to select all books

Need a hint?

Use SELECT * FROM books; to see all records.