Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Posted by James Calby
James Calby
15,202 Points

SQL In layman Terms

I understand that I'm getting the average price of items in each category. But I'm confused as to what the mean in layman term. 

SELECT itemCategoryName, AVG(retailPrice) AS averagePrice
FROM item
JOIN itemCategory ON item.itemCategoryId = itemCategory.itemCategoryId
GROUP BY item.itemCategoryId;

And this Getting the 3 categories with the highest average price of items released after 2015-07-01

SELECT itemCategoryName, AVG(retailPrice) as averagePrice
FROM item
JOIN itemCategory ON item.itemCategoryId = itemCategory.itemCategoryId
WHERE releaseDate > '2015-07-01'
GROUP BY item.itemCategoryId
ORDER BY averagePrice DESC
LIMIT 3;

Any help would be appreciated. Thank you!!

Steven Parker
Steven Parker
231,271 Points
Steven Parker
Steven Parker
231,271 Points

I'm not sure what your asking. Your queries look good, and I would think "average price of items in each category" and "the 3 categories with the highest average price of items released after 2015-07-01" are both "layman's terms" that describe your queries.

Can you rephrase the question?

James Calby
James Calby
15,202 Points

Thank You Steven,

I'm asking what each line of code means. So if you had to comment each line out and explain what the line of code is doing. I can hake the answers together to make it work. But I doing really know what it means. (If you as me to explain what its doing id probably confuse both you and me)

1 Answer

Elad Ohana
Elad Ohana
24,456 Points
Elad Ohana
Elad Ohana
24,456 Points

Here are the line by line comments that I wrote trying to explain this stuff, it's a little verbose, but hopefully readable:

SELECT itemCategoryName, AVG(retailPrice) AS averagePrice -- Queries the itemCategoryName column, and averages the prices from retailPrice (this will be relevant when you use GROUP BY)
FROM item -- pulls data from the item table
JOIN itemCategory ON item.itemCategoryId = itemCategory.itemCategoryId -- Joins the item table and itemCategory table and compares the itemCategoryId columns from both tables.  The item table refers to the itemCategory table in order to pull the data that corresponds to the itemCategoryId column
GROUP BY item.itemCategoryId; -- Groups the rows by the itemCategoryId, ex., all items from the same category will be grouped in one row, this means that you are using the average from items in the same category, and comparing to the average of other categories

Here is the second one:

SELECT itemCategoryName, AVG(retailPrice) as averagePrice -- see above
FROM item -- see above
JOIN itemCategory ON item.itemCategoryId = itemCategory.itemCategoryId -- see above
WHERE releaseDate > '2015-07-01' -- only display results that have a date after July 1, 2015
GROUP BY item.itemCategoryId -- same as above
ORDER BY averagePrice DESC -- sort by descending order of the average price 
LIMIT 3; -- only show the top 3 results

Hope this is helpful.