自学SQL网题库答案

###自学SQL网答案

leeson 1

1. 【简单查询】找到所有电影的名称title

   SELECT title from movies
   

2. 【简单查询】找到所有电影的导演

   select director from movies
   

3. 【简单查询】找到所有电影的名称和导演

   select title ,director from movies
   

4. 【简单查询】找到所有电影的名称和上映年份

   select title ,year from movies
   

5. 【简单查询】找到所有电影的所有信息

   select * from movies
   

6. 【简单查询】找到所有电影的名称,Id和播放时长

select title ,id,length_minutes from movies

leeson 2

1. 【简单条件】找到id为6的电影

select * from movies where id=6

2. 【简单条件】找到在2000-2010年间year上映的电影

select * from movies where year between 2000 and 2010

3. 【简单条件】找到不是在2000-2010年间year上映的电影

select * from movies where year not between 2000 and 2010

4. 【简单条件】找到头5部电影

select * from movies where id<=5 

5. 【简单条件】找到2010（含）年之后的电影里片长小于两个小时的片子

select * from movies where year>=2010 and length_minutes<120

leeson 3

1. 【复杂条件】找到所有Toy Story系列电影

   select * from movies where title like '%Toy Story%'
   

2. 【复杂条件】找到所有John Lasseter导演的电影

   select * from movies where director like '%John Lasseter%'
   

3. 【复杂条件】找到所有不是John Lasseter导演的电影

   select * from movies where director not like '%John Lasseter%'
   

4. 【复杂条件】找到所有电影名为"WALL-"开头的电影

   select * from movies where title like 'WALL-%'
   

5. 【复杂条件】有一部98年电影中文名《虫虫危机》请给我找出来

	select * from movies where year = '1998'

leeson 4

1. 【结果排序】按导演名排重列出所有电影(只显示导演)，并按导演名正序排列

select distinct director from movies order by director 

2. 【结果排序】列出按上映年份最新上线的4部电影

select * from movies  order by year desc limit 4

3. 【结果排序】按电影名字母序升序排列，列出前5部电影

select * from movies order by title limit 5

4. 【结果排序】按电影名字母序升序排列，列出上一题之后的5部电影

select * from movies order by title limit 5 offset 5

5. 【结果排序】如果按片长排列，John Lasseter导演导过片长第3长的电影是哪部，列出名字即可 ✓

select title from movies where director = 'John Lasseter' order by length_minutes desc limit 1 offset 2

leeson 5

1. 【复习】列出所有加拿大人的Canadian信息(包括所有字段)

select *  from North_american_cities  where Country = 'Canada'

2. 【复习】列出所有在Chicago西部的城市，从西到东排序(包括所有字段)

   select * from North_american_cities where Longitude <  -87.629798 order by Longitude
   

3. 【复习】用人口数population排序,列出墨西哥Mexico最大的2个城市(包括所有字段)

   select * from North_american_cities where Country ='Mexico'  order by population desc limit 2
   

4. 【复习】列出美国United States人口3-4位的两个城市和他们的人口(包括所有字段)

select * from North_american_cities where Country ='United States' order by population desc limit 2 offset 2

leeson 6

1. 【联表】找到所有电影的国内Domestic_sales和国际销售额

select * from movies,Boxoffice where id=Movie_id

2. 【联表】找到所有国际销售额比国内销售大的电影

select * from movies,Boxoffice where id = Movie_id and Domestic_sales < international_sales

3. 【联表】找出所有电影按市场占有率rating倒序排列

   select * from movies, Boxoffice where id = Movie_id order by rating
   

4. 【联表】每部电影按国际销售额比较,排名最靠前的导演是谁,国际销量多少

select director,international_sales from movies, Boxoffice where id =Movie_id order by international_sales desc limit 1 

leeson 7

1. 【复习】找到所有有雇员的办公室(buildings)名字

select distinct  building_name from Employees left join buildings on building =building_name where building not null

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

select distinct building_name,role from buildings  left join Employees on Building = Building_name  

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

select distinct  Building ,Capacity from Employees left join buildings on building =building_name where building not null

leeson 8

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

select role,name  from    Employees left join  Buildings  on Building = Building_name where Building is null

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

select  distinct Building_name   from 
  Buildings left join    Employees
on Building = Building_name where name is NULL;

leeson 9

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

   select id,title ,(Domestic_sales +International_sales)/1000000 as sum_sales from movies left join Boxoffice on id =Movie_id  
   

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

   select id,title, (rating*10)as rating   from Movies left join boxoffice on id=movie_id
   

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

   select id,title,year from movies left join boxoffice on id=movie_id where year%2=0
   

4. 【难题】John Lasseter导演的每部电影每分钟值多少钱,告诉我最高的3个电影名和价值就可以

   select title,((Domestic_sales+international_sales)/length_minutes)as minutes_price from movies left join boxoffice on id=movie_id where director='John Lasseter' order by minutes_price desc limit 3
   

leeson 10

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

select Name,max(Years_employed) 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(Building) from Employees
where Building is not null group by building

leeson 11

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

SELECT count(role) FROM employees where Role= 'Artist'

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

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

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

select sum(Years_employed) from employees where role='Engineer' group by role

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

SELECT count(*) as count,Role,building is not null FROM employees group by Role,building is not null

leeson 12

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

select director ,count(title)   from movies group by director

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

   select director,sum(Domestic_sales+International_sales) from movies left join boxoffice on id=movie_id group by director
   

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

   select sum(Domestic_sales+International_sales)as sum_sales,director,count(title)as count,avg(Domestic_sales+International_sales)as avg_sales from movies left join boxoffice on id=movie_id group by director  having count >1 order by avg_sales desc limit 1
   

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

select title,  maxt.max_s-(Domestic_sales+International_sales)
from(SELECT *
FROM  Movies m
left join Boxoffice b on m.Id = b.Movie_id)as mb
cross join(select sum(Domestic_sales+International_sales)as sum_sales  from movies left join boxoffice on id=movie_id group by title order by sum_sales desc limit 1)
as maxt