SQL*Plus Assignment: Car Accident Database
a) Create the tables and enter at least 5 sets of records in each table created that will help in retrieving data
from your database.
b) Write optimized queries for the following. No duplicates should be printed in any of the answers. Output
should display at least one record set.
1. Find the number of people who owned new cars that were involved in accidents in the year 2005 at
"Mumbai"
2. For every accident that took place in "Kolkata" and on "15-06-2001" retrieve the driver details, and the
amount of damage.
3. For the "Toyota" belonging to "Huffman", find the accidents that took place in "Delhi" but not in "Pune".
4. For every person who owns more than 2 cars, find the details for those cars which incurred a damage of
Rs.50000 and above for accidents that took place in 2005.
-- Creating Tables
CREATE TABLE person (
driver_id NUMBER PRIMARY KEY,
name VARCHAR2(50),
address VARCHAR2(100)
);
CREATE TABLE car (
registration_number VARCHAR2(20) PRIMARY KEY,
model VARCHAR2(30),
year NUMBER
);
CREATE TABLE accident (
report_no NUMBER PRIMARY KEY,
date DATE,
location VARCHAR2(50)
);
CREATE TABLE owns (
driver_id NUMBER,
registration_number VARCHAR2(20),
PRIMARY KEY (driver_id, registration_number),
FOREIGN KEY (driver_id) REFERENCES person(driver_id),
FOREIGN KEY (registration_number) REFERENCES car(registration_number)
);
CREATE TABLE participated_in (
driver_id NUMBER,
registration_number VARCHAR2(20),
report_no NUMBER,
damage_amount NUMBER,
PRIMARY KEY (driver_id, registration_number, report_no),
FOREIGN KEY (driver_id) REFERENCES person(driver_id),
FOREIGN KEY (registration_number) REFERENCES car(registration_number),
FOREIGN KEY (report_no) REFERENCES accident(report_no)
);
-- Insert into person
INSERT INTO person VALUES (1, 'Huffman', 'Delhi');
INSERT INTO person VALUES (2, 'Arjun', 'Mumbai');
INSERT INTO person VALUES (3, 'Neha', 'Kolkata');
INSERT INTO person VALUES (4, 'Raj', 'Delhi');
INSERT INTO person VALUES (5, 'Priya', 'Pune');
-- Insert into car
INSERT INTO car VALUES ('MH01AB1234', 'Toyota', 2005);
INSERT INTO car VALUES ('DL05XY6789', 'Honda', 2004);
INSERT INTO car VALUES ('WB22CD1111', 'Suzuki', 2005);
INSERT INTO car VALUES ('MH20GH4321', 'Toyota', 2005);
INSERT INTO car VALUES ('KA10EF5678', 'Hyundai', 2003);
-- Insert into accident
INSERT INTO accident VALUES (100, TO_DATE('15-06-2001', 'DD-MM-YYYY'), 'Kolkata');
INSERT INTO accident VALUES (101, TO_DATE('20-03-2005', 'DD-MM-YYYY'), 'Mumbai');
INSERT INTO accident VALUES (102, TO_DATE('25-12-2005', 'DD-MM-YYYY'), 'Delhi');
INSERT INTO accident VALUES (103, TO_DATE('10-10-2005', 'DD-MM-YYYY'), 'Pune');
INSERT INTO accident VALUES (104, TO_DATE('15-06-2001', 'DD-MM-YYYY'), 'Kolkata');
-- Insert into owns
INSERT INTO owns VALUES (1, 'MH01AB1234');
INSERT INTO owns VALUES (1, 'MH20GH4321');
INSERT INTO owns VALUES (2, 'DL05XY6789');
INSERT INTO owns VALUES (3, 'WB22CD1111');
INSERT INTO owns VALUES (4, 'KA10EF5678');
INSERT INTO owns VALUES (1, 'WB22CD1111');
-- Insert into participated_in
INSERT INTO participated_in VALUES (1, 'MH01AB1234', 102, 60000);
INSERT INTO participated_in VALUES (2, 'DL05XY6789', 101, 40000);
INSERT INTO participated_in VALUES (3, 'WB22CD1111', 100, 30000);
INSERT INTO participated_in VALUES (1, 'MH20GH4321', 102, 50000);
INSERT INTO participated_in VALUES (4, 'KA10EF5678', 104, 25000);
-- Query 1
SELECT COUNT(DISTINCT p.driver_id) AS number_of_people
FROM person p
JOIN owns o ON p.driver_id = o.driver_id
JOIN car c ON o.registration_number = c.registration_number
JOIN participated_in pi ON pi.registration_number = c.registration_number
JOIN accident a ON pi.report_no = a.report_no
WHERE c.year >= 2005 AND EXTRACT(YEAR FROM a.date) = 2005 AND a.location = 'Mumbai';
-- Query 2
SELECT DISTINCT p.driver_id, p.name, p.address, pi.damage_amount
FROM accident a
JOIN participated_in pi ON a.report_no = pi.report_no
JOIN person p ON pi.driver_id = p.driver_id
WHERE a.location = 'Kolkata' AND a.date = TO_DATE('15-06-2001', 'DD-MM-YYYY');
-- Query 3
SELECT DISTINCT a.report_no, a.date, a.location
FROM person p
JOIN owns o ON p.driver_id = o.driver_id
JOIN car c ON o.registration_number = c.registration_number
JOIN participated_in pi ON pi.registration_number = c.registration_number AND pi.driver_id =
p.driver_id
JOIN accident a ON a.report_no = pi.report_no
WHERE p.name = 'Huffman' AND c.model = 'Toyota' AND a.location = 'Delhi'
AND a.report_no NOT IN (
SELECT report_no FROM accident WHERE location = 'Pune'
);
-- Query 4
SELECT DISTINCT c.registration_number, c.model, c.year, p.driver_id, p.name, pi.damage_amount
FROM person p
JOIN owns o ON p.driver_id = o.driver_id
JOIN car c ON o.registration_number = c.registration_number
JOIN participated_in pi ON pi.registration_number = c.registration_number AND pi.driver_id =
p.driver_id
JOIN accident a ON pi.report_no = a.report_no
WHERE pi.damage_amount >= 50000 AND EXTRACT(YEAR FROM a.date) = 2005
AND p.driver_id IN (
SELECT driver_id FROM owns GROUP BY driver_id HAVING COUNT(*) > 2
);
