Problem 1:
The Acme limo service company needs a simple database to track their cars and drivers.
They also want to track driver assignments. Write the SQL statements to create tables to store driver, car and assignment information.
Assume each driver is assigned only one car. Each car can be assigned to multiple drivers. The tables must capture the following information for each car and driver:
Driver: Name, SSN, DOB
Car: VIN, Make, Model, Color
Be sure to include the appropriate primary and foreign keys
Problem 2:
Write the SQL statement to insert data for the following cars, drivers and their assignments:
|
John Doe
|
123121234
|
11-11-2000
|
|
Jill Doe
|
321434532
|
07-03-1999
|
|
Jane Smith
|
432121234
|
03-08-1983
|
|
Mike Smith
|
321125634
|
04-06-1995
|
|
VIN1234
|
Porsche
|
Cayene
|
Black
|
|
VIN2134
|
Porsche
|
Panamera
|
Red
|
|
VIN2134
|
Hummer
|
H2
|
Black
|
|
VIN5432
|
Mercedes Benz
|
S500
|
Red
|
|
VIN6543
|
Mercedes Benz
|
E350
|
Blue
|
|
VIN7654
|
Audi
|
A8
|
White
|
Driver assignments
John is assigned the Hummer
Jill is assigned the Audi
Mike is assigned the Panamera
Problem 3:
Write the SQL statements (update statements) to change the driver assignments as follows
John is assigned to drive the Audi
Jill is assigned to drive the Hummer