Insurance report.sql

use mysql;
show databases;
create database omkrrish;
show databases;
use omkrrish;
create table PERSON(
driver_id varchar(10),
name varchar(20), 
address varchar(30),
primary key(driver_id)
);
show tables;
desc person;
create table CAR(
reg_num varchar(10),
model varchar(10),
year int(4),
primary key(reg_num)
);
desc CAR;
create table ACCIDENT(
report_num int(10),
accident_date date,
location varchar(50),
primary key(report_num)
);
desc Accident;
create table OWNS(
driver_id varchar(10),
reg_num varchar(10),
report_num int,
damage_amount int,
primary key(driver_id,reg_num,report_num),
foreign key(driver_id) references person(driver_id),
foreign key(reg_num) references car(reg_num),
foreign key(report_num) references accident(report_num)
);
desc OWNS;
alter table OWNS rename to PARTICIPATED;
desc PARTICIPATED;
create table OWNS(
driver_id varchar(10),
reg_num varchar(10),
primary key(driver_id,reg_num),
foreign key(driver_id) references person(driver_id),
foreign key(reg_num) references car(reg_num)
);
desc OWNS;

INSERT INTO PERSON  VALUES
('A01', 'Richard', 'Srinivas nagar'),
('A02', 'Pradeep', 'Rajaji nagar'),
('A03', 'Smith', 'Ashok nagar'),
('A04', 'Venu', 'N R Colony'),
('A05', 'Jhon', 'Hanumanth nagar');


INSERT INTO CAR  VALUES
('KA052250', 'Indica', 1990),
('KA031181', 'Lancer', 1957),
('KA095477', 'Toyota', 1998),
('KA053408', 'Honda', 2008),
('KA041702', 'Audi', 2005);


INSERT INTO OWNS VALUES
('A01', 'KA052250'),
('A02', 'KA053408'),
('A03', 'KA031181'),
('A04', 'KA095477'),
('A05', 'KA041702');
select * from person;
select * from car;
select * from owns;
insert into accident values
(11,'2003-01-01','Mysore Road'),
(12,'2004-02-02','South end Circle'),
(13,'2003-01-21','Bull temple Road'),
(14,'2008-02-17','Mysore Road'),
(15,'2005-03-04','Kanakpura Road');
select * from accident;
INSERT INTO PARTICIPATED VALUES
('A01', 'KA052250', 11, 10000),
('A02', 'KA053408', 12, 50000),
('A03', 'KA095477', 13, 25000),
('A04', 'KA031181', 14, 3000),
('A05', 'KA041702', 15, 5000);
desc participated;
select * from participated ;
update participated set damage_amount=25000 where reg_num='KA053408' and report_num=12;
select * from participated ;
insert into accident values(16,'2008-03-15','Domlur');
select * from accident;
select accident_date,location from accident;
select driver_id from participated where damage_amount>=25000;
select * from participated order by damage_amount desc;
select avg(damage_amount) from participated;
delete from participated where damage_amount<(select avg(damage_amount) from participated);

/* i. Display CAR table in ascending order of manufacturing year */
SELECT * 
FROM CAR
ORDER BY year ASC;

/* ii. Number of accidents involving cars of model 'Lancer' */
SELECT COUNT(*) AS accident_count
FROM PARTICIPATED p
JOIN CAR c ON p.reg_num = c.reg_num
WHERE c.model = 'Lancer';

/* iii. Total number of people whose cars were involved in accidents in 2008 */
SELECT COUNT(DISTINCT p.driver_id) AS total_people
FROM PARTICIPATED p
JOIN ACCIDENT a ON p.report_num = a.report_num
WHERE YEAR(a.accident_date) = 2008;

/* iv. List PARTICIPATED relation in descending order of damage amount */
SELECT *
FROM PARTICIPATED
ORDER BY damage_amount DESC;

/* v. Find average damage amount */
SELECT AVG(damage_amount) AS avg_damage
FROM PARTICIPATED;

/* vi. Delete tuples whose damage amount is below the average */
DELETE FROM PARTICIPATED
WHERE damage_amount < (
    SELECT AVG(damage_amount) FROM PARTICIPATED
);

/* vii. Names of drivers whose damage is greater than average */
SELECT DISTINCT pe.name
FROM PERSON pe
JOIN PARTICIPATED p ON pe.driver_id = p.driver_id
WHERE p.damage_amount > (
    SELECT AVG(damage_amount) FROM PARTICIPATED
);

/* viii. Find maximum damage amount */
SELECT MAX(damage_amount) AS max_damage
FROM PARTICIPATED;