目录
1. SQL Lesson 6: 用JOINs进行多表联合查
2. SQL Lesson 7: 外连接(OUTER JOINs)
3. SQL Lesson 8: 关于特殊关键字 NULLs
5. SQL Lesson 10: 在查询中进行统计I (Pt. 1)
6. SQL Lesson 11: 在查询中进行统计II (Pt. 2)
1. SQL Lesson 6: 用JOINs进行多表联合查
1. 【联表】找到所有电影的国内Domestic_sales
和国际销售额 ✓
select *
from movies
inner join Boxoffice
on movies.id=Boxoffice.movie_id ;
2. 【联表】找到所有国际销售额比国内销售大的电影
select *
from movies
inner join Boxoffice
on movies.id=Boxoffice.movie_id
where Domestic_sales < international_sales ;
3. 【联表】找出所有电影按市场占有率rating
倒序排列
select *
from movies
inner join Boxoffice
on movies.id=Boxoffice.movie_id
order by rating desc ;
4. 【联表】每部电影按国际销售额比较,排名最靠前的导演是谁,国际销量多少
select director,international_sales
from movies
inner join Boxoffice
on movies.id=Boxoffice.movie_id
order by international_sales desc
limit 1;
2. SQL Lesson 7: 外连接(OUTER JOINs)
1. 找到所有有雇员的办公室(buildings
)名字 ✓
SELECT distinct Building
from Buildings
left join Employees
on Buildings.Building_name=Employees.Building
where name!="";
2. 找到所有办公室和他们的最大容量
SELECT distinct Building_name,Capacity FROM Buildings;
3. 找到所有办公室里的所有角色(包含没有雇员的),并做唯一输出(DISTINCT
)
select distinct Role,Building_name
from Buildings
left join Employees
on Buildings.Building_name=Employees.Building;
4. 找到所有有雇员的办公室(buildings
)和对应的容量
select distinct Building_name,capacity
from Buildings
left join Employees
on Buildings.Building_name=Building
where name != "";
3. SQL Lesson 8: 关于特殊关键字 NULLs
- 找到雇员里还没有分配办公室的(列出名字和角色就可以)
select distinct name,Role from Employees left join Buildings on Employees.Building=Buildings.Building_name where Building_name is null;
- 找到还没有雇员的办公室
select Building_name from Buildings left join Employees on Buildings.Building_name=Employees.Building where name is null;
4. SQL Lesson 9: 在查询中使用表达式
- 列出所有的电影ID,名字和销售总额(以百万美元为单位计算)
select id,title,(Domestic_sales+International_sales)/1000000.0 as "销售量/百万" from movies inner join Boxoffice on movies.id=Boxoffice.movie_id;
- 列出所有的电影ID,名字和市场指数(
Rating
的10倍为市场指数)select id,title,Rating*10 as "市场指数" from movies inner join Boxoffice on movies.id=Boxoffice.movie_id;
- 列出所有偶数年份的电影,需要电影ID,名字和年份
select id,title,year from movies inner join Boxoffice on movies.id=Boxoffice.movie_id where year%2=0;
- John Lasseter导演的每部电影每分钟值多少钱,告诉我最高的3个电影名和价值就可以
select title,(Domestic_sales+International_sales)/Length_minutes as "价值/分钟" from movies inner join Boxoffice on movies.id=Boxoffice.movie_id where director="John Lasseter" order by (Domestic_sales+International_sales)/Length_minutes desc limit 3;
5. SQL Lesson 10: 在查询中进行统计I (Pt. 1)
分组统计:GROUP BY
- 【统计】找出就职年份最高的雇员(列出雇员名字+年份)
select name,max(Years_employed) as "就职年份" from Employees;
- 【分组】按角色(
Role
)统计一下每个角色的平均就职年份select Role,avg(Years_employed) from Employees group by Role
- 【分组】按办公室名字总计一下就职年份总和
select Building,sum(Years_employed) from Employees group by Building
- 【难题】每栋办公室按人数排名,不要统计无办公室的雇员
select Building,count(*) from Employees where Building is not null group by Building
6. SQL Lesson 11: 在查询中进行统计II (Pt. 2)
在 GROUP BY
分组语法中,我们知道数据库是先对数据做WHERE
,然后对结果做分组,如果我们要对分组完的数据再筛选出几条如何办? (想一下按年份统计电影票房,要筛选出>100万的年份?)
- 【统计】统计一下Artist角色的雇员数量
select count(*) from Employees group by Role having role="Artist";
- 【分组】按角色统计一下每个角色的雇员数量
select Role,count(*) from Employees group by Role;
- 【分组】算出Engineer角色的就职年份总计
select sum(Years_employed) as "就职年份总计" from Employees group by Role having Role="Engineer";
- 【难题】按角色分组算出每个角色按有办公室和没办公室的统计人数(列出角色,数量,有无办公室,注意一个角色如果部分有办公室,部分没有需分开统计)
①按照题目要求,按角色、是否有办公室来分组 ②统计case when,如果是null要填0,有则填1。这里第二种方法is not null为1就有办公室
select Role,count(*),
case when Building is null then '0' else '1' end as have_building
from Employees
group by Role,building is null;
select Role,count(*),building is not null as have_building
from Employees
group by Role,Building is not null;
7. SQL Lesson 12: 查询执行顺序
- 【复习】统计出每一个导演的电影数量(列出导演名字和数量)
select director,count() from movies inner join Boxoffice on movies.id=Boxoffice.movie_id group by Director;
- 【复习】统计一下每个导演的销售总额(列出导演名字和销售总额)
select Director,(sum(Domestic_sales)+sum(International_sales)) as "销售总额" from movies inner join Boxoffice on movies.id=Boxoffice.movie_id group by Director;
- 【难题】按导演分组计算销售总额,求出平均销售额冠军(统计结果过滤掉只有单部电影的导演,列出导演名,总销量,电影数量,平均销量)
select Director,(sum(Domestic_sales)+sum(International_sales)),count(*),(sum(Domestic_sales)+sum(International_sales))/count(*) from movies inner join Boxoffice on movies.id=Boxoffice.movie_id group by Director having count(*)!=1 order by (sum(Domestic_sales)+sum(International_sales))/count(*) desc limit 1
- 【变态难】找出每部电影和单部电影销售冠军之间的销售差,列出电影名,销售额差额
select title,(select max(Domestic_sales+International_sales) from Boxoffice)-(Domestic_sales+International_sales) from movies inner join Boxoffice on movies.id=Boxoffice.movie_id;