DBMS-Slip9

Question 

Consider the following Entities and Relationships                                   
Musician (mno, mname, addr, phno)
Album (title, copy_right_date, format)
Relation between Musicians and Album is One to Many.
Constraint: Primary key.

Solution:-

Create a Database in 3NF & write queries for following.

•Display all albums composed by ‘A R Rehman’.

SQL> select title from musician,album where musician.mno=album.mnno and   mname='A R Rehman';

•Display musician details who have composed Audio album.

SQL> select * from musician ,album where musician.mno=album.mnno and format='Audio';


•Find all musicians who have composed maximum albums.

SQL> select mname,format from musician,album where musician.mno=album.mnno  and   title=(select max(title)from album);

•Display musician wise album details.

SQL> select mname,format from musician,album where musician.mno=album.mnno order by mname;

•Display Musian details from 'Pune'

SQL> select *from musician where addr='pune’;