SELECT 查询综合

Table: Movies (Read-Only)

Id	Title	Director	Year	Length_minutes
1	Toy Story	John Lasseter	1995	81
2	A Bug's Life	John Lasseter	1998	95
3	Toy Story 2	John Lasseter	1999	93
4	Monsters, Inc.	Pete Docter	2001	92
5	Finding Nemo	Finding Nemo	2003	107
6	The Incredibles	Brad Bird	2004	116
7	Cars	John Lasseter	2006	117
8	Ratatouille	Brad Bird	2007	115
9	WALL-E	Andrew Stanton	2008	104
10	Up	Pete Docter	2009	101
11	Toy Story 3	Lee Unkrich	2010	103
12	Cars 2	John Lasseter	2011	120
13	Brave	Brenda Chapman	2012	102
14	Monsters University	Dan Scanlon	2013	110

Table: Boxoffice (Read-Only)

Movie_id	Rating	Domestic_sales	International_sales
5	8.2	380843261	555900000
14	7.4	268492764	475066843
8	8	206445654	417277164
12	6.4	191452396	368400000
3	7.9	245852179	239163000
6	8	261441092	370001000
9	8.5	223808164	297503696
11	8.4	415004880	648167031
1	8.3	191796233	170162503
7	7.2	244082982	217900167
10	8.3	293004164	438338580
4	8.1	289916256	272900000
2	7.2	162798565	200600000
13	7.2	237283207	301700000

Table(表）: Movies

Director	Count()
Andrew Stanton	1
Brad Bird	2
Brenda Chapman	1
Dan Scanlon	1
Finding Nemo	1
John Lasseter	5
Lee Unkrich	1
Pete Docter	2

 

练习 do it — 请完成如下任务

1. 统计出每一个导演的电影数量（列出导演名字和数量） ✓
2. 统计一下每个导演的销售总额(列出导演名字和销售总额)
3. 按导演分组计算销售总额,求出平均销售额冠军（统计结果过滤掉只有单部电影的导演，列出导演名，总销量，电影数量，平均销量)
4. 找出每部电影和单部电影销售冠军之间的销售差，列出电影名，销售额差额

1.
select director, count() from movies group by director;


2.
select director, sum(Domestic_sales + International_sales) as totalSales
from movies inner join boxoffice on movies.id = boxoffice.movie_id
group by director;

3.难
SELECT director,SUM(b.domestic_sales+b.international_sales)
as Sum_sales,
count(title) as Total,
sum(b.domestic_sales+b.international_sales)/count(title) 
AS average FROM Movies m
LEFT JOIN Boxoffice b
ON m.Id = b.Movie_id
GROUP BY Director
HAVING COUNT(Title) > 1
ORDER BY Average DESC
LIMIT 1;

4.难
SELECT Title, (SELECT MAX(Domestic_sales+International_sales) 
FROM Boxoffice) - SUM(Domestic_sales+International_sales) 
AS Diff FROM Movies INNER JOIN Boxoffice 
ON Movies.ID = Boxoffice.Movie_id 
GROUP BY Title;