Practical Exam Question

4.1 Consider the insurance database of Figure 4.12, where the primary keys are underlined.
Construct the following SQL queries for this relational database.
a. Find the total number of people who owned cars that were involved in accidents
in 1989.
b. Find the number of accidents in which the cars belonging to “John Smith”
were involved.
c. Add a new accident to the database; assume any values for required attributes.
d. Delete the Mazda belonging to “John Smith”.
e. Update the damage amount for the car with license number “AABB2000” in
the accident with report number “AR2197” to $3000.
person (driver-id#, name, address)
car (license, model, year)
accident (report-number, date, location)
owns (driver-id#, license)
participated (driver-id, car, report-number, damage-amount)

Ans.
1.
select count (distinct name)
from accident, participated, person
where accident.report-number = participated.report-number
and participated.driver-id = person.driver-id
and date between date ’1989-00-00’ and date ’1989-12-31’
2.
select count (distinct *)
from accident
where exists
(select *
from participated, person
where participated.driver-id = person.driver-id
and person.name = ’John Smith’
and accident.report-number = participated.report-number)
3.
select count (distinct *)
from accident
where exists
(select *
from participated, person
where participated.driver-id = person.driver-id
and person.name = ’John Smith’
and accident.report-number = participated.report-number)
4.
delete car
where model = ’Mazda’ and license in
(select license
from person p, owns o
where p.name = ’John Smith’ and p.driver-id = o.driver-id)
5.
update participated
set damage-amount = 3000
where report-number = “AR2197” and driver-id in
(select driver-id
from owns
where license = “AABB2000”)