Question

Consider the automobile insurance company database we use for examples in class. For each problem, write an SQL statement to retrieve the specified data.

1. For each accident that occurred in 2005, the names of drivers involved and the make and year of the auto they were driving.
2. The name and ssn of automobile owners who have never been the driver in any accident.
3. The name and ssn of people involved in accidents while driving a car they do not own. (Remember that cars may have multiple owners.)

Database Schema:
• People (ssn, fname, lname, city, state)

• Agents (ssn, salary, photo)

• Autos (vin, make, year, agent_ssn)

• Owns (ssn, vin)

• Accidents (aid, accident_date, city, state)

• Involvements(aid, vin, damages, driver_ssn)

Answer 1

PLEASE GIVE IT A THUMBS UP, I SERIOUSLY NEED ONE, IF YOU NEED ANY MODIFICATION THEN LET ME KNOW, I WILL DO IT FOR YOU

-- For each accident that occurred in 2005, the names of drivers involved and the make and year of the auto they were driving.

SELECT fname, lname, make, year

FROM People AS P, Involvements AS I, Accidents AC, Autos AS A

WHERE P.ssn = I.driver_ssn AND I.aid = AC.aid AND I.vin = A.vin AND YEAR(accident_date)=2005;



-- The name and ssn of automobile owners who have never been the driver in any accident.

SELECT fname, lname, P.ssn

FROM People AS P, Autos AS A, Owns AS O

WHERE P.ssn = A.ssn AND A.vin = O.vin AND ssn NOT IN(

SELECT driver_ssn

FROM Involvements

);



-- The name and ssn of people involved in accidents while driving a car they do not own. (Remember that cars may have multiple owners.)

SELECT fname, lname, P.ssn

FROM People AS P, Involvements AS I, Owns AS O

WHERE P.ssn = I.driver_ssn AND I.vin = O.vin AND O.ssn <> I.driver_ssn;