SQL作业二
目的:通过加载chinook_db文件来把数据导入到sqllite,根据题目的要求进行查询
1.sql语句的基本语法
2.join多表查询的用法
3.group by分组的用法
4.order by排序,limit 页数展示的用法
热身题
1.从Track表中,找到10大作曲家
思路:从Track表中找到作曲家和总数,然后根据作曲家进行分组,在根据数量进行降序排序,获取前10位即为10大作曲家
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select count(*) as num,Composer from Track group by Composer order by num desc limit 10;
2.从Track表中,找到时长限定的歌曲
思路:从Track中,找到millseconds列,然后通过给定的区间来获取结果集
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select name,milliseconds from Track where milliseconds > 2500000 and milliseconds < 2600000 order by milliseconds;
3.从Album专辑表和Artist艺术家表来获取艺术家名称和关联的专辑名称
思路:使用join关键字,将2张表关联然后从大的结果集中获取字段
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select Artist.name, Album.Title from Album join Artist on Artist.ArtistId = Album .ArtistId where name = 'Iron Maiden' or name = 'Amy Winehouse';
实战题:
1.从Invoice表找出发票最多的排名前三的国家
思路:查询BillingCountry和总数,根据BillingCountry进行分组,然后根据总数降序排列,最后使用limit 3获取排名前三的数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select BillingCountry,count(*) as num from Invoice group by BillingCountry order by num desc limit 3
2.从Customer表和Invoice表找出的最佳客户的电子邮件
思路:将Customer表和Invoice表通过CustomerId进行关联,根据email进行分组,根据total进行排序,最后使用limit 1获取最佳客户的邮件
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT c.email,c.firstname,c.lastname,sum(i.total) as Total from Customer c join Invoice i on c.CustomerId = i.CustomerId group by c.email order by Total desc limit 1
3.推广摇滚音乐
思路:从Customer,Invoice,InvoiceLine,Track,Genre表中获取摇滚乐听众的邮箱,姓名,以及喜爱的音乐,注意表关联的字段
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT c.email,c.firstname,c.lastname,g.name from Customer c join Invoice i on c.CustomerId = i.CustomerId join InvoiceLine il on i.InvoiceId = il.InvoiceId join Track t on il.TrackId = t.TrackId join Genre g on t.GenreId = g.GenreId where g.name = "Rock" group by c.email order by c.email
4.音乐宣传活动
思路:从Invoice返回账单总额最高的城市,同2
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT BillingCity,sum(Total) as Total from Invoice group by BillingCity order by Total desc limit 1
5.城市热门音乐排行
思路:从Invoice,InvoiceLine,Track,Genre表中返回布拉格最流行的三大音乐风格
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT i.BillingCity,count(*) as num,g.name from Invoice i join InvoiceLine il on i.InvoiceId = il.InvoiceId join Track t on il.TrackId = t.TrackId join Genre g on t.GenreId = g.GenreId where BillingCity = "Prague" group by g.name order by num desc limit 3
6.寻找音乐家
思路:从Genre,Track,Album,Artist表中返回前10大摇滚音乐人的姓名和歌曲总数
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT a.name,count(g.name) as num from Genre g join Track t on g.GenreId = t.GenreId join Album al on t.AlbumId = al.AlbumId join Artist a on al.ArtistId = a.ArtistId where g.name = "Rock" group by a.name order by num desc limit 10
7.直通法国
思路:从Invoice,InvoiceLine,Track,Genre表中返回法国的账单城市,并且音乐的类型是朋克摇滚,把歌曲数量按照降序排列
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT i.BillingCity,count(g.name) as NumTracks from Invoice i join InvoiceLine il on i.InvoiceId = il.InvoiceId join Track t on il.TrackId = t.TrackId join Genre g on t.GenreId = g.GenreId where i.BillingCountry = "France" and g.name = "Alternative & Punk" group by i.BillingCity order by NumTracks desc
8.本地查询,爵士乐音轨
思路:从Customer,InvoiceLine,Invoice,Track和Genre找出类型为jazz的音乐种类,在来统计买了jazz音乐的消费者,注意去重
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select count(distinct(c.CustomerId)) from Customer c join Invoice i on c.CustomerId = i.CustomerId join InvoiceLine il on i.InvoiceId = il.InvoiceId join Track t on il.TrackId = t.TrackId join Genre g on t.GenreId = g.GenreId where g.name = 'Jazz' GROUP BY g.name
9.本地查询,低于平均值的歌曲长度
思路:先把Genre和Track表中的平均音乐长度取出作为外部查询的条件,然后外部整合Genre,Track和子查询结果在根据Genre的name分组,最后得出结果
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select count(Genre.Name) as num ,Genre.Name from Genre,Track, (SELECT avg(Milliseconds) as average FROM Genre,Track WHERE Genre.GenreId=Track.GenreId) as subquery where Track.GenreId = Genre.GenreId and Track.Milliseconds < average GROUP BY Genre.Name ORDER BY num DESC
10.本地查询,连接媒体类型与音轨
思路:将Track,Genre,和MediaType的相关字段关联起来,找出Genre名称是pop并且MediaType的名称是MPEG的结果即可
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select count(Genre.Name) from Genre,Track,MediaType where Track.GenreId = Genre.GenreId and Track.MediaTypeId = MediaType.MediaTypeId and MediaType.Name='MPEG audio file' and Genre.Name='Pop'
表结构整理,根据字段的对应关系整理