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' 

表结构整理,根据字段的对应关系整理