Relational Database-Assignment 1e Solved

Congratulations, you are now working on the last requirement of Assignment 1. I am assuming that you have understood the concepts well as you have independently built your own script from scratch, working on a ‘real life’ table.

Continue to work with your partner and the script you created in Assignments 1a through 1d. You are adding new code to the pre-existing script that already has code for Assignments 1a through 1d 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), retrieved data from this table (Assignment 1b), updated and deleted data of/from this table (Assignment 1c) and modified the structure of the table (Assignment 1d). Conclude by demonstrating your skills with aggregate/group functions in advanced select statements to retrieve data from the table

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.

I need you to comment the functionality of ALL new statements you code in this assignment.

You can add more records to the table if you require to, using insert statements. If adding column(s) is needed, go ahead and add relevant column(s). You know it all now – alter the structure and update the existing records with ‘new’ column data.

I want to see at least 12 SELECT statements, satisfying the criterion given below. A statement can include one or more than one criteria.

1)     Use of the function MAX or MIN on all records in the table.

2)     Use of the function MAX or MIN on ‘groups’ of records in the table.

3)     Use of the function AVG on all records in the table.

4)     Use of the function AVG on ‘groups’ of records in the table.

5)     Use of the COUNT function on all records in the table.

6)     Use of the COUNT function on ‘groups’ of records in the table.

7)     Use of the SUM function on all records in the table.

8)     Use of the SUM function on ‘groups’ of records in the table.

9)     Use of GROUP BY without the HAVING clause

10)  Use of GROUP BY with the HAVING clause

11)  Use of GROUP BY with the HAVING clause, yes another one

12)  A complete SELECT statement with all clauses- FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT