SQL WEEK2 ASSIGNMENT2解析

​​​​​​​https://ucde-rey.s3.amazonaws.com/DSV1015/ChinookDatabaseSchema.pnghttps://ucde-rey.s3.amazonaws.com/DSV1015/ChinookDatabaseSchema.pngER-DIAGRAM如上图

9. Show the number of orders placed by each customer (hint: this is found in the invoices table) and sort the result by the number of orders in descending order.

select total
,invoiceid
,customerid
from invoices 

order by total DESC

解析：题目没看清楚，他要先算number of orders，可是表格里没有直接给你number of orders，需要用count(*)自己算并且列出一个临时的表格，我对count还不熟悉

SELECT CustomerId, COUNT(*) AS Orders
FROM Invoices
GROUP BY CustomerId
ORDER BY Orders DESC

10. Find the albums with 12 or more tracks.

SELECT TRACKID
,NAME
,ALBUMID
FROM TRACKS

解析：同上题，不知道算count，这里还多了一个operation计算

select AlbumId, count(*) as track_numbers
from tracks
Group by AlbumId
having count(*) >=12