Advanced SQL Provide ORACLE/ SQL statements for the fellowing diagram Cars ERD Tip: counries.continent = car_makers.country; car_makers.id = model_details.maker; model_details.model = car_names.model; car_names.id = car_details.id 1. Provide a list of car details for the cars that were made by car makers in Germany. Use nested subqueries to minimize the number of joins that you will need. (Use a minimum of 2 nested queries). Display the out year and the model as one field called Model and display the mpg, cylinders, and horsepower as one field, each column separated by - called Specs. Order by year. 39 rows returned BONUS if the MPG is null, display X in its place +3 pts 2. Provide a list of models that have an average MPG for their cars that is less than the overall average for all cars. Display the average MGP for each model, the overall average for all models, and then the difference. Exclude the nulls from your calculations. Order by Model. 14 rows returned 3. Provide a list of the cars that have null MPG. In the list, display the model, description, MPG, and a count of all the cars that dont have a null MPG. 4. Provide a breakdown of the market share of each of the car manufacturers using a percentage. (HINT: total number of cars manufactured by a maker / total number of cars produced * 100) You need to know the total number of cars produced by each maker and the total number of cars produced overall. Order the results by market share in descending order. Display the model name with the first letter capitalized. BONUS modify the query to confirm that the percentages all add up to 100 + 2 pts 5. For each country, list the country id, country name, continent name and the number of cars produced by each country. Order the results by car count in descending order. BONUS: Which country produces the most number of cars? To answer this question consider the following nested aggregate function: +5 If you run the following, it will return a the total number of cars grouped by country. select c.countryid, (count(cn.id)) as carscount from car_names cn, countries c, car_makers cm, model_details md where cn.model = md.model and md.maker = cm.id and cm.country = c.countryid group by c.countryid The following is a nested aggregate function that will return the maximum number of cars provided by a single country. select max(count(c2.countryid)) from car_names cn2, countries c2, car_makers cm2, model_details md2 where cn2.model = md2.model and md2.maker = cm2.id and cm2.country = c2.countryid group by c2.countryid You will need to combine these 2 queries together in some way in order to tell me which country provides the most cars and how many. In order to get full credit, you cant use rownum or limit. The final output will look like this: HINT: you will need to use a subquery in the from clause, a subquery in the where clause, and one of them will be correlated.