Answer to Question #86259 in Databases | SQL | Oracle | MS Access for Zee Kay

Question

3. Find audio tracks which have a length longer than the average length of all
the audio tracks;
4. Which playlists do not contain any tracks for the artists “Black Sabbath" nor
“Chico Buarque"?

~~ Tracks is a table
~~ Playlist is a table

Answer 1

Problem.

3. Find audio tracks which have a length longer than the average length of all

the audio tracks;

4. Which playlists do not contain any tracks for the artists “Black Sabbath" nor

“Chico Buarque"?

Solution:

3.

SELECT * FROM Track
WHERE Milliseconds > 
           (SELECT AVG(Milliseconds) FROM Track);

4.

SELECT * FROM Playlist
WHERE PlaylistId NOT IN  
           (SELECT DISTINCT PlaylistId FROM PlaylistTrack, Track, Album, Artist
           WHERE PlaylistTrack.TrackId = Track.TrackId 
           AND Track.AlbumId = Album.AlbumId 
           AND Album.ArtistId = Artist.ArtistId
           AND Artist.Name = 'Black Sabbath' OR Artist.Name = 'Chico Buarque');