Practice with SQL queries of all types!

Problem 1: Create a database for this schema and populate it. Tables Artist and Album must each contain at least 5 tuples, and each album must consist of at least 5 tracks in Tracklist. Do not use NULL. Good sources for getting data are MusicBrainz, AllMusic and Discogs.

Problem 2: Write the following 15 queries in SQL. (1) List artists who released a live album and a compilation in the same year. (2) List artists who have only released studio albums. (3) List albums which have a higher rating than every previous album by the same band. (4) List live albums released by British artists and having a higher rating than the average rating of all albums released in the same year. (5) List songs shorter than 2 minutes and 34 seconds from albums rated 4 or 5 stars and released in the last 20 years. Include the album title and artist name in the output. (6) Find the average total running time of all albums released in the ’90s and having at least 10 tracks. (Assume no track is missing in the tracklist) (7) List artists who have never released two consecutive studio albums more than 4 years apart. (8) List artists who have released more live and compilation albums (together) than studio ones. (9) Assuming that the last track of every album is always present, list albums without missing tracks and their total running time. (10) Among artists who have released at least 3 studio albums, 2 live albums and one compilation, list those whose every album is rated no less than 3. (11) Find the number of US bands whose debut album was rated 5 stars. (Assume that there is only one such album per artist) (12) For every artist, find the percentage p of their albums rated less than 3. Return artist names with a lower p first and include p in the output as a number between 0 and 100 with 2 decimal digits. (13) List artists who released no fewer studio albums than any other artist from the same country. (14) List pairs of albums released by artists of different countries in the same year and such that one has a higher rating than the other. (15) Sort albums by the ratio (highest first) between their rating and the number of tracks they consist of. (Assume no track is missing in the tracklist)