Music Database in SQL

Music Database 

Refer the ER diagram for Music Database. 

The music collection consists of albums. 

•     An album is made by one artist.
•     An artist makes one or more albums.
•     An album contains one or more tracks.
•     Each track is on exactly one album.
•    Artists, albums, and tracks each have a name.

Write a query to display the names of artists who have composed atleast 3 albums. Display the records sorted in order based on artist name. 
Hints: 
Tables used : artist,album 
Statements and clause used : group by,having,order by 
Function used : count 
Joins used : inner join 
Joins using field : artist_id 
select artist.artist_name from artist inner join album on artist.id=album.artist_id group by artist.artist_name having count(album.id)>=3 order by artist.artist_name; select artist.artist_name from artist inner join album on artist.id=album.artist_id group by artist.artist_name having count(album.id)>=3 order by artist.artist_name;

Write a query to find the name of the artist who has composed the second maximum number of albums. 
Hints: 
Tables used : artist,album 
Statements and clause used : group by, having, order by 
Function used : count, max 
Joins used : inner join 
Joins using field : artist_id

 select a.artist_name from artist a inner join album al on a.id=al.artist_id 
group by a.id having count(a.id)= (select max(c) 
from (select count(a.id) c from artist a inner join album al on a.id=al.artist_id 
group by a.id) s1 where c <  (select max(c) from (select count(a.id) c 
from artist a inner join album al on a.id=al.artist_id group by a.id) s)) 
order by a.artist_name; 

Write a query to display the name of the artist and the artist age category. Give an alias to the age category as age_category. If the age of the artist is less than 30, the age category is "Young". If the age of the artist is between 30 and 50 (both inclusive), the age category is "Medium". If the age of the artist is greater than 50, the age category is "Old". Display the records sorted in order based on the artist name. 
Hints: 
Tables used : artist 
Statements and clause used : case, when, then, end, order by 

select artist_name, case when age < <30 age="" then="" when="" young="">30 and then 'Young' when age > =30 and age < =<50 age="" medium="" then="" when="">50 then 'Medium' when age > 50 then  'old' end as age_category from artist order by artist_name;

Write a query to display the name of the artist and the artist expertise category. Give an alias to the expertise category as expertise_category. If the number of albums composed by an artist is 0, then expertise category is "YET TO START". If the number of albums composed by an artist is between 1 and 3 (both inclusive), then expertise category is "NEWBIE". If the number of albums composed by an artist is between 4 and 6 (both inclusive), then expertise category is "STAR". If the number of albums composed by an artist is between 7 and 10 (both inclusive) , then expertise category is "SUPERSTAR". If the number of albums composed by an artist is greater than 10 , then expertise category is "MAESTRO". Display the records sorted in order based on artist name. 
Hints: 
Tables used : artist,album 
Statements and clause used : if, group by, order by 
Joins used : left join 
Joins using field : artist_id 

Write a query to display the names of the albums composed by the artist who has composed the maximum number of albums. Assume that there is only one artist who has composed the maximum number of albums. Display the records sorted in ascending order based on the album name. 
Hints: 
Tables used : artist,album 
Statements and clause used : group by, having, order by 
Function used : count, max 
Joins used : inner join 
Joins using field : artist_id