NEED HELP WITH SQL URGENT THANKS

A data model has been developed for this situation. The logical model is shown below: CARNIVAL P* CarnDate has TU * CarnName TEAM P * TeamID U * TeamName UF CarnDate * TeamNoMembers F CharName F * EntryID * CarnDirector * CarnLocation Ho made up of 1 has members EVENT 1 PF * CarnDate PF EventTypeCode lead by + 1 has 1 1 + ENTRY P * EntryID EntryStart Time EntryFinishTime CharName F * CompNo F* CarnDate OF * Event Type Code F TeamID 1 1 supported by CHARITY P* CharName CharContact CharPhone supported by HO classifies EVENTTYPE P - Event TypeCode * Event Type Desc 0 EMERCONTACT P * EmerConPhone * EmerConFName EmerConLName contact for HI- | 1 registers for + COMPETITOR P * CompNo CompFName CompLName * CompGender * CompDOB CompEmail CompUniOpt CompPhone * CompECRelation F* EmerConPhone From this logical model the following relational model has been created: TEAM P. TeamID NUMBER (3) U TeamName VARCHAR2 (30) UF CarnDate DATE TeamNoMembers NUMBER (2) F CharName VARCHAR2 (30) F * EntryID NUMBER (5) 5) TEAM_PK (TeamID) TEAM_UN (CarnDate, TeamName) 3 TEAM_CARNIVAL_FK (CarnDate) TEAM CHARITY_FK (CharName) TEAM_ENTRY_FK (EntryID) TEAM_IDX (EntryID) CARNIVAL P - Carn Date DATE U * CarnName VARCHAR2 (50) CarnDirector VARCHAR2 (50) CarnLocation VARCHAR2 (50) CARNIVAL PK (CarnDate) CARNIVAL_UN (CarnName) ENTRY EntryID NUMBER (5) EntryStart Time DATE EntryFinishTime DATE CharName VARCHAR2 (30) CompNo NUMBER (4) F CarnDate DATE Event TypeCode CHAR (3) TeamID NUMBER (3) ENTRY_PK (EntryID) ENTRY_CHARITY_FK (CharName) ENTRY COMPETITOR_FK (CompNo) ENTRY_EVENT_FK (CarnDate, Event TypeCode) ENTRY_TEAM_FK (TeamID) EVENT PF CarnDate DATE PF Event TypeCode CHAR (3) > EVENT_PK (CarnDate, Event TypeCode) EVENT_CARNIVAL_FK (CarnDate) EVENT_EVENTTYPE_FK (Event TypeCode) CHARITY Char Name VARCHAR2 (30) CharContact VARCHAR2 (50) CharPhone CHAR (10) CHARITY_PK (CharName) EVENTTYPE P. Event TypeCode CHAR (3) Event Type Desc VARCHAR2 (50) EVENTTYPE_PK (EventTypeCode) EMERCONTACT EmerConPhone CHAR (10) EmerConFName VARCHAR2 (30) * EmerConLName VARCHAR2 (30) EMERCONTACT_PK (EmerConPhone) COMPETITOR P. CompNo NUMBER (4) CompFName VARCHAR2 (30) ComplName VARCHAR2 (30) CompGender CHAR (1) CompDOB DATE CompEmail VARCHAR2 (50) * CompUniOpt CHAR (1) CompPhone CHAR (10) CompECRelation CHAR (1) F* EmerConPhone CHAR (10) > COMPETITOR_PK (CompNo) 3 COMPETITOR EMERCONTACT_FK (EmerConPhone) (a) List the first name and the last name of the runners who have registered using a Monash University email address (monash.edu) in carnivals organised by Run Monash. The listing must include the date of the carnival(s), the name of the carnival(s), the event description of the event(s) joined for the carnival(s), and the first name and last name of the runners as a single column called fullname. The listing should be displayed in ascending order of the carnival date and runner's full name within a carnival. Your output must have the form shown below. 1 CARNIVAL_DATE CARNNAME EVENTTYPEDESC Fri 01 February 2019 RM Summer Series Caulfield 2019 10 km Run Thu 04 April 2019 RM Easter Series Caulfield 2019 5 km Run Thu 04 April 2019 RM Easter Series Caulfield 2019 10 km Run Thu 04 April 2019 RM Easter Series Caulfield 2019 5 km Run Thu 04 April 2019 RM Easter Series Caulfield 2019 10 km Run Thu 04 April 2019 RM Easter Series Caulfield 2019 5 km Run Thu 04 April 2019 RM Easter Series Caulfield 2019 10 km Run Sun 08 September 2019 RM Spring Series Caulfield 2019 5 km Run FULLNAME Sam Ryan Bob Ryan Dan Chu Jane Ryan Nithin Pal Sam Ryan Srini Vash Jane Ryan 1 [4 marks] (b) List all registered runners who registered to support a charity as an INDIVIDUAL for the '42.2 km Marathon' event. The listing must include the carnival date, Page 7 of 13 the first name and last name of the runner as a single column called runner, the charity name, the charity contact person, and the full description of the supported event in which the runner is running order the listing in ascending order of carnival date, within a carnival order by the charity name and then by the runners' full name within a supported charity. [4 marks] (c) List the number of events all competitors have completed over the previous two calendar years. For example if this report is run in 2021 it should show the events completed for 2019 and 2020. If it is run in 2022, it should show the events completed for 2021 and 2020, etc. To allow this to occur dates must not be hardcoded as actual values eg. 2019. The listing must include the competitors number, the competitors first name, the competitors last name, the competitors gender, how many events they entered two calendar years back, how many events they entered for the previous calendar year, and how many events they entered across these two previous calendar years. If they have entered no events for the two years, display 'Completed No Runs' order the listing to show those who have completed no runs first, and for each of the two groups (those who have completed some runs and those who have completed no runs) by competitor number. Your output must have the form shown below. TWOYRSBACK 0 LASTCALYEAR 0 0 0 0 0 LAST2 CALENDARYEARS Completed No Runs Completed No Runs Completed No Runs Completed No Runs Completed No Runs Completed No Runs 0 COMPNO COMPENAME COMPLNAME COMPGENDER 2 Rob De Costella M 13 William Wang M 16 Fernando Rose M 17 Adrianna Rose F 19 Juan Rose M 20 Lynn Nguyen F 5 Sam Ryan M 3 Brigid Radcliffe F 6 Jane Ryan F 0 0 0 0 0 3 1 4 2 1 3 2 1 3 [5 marks] (d) List the oldest runner's age and youngest runner's age for each carnival held The listing must include the carnival name, the carnival date, the oldest runner's age, the youngest runner's age order the output by the oldest runner's age (oldest first) then by the carnival date. Your output must have the form: Page 8 of 13 CARNNAME CARNIVAL_DATE RM Easter Series Caulfield 2019 04 April 2019 RM Autumn Series Caulfield 2020 06 February 2020 RM Spring Series Caulfield 2019 08 September 2019 RM Summer Series Caulfield 2019 01 February 2019 OLDEST COMPETITOR_AGE 75 years 2 month/s old 75 years 2 month/s old 60 years 2 month/s old 46 years 6 month/s old YOUNGEST_COMPETITOR_AGE 11 years 9 month/s old 11 years 9 month/s old 10 years 11 month/s old 11 years 9 month/s old [5 marks] (e) List the total number of entries/participants in the '5 Km Run" for each carnival held in the year 2019. The listing must include the date of the carnival, the carnival name, and the total number of entries/participants in the carnival for this event. Name the column as total_entries5Km order the listing in descending order of total number of entries, if several carnivals have the same number of entries they should be ordered with the group in ascending carnival date order. Your output must have the form shown below. CARNIVAL_DATE CARNNAME 08-Sep-2019 RM Spring Series Caulfield 2019 04-Apr-2019 RM Easter Series Caulfield 2019 TOTAL_ENTRIES5KM 7 4 [5 marks] (f) For all carnivals which have been run by Run Monash (ie. the carnival has been finished), list those events which have had no entries. The listing must include the date of the carnival, the carnival name, the event description order the output by event description within the carnival date. Your output must have the form shown below. CARNIVAL_DATE CARNNAME EVENTTYPEDESC 01-Feb-2019 RM Summer Series Caulfield 2019 5 km Run 08-Sep-2019 RM Spring Series Caulfield 2019 3 Km Community Run/Walk 06-Feb-2020 RM Autumn Series Caulfield 2020 10 km Run 06-Feb-2020 RM Autumn Series Caulfield 2020 21.1 km Half Marathon 06-Feb-2020 RM Autumn Series Caulfield 2020 5 Km Run [7 marks] (g) For all charities, list the number of times the charity has been nominated (selected for support) by a team, the number of times it has been nominated by an individual and the total number of nominations (team and individual). If no nominations have been made in any of these categories, for a particular charity, the number of nominations must be shown as 0. The listing must include the charity name, Page 9 of 13 the number of times it has been nominated by a team, the number of times it has been nominated by an individual, and the total number of nominations order the output by the total number of nominations (highest first), if the total number is the same then order it by the number of team nominations (highest first) then by the number of individual nominations (highest first). Your output must have the form: CHARNAME TEAM_NOMINATIONS INDIVIDUAL_NOMINATIONS TOTAL_NOMINATIONS Amnesty International 8 Salvation Army 4 4 8 Beyond Blue 1 1 RSPCA 0 0 0 4 4 0 [7 marks] (h) List the team details for each carnival where the most popular team namels (the team name/s used most often across all carnivals) have been used. The listing must include the team name, the date of the carnival, the team leaders competitor number as four digits and the first name and last name of the team leader as a single column called TEAMLEADER, and the number of members in the team order the output by the team name and within the team name by carnival date. Your output must have the form: 10 TEAMNAME CARNIVALDATE TEAMLEADER Happy Feet 04-Apr-2019 0015 Sebastian Coe Happy Feet 06-Feb-2020 0004 Bob Ryan TEAMNOMEMBERS 2 4 [10 marks] (i) List all the runners who ran in the '5 km Run' at the RM carnival held on the 8th September 2019 which were slower than the average run (elapsed) time by runners in the '5 km Run' at the RM carnival held on the 4th April 2019. The listing must include the runner's first name and last name as a single column called fullname, the runners start time, the runners finish time, and the run duration (elapsed time) in hours, minutes and seconds in a single column called 'RUN DURATION (hh:mi:ss)'. The run duration must be shown in the form hh:mi:ss order the output from the slowest runner to the fastest. Your output must have the form: Page 10 of 13 STARTTIME FINISHTIME RUN DURATION (hh:mi:ss) 01:01:58 FULLNAME Annamaria Rose 10:00:00 Ling Shu 09:00:00 Fan Shu 09:00:00 Nan Shu 09:00:00 11:01:58 09:45:36 09:44:23 09:42:48 00:45:36 00:44:23 00:42:48 [13 marks] A data model has been developed for this situation. The logical model is shown below: CARNIVAL P* CarnDate has TU * CarnName TEAM P * TeamID U * TeamName UF CarnDate * TeamNoMembers F CharName F * EntryID * CarnDirector * CarnLocation Ho made up of 1 has members EVENT 1 PF * CarnDate PF EventTypeCode lead by + 1 has 1 1 + ENTRY P * EntryID EntryStart Time EntryFinishTime CharName F * CompNo F* CarnDate OF * Event Type Code F TeamID 1 1 supported by CHARITY P* CharName CharContact CharPhone supported by HO classifies EVENTTYPE P - Event TypeCode * Event Type Desc 0 EMERCONTACT P * EmerConPhone * EmerConFName EmerConLName contact for HI- | 1 registers for + COMPETITOR P * CompNo CompFName CompLName * CompGender * CompDOB CompEmail CompUniOpt CompPhone * CompECRelation F* EmerConPhone From this logical model the following relational model has been created: TEAM P. TeamID NUMBER (3) U TeamName VARCHAR2 (30) UF CarnDate DATE TeamNoMembers NUMBER (2) F CharName VARCHAR2 (30) F * EntryID NUMBER (5) 5) TEAM_PK (TeamID) TEAM_UN (CarnDate, TeamName) 3 TEAM_CARNIVAL_FK (CarnDate) TEAM CHARITY_FK (CharName) TEAM_ENTRY_FK (EntryID) TEAM_IDX (EntryID) CARNIVAL P - Carn Date DATE U * CarnName VARCHAR2 (50) CarnDirector VARCHAR2 (50) CarnLocation VARCHAR2 (50) CARNIVAL PK (CarnDate) CARNIVAL_UN (CarnName) ENTRY EntryID NUMBER (5) EntryStart Time DATE EntryFinishTime DATE CharName VARCHAR2 (30) CompNo NUMBER (4) F CarnDate DATE Event TypeCode CHAR (3) TeamID NUMBER (3) ENTRY_PK (EntryID) ENTRY_CHARITY_FK (CharName) ENTRY COMPETITOR_FK (CompNo) ENTRY_EVENT_FK (CarnDate, Event TypeCode) ENTRY_TEAM_FK (TeamID) EVENT PF CarnDate DATE PF Event TypeCode CHAR (3) > EVENT_PK (CarnDate, Event TypeCode) EVENT_CARNIVAL_FK (CarnDate) EVENT_EVENTTYPE_FK (Event TypeCode) CHARITY Char Name VARCHAR2 (30) CharContact VARCHAR2 (50) CharPhone CHAR (10) CHARITY_PK (CharName) EVENTTYPE P. Event TypeCode CHAR (3) Event Type Desc VARCHAR2 (50) EVENTTYPE_PK (EventTypeCode) EMERCONTACT EmerConPhone CHAR (10) EmerConFName VARCHAR2 (30) * EmerConLName VARCHAR2 (30) EMERCONTACT_PK (EmerConPhone) COMPETITOR P. CompNo NUMBER (4) CompFName VARCHAR2 (30) ComplName VARCHAR2 (30) CompGender CHAR (1) CompDOB DATE CompEmail VARCHAR2 (50) * CompUniOpt CHAR (1) CompPhone CHAR (10) CompECRelation CHAR (1) F* EmerConPhone CHAR (10) > COMPETITOR_PK (CompNo) 3 COMPETITOR EMERCONTACT_FK (EmerConPhone) (a) List the first name and the last name of the runners who have registered using a Monash University email address (monash.edu) in carnivals organised by Run Monash. The listing must include the date of the carnival(s), the name of the carnival(s), the event description of the event(s) joined for the carnival(s), and the first name and last name of the runners as a single column called fullname. The listing should be displayed in ascending order of the carnival date and runner's full name within a carnival. Your output must have the form shown below. 1 CARNIVAL_DATE CARNNAME EVENTTYPEDESC Fri 01 February 2019 RM Summer Series Caulfield 2019 10 km Run Thu 04 April 2019 RM Easter Series Caulfield 2019 5 km Run Thu 04 April 2019 RM Easter Series Caulfield 2019 10 km Run Thu 04 April 2019 RM Easter Series Caulfield 2019 5 km Run Thu 04 April 2019 RM Easter Series Caulfield 2019 10 km Run Thu 04 April 2019 RM Easter Series Caulfield 2019 5 km Run Thu 04 April 2019 RM Easter Series Caulfield 2019 10 km Run Sun 08 September 2019 RM Spring Series Caulfield 2019 5 km Run FULLNAME Sam Ryan Bob Ryan Dan Chu Jane Ryan Nithin Pal Sam Ryan Srini Vash Jane Ryan 1 [4 marks] (b) List all registered runners who registered to support a charity as an INDIVIDUAL for the '42.2 km Marathon' event. The listing must include the carnival date, Page 7 of 13 the first name and last name of the runner as a single column called runner, the charity name, the charity contact person, and the full description of the supported event in which the runner is running order the listing in ascending order of carnival date, within a carnival order by the charity name and then by the runners' full name within a supported charity. [4 marks] (c) List the number of events all competitors have completed over the previous two calendar years. For example if this report is run in 2021 it should show the events completed for 2019 and 2020. If it is run in 2022, it should show the events completed for 2021 and 2020, etc. To allow this to occur dates must not be hardcoded as actual values eg. 2019. The listing must include the competitors number, the competitors first name, the competitors last name, the competitors gender, how many events they entered two calendar years back, how many events they entered for the previous calendar year, and how many events they entered across these two previous calendar years. If they have entered no events for the two years, display 'Completed No Runs' order the listing to show those who have completed no runs first, and for each of the two groups (those who have completed some runs and those who have completed no runs) by competitor number. Your output must have the form shown below. TWOYRSBACK 0 LASTCALYEAR 0 0 0 0 0 LAST2 CALENDARYEARS Completed No Runs Completed No Runs Completed No Runs Completed No Runs Completed No Runs Completed No Runs 0 COMPNO COMPENAME COMPLNAME COMPGENDER 2 Rob De Costella M 13 William Wang M 16 Fernando Rose M 17 Adrianna Rose F 19 Juan Rose M 20 Lynn Nguyen F 5 Sam Ryan M 3 Brigid Radcliffe F 6 Jane Ryan F 0 0 0 0 0 3 1 4 2 1 3 2 1 3 [5 marks] (d) List the oldest runner's age and youngest runner's age for each carnival held The listing must include the carnival name, the carnival date, the oldest runner's age, the youngest runner's age order the output by the oldest runner's age (oldest first) then by the carnival date. Your output must have the form: Page 8 of 13 CARNNAME CARNIVAL_DATE RM Easter Series Caulfield 2019 04 April 2019 RM Autumn Series Caulfield 2020 06 February 2020 RM Spring Series Caulfield 2019 08 September 2019 RM Summer Series Caulfield 2019 01 February 2019 OLDEST COMPETITOR_AGE 75 years 2 month/s old 75 years 2 month/s old 60 years 2 month/s old 46 years 6 month/s old YOUNGEST_COMPETITOR_AGE 11 years 9 month/s old 11 years 9 month/s old 10 years 11 month/s old 11 years 9 month/s old [5 marks] (e) List the total number of entries/participants in the '5 Km Run" for each carnival held in the year 2019. The listing must include the date of the carnival, the carnival name, and the total number of entries/participants in the carnival for this event. Name the column as total_entries5Km order the listing in descending order of total number of entries, if several carnivals have the same number of entries they should be ordered with the group in ascending carnival date order. Your output must have the form shown below. CARNIVAL_DATE CARNNAME 08-Sep-2019 RM Spring Series Caulfield 2019 04-Apr-2019 RM Easter Series Caulfield 2019 TOTAL_ENTRIES5KM 7 4 [5 marks] (f) For all carnivals which have been run by Run Monash (ie. the carnival has been finished), list those events which have had no entries. The listing must include the date of the carnival, the carnival name, the event description order the output by event description within the carnival date. Your output must have the form shown below. CARNIVAL_DATE CARNNAME EVENTTYPEDESC 01-Feb-2019 RM Summer Series Caulfield 2019 5 km Run 08-Sep-2019 RM Spring Series Caulfield 2019 3 Km Community Run/Walk 06-Feb-2020 RM Autumn Series Caulfield 2020 10 km Run 06-Feb-2020 RM Autumn Series Caulfield 2020 21.1 km Half Marathon 06-Feb-2020 RM Autumn Series Caulfield 2020 5 Km Run [7 marks] (g) For all charities, list the number of times the charity has been nominated (selected for support) by a team, the number of times it has been nominated by an individual and the total number of nominations (team and individual). If no nominations have been made in any of these categories, for a particular charity, the number of nominations must be shown as 0. The listing must include the charity name, Page 9 of 13 the number of times it has been nominated by a team, the number of times it has been nominated by an individual, and the total number of nominations order the output by the total number of nominations (highest first), if the total number is the same then order it by the number of team nominations (highest first) then by the number of individual nominations (highest first). Your output must have the form: CHARNAME TEAM_NOMINATIONS INDIVIDUAL_NOMINATIONS TOTAL_NOMINATIONS Amnesty International 8 Salvation Army 4 4 8 Beyond Blue 1 1 RSPCA 0 0 0 4 4 0 [7 marks] (h) List the team details for each carnival where the most popular team namels (the team name/s used most often across all carnivals) have been used. The listing must include the team name, the date of the carnival, the team leaders competitor number as four digits and the first name and last name of the team leader as a single column called TEAMLEADER, and the number of members in the team order the output by the team name and within the team name by carnival date. Your output must have the form: 10 TEAMNAME CARNIVALDATE TEAMLEADER Happy Feet 04-Apr-2019 0015 Sebastian Coe Happy Feet 06-Feb-2020 0004 Bob Ryan TEAMNOMEMBERS 2 4 [10 marks] (i) List all the runners who ran in the '5 km Run' at the RM carnival held on the 8th September 2019 which were slower than the average run (elapsed) time by runners in the '5 km Run' at the RM carnival held on the 4th April 2019. The listing must include the runner's first name and last name as a single column called fullname, the runners start time, the runners finish time, and the run duration (elapsed time) in hours, minutes and seconds in a single column called 'RUN DURATION (hh:mi:ss)'. The run duration must be shown in the form hh:mi:ss order the output from the slowest runner to the fastest. Your output must have the form: Page 10 of 13 STARTTIME FINISHTIME RUN DURATION (hh:mi:ss) 01:01:58 FULLNAME Annamaria Rose 10:00:00 Ling Shu 09:00:00 Fan Shu 09:00:00 Nan Shu 09:00:00 11:01:58 09:45:36 09:44:23 09:42:48 00:45:36 00:44:23 00:42:48 [13 marks]