DBMS Lab 2
Tables
Owner (driver_id, name, home_town, birth_year)
accident_record (report_number, accident_year, location, driver_id)
compensation_record (report_number, amount)
CREATE TABLE Owner (
driver_id VARCHAR(20) NOT NULL PRIMARY KEY,
name VARCHAR(20),
home_town VARCHAR(20),
birth_year REAL,
CHECK (birth_year < 2008)
);
CREATE TABLE accident_record (
report_number VARCHAR(20) PRIMARY KEY,
accident_year REAL,
location VARCHAR(20),
driver_id VARCHAR(20),
FOREIGN KEY (driver_id) REFERENCES Owner(driver_id)
);
CREATE TABLE compensation_record (
report_number VARCHAR(20),
amount REAL,
FOREIGN KEY (report_number) REFERENCES
accident_record(report_number)
);
Q1) Add a column car_model just next to the column name.
Ans. ALTER TABLE Owner ADD COLUMN car_model VARCHAR(20) AFTER
name;
Insert values into Owner
INSERT INTO Owner (driver_id, name, car_model, home_town, birth_year)
VALUES ('D101','Priyabrata','Mercedes','Tarakeswar',1996);
INSERT INTO Owner (driver_id, name, car_model, home_town, birth_year)
VALUES ('D102','Surajit','Audi','Guptipara',1995);
INSERT INTO Owner (driver_id, name, car_model, home_town, birth_year)
VALUES ('D103','Suman','Maruti800','Tamluk',1999);
INSERT INTO Owner (driver_id, name, car_model, home_town, birth_year)
VALUES ('D104','Kaustav','Mercedes','Guskara',1999);
Q) Display all driver records.
Ans. Select * from Owner;
Output:
Insert values into accident_record
INSERT INTO accident_record (report_number, accident_year, location,
driver_id)
VALUES ('R139',2018,'Dhanbad','D101');
INSERT INTO accident_record (report_number, accident_year, location,
driver_id)
VALUES ('R140',2018,'Jharia','D102');
INSERT INTO accident_record (report_number, accident_year, location,
driver_id)
VALUES ('R141',2019,'Govindpur','D103');
INSERT INTO accident_record (report_number, accident_year, location,
driver_id)
VALUES ('R142',2020,'Koylanagar','D103');
Q) Display all inserted accident records.
Ans. Select * from accident_record;
Output:
Insert values into compensation_record
INSERT INTO compensation_record (report_number, amount)
VALUES ('R139',200000);
INSERT INTO compensation_record (report_number, amount)
VALUES ('R140',250000);
INSERT INTO compensation_record (report_number, amount)
VALUES ('R140',150000);
UPDATE compensation_record SET report_number='R141' WHERE
amount=150000;
INSERT INTO compensation_record (report_number, amount)
VALUES ('R142',350000);
Q) Display all inserted compensation_records.
Ans. Select * from compensation_record;
Questions:
Q1) Find the number of accidents happened in year 2018.
Ans. SELECT COUNT(report_number) AS accidents_2018 FROM
accident_record WHERE accident_year = 2018;
Output:
Q2) Find the sum and average of the compensation amount.
Ans. SELECT SUM(amount) AS total_compensation FROM
compensation_record;
SELECT AVG(amount) AS avg_compensation FROM compensation_record;
Q3) Find the location and year of accident with report number 'R141'.
Ans. SELECT location, accident_year FROM accident_record WHERE
report_number='R141';
Q4) Show accidents before 2020 by creating a Virtual table (View).
Ans. CREATE VIEW 2019_records AS
SELECT
Owner.driver_id,
[Link],
Owner.car_model,
accident_record.report_number,
compensation_record.amount
FROM Owner, accident_record, compensation_record
WHERE Owner.driver_id = accident_record.driver_id
AND accident_record.report_number = compensation_record.report_number
AND accident_record.accident_year < 2020;
SELECT * FROM 2019_records;
Q5) Create a view for compensation amount less than 210000.
Ans. CREATE VIEW damage_records AS
SELECT
Owner.driver_id,
[Link],
accident_record.report_number,
compensation_record.amount
FROM Owner, accident_record, compensation_record
WHERE Owner.driver_id = accident_record.driver_id
AND accident_record.report_number =
compensation_record.report_number
AND compensation_record.amount < 210000;
SELECT * FROM damage_records;
Q6) Update the damage amount (as shown in terminal work)
Ans. UPDATE compensation_record
SET amount = 300000
WHERE report_number IN (
SELECT report_number
FROM accident_record
WHERE driver_id = 'D101'
);
Q7) Find the maximum and minimum damage amounts
Ans. SELECT MAX(amount) AS max_amount FROM compensation_record;
SELECT MIN(amount) AS min_amount FROM compensation_record;