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 those tracks that were recorded by at least two different artists. All attributes of qualified tracks should be returned.
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 those tracks that were recorded by at least two different artists. All attributes of qualified tracks should be returned.
Step by step
Solved in 2 steps with 3 images