The music database consists of 9 tables (where keys are underlined): album_count
ID: 3883595 • Letter: T
Question
The music database consists of 9 tables (where keys are underlined):
album_country (country, album_id), album_genre (album_id, genre_id), album_track (album_id, position, song_id), albums (album_id, title, released), artist_album (artist_id, album_id), artists (id, name), countries (code, name, continent), genres(genre_id, genre), songs (song_id, title, duration), album_country.country references countries.code, album_country.album_id references albums.album_id, album_genre.album_id references albums.album_id, album_genre.genre_id references genres.genre_id, album_track.album_id references albums.album_id, album_track.song_id references songs.song_id, artist_album.artist_id references artists.artist_id, artist_album.album_id references albums.album_id
In SQL write these queries:
1. Find the highest number of albums released by any artist.
2. Find the songs that have duration greater than 59:59 (i.e., 59 minutes and 59 seconds). Note: for most songs, duration is specified as hours:minutes:seconds. However, there are some exceptions where duration is given as a number of minutes greater than 59. Your solution should consider both cases.
3. Find the number of albums that are collaborations between two or more artists.
4. Find the number of albums that have titles with more than three words.
5. Find the number of artists that have one-word names longer than 5 characters and shorter than 10 characters?
6. Assuming you are born in year x (e.g., 1988) find the albums released in year (x- 1), year (x) and year (x+1). Return the album title, released, and the string ‘before’, ‘same’ or ‘after’ for years (x-1), (x), (x+1), respectively. Sample output: Title-a | 1988 | same; Title-b | 1987 | before; Title-c |1989 | after;
Explanation / Answer
Some of answers
1.select max(count(album_id)) from artist_album
3.select count(*) from albums a where count(album_id)>2
4.select count(*) from albums where (length(title) - length(replace(title, ' ', '')) + 1) > 3
5. select count(*) from artists where (length(name) - length(replace(name, ' ', '')) + 1 =1) and length(name)>5 and length(name)<10
6.