Unit 3 Database - Implementation - SQL UPDATE

Question 1

What is the purpose of the UPDATE query?

Answer 1

The UPDATE query is used to amend rows in a table and can update multiple rows at the same time.

Question 2

What is the basic syntax of an UPDATE query?

Answer 2

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

Question 3

Why is the WHERE clause important in an UPDATE query?

Answer 3

The WHERE clause specifies which rows to update; without it, all rows in the table will be updated.

Question 4

Example: How would you update a user’s last login date to ‘01/01/2019’ where userid is 7914?

Answer 4

UPDATE user SET lastlogin = ‘01/01/2019’ WHERE userid = 7914;

Question 5

How do you update one row value in a table?

Answer 5

Use the UPDATE query with a specific WHERE condition to target one row.

Question 6

Example: Update the salon to ‘Westside’ for hairdresserid 2210.

Answer 6

UPDATE hairdresser SET salon = ‘Westside’ WHERE hairdresserid = 2210;

Question 7

How do you update several row values at once?

Answer 7

Use the UPDATE query with a condition that matches multiple rows.

Question 8

Example: Change hairdresserid to 3030 where it equals 1928.

Answer 8

UPDATE client SET hairdresserid = 3030 WHERE hairdresserid = 1928;

Question 9

What is an equi-join used for in an UPDATE query?

Answer 9

An equi-join updates rows based on matching values between two tables.

Question 10

Example: Update client.hairdresserid to 3030 where it matches hairdresser.hairdresserid and equals 1928.

Answer 10

UPDATE client, hairdresser SET client.hairdresserid = 3030 WHERE client.hairdresserid = hairdresser.hairdresserid AND hairdresser.hairdresserid = 1928;

Question 11

What should you do when updating a primary key?

Answer 11

Ensure corresponding foreign key values are updated to maintain referential integrity.

Question 12

Example: Update hairdresserid to 9255 where userId is 2210.

Answer 12

UPDATE hairdresser SET hairdresserid = 9255 WHERE userId = 2210;

Question 13

What happens if your RDBMS does not support cascade updates?

Answer 13

You must manually update corresponding foreign keys using another UPDATE query.

Question 14

Example: Manually update foreign keys for client table after changing a primary key.

Answer 14

UPDATE client SET hairdresserid = 9255 WHERE hairdresserid = 2210;

Question 15

How can you update text columns based on other text values?

Answer 15

Use the UPDATE query with a condition matching text values in the WHERE clause.

Question 16

Example: Update lastname to ‘McLeod’ where salon is ‘On The Corner’.

Answer 16

UPDATE hairdresser SET lastname = ‘McLeod’ WHERE salon = ‘On The Corner’;

Question 17

How can you update number columns based on other numbers?

Answer 17

Use the UPDATE query with numeric conditions in the WHERE clause.

Question 18

Example: Change hairdresserid to 3030 where it equals 2019.

Answer 18

UPDATE client SET hairdresserid = 3030 WHERE hairdresserid = 2019;

Question 19

How can you update text columns based on numbers?

Answer 19

Use the UPDATE query with numeric conditions in the WHERE clause.

Question 20

Example: Update clientfirstname to ‘George’ where clientid equals 11766.

Answer 20

UPDATE client SET clientfirstname = ‘George’ WHERE clientid = 11766;

Question 21

What precaution should you take before running an UPDATE query?

Answer 21

Run a SELECT query with the same WHERE clause to verify which records will be updated.

Question 22

What happens if you omit the WHERE clause in an UPDATE query?

Answer 22

All rows in the table will be updated, which can lead to unintended changes.