CREATE TABLE Person (
driver_id INT PRIMARY KEY,
name VARCHAR(50),
address VARCHAR(50)
);
INSERT INTO Person VALUES(1, 'John Smith', 'CR C7 101');
INSERT INTO Person VALUES(2, 'Jane Doe', '456 Elm St');
INSERT INTO Person VALUES(3, 'Alice Johnson', '789 Oak St');
INSERT INTO Person VALUES(4, 'Bob Williams', '101 Pine St');
INSERT INTO Person VALUES(5, 'Charlie Brown', '202 Maple St');
___________________________________________________________________________________
_______________
CREATE TABLE Car (
license VARCHAR(20) PRIMARY KEY,
model VARCHAR(50),
year INT
);
INSERT INTO Car VALUES ('ABC123', 'Toyota Camry', 1989);
INSERT INTO Car VALUES ('DEF456', 'Honda Civic', 2005);
INSERT INTO Car VALUES ('GHI789', 'Ford Mustang', 2010);
INSERT INTO Car VALUES ('JKL012', 'Chevrolet Tahoe', 2015);
INSERT INTO Car VALUES ('MNO345', 'Mazda CX-5', 2018);
SELECT * FROM CAR ;
___________________________________________________________________________________
__________
CREATE TABLE Accident (
report_no INT PRIMARY KEY,
date DATE,
location VARCHAR(100)
);
INSERT INTO Accident VALUES (1, '2024-04-10', 'Intersection of Elm St');
INSERT INTO Accident VALUES (2, '2024-04-09', 'Highway 101');
INSERT INTO Accident VALUES (3, '2024-04-08', 'Parking lot of Oak St Mall');
INSERT INTO Accident VALUES (4, '2024-04-07', 'Interstate 95');
INSERT INTO Accident VALUES (5, '2024-04-06', 'Downtown');
select * from accident;
___________________________________________________________________________________
_______
CREATE TABLE Owns (
driver_id INT,
license VARCHAR(20),
FOREIGN KEY (driver_id) REFERENCES Person(driver_id),
FOREIGN KEY (license) REFERENCES Car(license),
PRIMARY KEY (driver_id, license)
);
INSERT INTO Owns VALUES (1, 'ABC123');
INSERT INTO Owns VALUES(2, 'DEF456');
INSERT INTO Owns VALUES(3, 'GHI789');
INSERT INTO Owns VALUES(4, 'JKL012');
INSERT INTO Owns VALUES(5, 'MNO345');
select * from owns ;
_________________________________________________________________________________
CREATE TABLE Participated (
driver_id INT,
report_no INT,
damage_amount DECIMAL(10, 2),
FOREIGN KEY (driver_id) REFERENCES Person(driver_id),
FOREIGN KEY (report_no) REFERENCES Accident(report_no),
PRIMARY KEY (driver_id, report_no)
);
INSERT INTO Participated VALUES (1, 1, 1000.00);
INSERT INTO Participated VALUES (2, 2, 500.00);
INSERT INTO Participated VALUES (3, 3, 2000.00);
INSERT INTO Participated VALUES (4, 4, 1500.00);
INSERT INTO Participated VALUES (5, 5, 800.00);
select * from Participated;
________________________________________________________________________________
Q1.
Find the total number of people who owned cars that were involved in accidents in
1989:
__________________________________________________________________________
Q4 Add a new accident to the database:
INSERT INTO Accident (report_no, date, location) VALUES
(6, '2024-04-11', 'Suburbia');
___________________________________________------------------------
Q 5
Delete the Mazda belonging to John Smith:
DELETE FROM Car
WHERE license IN (SELECT license FROM Owns WHERE driver_id = (SELECT driver_id FROM
Person WHERE name = 'John Smith'));
