Question

Consider an insurance database schema given below.

Person(driver_id, Name, address)
Car(licenseNo, model, year)
Accident(report_no, date, location)
Owns (driver_id, licenseNo)
Participated (driver_id,licenseNo,report_no,damage_amount)
a)Write SQL command that would decrease the damaged amount of each accident involving any car made before the year 2010?
b)Write SQL command that would remove every car that has not been involved in any accident?

Answer 1

The student's SQL queries involve an UPDATE command to decrease damage amounts for pre-2010 cars, and a DELETE command to remove cars not involved in accidents, using JOINs to target the correct data within the tables.

Step-by-step explanation:

To assist a student with their schema-focused SQL queries, it's crucial to write precise commands tailored to the schema and requirements provided.

Decrease Damage Amount for Pre-2010 Cars

To decrease the damaged amount in accidents involving cars made before the year 2010, we'd employ an UPDATE statement with a JOIN to ensure we're targeting the correct rows within the Participated table. (Note: The exact amount to decrease and the specific condition to select these cars are not specified in the question, so we'll assume a generic placeholder like NEW_AMOUNT for the sake of giving an example.)

UPDATE Participated
SET damage_amount = NEW_AMOUNT
FROM Participated
JOIN Car ON Participated.licenseNo = Car.licenseNo
WHERE Car.year < 2010;
Remove Cars Not Involved in Accidents

To remove cars that haven't been involved in any accident, we would use a DELETE command combined with a LEFT JOIN to identify those without any corresponding records in the Participated table:

DELETE FROM Car
WHERE Car.licenseNo NOT IN (
SELECT DISTINCT licenseNo FROM Participated
);