Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Your first view
How to use views
10. Additional exercise
Summary

Instruction

All right! Let's do one more exercise before we wrap things up.

Exercise

You're given a table with information on the car trips made by publishing house employees:

CREATE TABLE company_car_trip (
  id integer PRIMARY KEY,
  country varchar(64),
  car_size varchar(8),
  distance decimal(6, 2),
  fuel_consumed decimal(5, 2)
);

Your task is to create a view showing the average fuel consumption per each combination of country and car size.

Create a view named car_trip_stats with the following columns:

  1. country
  2. car_size
  3. avg_fuel_per_100 – the average fuel consumed, divided by the distance driven and then multiplied by 100.

Only consider those country-car_size combinations that have at least two trips.

Stuck? Here's a hint!

You can count avg_fuel_per_100 in the following way:

AVG((fuel_consumed / distance) * 100)

Remember to GROUP BY country, car_size and use a HAVING clause.