Please help write SQL queries for the following questions:
-
List all participant ID and last names of participants who took a course that allows a capacity of 5. Use a series of nested IN + subquery clauses
-
List all participants who did not enroll in a session of the course 'Brakes'. Use a type of Set difference operation (e.g., MINUS, NOT IN, NOT EXISTS)
-
List all bikes with an above average volunteer time required.
-
List all participants who received a giveaway bike before (not including) August 1st, 2018. Use a join.
-
List the participants first and last name of all participants who enrolled in the course with the name 'Brakes' (do not provide a value for course number, use course name). Use joins.
-
List the participant ID, last name, and giveaway date for all participants. If the participant has not received a giveaway bike, there should a null for giveaway_date.
has P. ses id a1enrollment PF* part id NUMERIC (6) PF ses id NUMERIC (6) enroll attending CHAR (1) H P belongs to afcoursesession NUMERIC (6) * ses date Date (7) ses time VARCHAR (10) ses roomnumber VARCHAR (5) ses enrollmentnumber NUMERIC (3) course number VARCHAR (5) offered through - - based on - -IH P a course course number VARCHAR (5) course name VARCHAR (50) * course description VARCHAR (650) course capacity NUMERIC (3) enrolls in is for volunteers and captures times in It a participant part fname VARCHAR (100) * part Iname VARCHAR (100) part address VARCHAR (200) part city VARCHAR (100) * part zipcode NUMERIC (5) * part phone VARCHAR (20) U* part email VARCHAR (50) part dateofbirth Date (7) P . partid NUMERIC (6) a volunteeringtime P. voltime date Date (7) HP * voltime arrivaltime Date (7) voltime leavetime Date (7) voltime durationinminutes NUMERIC (3) PF* part id NUMERIC (6) records volunteering time from Igives bike to gets bike through angiveaway P* giveaway id NUMERIC (6) giveaway date Date (7) F + partid NUMERIC (6) F* bike serialnumber NUMERIC (6) a1bike P.bike serialnumber is given away through * bike name bike brand - - - - -- ---IH bike condition gives away bike sizeininches bike volunteertimeinhrreg bike comment btype id NUMERIC (6) VARCHAR (40) VARCHAR (50) VARCHAR (20) NUMERIC (2) NUMERIC (4) VARCHAR (350) NUMERIC (4) categorizes albiketype Dt -------THP .btype id Dt --- NUMERIC (4) I categorized in "btype name VARCHAR (20) has P. ses id a1enrollment PF* part id NUMERIC (6) PF ses id NUMERIC (6) enroll attending CHAR (1) H P belongs to afcoursesession NUMERIC (6) * ses date Date (7) ses time VARCHAR (10) ses roomnumber VARCHAR (5) ses enrollmentnumber NUMERIC (3) course number VARCHAR (5) offered through - - based on - -IH P a course course number VARCHAR (5) course name VARCHAR (50) * course description VARCHAR (650) course capacity NUMERIC (3) enrolls in is for volunteers and captures times in It a participant part fname VARCHAR (100) * part Iname VARCHAR (100) part address VARCHAR (200) part city VARCHAR (100) * part zipcode NUMERIC (5) * part phone VARCHAR (20) U* part email VARCHAR (50) part dateofbirth Date (7) P . partid NUMERIC (6) a volunteeringtime P. voltime date Date (7) HP * voltime arrivaltime Date (7) voltime leavetime Date (7) voltime durationinminutes NUMERIC (3) PF* part id NUMERIC (6) records volunteering time from Igives bike to gets bike through angiveaway P* giveaway id NUMERIC (6) giveaway date Date (7) F + partid NUMERIC (6) F* bike serialnumber NUMERIC (6) a1bike P.bike serialnumber is given away through * bike name bike brand - - - - -- ---IH bike condition gives away bike sizeininches bike volunteertimeinhrreg bike comment btype id NUMERIC (6) VARCHAR (40) VARCHAR (50) VARCHAR (20) NUMERIC (2) NUMERIC (4) VARCHAR (350) NUMERIC (4) categorizes albiketype Dt -------THP .btype id Dt --- NUMERIC (4) I categorized in "btype name VARCHAR (20)
