Update Statements

Write an UPDATE statement to change the salary of an employee with employee_id 101 to 60000.

• UPDATE employees SET salary = 60000 WHERE employee_id = 101;
• UPDATE employees SET salary = 60000;
• UPDATE employees SET salary = 60000 WHERE employee_id = '101';
• UPDATE employees SET salary = 60000 WHERE employee_id = 100;

How do you update multiple columns in a single UPDATE statement?

• UPDATE table_name SET column1 = value1 SET column2 = value2 WHERE condition;
• UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
• UPDATE table_name SET column1 = value1 AND column2 = value2 WHERE condition;
• UPDATE table_name SET column1 = value1, column2 = value2;

Write an UPDATE statement to set the status to 'inactive' for all users who haven't logged in for over a year.

• UPDATE users SET status = 'inactive' WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR);
• UPDATE users SET status = 'inactive' WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 MONTH);
• UPDATE users SET status = 'inactive' WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 DAY);
• UPDATE users SET status = 'inactive' WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 HOUR);

How would you update a column based on a calculation involving another column? For example, increasing price by 10%.

• UPDATE products SET price = price + 10;
• UPDATE products SET price = price * 1.10;
• UPDATE products SET price = price / 1.10;
• UPDATE products SET price = price - 10;

Write an UPDATE statement to change the department of employees with department_id 5 to 'Sales'.

• UPDATE employees SET department = 'Sales' WHERE department_id = 5;
• UPDATE employees SET department = 'Sales';
• UPDATE employees SET department = 'Sales' WHERE department_id = 6;
• UPDATE employees SET department = 'Sales' WHERE department_id = '5';

Why is it important to use the primary key in an UPDATE statement?

• It ensures that the UPDATE statement targets a specific, unique row.
• It allows updating multiple rows at once.
• It improves the readability of the SQL code.
• It prevents the need for a WHERE clause.

What could happen if you omit the WHERE clause in an UPDATE statement?

• Only the first row will be updated.
• All rows in the table will be updated.
• No rows will be updated.
• An error will be thrown.

Write an UPDATE statement using a primary key to change the email of a user with user_id 42.

• UPDATE users SET email = '[e-mail]' WHERE user_id = 42;
• UPDATE users SET email = '[e-mail]';
• UPDATE users SET email = '[e-mail]' WHERE user_id = '42';
• UPDATE users SET email = '[e-mail]' WHERE user_id = 43;

How does using a primary key in the WHERE clause improve performance?

• It allows the database to quickly locate the specific row to update.
• It makes the SQL code easier to read.
• It reduces the size of the database.
• It ensures that no rows are updated.

Explain the potential risks of updating rows without using a primary key.

• Accidental updates to multiple rows.
• Data inconsistency.
• Difficulty in tracking changes.
• All of the above.