The music database consists of 9 tables (where keys are underlined): album_count
ID: 3883723 • 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.countryreferences countries.code, album_country.album_id references albums.album_id, album_genre.album_idreferences albums.album_id, album_genre.genre_id references genres.genre_id, album_track.album_idreferences albums.album_id, album_track.song_id references songs.song_id, artist_album.artist_idreferences 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
Below are the SQL Queries for the Part 1,2,3,4 ( sorry but at max can answer only 4 parts in one question):
1. Select max(count(a.album_id)) , a.artist_id from artist_album a
group by a.artist id;
2. Select a.song_id , a.duration from songs a
where Left(a.duration,CharIndex(":",a.duration,1)-1) > 0 OR Mid(a.duration,3,2) > 59
3.Select count(a.album_id) from
(select count(distinct b.artist_id) as count,b.album_id from b.artist_albums group by b.album_id
where count > 2) a
4. Select count(a.album_id) from albums a
where (LEN(a.tittle) - LEN(replace(a.title,' ' , ' ') ) > 1