UPDATE with expressions in SQL - Mini Project: Build & Apply

Update Records Using Expressions in SQL

📖 Scenario: You are managing a small store's database. The store keeps track of products and their prices. Sometimes, the store wants to increase prices by a certain percentage or apply discounts.

🎯 Goal: Learn how to update records in a SQL table using expressions to change values based on existing data.

📋 What You'll Learn

Create a table called products with columns id, name, and price.

Insert specific product data into the products table.

Write an UPDATE statement that increases prices by 10%.

Write an UPDATE statement that decreases prices by 5 for products costing more than 50.

💡 Why This Matters

🌍 Real World

Stores and businesses often need to update prices or quantities in their databases based on rules or calculations.

💼 Career

Knowing how to write update queries with expressions is essential for database administrators and developers managing real data.

1

Create the products table and insert data

Create a table called products with columns id (integer), name (text), and price (numeric). Then insert these exact rows: (1, 'Pen', 10), (2, 'Notebook', 55), (3, 'Backpack', 80).

SQL

-- Create the products table and insert data
-- Your code here

Need a hint?

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

2

Set a percentage increase factor

Create a variable or placeholder for the percentage increase factor called increase_factor and set it to 1.10 (which means a 10% increase). Since SQL does not support variables in all systems, just write a comment line defining increase_factor as 1.10 for reference.

SQL

CREATE TABLE products (
  id INTEGER,
  name TEXT,
  price NUMERIC
);

INSERT INTO products (id, name, price) VALUES
  (1, 'Pen', 10),
  (2, 'Notebook', 55),
  (3, 'Backpack', 80);

-- Define increase_factor as 1.10
-- Your code here

Need a hint?

Since SQL does not always support variables, use a comment to represent the factor.

3

Increase all product prices by 10%

Write an UPDATE statement that multiplies the price column by 1.10 for all rows in the products table.

SQL

CREATE TABLE products (
  id INTEGER,
  name TEXT,
  price NUMERIC
);

INSERT INTO products (id, name, price) VALUES
  (1, 'Pen', 10),
  (2, 'Notebook', 55),
  (3, 'Backpack', 80);

-- increase_factor = 1.10

-- Update prices by multiplying by 1.10
-- Your code here

Need a hint?

Use UPDATE with SET to change the price using an expression.

4

Decrease prices by 5 for expensive products

Write an UPDATE statement that subtracts 5 from the price column only for products where the price is greater than 50.

SQL

CREATE TABLE products (
  id INTEGER,
  name TEXT,
  price NUMERIC
);

INSERT INTO products (id, name, price) VALUES
  (1, 'Pen', 10),
  (2, 'Notebook', 55),
  (3, 'Backpack', 80);

-- increase_factor = 1.10

UPDATE products
SET price = price * 1.10;

-- Decrease price by 5 where price > 50
-- Your code here

Need a hint?

Use WHERE to update only rows with price greater than 50.