3.4

Consider the insurance database of Figure 3.17, where the primary keys are underlined. Construct the following SQL queries for this relational database.
a. Find the total number of people who owned cars that were involved in accidents in 2017.
b. Delete all year-2010 cars belonging to the person whose ID is ‘12345’.

  1. Find the total number of people who owned cars that were involved in accidents in 2017.

Note: This is not the same as the total number of accidents in 2017. We must count people with several accidents only once. Furthermore, note that the question asks for owners, and it might be that the owner of the car was not the driver actually involved in the accident.

SELECT COUNT(DISTINCT person.driver_id)
FROM accident, participated, person, owns
WHERE accident.report_number = participated.report_number
      AND owns.driver_id = person.driver_id 
      AND owns.license_plate = participated.license_plate
      AND year = 2017
  1. Delete all year-2010 cars belonging to the person whose ID is ‘12345’.
DELETE FROM car
WHERE year = 2010 AND license_plate IN 
    (SELECT license_plate FROM owns WHERE driver_id = '12345')

Note: The owns, accident and participated records associated with the deleted cars still exist. (If you want the corresponding rows in owns and participated to also be deleted you can use ON CASCADE DELETE on the schema of the tables).