Write SQL queries for given Database , Find total number of people who owned cars that involved in accident 2004

Question

Write SQL queries for given Database

 

For the following given database

Person(driver_id#,name,address)
Car(license,model,year)
Accident(reportno,date,location)
Owns(driver_id#,license)

1. Find total number of people who owned cars that involved in accident 2004
2. Find total no of accidents in whichcars belong to accident “HT” involved
3. Update damage amount of car in license no”mum2011”in accident with report
number”AR2011”to Rs.4000.

Answer 1

1. To find the total number of people who owned cars that were involved in accidents in 2004, we can join the tables Owns, Car, and Accident and filter the accidents that happened in 2004. Then, we can count the distinct driver_id# to get the total number of people.

SELECT COUNT(DISTINCT o.driver_id#)
FROM Owns o
JOIN Car c ON o.license = c.license
JOIN Accident a ON c.license = a.license
WHERE YEAR(a.date) = 2004;

2. To find the total number of accidents in which cars belonging to the accident "HT" were involved, we can join the tables Car and Accident and filter the accidents with reportno "HT". Then, we can count the number of accidents.

SELECT COUNT(*)
FROM Car c
JOIN Accident a ON c.license = a.license
WHERE a.reportno = 'HT';

3. To update the damage amount of a car with license no "mum2011" in the accident with report number "AR2011" to Rs. 4000, we need to update the Accident table with the new damage amount for the corresponding accident and car.

UPDATE Accident
SET damage_amount = 4000
WHERE reportno = 'AR2011'
AND license = 'mum2011';

Comments

sample database -


CREATE TABLE Person (
  driver_id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  address VARCHAR(100)
);

CREATE TABLE Car (
  license VARCHAR(20) PRIMARY KEY,
  model VARCHAR(50) NOT NULL,
  year INT
);

CREATE TABLE Accident (
  reportno INT PRIMARY KEY,
  date DATE NOT NULL,
  location VARCHAR(100)
);

CREATE TABLE Owns (
  driver_id INT,
  license VARCHAR(20),
  PRIMARY KEY(driver_id, license),
  FOREIGN KEY(driver_id) REFERENCES Person(driver_id),
  FOREIGN KEY(license) REFERENCES Car(license)
);

INSERT INTO Person VALUES (1, 'John Doe', '123 Main St, Anytown, USA');
INSERT INTO Person VALUES (2, 'Jane Smith', '456 Oak St, Othertown, USA');
INSERT INTO Person VALUES (3, 'Bob Johnson', '789 Elm St, Somewhere, USA');

INSERT INTO Car VALUES ('ABC123', 'Toyota Camry', 2005);
INSERT INTO Car VALUES ('DEF456', 'Ford Mustang', 2010);
INSERT INTO Car VALUES ('GHI789', 'Chevy Malibu', 2007);

INSERT INTO Accident VALUES (1001, '2004-01-01', 'Main St and Elm St');
INSERT INTO Accident VALUES (1002, '2004-02-02', 'Oak St and Maple St');
INSERT INTO Accident VALUES (1003, '2005-03-03', 'Elm St and Pine St');

INSERT INTO Owns VALUES (1, 'ABC123');
INSERT INTO Owns VALUES (1, 'DEF456');
INSERT INTO Owns VALUES (2, 'GHI789');