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

Racer

Car

A_id	Name	gender	Birth_year
1	Sam	M	1984
2	Suzan	F	1982
3	Alice	F	1980

c_id	Manufacturer	Hourse_power
C1	Mercedes	1000
C2	Ferrari	5000
C3	Mercedes	700

Result

Race Sponsor

result_id	c_id	r_id	A_id	Time
R1_1	C1	R1	1	400
R1_2	C2	R1	2	600
R1_3	C3	R1	3	375

R_id	Type	Location
R1	F1	UK
R2	RallyCross	Portugal
R3	F1	USA

R_id

C_ID

Sponsor

R1

C1

Motorola

R1

C3

Pepsi

R2

C4

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)

I-1 (3 points) Write an SQL statement that create a new table raceLap that stores the race Laps 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.

I -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

Question I.1 (3 Points) Write the SQL statement that returns the sponsor, the race and the racer name they sponsored.