[Solved] RelationalDatabase Assignment 1c

You have created a table of your choice with relevant fields (Assignment 1a) and retrieved data from this table (Assignment 1b). You are now looking to update and delete data of/from this table. Add relevant UPDATE and DELETE statements at the end of the Assignment (1a + 1b), clearly commenting the functionality of each of them. Think of what type of updates and deletes can occur on the table data in a real life scenario and come up with associated statements for them. You can insert more relevant records in the table if you require to do so.

Important Note: Use literal text data in quotes, numeric data without quotes and CAST literals to DATE wherever required. You will lose marks if you do not follow this instruction.

Write 8 UPDATE statements, one for each criteria given below. Number them to match the criteria number 1, 2 etc in comments clearly stating the purpose of each update statement. Refer to scripts I have provided on blackboard.

1. Only one column is updated in a single record using the primary key
2. Only one column is updated in some, but not all, records
3. One update statement only to set more than one column/field in a single record without using the primary key
4. One update statement only to set more than one column/field in some, but not all, records
5. One or more fields/columns are updated in ALL records
6. SET involves a mathematical operation
7. SET sets a field value to NULL of one or more, but not all records (obviously, this field cannot be defined with NOT NULL in its definition)

Note: If you do not have a NOT NULL field, just change the definition of a relevant field by removing NOT NULL and leave a comment for me that you changed it for this update.

8. A statement with SET, WHERE, ORDER BY and LIMIT

Now add 6 DELETE statements, one for each criteria given below. Number them to match the criteria number 9, 10 etc in comments clearly stating the purpose of each delete statement. Refer to scripts I have provided on blackboard.

9. WHERE condition involves a compound condition, each simple condition using a relational operator
10. WHERE condition uses the NOT Boolean operator
11. WHERE condition uses BETWEEN with a DATE or CHAR/VARCHAR field
12. WHERE condition checks for NULL or NOT NULL on a certain field
13. WHERE works with a DATE field data (do not forget to CAST the literal value)
14. A statement with WHERE, ORDER BY and LIMIT