Database Reference
In-Depth Information
26.7 Sample Assignment 6A
Figure 26-2 is repeated as Figure 26-4 for ease of reference. Carefully analyze the figure,
and answer the questions that follow.
Figure 26-4. Basic Specifications for Music Database
1.
Write appropriate SQL statements to create the tables
specified in the figure (you may assign appropriate column
types and lengths). Also include important integrity
constraints.
{30}
2.
Write SQL statements to realize the following:
2a.
Registered musicians (MNO and MNAME) from USA or JAM
(where “USA” and “JAM” are abbreviated codes for United
States and Jamaica respectively).
{03}
2b.
List every ensemble (ENO) that includes a SAXAPHONE
or CLARINET player.
{03}
2c.
Give the ENO of every ensemble that includes a SAXAPHONE
but not a CLARINET player.
{04}
2d. List all compositions (CNO and TITLE) by
MOZART.
{04}
2e.
List all performances (PNO, CNO, MNO, and PCOUNTRY)
of compositions in the country of origin.
{06}
2f.
Give the ENO of every ensemble that includes a SAXAPHONE
or CLARINET player, but not both.
{06}
2g.
Find CNO for compositions all of which have been
performed in USA.
{03}
2h. List countries in which MOZART's compositions have been
performed.
{04}
2i.
Give ENAME of ensembles whose manager is RUSSIAN.
{03}
3.
Define and specify SQL views to realize the above
requirements.
{36}
 
Search WWH ::




Custom Search