COMMIT and ROLLBACK behavior in SQL - Mini Project: Build & Apply

Understanding COMMIT and ROLLBACK Behavior in SQL

📖 Scenario: You are managing a simple customer database for a small store. Sometimes, you need to add new customers or update their information. But if something goes wrong, you want to undo those changes to keep the data clean.

🎯 Goal: Learn how to use COMMIT and ROLLBACK commands in SQL to save or undo changes in a database transaction.

📋 What You'll Learn

Create a table named customers with columns id (integer), name (text), and email (text).

Insert initial data into the customers table.

Start a transaction and insert a new customer.

Use ROLLBACK to undo the insertion.

Start another transaction and insert a new customer.

Use COMMIT to save the insertion.

💡 Why This Matters

🌍 Real World

In real businesses, transactions ensure data stays correct even if errors happen during updates.

💼 Career

Database administrators and developers use COMMIT and ROLLBACK to manage data safely and avoid mistakes.

1

Create the customers table and insert initial data

Write SQL commands to create a table called customers with columns id (integer), name (text), and email (text). Then insert these exact rows: (1, 'Alice', '[e-mail]') and (2, 'Bob', '[e-mail]').

SQL

-- Create the customers table and insert initial data
-- Your code here

Need a hint?

Use CREATE TABLE to make the table and INSERT INTO to add rows.

2

Start a transaction and insert a new customer

Write SQL commands to start a transaction with BEGIN and insert a new customer with id 3, name 'Charlie', and email '[e-mail]' into the customers table.

SQL

CREATE TABLE customers (
  id INTEGER,
  name TEXT,
  email TEXT
);

INSERT INTO customers (id, name, email) VALUES
  (1, 'Alice', '[e-mail]'),
  (2, 'Bob', '[e-mail]');

-- Start a transaction and insert a new customer
-- Your code here

Need a hint?

Use BEGIN to start the transaction and INSERT INTO to add the new row.

3

Undo the insertion with ROLLBACK

Write the SQL command to undo the last transaction using ROLLBACK.

SQL

CREATE TABLE customers (
  id INTEGER,
  name TEXT,
  email TEXT
);

INSERT INTO customers (id, name, email) VALUES
  (1, 'Alice', '[e-mail]'),
  (2, 'Bob', '[e-mail]');

BEGIN;
INSERT INTO customers (id, name, email) VALUES (3, 'Charlie', '[e-mail]');

-- Undo the insertion
-- Your code here

Need a hint?

Use ROLLBACK to undo the changes made in the current transaction.

4

Start another transaction, insert a new customer, and save with COMMIT

Write SQL commands to start a new transaction with BEGIN, insert a new customer with id 4, name 'Diana', and email '[e-mail]', and then save the changes permanently using COMMIT.

SQL

CREATE TABLE customers (
  id INTEGER,
  name TEXT,
  email TEXT
);

INSERT INTO customers (id, name, email) VALUES
  (1, 'Alice', '[e-mail]'),
  (2, 'Bob', '[e-mail]');

BEGIN;
INSERT INTO customers (id, name, email) VALUES (3, 'Charlie', '[e-mail]');
ROLLBACK;

-- Start a new transaction, insert a new customer, and commit
-- Your code here

Need a hint?

Use BEGIN to start, INSERT INTO to add the row, and COMMIT to save.