Question

4. Consider the following schema for an online streaming service, where users are allowed to play (stream) songs performed by different artists. Primary and foreign key constraints are also listed for the schema of each table.

User (ID, Password, Name, Location)   Primary key = ID

Artist (ID, Name, Birthyear)   Primary key = ID

Song (ID, Title, Album, ArtistID)   

    Primary key = ID

    Song(ArtistID) references Artist(ID)

Play (ID, UserID, SongID, Timestamp)   

    Primary key = ID

    Play(UserID) references User(ID)

    Play(SongID) references Song(ID)

Express each of the following query in SQL.

(a) Find the unique IDs of users who have played songs performed by the artist named Michael Jackson.

Solution:

(b) Find the unique IDs of users who have played more than one song performed by the artist named Michael Jackson. In other words, your query result must include only those users who have played at least 2 different songs performed by Michael Jackson.

Solution:

(c) Find the title of the most popular song, i.e., the song that received the highest play count (i.e., number of times it was played), and its corresponding play count. Note that a user can play the same song more than once (so the play count should sum up the total number of times each user has played a given song).

Solution:

(d) Find the titles of songs that have never been streamed (played) by any user.

Solution:

Answer 1

(a) Find the unique IDs of users who have played songs performed by the artist named Michael Jackson.

Solution:  SELECT DISTINCT p.UserID FROM Play p, Song s, Artist a WHERE p.SongID = s.SongID AND s.ArtistID = a.ID AND a.Name = 'Michael Jackson';

(b) Find the unique IDs of users who have played more than one song performed by the artist named Michael Jackson. In other words, your query result must include only those users who have played at least 2 different songs performed by Michael Jackson.

Solution: SELECT DISTINCT p.UserID FROM Play p, Song s, Artist a WHERE p.SongID = s.ID AND s.ArtistID = a.ID AND a.Name = 'Michael Jackson' GROUP BY p.UserID HAVING count(*) > 1;

(c) Find the title of the most popular song, i.e., the song that received the highest play count (i.e., number of times it was played), and its corresponding play count. Note that a user can play the same song more than once (so the play count should sum up the total number of times each user has played a given song).

Solution: SELECT s.Title, temp.COUNT FROM Song s, (SELECT SongID, max(sum(*)) AS COUNT FROM Play GROUP BY SongID, UserID) AS temp WHERE temp.SongID = s.ID;

(d) Find the titles of songs that have never been streamed (played) by any user.

Solution: SELECT Title FROM Song WHERE ID NOT IN (SELECT UserID FROM Play);