SQL学习---多张数据表-自学SQL网(6-12课题解）

目录

1. SQL Lesson 6: 用JOINs进行多表联合查

2. SQL Lesson 7: 外连接（OUTER JOINs）

3. SQL Lesson 8: 关于特殊关键字 NULLs

4.  SQL Lesson 9: 在查询中使用表达式

5.  SQL Lesson 10: 在查询中进行统计I (Pt. 1)

6.  SQL Lesson 11: 在查询中进行统计II (Pt. 2)

7.  SQL Lesson 12: 查询执行顺序​

---

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

1. 找到雇员里还没有分配办公室的(列出名字和角色就可以)

   select distinct name,Role
   from Employees
   left join Buildings
   on Employees.Building=Buildings.Building_name
   where Building_name is  null;

2. 找到还没有雇员的办公室

   select Building_name
   from Buildings
   left join Employees
   on Buildings.Building_name=Employees.Building
   where name is null;

4.  SQL Lesson 9: 在查询中使用表达式

1. 列出所有的电影ID,名字和销售总额(以百万美元为单位计算)

   select id,title,(Domestic_sales+International_sales)/1000000.0 as "销售量/百万"
   from movies
   inner join Boxoffice
   on movies.id=Boxoffice.movie_id;

2. 列出所有的电影ID,名字和市场指数(Rating的10倍为市场指数)

   select id,title,Rating*10 as "市场指数"
   from movies
   inner join Boxoffice
   on movies.id=Boxoffice.movie_id;
   
   

3. 列出所有偶数年份的电影，需要电影ID,名字和年份

   select id,title,year
   from movies
   inner join Boxoffice
   on movies.id=Boxoffice.movie_id
   where year%2=0;
   

4. 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

1. 【统计】找出就职年份最高的雇员(列出雇员名字+年份） 

   select name,max(Years_employed) as "就职年份"
   from Employees;

2. 【分组】按角色(Role)统计一下每个角色的平均就职年份

   select Role,avg(Years_employed)
   from Employees
   group by Role

3. 【分组】按办公室名字总计一下就职年份总和

   select Building,sum(Years_employed)
   from Employees
   group by Building

4. 【难题】每栋办公室按人数排名,不要统计无办公室的雇员

   select Building,count(*)
   from Employees
   where Building is not null
   group by Building

6.  SQL Lesson 11: 在查询中进行统计II (Pt. 2)

 在 GROUP BY 分组语法中，我们知道数据库是先对数据做WHERE，然后对结果做分组，如果我们要对分组完的数据再筛选出几条如何办？ （想一下按年份统计电影票房，要筛选出>100万的年份？）

1. 【统计】统计一下Artist角色的雇员数量 

   select count(*)
   from Employees
   group by Role
   having role="Artist";

2. 【分组】按角色统计一下每个角色的雇员数量

   select Role,count(*)
   from Employees
   group by Role;

3. 【分组】算出Engineer角色的就职年份总计

   select sum(Years_employed) as "就职年份总计"
   from Employees
   group by Role
   having Role="Engineer";
   

4. 【难题】按角色分组算出每个角色按有办公室和没办公室的统计人数(列出角色，数量，有无办公室,注意一个角色如果部分有办公室，部分没有需分开统计）

①按照题目要求，按角色、是否有办公室来分组 ②统计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: 查询执行顺序

1. 【复习】统计出每一个导演的电影数量（列出导演名字和数量） 

   select director,count()
   from movies
   inner join Boxoffice
   on movies.id=Boxoffice.movie_id
   group by Director;

2. 【复习】统计一下每个导演的销售总额(列出导演名字和销售总额)

   select Director,(sum(Domestic_sales)+sum(International_sales)) as "销售总额"
   from movies
   inner join Boxoffice
   on movies.id=Boxoffice.movie_id
   group by Director;

3. 【难题】按导演分组计算销售总额,求出平均销售额冠军（统计结果过滤掉只有单部电影的导演，列出导演名，总销量，电影数量，平均销量)

   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

4. 【变态难】找出每部电影和单部电影销售冠军之间的销售差，列出电影名，销售额差额

   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;