Auto-commit behavior in SQL - Mini Project: Build & Apply

Understanding Auto-commit Behavior in SQL

📖 Scenario: You are managing a small library database. You want to understand how changes to the database are saved automatically or manually.

🎯 Goal: Learn how to control auto-commit behavior by creating a table, inserting data, and managing transactions.

📋 What You'll Learn

Create a table named books with columns id (integer) and title (text).

Set a variable to control auto-commit mode.

Insert a new book record using a transaction.

Commit the transaction explicitly if auto-commit is off.

💡 Why This Matters

🌍 Real World

Managing transactions is important in real-world databases to control when changes are saved or discarded.

💼 Career

Understanding auto-commit and transactions is essential for database administrators and developers to ensure data integrity.

1

Create the books table

Write a SQL statement to create a table called books with two columns: id as an integer primary key and title as text.

SQL

-- Your code here to create the books table

Need a hint?

Use CREATE TABLE with column definitions inside parentheses.

2

Set auto-commit mode off

Write a SQL statement to turn off auto-commit mode by starting a transaction explicitly with BEGIN TRANSACTION;.

SQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT
);
-- Your code here to start a transaction

Need a hint?

Use BEGIN TRANSACTION; to disable auto-commit temporarily.

3

Insert a book record

Write a SQL statement to insert a book with id 1 and title 'The Great Gatsby' into the books table.

SQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT
);
BEGIN TRANSACTION;
-- Your code here to insert a book

Need a hint?

Use INSERT INTO books (id, title) VALUES (1, 'The Great Gatsby');

4

Commit the transaction

Write a SQL statement to commit the current transaction using COMMIT; so the inserted book is saved.

SQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT
);
BEGIN TRANSACTION;
INSERT INTO books (id, title) VALUES (1, 'The Great Gatsby');
-- Your code here to commit the transaction

Need a hint?

Use COMMIT; to save changes when auto-commit is off.