Day23classwork - FROM(tbl_Albums INNER JOIN tbl_Tracks ON...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
Limit the results to only those genres in which there are three or more albums ---- SELECT Count(tbl_Albums.AlbumID) AS NumberAlbums, tbl_Albums.GenreName FROM tbl_Albums GROUP BY tbl_Albums.GenreName HAVING (((Count(tbl_Albums.AlbumID))>=3)) ORDER BY Count(tbl_Albums.AlbumID) DESC , tbl_Albums.GenreName; Now modify the query to limit it to only counting the albums in each genre with more than 12 tracks ---- SELECT Count(tbl_Albums.AlbumID) AS CountOfAlbumID, tbl_Albums.GenreName, tbl_Tracks.TrackID FROM tbl_Albums INNER JOIN tbl_Tracks ON tbl_Albums.AlbumID = tbl_Tracks.AlbumID GROUP BY tbl_Albums.GenreName, tbl_Tracks.TrackID HAVING (((tbl_Tracks.TrackID)>=12)) ORDER BY Count(tbl_Albums.AlbumID) DESC , tbl_Albums.GenreName, tbl_Tracks.TrackID; Start a NEW query in Design View, adding tbl_Albums, tbl_Tracks, and the previous query Select AlbumTitle, TrackTitle and TrackDuration fields Add criteria under TrackDuration using Shortest ---- SELECT tbl_Albums.AlbumTitle, tbl_Tracks.TrackTitle, tbl_Tracks.TrackDuration
Background image of page 1
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: FROM (tbl_Albums INNER JOIN tbl_Tracks ON tbl_Albums.AlbumID = tbl_Tracks.AlbumID) INNER JOIN qry_day23_ShortestTracks_1 ON tbl_Albums.AlbumID = qry_day23_ShortestTracks_1.AlbumID WHERE (((tbl_Tracks.TrackDuration)=[qry_day23_ShortestTracks_1].[Shortest])); List the digital tracks (names) that have filesizes larger than the average filesize in your music database Query 1. Find average filesize of all digital tracks----SELECT Avg(tbl_DigitalTracks.DigitalFilesize) AS AvgOfDigitalFilesize FROM tbl_DigitalTracks; Query 2. Find which digital tracks have filesizes larger than that average (sort largest down)----SELECT tbl_DigitalTracks.DigitalFilename, tbl_DigitalTracks.DigitalFilesize FROM tbl_DigitalTracks, qry_day23_AboveAvFilesize_1 WHERE (((tbl_DigitalTracks.DigitalFilesize)>[qry_day23_AboveAvFilesize_1].[AvgOfDigitalFilesize])) ORDER BY tbl_DigitalTracks.DigitalFilesize DESC;...
View Full Document

{[ snackBarMessage ]}

Ask a homework question - tutors are online