【SQL学习】-初学02-答案

07 外连接（OUTER JOINs）

练习 do it — 请完成如下任务
【复习】找到所有有雇员的办公室(buildings)名字

SELECT distinct building_name FROM buildings left join employees on 
buildings.building_name=employees.building
where employees.name is not null

【复习】找到所有办公室里的所有角色（包含没有雇员的）,并做唯一输出(DISTINCT)

SELECT distinct role,building_name from  buildings
left join employees
on employees.building = buildings.building_name

【难题】找到所有有雇员的办公室(buildings)和对应的容量

SELECT distinct building_name,capacity from  buildings
left join employees
on employees.building = buildings.building_name
where building is not null

08 关于特殊关键字 NULLs

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

SELECT name,role FROM employees 
left join buildings on
buildings.building_name = employees.building
where building is null

【难题】找到还没有雇员的办公室

SELECT building_name FROM buildings 
left join employees on
buildings.building_name = employees.building
where role is null

标题09 在查询中使用表达式

练习 do it — 请完成如下任务
【计算】列出所有的电影ID,名字和销售总额(以百万美元为单位计算)

SELECT id as 电影ID ,title,(domestic_sales+international_sales)/1000000 as 销售总额
FROM movies inner join boxoffice
on movies.id=boxoffice.movie_id

【计算】列出所有的电影ID,名字和市场指数(Rating的10倍为市场指数)

--请输入sql
SELECT id as 电影ID ,title,(rating*10)as 市场指数
FROM movies inner join boxoffice
on movies.id=boxoffice.movie_id

【计算】列出所有偶数年份的电影，需要电影ID,名字和年份

SELECT id as 电影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 价值 desc limit 3

标题10 在查询中进行统计I (Pt. 1)

练习 do it — 请完成如下任务
【统计】找出就职年份最高的雇员(列出雇员名字+年份）

SELECT name,years_employed FROM employees
order by years_employed desc limit 1

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

SELECT role,avg(years_employed) FROM employees
group by role

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

SELECT building,sum(years_employed) FROM employees
group by building

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

SELECT building, count(building) as count FROM employees
group by building
order by count desc limit 2

11 在查询中进行统计II练习

do it — 请完成如下任务
【统计】统计一下Artist角色的雇员数量

select count(role) from employees
where role ='Artist'

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

select role,count(*) from employees
group by role

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

select sum(years_employed) from employees
where role = "Engineer"
group by role

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

SELECT role,count(name),case when Building is null then 0
                        else 1 end as a
FROM employees group by role ,a;

12 查询执行顺序

完整的SELECT查询

SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM mytable
    JOIN another_table
      ON mytable.column = another_table.column
    WHERE constraint_expression
    GROUP BY column
    HAVING constraint_expression
    ORDER BY column ASC/DESC
    LIMIT count OFFSET COUNT;

练习 do it — 请完成如下任务
【复习】统计出每一个导演的电影数量（列出导演名字和数量）

SELECT director, count(*) FROM movies
group by director

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

select director,sum(domestic_sales+international_sales) as 销售总额
from movies join boxoffice
on movies.id = boxoffice.movie_id
group by director

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

select director,sum(domestic_sales+international_sales) as 总销量,
count(title) as 电影数量,
sum(domestic_sales+international_sales)/count(title) as 平均销量
from movies join boxoffice
on movies.id = boxoffice.movie_id
group by director
having 电影数量>1
order by 平均销量 desc
limit 1

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

首先在boxoffice表中查找出销售额最大的值，然后用这个值减去每一部电影的销售额。
ps: 这题有点难度建议再仔细想一想，我没做出来o(╥﹏╥)o

SELECT title,(select max(Domestic_sales+International_sales)
				from boxoffice) - (Domestic_sales+International_sales) as cha
FROM movies
inner join boxoffice
on movies.id=boxoffice.movie_id