Question

Assume a database has the relations shown in the appendix. The purpose of the database is to correlate weather and traffic conditions with car repairs.

* Find the average number of breakdowns happened in a rainy day in “Los Angeles”.

* Find names of the owners who owned every car (considering brands and models) that John owned. You may assume John is a unique name.

* Find names of the city(s) that had the highest average congestion rate (i.e., they had the worst traffic.)

* Find the brand(s) that had the lowest average repair cost for cars whose price was over $40,000.

APPENDIX

• owner(owner_id, name, age, city)

• car(car_id, brand, model, year, owner_id, price)

• repair(repair_id, car_id, problem, date, cost, city)

  //note: Problem belongs to a predefined category such as “breakdown”, //”flat_tire”, etc. Date is the date the problem occurred.

• weather(weather_id, temp_high, temp_low, wind_level, weather_type, date, city) //note: Weather_type can be “rain”, “snow”, “cloudy”, etc.

• traffic(traffic_id, congestion_rate, date, city)

Answer 1

If you have any doubts / errors, please give me comment...

-- 1)

SELECT problem, AVG(COUNT(*)) AS avg_no_of_breakdowns

FROM repair

WHERE city = 'Los Angeles'

GROUP BY problem;

-- 2

SELECT O.name

FROM owner O, car C, owner O1, car C1

WHERE O.owner_id = C.owner_id AND O1.owner_id = C1.owner_id AND O1.name = 'John' AND C1.model = O1.model AND C1.brand = O1.brand;

-- 3

SELECT city

FROM traffic

GROUP BY city

HAVING AVG(congestion_rate) >= ALL(

SELECT AVG(congestion_rate)

FROM traffic

GROUP BY city

);

-- 4

SELECT brand

FROM cars C, repair R

WHERE C.car_id = R.car_id AND price > 40000

GROUP BY brand

HAVING AVG(cost) <= ALL(

SELECT AVG(cost)

FROM cars C1, repair R1

WHERE C1.car_id = R1.car_id AND price > 40000

GROUP BY brand

)