Relationships, JOIN, GROUP BY, MIN, MAX, etc...

Relationships_Homework_2.1.V.2.0.sql

SELECT MAX (Invoice.Total), *
FROM Invoice;

SELECT MIN (Invoice.Total), *
FROM Invoice;

SELECT COUNT(Invoice.BillingCity) AS Invoice_num, Invoice.BillingCity
FROM Invoice
GROUP BY Invoice.BillingCity
ORDER BY Invoice_num DESC;

SELECT COUNT(Track.MediaTypeId) AS Sum_Track, MediaType.Name
FROM Track
JOIN MediaType ON Track.MediaTypeId=MediaType.MediaTypeId 
WHERE MediaType.MediaTypeId=2;

SELECT count(Album.ArtistId) AS Sum_Album, Artist.Name
FROM Album
JOIN Artist ON Album.ArtistId=Artist.ArtistId
GROUP BY Artist.Name
ORDER BY Sum_Album DESC;

SELECT Genre.Name, count (Track.GenreId) AS Sum_Track
FROM Genre
JOIN Track ON Genre.GenreId=Track.GenreId
GROUP BY Track.GenreId
ORDER BY Sum_Track DESC;

SELECT Customer.LastName, Customer.FirstName, sum(Invoice.Total)
From Customer
JOIN Invoice ON Customer.CustomerId=Invoice.CustomerId
GROUP BY Invoice.CustomerId
Order BY sum(Invoice.Total) DESC;

SELECT Invoice.InvoiceId, Track.Name
FROM Track
JOIN InvoiceLine ON Track.TrackId=InvoiceLine.TrackId
JOIN Invoice ON InvoiceLine.InvoiceId=Invoice.InvoiceId
ORDER BY Invoice.InvoiceId;