Realational Database -Assignment 1c Solved

Assignment 1c

Continue to work with your partner and the script you created in Assignments 1a and 1b. You are adding new code to the pre-existing script that already has code for Assignments 1a and 1b and submitting me the complete script. PLEASE MODIFY THE PURPOSE OF THE SCRIPT AT THE HEAD as you build this script.

PLEASE SUBMIT A RUNNING SCRIPT WITH NO ERRORS TO ME. IF THE SCRIPT DOES NOT RUN COMPLETELY, I WILL ONLY MARK UNTIL WHERE IT RUNS OR THE OUTPUT(S) I CAN SEE. I WILL NOT CORRECT YOUR SCRIPT OR MARK THE CODE AFTER THE STATEMENT CAUSING THE ERROR, EVEN IF THEY ARE CORRECT. THEREFORE, IF SOMETHING DOES NOT WORK IN YOUR SCRIPT, REMOVE THE CODE BUT PLEASE GIVE ME AN ERROR FREE COMPLETELY RUNNING SCRIPT.

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