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)List every car license number that was not involved in any accident with a damage amount of more than $1000.
b)List the drive names that owns the maximum number of cars.

Answer 1

To list every car license number that was not involved in any accident with a damage amount of more than $1000, use a SQL query. To list the driver names that own the maximum number of cars, use another SQL query.

Step-by-step explanation:

To list every car license number that was not involved in any accident with a damage amount of more than $1000, you can use the following SQL query:

SELECT licenseNo FROM Car
WHERE licenseNo NOT IN (
SELECT licenseNo FROM Participated
WHERE damage_amount > 1000
)

To list the driver names that own the maximum number of cars, you can use the following SQL query:

SELECT Name FROM Person
JOIN Owns ON Person.driver_id = Owns.driver_id
GROUP BY Person.driver_id
HAVING COUNT(Owns.licenseNo) = (
SELECT MAX(count_cars) FROM (
SELECT COUNT(Owns.licenseNo) as count_cars FROM Owns
GROUP BY driver_id
) AS subquery
)