【Tips】
sqlzoo官网链接:【https://sqlzoo.net/wiki/SQL_Tutorial/zh】
每个系列开头介绍所用表及其信息
每道题含有题目描述、代码和结果,本系列第10题测试未通过
题目为自己简写,最好在官网查看具体题目
部分测试结果不完整,仅为一部分截图
合并表格之音乐数据库练习链接:【https://sqlzoo.net/wiki/Music_Tutorial/zh】
所用的表album,track
![](https://img2020.cnblogs.com/blog/2015736/202010/2015736-20201027091414209-2051212181.png)
![](https://img2020.cnblogs.com/blog/2015736/202010/2015736-20201027091432825-2116038396.png)
大碟(asin,碟名,歌手,售价,发行,标签,排名)
曲目(大碟,碟号,轨号,歌名)
1.列出收录歌曲Alison的title和artist
SELECT title,artist
FROM album JOIN track
ON (album.asin=track.album)
WHERE song = 'Alison'
![](https://uploader.shimo.im/f/GmDHbOElMY8cKEGj.png!thumbnail)
2.哪个歌手artist收录了歌曲Exodus
SELECT artist
FROM album JOIN track ON album.asin=track.album
WHERE song='Exodus'
3.为大碟album的'Blur',显示每一首歌的歌名song
SELECT song
FROM album JOIN track ON album.asin=track.album
WHERE title='Blur'
4.每一个大碟显示歌名title和每大碟的歌曲数量
SELECT title,COUNT(song)
FROM album JOIN track ON (asin=album)
GROUP BY title
5.每一大碟列出碟名title歌名中有Heart一词的歌曲数量 (没有这些歌的大碟不用列出).
SELECT title,COUNT(song)
FROM album JOIN track ON album.asin=track.album
WHERE song LIKE '%Heart%'
GROUP BY title
6.主题曲(title和song)相同
SELECT song
FROM album JOIN track ON album.asin=track.album
WHERE song=title
7.同名大碟(大碟和歌手名字相同)
SELECT title FROM album
WHERE title=artist
8.找出歌曲收录在2个以上的大碟中,列出收录次数
SELECT song,COUNT(DISTINCT title)
FROM track JOIN album ON album=asin
GROUP BY song
HAVING COUNT(DISTINCT title)>2
9.好价大碟:大碟中每一首歌的价格都少于5角,列出大碟名字,售价和歌曲数量
SELECT title,price,COUNT(song)
FROM album JOIN track ON asin=album
GROUP BY title,price
HAVING price/COUNT(song) |