Query executions
Insurance Company Database
PERSON driver_id:string name:string address:string
CAR regno:string model:string year:int
ACCIDENT rep_no:int accd_date:date location:string
OWNS driver_id:string regno:string
PARTICIPATED driver_id:strin regno:string repno:int damount:int
g
PERSON driver_id:string name:string address:string
CAR regno:string model:string year:int
ACCIDENT rep_no:int accd_date:date location:string
OWNS driver_id:string regno:string
PARTICIPATED driver_id:strin regno:string repno:int damount:int
g
• SQL> create table person
(
driver_id varchar(10),
name varchar(10),
address varchar(10),
primary key(driver_id)
);
• SQL> create table car
(
regno varchar(10),
model varchar(10),
year int,
primary key(regno)
);
• SQL> create table accident
(
report_number int,
accd_date date,
location varchar(10),
primary key(report_number)
);
• SQL> create table owns
( driver_id varchar(10),
regno varchar(10),
primary key(driver_id,regno),
foreign key(driver_id) references person(driver_id),
foreign key(regno) references car(regno)
);
• SQL> create table participated
(
driver_id varchar(10),
regno varchar(10),
report_number int,
damage_amount int,
primary key(driver_id,regno,report_number),
foreign key(driver_id) references person(driver_id),
foreign key(regno) references car(regno),
foreign key(report_number) references accident(report_number)
);
• Find the driver name and the model of the car
which is own by them.
• Find the driver name and the model of the car
which is own by them.
SELECT [Link],[Link]
FROM PERSON P, CAR C, OWNS O
WHERE [Link]=[Link] AND
[Link]=[Link];
• Update the damage amount for the car with
specific regno in the accident with report
number 12 to 25000
• Update the damage amount for the car with
specific regno in the accident with report
number 12 to 25000
UPDATE PARTICIPATED
SET DAMAGE_AMOUNT=25000
WHERE REPORT_NUMBER=12 AND
REGNO='5';
• Find the total number of people who owned
cars that were involved in accidents in the
year 2008.
• Find the total number of people who owned
cars that were involved in accidents in the
year 2008.
SELECT COUNT(DISTINCT O.DRIVER_ID) AS
PEOPLE
FROM OWNS O,PARTICIPATED P,ACCIDENT A
WHERE A.ACCD_DATE LIKE '%08' AND
[Link]=[Link] AND
P.REPORT_NUMBER=A.REPORT_NUMBER;
• Find the number of accidents in which cars
belonging to a specific model were involved.
• Find the number of accidents in which cars
belonging to a specific model were involved.
SELECT COUNT(*) AS TOTALCARS
FROM CAR C,PARTICIPATED P
WHERE [Link]=[Link] AND
[Link]='ALTO'
• Find the driver id and names of the drivers
who stays in either belgaum or gokak
• Find the driver id and names of the drivers
who stays in either belgaum or gokak
SELECT [Link], [Link]
FROM DRIVER
WHERE CITY IN (‘BELGAUM’, ‘GOKAK’);