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)

Write SQL statements to create the tables (including primary and foreign keys, where applicable):
1. Album, Composition, and Author

Formulate SQL queries to find:

1. Albums released between 1970 and 1973, inclusive, showing the album title

2. Singers (bands) that have released a song between 1970 and 1975, inclusive, showing the singer name

3. Singer-songwriters, that is people (bands) that sang the song they wrote, whose name starts with "B" or "P"

4. Singers that are not authors

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

1. select aID,AlbumTitle,Year from ALBUM where Year between 1970 and 1973;

The above query will retrieve the album information that are released between the given years.

2. select s.SingerName,r.SongTitle,r.Year from SINGER s,Recording r where s.RecordingID=r.rID and Year between 1970 and 1975;

The above query will retrieve the required song information with the singer name during the given years.

3. select s.SingerName,a.SongTitle from SINGER s,AUTHOR a where s.SingerName=a.AuthorName and a.AuthorName like 'B%' or 'P%';

The above query will retrieve the singer name who wrote their song and those names start with B or P.

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

This will retrieve the singer names those who are not authors.

according to chegg policy i cant answer more than 4 parts. Please post the remaining as new post.

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

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