Find the nationalities of all users (remove duplicates).
|
CREATE TABLE ARTISTS( artist_id CHAR(30), artist_name CHAR(30), artist_pop INT(5), PRIMARY KEY (artist_id)); |
|
CREATE TABLE TRACKS( track_id CHAR(30), track_name CHAR(30), duration INTL(10), tempo REAL, PRIMARY KEY (track_id)); |
|
CREATE TABLE USERS( user_id CHAR(30), user_name CHAR(30), age INT(5), nationality CHAR(30), num_track_listened INT(10), PRIMARY KEY (user_id)); |
|
CREATE TABLE RECORD( artist_id CHAR(30), track_id CHAR(30), PRIMARY KEY (artist_id, track_id), FOREIGN KEY (artist_id) REFERENCES ARTISTS, FOREIGN KEY (track_id) REFERENCES TRACKS); |
|
CREATE TABLE LISTEN( user_id CHAR(30), track_id CHAR(30), PRIMARY KEY (user_id, track_id), FOREIGN KEY (user_id) REFERENCES USERS, FOREIGN KEY (track_id) REFERENCES TRACKS); |
|
CREATE TABLE FOLLOW( user_id CHAR(30), artist_id CHAR(30), follow_date datetime, PRIMARY KEY (user_id, artist_id) FOREIGN KEY (user_id) REFERENCES USERS, FOREIGN KEY (artist_id) REFERENCES ARTISTS); |
Based on the six tables above, write a single SQL query for each request below:
- Find the nationalities of all users (remove duplicates).
- Find the names and ages of those users whose nationality is United States and has listened to more than 3 different tracks.
- Find the ids of those users who have not only listened to at least one track but also have followed at least one artist.
- Find the ids of those users who have listened to at least one track but have not followed any artists.
- Find all information of those tracks that last no more than 4 minutes (240,000ms), and sort results in terms of duration by descending order.
- Find the ids and names of those artists whose name starts with the letter ‘B’ and whose popularity exceeds 70 (excluding 70).
- Find the name of those artists who have been followed by a user named ‘Alice’.
- Find the ids and names of those tracks that have tempo value higher than 100 (excluding 100) and have been listened to by some users over 70 years old.
- Find the total number of recorded tracks by each artist. For each artist, show the artist id and the total number of recorded tracks.
- Find the total number of listened users for each track, provided that the track has been listened to by at least three users. For each track, display track id, its name, and total number of listened users.
- Find the ids and names of those users who have listened to the most tracks.
- Find the ids and names of those users who have listened to some tracks recorded by the artist named ‘Adele’.
- Find the ids of those users who have listened to some tracks whose tempo is lower than 70 (excluding 70). Use “EXISTS” to answer this query.
- Find the ids and names of those users who have not listened to any track recorded by the artist named ‘Adele’. Use “NOT EXISTS” to answer this query.
- Find the ids of those artist who have recorded tracks together with the artist named ‘Drake’. Drake’s id is “3TVXtAsR1Inumwj472S9r4” that should be included in the result.
- Find those tracks that were recorded by at least two different artists. All attributes of qualified tracks should be returned.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
