UPDATE with expressions in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SQL UPDATE statement do?

The UPDATE statement changes existing data in a table by modifying one or more columns for rows that match a condition.

beginner

How can you use expressions in an UPDATE statement?

You can use expressions to calculate new values based on existing column values, like adding 10 to a salary: SET salary = salary + 10.

beginner

Example: What does this SQL do? <br>UPDATE employees SET salary = salary * 1.05 WHERE department = 'Sales';

It increases the salary by 5% for all employees in the Sales department.

intermediate

Can you use functions in UPDATE expressions? Give an example.

Yes, you can use functions. For example, to set a date column to the current date: UPDATE orders SET shipped_date = CURRENT_DATE WHERE shipped_date IS NULL;

beginner

Why is it important to use a WHERE clause in an UPDATE statement with expressions?

Without a WHERE clause, the UPDATE will change all rows in the table, which might cause unintended data changes.

What happens if you run an UPDATE statement without a WHERE clause?

A Only the first row is updated

B No rows are updated

C All rows in the table are updated

D The statement will cause an error

Without a WHERE clause, the UPDATE affects every row in the table.

Which of the following is a valid expression in an UPDATE statement?

A SET price = price - 'ten'

B SET price = price + 10

C SET price = price & 10

D SET price = 'price + 10'

You can add numbers directly in expressions like price = price + 10. The others are invalid or use wrong syntax.

How do you increase a numeric column 'score' by 20% using UPDATE?

A SET score = score * 1.2

B SET score = score + 20

C SET score = score / 1.2

D SET score = score - 20

Multiplying by 1.2 increases the value by 20%.

Which SQL function can you use to set a date column to today’s date in an UPDATE?

A CURRENT_DATE

B NOW()

C GETDATE()

D TODAY()

CURRENT_DATE is standard SQL for today's date. NOW() and GETDATE() are specific to some databases; TODAY() is not standard.

What does this statement do? <br>UPDATE products SET stock = stock - 1 WHERE product_id = 100;

A Deletes product 100

B Sets stock to 1 for product 100

C Increases stock by 1 for product 100

D Decreases stock by 1 for product 100

It subtracts 1 from the stock column for the product with ID 100.

Explain how to use expressions in an UPDATE statement to modify data.

Think about changing values based on current data.

Describe why the WHERE clause is important in UPDATE statements with expressions.

Consider what happens if you forget WHERE.