COMP2865 Lab exercise about SQL (10%)
Due date: 23:59, December 9, 2021
Student name: _____________
Student ID: ________________
*******************
This exercise is to create a simplified version of the database for a music recommender
system (as described in the previous exercise) by using SQLite, and then perform some
queries.

The database should contain the following six tables:

Artists (artist_id: char(30), artist_name: char(30), artist_pop: int(5))
Tracks (track_id: char(30), track_name: char(30), duration: int(10), tempo:real)
Record (artist_id: char(30), track_id: char(30))
Users (user_id: char(30), user_name: char(30), age: int(5), nationality: char(30),
num_track_listened: int(10))
Listen (user_id: char(30), track_id: char(30))
Follow (user_id: char(30), artist_id: char(30), follow_date: datetime)

Note: the duration of a track is in milliseconds (ms).

Question 1 Create Tables (20 marks):

Create the above tables in SQLite and insert the records (see all INSERT INTO statements
in the attached file Assignment_5_Lab_data.sql) into the tables.

Question 2 Query Tables (80 marks):

Write a single SQL query for each request below.

1. Find the nationalities of all users (remove duplicates).

2. Find the names and ages of those users whose nationality is United States and has
listened to more than 3 different tracks.

3. Find the ids of those users who have not only listened to at least one track but also
have followed at least one artist.

4. Find the ids of those users who have listened to at least one track but have not
followed any artists.

5. 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.
6. Find the ids and names of those artists whose name starts with the letter ‘B’ and
whose popularity exceeds 70 (excluding 70).

7. Find the name of those artists who have been followed by a user named ‘Alice’.

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

9. Find the total number of recorded tracks by each artist. For each artist, show the
artist id and the total number of recorded tracks.

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

11. Find the ids and names of those users who have listened to the most tracks.

12. Find the ids and names of those users who have listened to some tracks recorded by
the artist named ‘Adele’.

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

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

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

16. Find those tracks that were recorded by at least two different artists. All attributes
of qualified tracks should be returned.

To submit: You should submit both a xxx.sql file and a word/pdf document containing
all your SQL statements and screenshots of query results.