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); According to the six table, write a single SQL query for each request below. 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.
|
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); |
According to the six table, write a single SQL query for each request below.
- 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.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
