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 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).

New Perspectives on HTML5, CSS3, and JavaScript

6th Edition

ISBN:9781305503922

Author:Patrick M. Carey

Publisher:Patrick M. Carey

Chapter6: Working With Tables And Columns: Creating A Program Schedule For A Radio Station

Section: Chapter Questions

Problem 8CP3

See similar textbooks

Related questions

Question

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 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).

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.