Instructions When writing a query, write the query in a way that it would work over all possible database instances and not just for the given example instance! Consider the following database schema and example instance for a race car database: Good luck c_id Racer A_id Name 1 Sam 2 Suzan 3 Alice gender Birth_year M 1984 1982 F 1980 Manufacturer Hourse..power Mercedes 1000 Ferrari 5000 Mercedes 700 Result result_id c_id r_id A_id Time R1_1 C1R11 400 R1_2 c2R12 600 R1_ 3c3R13 375 Race R_id Type Location R1 F1 UK R2 Rallycross Portugal R3 F1 USA Sponsor R_id CID R1 C1 R1C3 R2 04 Sponsor Motorola Pepsi Michelin Schema: Racer: (A_id, Name, gender, birth_year) Race (r_id, type, location) Result (result_id, c_id, A_id, R_id, time) c_id is a foreign key for car and A_id is foreign key for Racer and R_id is a foreign key for Race Sponsor (R_id, C_ID, Sponsor) Car (c_id, make, horsepower) Q.1 (3 points) Write an SQL statement that create a new table raceLap that stores the race Lap's name, location, streetName, streetNumber and the length. The primary key being the combination of name and location. The length must be positive and not left blank. Q.2 (4 points) Write an SQL statement to add an attribute difficultyLevel to the relation raceLap. Add an attribute name to the relation race and set name, location to be a foreign key in the relation race references the relation raceLap. In case of lap record is removed from the raceLap, this will reflect on the race table and remove. Note values of difficultyLevel are 1,2,3 Q.3 (3 Points) Write the SQL statement that returns the sponsor, the race and the racer name they sponsored. Q.4 (4 Points) Write the SQL statement that returns the male racer 's name, born before 1982 with their average race time. Q.5 (4 Points) Write the SQL statement that returns the racer's name and associated race sponsor that drove a 'Mercedes' car during at least one of their races. Q.6 (5 points). Write an SQL query that returns the manufacturer whose cars won the most races Q.7 (3 points) Write an SQL query that returns the racer's name that only drives Mercedes. Q.8 (4 points) Write an SQL query that returns the racer's name that never participated in a 'rallycross' race Instructions When writing a query, write the query in a way that it would work over all possible database instances and not just for the given example instance! Consider the following database schema and example instance for a race car database: Good luck c_id Racer A_id Name 1 Sam 2 Suzan 3 Alice gender Birth_year M 1984 1982 F 1980 Manufacturer Hourse..power Mercedes 1000 Ferrari 5000 Mercedes 700 Result result_id c_id r_id A_id Time R1_1 C1R11 400 R1_2 c2R12 600 R1_ 3c3R13 375 Race R_id Type Location R1 F1 UK R2 Rallycross Portugal R3 F1 USA Sponsor R_id CID R1 C1 R1C3 R2 04 Sponsor Motorola Pepsi Michelin Schema: Racer: (A_id, Name, gender, birth_year) Race (r_id, type, location) Result (result_id, c_id, A_id, R_id, time) c_id is a foreign key for car and A_id is foreign key for Racer and R_id is a foreign key for Race Sponsor (R_id, C_ID, Sponsor) Car (c_id, make, horsepower) Q.1 (3 points) Write an SQL statement that create a new table raceLap that stores the race Lap's name, location, streetName, streetNumber and the length. The primary key being the combination of name and location. The length must be positive and not left blank. Q.2 (4 points) Write an SQL statement to add an attribute difficultyLevel to the relation raceLap. Add an attribute name to the relation race and set name, location to be a foreign key in the relation race references the relation raceLap. In case of lap record is removed from the raceLap, this will reflect on the race table and remove. Note values of difficultyLevel are 1,2,3 Q.3 (3 Points) Write the SQL statement that returns the sponsor, the race and the racer name they sponsored. Q.4 (4 Points) Write the SQL statement that returns the male racer 's name, born before 1982 with their average race time. Q.5 (4 Points) Write the SQL statement that returns the racer's name and associated race sponsor that drove a 'Mercedes' car during at least one of their races. Q.6 (5 points). Write an SQL query that returns the manufacturer whose cars won the most races Q.7 (3 points) Write an SQL query that returns the racer's name that only drives Mercedes. Q.8 (4 points) Write an SQL query that returns the racer's name that never participated in a 'rallycross' race
