Index impact on INSERT and UPDATE in SQL - Mini Project: Build & Apply

Index Impact on INSERT and UPDATE

📖 Scenario: You are managing a small online store database. You want to understand how adding an index affects the speed of inserting new orders and updating existing orders.

🎯 Goal: Build a simple orders table, add an index, and write queries to insert and update data to see how the index affects these operations.

📋 What You'll Learn

Create a table called orders with columns order_id (integer primary key), customer_name (text), and order_total (decimal).

Add an index on the customer_name column.

Write an INSERT statement to add a new order.

Write an UPDATE statement to change the order_total for a specific order_id.

💡 Why This Matters

🌍 Real World

Indexes help databases find data faster but can slow down inserting and updating because the index must be updated too.

💼 Career

Database administrators and developers must balance indexes to optimize both read and write operations.

1

Create the orders table

Write a SQL statement to create a table called orders with columns: order_id as an integer primary key, customer_name as text, and order_total as decimal.

SQL

-- Your code here to create the orders table

Need a hint?

Use CREATE TABLE with the specified columns and types.

2

Add an index on customer_name

Write a SQL statement to create an index called idx_customer_name on the customer_name column of the orders table.

SQL

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_name TEXT,
    order_total DECIMAL
);
-- Your code here to create the index

Need a hint?

Use CREATE INDEX with the index name and target column.

3

Insert a new order

Write a SQL INSERT statement to add a new order with order_id 1, customer_name 'Alice', and order_total 100.50 into the orders table.

SQL

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_name TEXT,
    order_total DECIMAL
);
CREATE INDEX idx_customer_name ON orders(customer_name);
-- Your code here to insert a new order

Need a hint?

Use INSERT INTO with the specified values.

4

Update the order total

Write a SQL UPDATE statement to change the order_total to 120.75 for the order with order_id 1 in the orders table.

SQL

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_name TEXT,
    order_total DECIMAL
);
CREATE INDEX idx_customer_name ON orders(customer_name);
INSERT INTO orders (order_id, customer_name, order_total) VALUES (1, 'Alice', 100.50);
-- Your code here to update the order total

Need a hint?

Use UPDATE with SET and WHERE clauses.