正文
SQL作业二
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
目的:通过加载chinook_db文件来把数据导入到sqllite,根据题目的要求进行查询
1.sql语句的基本语法
2.join多表查询的用法
3.group by分组的用法
4.order by排序,limit 页数展示的用法
热身题
1.从Track表中,找到10大作曲家
思路:从Track表中找到作曲家和总数,然后根据作曲家进行分组,在根据数量进行降序排序,获取前10位即为10大作曲家
select count(*) as num,Composer from Track group by Composer order by num desc limit 10;
2.从Track表中,找到时长限定的歌曲
思路:从Track中,找到millseconds列,然后通过给定的区间来获取结果集
select name,milliseconds from Track where milliseconds > 2500000 and milliseconds < 2600000 order by milliseconds;
3.从Album专辑表和Artist艺术家表来获取艺术家名称和关联的专辑名称
思路:使用join关键字,将2张表关联然后从大的结果集中获取字段
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获取排名前三的数据
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获取最佳客户的邮件
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表中获取摇滚乐听众的邮箱,姓名,以及喜爱的音乐,注意表关联的字段
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
SELECT BillingCity,sum(Total) as Total
from Invoice
group by BillingCity
order by Total desc
limit 1
5.城市热门音乐排行
思路:从Invoice,InvoiceLine,Track,Genre表中返回布拉格最流行的三大音乐风格
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大摇滚音乐人的姓名和歌曲总数
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表中返回法国的账单城市,并且音乐的类型是朋克摇滚,把歌曲数量按照降序排列
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音乐的消费者,注意去重
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分组,最后得出结果
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的结果即可
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'
表结构整理,根据字段的对应关系整理
