Given the following schema: ALBUM(aID, AlbumTitle, Year, Price) COMPOSITION(Albu

Question

Given the following schema:
ALBUM(aID, AlbumTitle, Year, Price)
COMPOSITION(AlbumID, RecordingID, SongNumber)
RECORDING(rID, SongTitle, Year)
AUTHOR(AuthorName, SongTitle)
SINGER(SingerName, RecordingID)

5. Albums containing at least one song for which the recording year is not known

6. Albums for which the year of recording is known for all songs composing the album

7. Albums containing at least two songs recorded in different years

8. Albums containing at least 5 songs, showing the album title

9. Both: the authors that never sang a song, and the singers that never wrote a song. Show the name of these people.

10. Albums with the highest number of songs, showing for each album: the title and the number of songs

Explanation / Answer

5. select a.aID,a.AlbumTitle from ALBUM a,COMPOSITION c,RECORDING r where a.aID=c.AlbumID and c.RecordingID=r.rID and r.Year='NULL';

6. select a.aID,a.AlbumTitle from ALBUM a,COMPOSITION c,RECORDING r where a.aID=c.AlbumID and c.RecordingID=r.rID and r.Year is not null;

7. select a.aID,a.AlbumTitle from ALBUM a,COMPOSITION c,RECORDING r where a.aID=c.AlbumID and c.RecordingID=r.rID group by aID having distinct(r.year);

8. select a.aID,a.AlbumTitle from ALBUM a,COMPOSITION c,RECORDING r where a.aID=c.AlbumID and c.RecordingID=r.rID group by aID having count(RecordingID)=5;

9.select AuthorName,SingerName from AUTHOR a,SINGER s where AuthorName<>SingerName and SingerName<>AuthorName;

10. select aID, AlbumTitle, count(rID) as No_Of_Songs from ALBUM,COMPOSITION,RECORDING where a.aID=c.AlbumID and c.RecordingID=r.rID group by aID having max(count(rID));

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.