12|SQL – New Management, New Focus

[Christina_Goodin]

This was my solution

[Spencer_Fleming]

Here is my code, I had a few issues on getting it perfect but I thought this was fun to try and filter the data!

[Ben_Shaw]

This was a great challenge! I struggled to figure out the depreciation but it was some good exercise in SQL. Here’s my solution:

SELECT TOP 10 Manufacturer, ROUND(AVG(((Price_in_thousands)-(year_resale_value))*1000), 2) AS Average_Depreciation
FROM Car_sales
WHERE (((Price_in_thousands)-(year_resale_value))*1000) IS NOT NULL
GROUP BY Manufacturer
HAVING COUNT(ID)>2
ORDER BY ROUND(AVG(((Price_in_thousands)-(year_resale_value))*1000), 2);

[Tate_Telford]

Here is what I got:

SELECT TOP 10 Manufacturer, AVG(((Price_in_thousands)-(year_resale_value))*1000) AS Average_Depreciation
FROM Car_sales
WHERE (((Price_in_thousands)-(year_resale_value))*1000) IS NOT NULL
GROUP BY Manufacturer
HAVING COUNT(ID)>2
ORDER BY AVG(((Price_in_thousands)-(year_resale_value))*1000);

However, my query would not go through. It kept asking for a parameter for year_resale_value, and I could not find my way around that.

[Jacob_Smith]

This is my code:
SELECT TOP 10 Manufacturer, ROUND(AVG(((Price_in_thousands) - (year_resale_value)) * 1000),2) as Average_Depreciation
FROM Car_Sales
WHERE Price_in_thousands IS NOT NULL AND Year_resale_value IS NOT NULL
GROUP BY Manufacturer
HAVING COUNT(Manufacturer) >= 3
ORDER BY ROUND(AVG(((Price_in_thousands) - (year_resale_value)) * 1000),2)

[Daniel_Taylor]

Super fun challenge! It took me some time to figure out how to round to two decimals. I had to research online a bit to figure that out. Additionally, I overthought how to convert the depreciation to regular units. I eventually realized how easy this step was. The TOP 10 feature is really cool and is going to help me a lot in the future. It looks like I got rid of NULL values differently than some people.

[Spencer_Forsberg]

Here is my code for this problem.

SELECT TOP 10 Manufacturer, ROUND(AVG(((Price_in_thousands)-(year_resale_value))*1000), 2) AS Average_Depreciation
FROM Car_sales
WHERE (((Price_in_thousands)-(year_resale_value))*1000) IS NOT NULL
GROUP BY Manufacturer
HAVING COUNT(ID)>2
ORDER BY ROUND(AVG(((Price_in_thousands)-(year_resale_value))*1000), 2);

[Sydney_Livingston]

This helped me figure out simple things–like Access’s default being Ascending.

SELECT TOP 10 Manufacturer, ROUND(AVG(((car.Price_in_thousands) - (car.year_resale_value))* 1000), 2) AS Average_Depreciation
FROM Table1
WHERE (((Price_In_Thousands)-(year_resale_value))1000) IS NOT NULL
GROUP BY Maufacturer
HAVING COUNT(ID)>2
ORDER BY ROUND(AVG(((car.Price_in_thousands) - (car.year_resale_value)) 1000), 2);

[Tessa_Croft]

I haven’t used SQL for awhile so this helped refresh my memory!

SELECT TOP 10 Manufacturer, ROUND(AVG(((Price_in_thousands)-year_resale_value))*1000), 2) AS Average_Depreciation
FROM Car_sales
WHERE (((Price_in_thousands)-(year_resale_value))*1000 IS NOT NULL
GROUP BY Manufacturer
HAVING COUNT(ID)>2
ORDER BY ROUND(AVG(((Price_in_thousands)-(year_resale_value))*1000), 2);

[Masahiro_Hokazono]

Here is what I got. Thank you for this challenge.

SELECT TOP 10 Manufacturer, ROUND(AVG(((Price_in_thousands)-(year_resale_value))*1000), 2) AS Average_Depreciation
FROM Car_sales
WHERE (((Price_in_thousands)-(year_resale_value))*1000) IS NOT NULL
GROUP BY Manufacturer
HAVING COUNT(ID)>2
ORDER BY ROUND(AVG(((Price_in_thousands)-(year_resale_value))*1000), 2);

[Justin_Rydalch]

Here is what I got. Great challenge, always enjoy doing math!

[Gabby_Wilson]

Thank you for the fun challenge!

[Jared_VanMaanen]

10/10 skills

[Derek_McNabb]

Here is my attempt at this challenge! Loved it! Really stretched me.

SELECT TOP 10 Manufacturer, ROUND(AVG(((Price_in_thousands)-(year_resale_value))*1000), 2) as Average_Depreciation
FROM Car_sales
WHERE Price_in_thousands IS NOT NULL AND year_resale_value IS NOT NULL
GROUP BY Manufacturer
HAVING COUNT(ID) > 2
ORDER BY ROUND(AVG((Price_in_thousands) - (year_resale_value)*1000), 2)

[Jared_VanMaanen]

[Evan_Scott]

A ratio is more helpful when comparing entities of different value. I went with an approximate depreciation % to account for different brand values. Porsche may depreciate by the highest dollar amount, but the brand retains the highest value. Hyundai, for example, is top 3 in the solution chart, but doesn’t even make the top 10 in my chart.

SELECT TOP 10 cs.Manufacturer, ROUND(AVG(((cs.Price_in_thousands)-(cs.year_resale_value ))/cs.Price_in_thousands), 2) AS Average_Depreciation
FROM Car_sales AS cs
WHERE cs.Price_in_thousands IS NOT NULL AND cs.year_resale_value IS NOT NULL
GROUP BY cs.Manufacturer
HAVING COUNT(cs.MODEL)>2
ORDER BY ROUND(AVG(((cs.Price_in_thousands)-(cs.year_resale_value ))/cs.Price_in_thousands), 2);

[Ramya_Ramesh]

This is my solution! I do not know what is wrong with my code but I am prompted for the value of the resale year value everytime I run my code but here is my solution photo and code. I had a lot of fun with this problem. It took me some time to figure out how to calculate depreciation but it was very thought provoking and let me be able to be creative to find a solution.

[Ramya_Ramesh]

Good job! I really like how you chose to split up price and year resale value using the WHERE. I think that is what I need to do to help fix the null error that I keep getting. Good work!

[Alex_Beard]

For whatever reason, the double underscore before “year_resale_value” was messing up my query, so I had to take out the double underscore in the column name to get my query to run. Anyone else have that issue?

[Alex_Beard]

I was also being prompted to enter the value for year_resale_value, so I adjusted the column name to have no double underscore at the start in order to get my query to run. However, I know that’s not the ideal solution.