SqlZoo.net习题答案：How to do joins.【Album】

习题地址：http://sqlzoo.net/3a.htm

 

表结构：　　album(asin, title, artist, price, release, label, rank)

　　　　　　track(album, dsk, posn, song)

 

 

1b.Which artist recorded the song 'Exodus'?

select album.artist 
from album join track
on (album.asin = track.album)
where track.song = 'Exodus'

 

1c.Show the song for each track on the album 'Blur'

select track.song 
from track join album
on (track.album = album.asin)
where album.title = 'Blur'

 

2a.For each album show the title and the total number of track.

select album.title, count(track.song)
from album join track
on (album.asin = track.album)
group by album.title

 

2b.For each album show the title and the total number of tracks containing the word 'Heart' (albums with no such tracks need not be shown).

select album.title, count(track.song)
from album join track
on (album.asin = track.album)
where track.song like '%Heart%'
group by album.title

 

2c.A "title track" is where the song is the same as the title. Find the title tracks.

select album.title
from album join track
on (album.asin = track.album)
where track.song = album.title

 

2d.An "eponymous" album is one where the title is the same as the artist (for example the album 'Blur' by the band 'Blur'). Show the eponymous albums.

select title
from album
where title = artist

 

3a.Find the songs that appear on more than 2 albums. Include a count of the number of times each shows up.

select track.song, count(track.song)
from album  join track 
on (album.asin = track.album)
group by track.song
having count(distinct album.title) > 2

 

3b.A "good value" album is one where the price per track is less than 50 pence. Find the good value album - show the title, the price and the number of tracks.

select album.title, album.price, count(track.song)
from album join track
on (album.asin = track.album)
group by album.asin
having album.price/count(track.song) < 0.5

 

3c.Wagner's Ring cycle has an imposing 173 tracks, Bing Crosby clocks up 101 tracks.

List albums so that the album with the most tracks is first. Show the title and the number of tracks

 

select album.title, count(track.song) as num
from album join track
on (album.asin = track.album)
group by album.title,track.album
order by num desc