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

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

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

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

Hint

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

Practice

1. What does the SQL statement UPDATE employees SET salary = salary + 500 do?

easy

A. Sets all salaries to 500

B. Increases the salary of all employees by 500

C. Deletes employees with salary 500

D. Creates a new employee with salary 500

Solution

1. Step 1: Understand the UPDATE statement

   The statement updates the 'salary' column in the 'employees' table.

2. Step 2: Analyze the expression used

   It adds 500 to the current salary value for every row because no WHERE clause limits the rows.

3. Final Answer:

   Increases the salary of all employees by 500 -> Option B

4. Quick Check:

   salary = salary + 500 [OK]

Hint: No WHERE means update all rows with the expression [OK]

Common Mistakes:

• Thinking it sets salary to 500 instead of adding
• Confusing UPDATE with DELETE
• Assuming it adds 500 only to some rows without WHERE

2. Which of the following is the correct syntax to increase the price by 10% only for products in category 'Books'?

easy

A. UPDATE products SET price = price * 1.10 WHERE category = 'Books';

B. UPDATE products SET price = price + 10% WHERE category = 'Books';

C. UPDATE products SET price = price + 0.10 WHERE category = 'Books';

D. UPDATE products SET price = price * 10 WHERE category = 'Books';

Solution

1. Step 1: Identify correct expression for 10% increase

   Multiplying price by 1.10 increases it by 10%.

2. Step 2: Check WHERE clause for category filtering

   WHERE category = 'Books' limits update to only books.

3. Final Answer:

   UPDATE products SET price = price * 1.10 WHERE category = 'Books'; -> Option A

4. Quick Check:

   price * 1.10 with WHERE category = 'Books' [OK]

Hint: Use multiplication by 1.10 for 10% increase [OK]

Common Mistakes:

• Using + 10% which is invalid syntax
• Adding 0.10 instead of 10%
• Multiplying price by 10 instead of 1.10

3. Given the table accounts with columns id, balance, what will be the balances after running:
UPDATE accounts SET balance = balance - 100 WHERE balance >= 500;
Initial balances: 400, 500, 600

medium

A. [400, 600, 700]

B. [300, 400, 500]

C. [400, 500, 600]

D. [400, 400, 500]

Solution

1. Step 1: Identify which rows meet the WHERE condition

   Only balances 500 and 600 are >= 500, so only these rows update.

2. Step 2: Subtract 100 from qualifying balances

   500 - 100 = 400, 600 - 100 = 500; 400 remains unchanged.

3. Final Answer:

   [400, 400, 500] -> Option D

4. Quick Check:

   Only balances >= 500 reduced by 100 [OK]

Hint: Only rows matching WHERE get updated [OK]

Common Mistakes:

• Updating all rows ignoring WHERE
• Subtracting 100 from all balances
• Confusing >= with >

4. What is wrong with this SQL statement?
UPDATE orders SET total = total + discount WHERE discount;

medium

A. The SET expression cannot use other columns

B. The UPDATE statement is missing a table name

C. The WHERE clause is invalid because it lacks a comparison

D. The total column cannot be updated

Solution

1. Step 1: Analyze the WHERE clause

   WHERE discount is incomplete; it needs a condition like discount > 0.

2. Step 2: Check other parts of the statement

   Table name and SET expression are correct; updating with another column is valid.

3. Final Answer:

   The WHERE clause is invalid because it lacks a comparison -> Option C

4. Quick Check:

   WHERE must have a condition, not just a column name [OK]

Hint: WHERE needs a condition, not just a column name [OK]

Common Mistakes:

• Assuming column names alone are valid WHERE conditions
• Thinking SET cannot use other columns
• Ignoring missing comparison in WHERE

5. You want to increase the salary by 5% for employees whose current salary is below the average salary. Which SQL statement correctly does this?

hard

A. UPDATE employees SET salary = salary * 1.05 WHERE salary < (SELECT AVG(salary) FROM employees);

B. UPDATE employees SET salary = salary + 5 WHERE salary < AVG(salary);

C. UPDATE employees SET salary = salary * 1.05 WHERE salary > (SELECT AVG(salary) FROM employees);

D. UPDATE employees SET salary = salary * 0.95 WHERE salary < (SELECT AVG(salary) FROM employees);

Solution

1. Step 1: Use a subquery to get average salary

   The subquery (SELECT AVG(salary) FROM employees) returns the average salary.

2. Step 2: Apply 5% increase only to salaries below average

   WHERE salary < average filters employees below average; salary * 1.05 increases salary by 5%.

3. Final Answer:

   UPDATE employees SET salary = salary * 1.05 WHERE salary < (SELECT AVG(salary) FROM employees); -> Option A

4. Quick Check:

   Use subquery in WHERE and multiply salary by 1.05 [OK]

Hint: Use subquery in WHERE and multiply salary by 1.05 [OK]

Common Mistakes:

• Using AVG(salary) without subquery
• Adding 5 instead of 5%
• Updating salaries above average instead of below
• Multiplying by 0.95 which decreases salary