Skip to content
doubtly

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

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.

MrRam Asked Updated Student doubt sql queries Save WhatsApp Report

1 answer

Best answer

Answer: The damage amount and the link between a car and an accident are not in the four tables given, so this is the standard insurance schema with a fifth table Participated(driver_id, license, reportno, damage_amount). With it, the queries are below.

Schema used (the # in driver_id# only marks the key; it cannot be part of a column name in SQL):

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

1. Total number of people who owned cars that were involved in accidents in 2004

SELECT COUNT(DISTINCT o.driver_id) AS total_people
FROM Owns o
JOIN Participated p ON o.license = p.license
JOIN Accident a ON p.reportno = a.reportno
WHERE a.date BETWEEN '2004-01-01' AND '2004-12-31';

(In MySQL, WHERE YEAR(a.date) = 2004 also works.) DISTINCT makes sure an owner is counted once even if his car had several accidents.

2. Total number of accidents in which cars belonging to "HT" were involved (HT is taken as the owner's name)

SELECT COUNT(DISTINCT a.reportno) AS total_accidents
FROM Person pr
JOIN Owns o ON pr.driver_id = o.driver_id
JOIN Participated p ON o.license = p.license
JOIN Accident a ON p.reportno = a.reportno
WHERE pr.name = 'HT';

3. Update the damage amount of car mum2011 in accident AR2011 to Rs. 4000

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

Tested on sample data in SQLite (accidents AR2004 and AR2005 in 2004 involving cars owned by three people; HT owns mum2011 and mh04cd, which were in four accidents):

Query 1: total_people = 3
Query 2: total_accidents = 4
Query 3: Participated row (D1, mum2011, AR2011) now has damage_amount = 4000;
         the other row of mum2011 (AR2012, 800) is unchanged
Doubtly · 23 Apr 2023 Report
Was this answer helpful?
  • 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, 'Ja – Doubtly ·
Sign in to comment

Your answer

Log in or create a free account to answer this question.