create Database Insurance
use Insurance
CREATE TABLE PERSON (
driverid varchar(10),
fname char(15) not null,
address varchar(30),
primary key (driverid)
)
insert into PERSON values ('111','John Smith' , 'SP Road, Bangalore-12')
insert into PERSON values ('112','Ramesh Babu' , 'KP Nagar, Udupi -13')
insert into PERSON values ('113','Raju SK' , 'KS Circle, Mangalore-12')
insert into PERSON values ('114','Ramesh Babu' , 'AS Road, Bangalore-14')
insert into PERSON values ('115','Alica wallace' , 'SS Road, Karkala-16')
select * from PERSON
CREATE TABLE CAR (
regno varchar(10),
model varchar(10)not null,
cyear int,
primary key(regno)
)
insert into CAR values ('KA-12','FORD' ,1980)
insert into CAR values ('KA-13','SWIFT' ,1990)
insert into CAR values ('MH-11','INDIGO' ,1998)
insert into CAR values ('AP-10','SWIFT' ,1988)
insert into CAR values ('TN-11','FORD' ,2001)
insert into CAR values ('TN-12','TOYATA' ,2001)
insert into CAR values ('MH-14','SWIFT' ,2001)
insert into CAR values ('KL-15','TOYATA' ,2001)
insert into CAR values ('KL-4','INDIGO' ,2001)
insert into CAR values ('AP-05','SANTRO' ,2001)
select * from CAR
CREATE TABLE ACCIDENT (
reportno int ,
accdate datetime,
location varchar(20),
primary key(reportno)
)
insert into ACCIDENT values (1,'1998-07-22' ,'Nitte')
insert into ACCIDENT values (2,'1998-07-22','Karkala')
insert into ACCIDENT values (3,'1998-07-23','Mangalore')
insert into ACCIDENT values (4,'1990-09-09','Bhatkal')
insert into ACCIDENT values (5,'2001-02-22' ,'Udupi')
insert into ACCIDENT values (6,'1990-09-09','Udupi')
select * from ACCIDENT
delete from ACCIDENT
insert into ACCIDENT values (7,'1981-09-09','Karkala')
insert into ACCIDENT values (8,'1990-09-09','Bhatkal')
insert into ACCIDENT values (9,'2001-02-22' ,'Udupi')
insert into ACCIDENT values (10,'1998-02-02','Udupi')
delete from ACCIDENT
CREATE TABLE OWNS (
driverid varchar(10) ,
regno varchar(10)
primary key(driverid,regno)
foreign key(driverid) references PERSON(driverid)on delete cascade on update cascade,
foreign key(regno) references CAR(regno)on delete cascade on update cascade,
unique(regno)
)
drop table OWNS
select * from PERSON
select * from car
insert into OWNS values ('111','KA-13')
insert into OWNS values ('111','KA-12')
insert into OWNS values ('111','MH-11')
insert into OWNS values ('112','AP-10')
insert into OWNS values ('112','TN-11')
insert into OWNS values ('113','TN-12')
insert into OWNS values ('113','KL-15')
insert into OWNS values ('114','AP-05')
insert into OWNS values ('114','KL-4')
insert into OWNS values ('115','TN-12')
insert into OWNS values ('115','MH-14')
select * from OWNS
delete from OWNS
drop table PARTCIPATED
CREATE TABLE PARTCIPATED (
driverid varchar(10) ,
regno varchar(10),
reportno int,
dmgamt int,
primary key(driverid,regno,reportno) ,
foreign key(driverid) references PERSON(driverid)on delete cascade on update cascade,
foreign key(regno) references CAR(regno)on delete cascade on update cascade,
foreign key(reportno) references ACCIDENT(reportno) on delete cascade on update cascade,
foreign key(driverid,regno) references OWNS(driverid,regno),
unique(reportno)
)
drop table PARTCIPATED
select * from accident
insert into PARTCIPATED values ('111','KA-12',1,20000)
insert into PARTCIPATED values ('111','KA-12',2,10000)
insert into PARTCIPATED values ('111','KA-12',3,60000)
insert into PARTCIPATED values ('111','KA-12',4,60000)
insert into PARTCIPATED values ('111','KA-12',5,60000)
delete from PARTCIPATED where reportno=5
insert into PARTCIPATED values ('111','KA-13',6,10000)
insert into PARTCIPATED values ('112','AP-10',7,30000)
insert into PARTCIPATED values ('112','TN-11',8,40000)
insert into PARTCIPATED values ('113','TN-12',9,40000)
insert into PARTCIPATED values ('113','KL-15',10,50000)
insert into PARTCIPATED values ('114','AP-05',4,10000)
insert into PARTCIPATED values ('114','KL-4',6,10000)
insert into PARTCIPATED values ('114','KL-4',3,70000)
insert into PARTCIPATED values ('111','KA-12',2,20000)
insert into PARTCIPATED values ('111','KA-12',5,20000)
drop table PERSON
drop table CAR
drop table ACCIDENT
drop table OWNS
drop table PARTCIPATED
select * from PERSON
select * from CAR
select * from ACCIDENT
select * from OWNS
select * from PARTCIPATED
select distinct(location) from ACCIDENT
1. Find the total number of people who owned cars that were involved in accidents in 1989.
select count (distinct P.driverid)
from accident A, partcipated P
where A.reportno = P.reportno
and A.accdate between '1998-01-01' and '1998-12-31'
select count (distinct P.driverid)
from accident A, partcipated P
where A.reportno = P.reportno
and year(A.accdate) = '1998'
select count (distinct P.driverid)
from partcipated P where P.reportno in
(
select reportno from accident
where reportno = P.reportno and year(accdate) ='1998')
select count (distinct P.driverid)
from partcipated P where P.reportno in
(
select reportno from accident
where year(accdate) ='1998')
2a. Find the number of accidents in which the cars belonging to “John Smith” were involved.
select count (P.reportno) as NO_OF_ACC
from partcipated P, person PN
where P.driverid = PN.driverid
and PN.fname = 'John Smith'
2b. Find the number of accidents in which the cars belonging to specific model were involved.
select count (P.reportno) as NO_OF_ACC
from partcipated P, car C
where P.regno = C.regno
and C.model = 'SWIFT'
3. Add a new accident to the database; assume any values for required attributes.
We assume the driver was “Ramesh Babu,” although it could be someone else.
Also, we assume “Ramesh Babu” owns one Toyota. First we must find the license of
the given car. Then the participated and accident relations must be updated
in order to both record the accident and tie it to the given car. We assume
values “Berkeley” for location, ’2001-09-01’ for date and date, 4007 for reportnumber
and 3000 for damage amount.
insert into accident values (7, '2001-09-01', 'Karkala')
insert into partcipated
select O.driverid, C.regno, 7, 100000
from person P, owns O, car C
where P.fname = 'Ramesh Babu'
and P.driverid = O.driverid
and O.regno = C.regno
and C.model = 'SWIFT'
select * from partcipated
select * from accident
4. Delete the Mazda belonging to “John Smith”.
delete from car
where model = 'INDIGO' and regno in
(select regno
from person P, owns O
where P.fname = 'John Smith' and P.driverid = O.driverid)
5. Update the damage amount for the car with reg number “KA-12” in
the accident with report number “1” to $3000.
update PARTCIPATED set dmgamt = 29000
where reportno = 1 and driverid in
(select driverid
from owns
where regno = 'KA-13')
select * from person
select * from car
select * from accident
select * from PARTCIPATED
select * from OWNS
delete from PARTCIPATED
create view veiw1 as
select distinct(driverid)
from (
select driverid,regno from OWNS as T
where not exists
(
select distinct (location) from accident A
where location not in
(
select location from accident A1,PARTCIPATED P1
where A1.reportno = P1.reportno and P1.driverid = T.driverid
and P1.regno=T.regno
)
)
) as T
select driverid ,fname from person P
where exists
(
select regno from car where not exists
(
select distinct (location) from accident
where location not in
(
select location from accident A,PARTCIPATED PT
where A.reportno = PT.reportno and PT.driverid = P.driverid
)
)
)
select P.driverid , P.regno , count(*) as no_of_accidents from PARTCIPATED P
where P.driverid in ( select driverid from OWNS group by driverid having count(*) >= 2)
and
P.regno in ( select regno from PARTCIPATED where driverid = P.driverid group by regno
having sum(dmgamt) >= all ( select sum(dmgamt) from PARTCIPATED
where driverid = P.driverid group by regno))
group by P.driverid , P.regno
select driverid,count(*) from OWNS group by driverid
select driverid,regno from OWNS where driverid =112
select * from person
No comments:
Post a Comment