assignment.sql

-- List all tracks that are longer than the most purchased track and their lengths (in seconds;
-- 1 second = 1000 ms), ordered from shortest to longest. If there is a tie for most purchased
-- track, use the longest (in milliseconds) most purchased track. Your query should list the
-- output columns as TrackName and Seconds in that order. (query34.sql)
SELECT
	t.Name AS TrackName,
	(t.Milliseconds / 1000) AS Seconds
FROM
	Track AS t
WHERE
	Milliseconds > (
		SELECT
			Milliseconds
		FROM
			Invoice
		NATURAL JOIN InvoiceLine
		LEFT JOIN Track ON Track.TrackId = InvoiceLine.TrackId
	GROUP BY
		InvoiceLine.TrackId
	ORDER BY
		COUNT(*)
		DESC,
		Milliseconds DESC
	LIMIT 0,
	1)
ORDER BY
	Milliseconds;

-- - List the average length of the albums sung by the artist who sang the album with the longest
-- total length (e.g., if there are three songs on that album with millisecond lengths of 100, 250,
-- and 150, the length of the album would be 500). In addition to the average length of the
-- albums (this can be left in milliseconds), also include the name of the artist who sang those
-- albums and the number of albums they sang. Your query should list the output columns as
-- ArtistName, NumAlbums and AvgAlbumLength in that order. (query35.sql)
SELECT
	Artist. "Name" AS ArtistName,
	COUNT(DISTINCT Album.AlbumId) AS NumAlbums,
	AVG(Track.Milliseconds) / 1000 AS AvgAlbumLength
FROM
	Album
	LEFT JOIN Artist ON Artist.ArtistId = Album.ArtistId
	LEFT JOIN Track ON Album.AlbumId = Track.AlbumId
WHERE
	Album.ArtistId = (
		SELECT
			Album.ArtistId
		FROM
			Album
		LEFT JOIN Track ON Album.AlbumId = Track.AlbumId
		LEFT JOIN Artist ON Artist.ArtistId = Album.ArtistId
	GROUP BY
		Album.AlbumId
	ORDER BY
		SUM(Milliseconds) / 1000 DESC
	LIMIT 0,
	1);