SELECT BillingCountry AS billingCountry,
COUNT(*) AS Invoices
FROM Invoice
GROUP BY BillingCountry
ORDER BY Invoices DESC;SELECT BillingCity AS billingCity,
SUM(Total) AS InvoiceDollars
FROM Invoice
GROUP BY BillingCity
ORDER BY InvoiceDollars DESC;SELECT CustomerId AS customerId,
SUM(Total) AS money_spent
FROM Invoice
GROUP BY CustomerId
ORDER BY money_spent DESC
LIMIT 1;Use your query to return the email, first name, last name, and Genre of all Rock Music listeners. Return your list ordered alphabetically by email address starting with A. Can you find a way to deal with duplicate email addresses so no one receives multiple emails?
SELECT DISTINCT AS distinct
C.Email AS email,
C.FirstName AS firstName,
C.LastName AS lastName,
G.Name AS name
FROM Customer C JOIN Invoice I
ON C.CustomerId = I.CustomerId JOIN InvoiceLine IL
ON I.InvoiceId = IL.InvoiceId JOIN Track T
ON IL.TrackId = T.TrackId JOIN Genre G
ON T.GenreId = G.GenreId
WHERE G.Name = "Rock"
ORDER BY C.Email;Now that we know that our customers love rock music, we can decide which musicians to invite to play at the concert. Let's invite the artists who have written the most rock music in our dataset. Write a query that returns the Artist name and total track count of the top 10 rock bands.
SELECT Artist.ArtistId AS artistId,
Artist.Name AS name,
COUNT(Track.Name) AS Songs
FROM Artist JOIN Album
ON Album.ArtistId = Artist.ArtistId JOIN Track
ON Album.AlbumId = Track.AlbumId JOIN Genre
ON Track.GenreId = Genre.GenreId
WHERE Genre.Name = 'Rock'
GROUP BY Artist.ArtistId,
Artist.Name,
Genre.Name
ORDER BY Songs DESC
LIMIT 10;First, find which artist has earned the most according to the InvoiceLines? Now use this artist to find which customer spent the most on this artist.
SELECT a.Name AS name,
SUM(il.Quantity AS quantity
* AS *
il.UnitPrice) AS AmountSpent,
c.CustomerId AS customerId,
c.FirstName AS firstName,
c.LastName AS lastName
FROM Artist a JOIN Album al
ON a.ArtistId = al.ArtistId JOIN Track t
ON t.AlbumId = al.AlbumId JOIN InvoiceLine il
ON t.TrackId = il.Trackid JOIN Invoice i
ON il.InvoiceId = i.InvoiceId JOIN Customer c
ON c.CustomerId = i.CustomerId
WHERE a.Name = 'Iron Maiden'
GROUP BY c.CustomerId
ORDER BY AmountSpent DESC;We want to find out the most popular music Genre for each country. We determine the most popular genre as the genre with the highest amount of purchases. Write a query that returns each country along with the top Genre. For countries where the maximum number of purchases is shared return all Genres.
WITH t1 AS (
SELECT
COUNT(i.InvoiceId) Purchases, c.Country, g.Name, g.GenreId
FROM Invoice i
JOIN Customer c ON i.CustomerId = c.CustomerId
JOIN InvoiceLine il ON il.Invoiceid = i.InvoiceId
JOIN Track t ON t.TrackId = il.Trackid
JOIN Genre g ON t.GenreId = g.GenreId
GROUP BY c.Country, g.Name
ORDER BY c.Country, Purchases DESC
)
SELECT t1.*
FROM t1
JOIN (
SELECT MAX(Purchases) AS MaxPurchases, Country, Name, GenreId
FROM t1
GROUP BY Country
)t2
ON t1.Country = t2.Country
WHERE t1.Purchases = t2.MaxPurchases;Return all the track names that have a song length longer than the average song length. Though you could perform this with two queries. Imagine you wanted your query to update based on when new data is put in the database. Therefore, you do not want to hard code the average into your query. You only need the Track table to complete this query.
SELECT Name, Milliseconds FROM (
SELECT t.Name, t.Milliseconds, (SELECT AVG(Milliseconds) FROM Track) AS AvgLenght
FROM Track t
WHERE AvgLenght < t.Milliseconds
ORDER BY t.Milliseconds DESC
);Write a query that determines the customer that has spent the most on music for each country. Write a query that returns the country along with the top customer and how much they spent. For countries where the top amount spent is shared, provide all customers who spent this amount.
WITH t1 AS
(
SELECT c.Country, SUM(i.Total) TotalSpent, c.FirstName, c.LastName, c.CustomerId
FROM
Customer c
JOIN Invoice i ON c.CustomerId = i.CustomerId
GROUP BY c.CustomerId
)
SELECT t1.*
FROM t1
JOIN(
SELECT Country, MAX(TotalSpent) AS MaxTotalSpent, FirstName, LastName, CustomerId
FROM t1
GROUP BY Country
)t2
ON t1.Country = t2.Country
WHERE t1.TotalSpent = t2.MaxTotalSpent
ORDER BY Country;
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent