Queries.txt

SQL1:

SELECT t1.Name, COUNT(*) AS "Number of listeners", COUNT(*)*100/59 AS "Percentage %"
FROM(
	SELECT Customer.CustomerId, Genre.Name, COUNT(*)
	FROM Customer
	JOIN Invoice
	ON Invoice.CustomerId = Customer.CustomerId
	JOIN InvoiceLine
	ON InvoiceLine.InvoiceId = Invoice.InvoiceId
	JOIN Track
	ON Track.TrackId = InvoiceLine.TrackId
	JOIN Genre
	ON Genre.GenreId = Track.GenreId

	GROUP BY  Customer.CustomerId, Genre.Name
	ORDER BY Customer.CustomerId) as t1
GROUP BY 1
ORDER BY 2 DESC

------------------------------------------------------------------------------------------------------------------------------------------

SQL2:

SELECT Customer.Country, SUM(InvoiceLine.UnitPrice) AS "Total Spend"
FROM Customer
JOIN Invoice
ON Invoice.CustomerId = Customer.CustomerId
JOIN InvoiceLine
ON InvoiceLine.InvoiceId = Invoice.InvoiceId
JOIN Track
ON Track.TrackId = InvoiceLine.TrackId
JOIN Genre
ON Genre.GenreId = Track.GenreId
WHERE Genre.Name = "Rock"
GROUP BY Customer.Country
ORDER BY 2 DESC

------------------------------------------------------------------------------------------------------------------------------------------

SQL3:

SELECT t1.month, sum(t1.UnitPrice) AS "Total Profit"
FROM(
	SELECT *, strftime("%m",Invoice.InvoiceDate) month
	FROM Invoice
	JOIN InvoiceLine
	ON InvoiceLine.InvoiceId = Invoice.InvoiceId
	JOIN Track
	ON Track.TrackId = InvoiceLine.TrackId
	JOIN Genre
	ON Genre.GenreId = track.GenreId
	WHERE Genre.Name = "Rock") AS t1
GROUP BY 1
ORDER BY 1

------------------------------------------------------------------------------------------------------------------------------------------

SQL4:

SELECT Artist.Name as "Artist/Band Name", SUM(InvoiceLine.UnitPrice) AS "2013 Total Gross "
FROM Invoice
JOIN InvoiceLine
ON InvoiceLine.InvoiceId = Invoice.InvoiceId
JOIN Track
ON track.TrackId = InvoiceLine.TrackId
JOIN Album
ON Album.AlbumId = Track.AlbumId
JOIN Artist
ON Artist.ArtistId = Album.ArtistId
JOIN genre
ON Genre.GenreId = Track.GenreId
WHERE genre.Name = "Rock"
AND Invoice.InvoiceDate > "2012-12-31" 
GROUP BY 1 
ORDER BY 2 DESC