create table PERSON(
Driverid varchar(5) not null primary key,
Name varchar(20),
Address varchar(20)
);


insert into PERSON values('d1','John Smith','New York');
insert into PERSON values('d2','Peter Parker','Washington');
insert into PERSON values('d3','Aaron','New Delhi');
insert into PERSON values('d4','Tony Stark','USA');
insert into PERSON values('d5','Chris','New York');


create table CAR(
Regno varchar(10) not null primary key,
Model varchar(20),
Cyear int
);


insert into CAR values('KA-12','Toyota',1989);
insert into CAR values('KA-13','Honda',1989);
insert into CAR values('KA-14','Maruthi',1989);
insert into CAR values('KA-15','Toyota',2004);
insert into CAR values('KA-16','Tesla',1990);

create table ACCIDENT(
Reportno int not null primary key,
Accdate date,
Location varchar(20)
);

insert into ACCIDENT values(1001,'2021-02-12','Washington');
insert into ACCIDENT values(1002,'2018-06-05','New York');
insert into ACCIDENT values(1003,'2011-02-07','London');
insert into ACCIDENT values(1004,'2004-05-04','Canada');
insert into ACCIDENT values(1005,'1999-04-14','New Delhi');

select * from ACCIDENT;
select * from PERSON;

create table OWNS(
Driverid varchar(5) not null,
Regno varchar(10) not null,
foreign key(Driverid) references PERSON(Driverid),
foreign key(Regno) references CAR(Regno),
primary key(Driverid,Regno)
);

insert into OWNS values('d1','KA-15');
insert into OWNS values('d2','KA-12');
insert into OWNS values('d3','KA-14');
insert into OWNS values('d4','KA-16');
insert into OWNS values('d5','KA-13');


create table PARTICIPATED(
Driverid varchar(5) not null,
Regno varchar(10) not null,
Reportno int not null,
Damage int,
foreign key(Driverid) references PERSON(Driverid),
foreign key(Regno) references CAR(Regno),
foreign key(Reportno) references ACCIDENT(Reportno),
primary key(Driverid,Regno,Reportno)
);


insert into PARTICIPATED values('d1','KA-15',1005,100000);
insert into PARTICIPATED values('d2','KA-12',1004,150000);
insert into PARTICIPATED values('d3','KA-14',1002,200000);
insert into PARTICIPATED values('d4','KA-16',1001,10000);
insert into PARTICIPATED values('d5','KA-13',1003,80500);

select count(distinct Driverid)
from PARTICIPATED
where Reportno
in
(
  select Reportno
  from ACCIDENT
  where YEAR(Accdate) = '1999');
  
  select count(Reportno)
  from PERSON P, PARTICIPATED P1
  where P.Driverid = P1.Driverid and
  P.Name = 'John Smith';

update PARTICIPATED
set Damage = 3000
where Reportno = '1004'
and Regno = 'KA-12'

select * from PARTICIPATED;

select * from OWNS;
select * from PARTICIPATED;