Description
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.
- Use of the function MAX or MIN on all records in the table.
- Use of the function MAX or MIN on ‘groups’ of records in the table.
- Use of the function AVG on all records in the table.
- Use of the function AVG on ‘groups’ of records in the table.
- Use of the COUNT function on all records in the table.
- Use of the COUNT function on ‘groups’ of records in the table.
- Use of the SUM function on all records in the table.
- Use of the SUM function on ‘groups’ of records in the table.
- Use of GROUP BY without the HAVING clause
- Use of GROUP BY with the HAVING clause
- Use of GROUP BY with the HAVING clause, yes another one
- A complete SELECT statement with all clauses- FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT
