自学SQL网前13节答案及总结

🤗写在前面

本笔记是Onekey在学习自学SQL网课程中的本人的笔记记录。仅分享前十三节免费版练习答案，十分推荐这个学习网站，对入门的我受益良多(前面几节比较简单也没有记录，直接使用网络资源所写代码)

网址http://xuesql.cn/

💯 总结（Ctrl C V）

一个查询SQL的执行总是先从数据里按条件选出数据，然后对这些数据再次做一些整理处理，按要求返回成结果，让结果尽可能是简单直接的。因为一个 查询SQL由很多部分组成，所以搞清楚这些部分的执行顺序还挺重要的，这有助于我们更深刻的理解SQL执行过程.

查询执行顺序

1. FROM 和 JOINs

FROM 或 JOIN会第一个执行，确定一个整体的数据范围. 如果要JOIN不同表，可能会生成一个临时Table来用于 下面的过程。总之第一步可以简单理解为确定一个数据源表（含临时表)

2. WHERE

我们确定了数据来源 WHERE 语句就将在这个数据源中按要求进行数据筛选，并丢弃不符合要求的数据行，所有的筛选col属性 只能来自FROM圈定的表. AS别名还不能在这个阶段使用，因为可能别名是一个还没执行的表达式

3. GROUP BY

如果你用了 GROUP BY 分组，那GROUP BY 将对之前的数据进行分组，统计等，并将是结果集缩小为分组数.这意味着 其他的数据在分组后丢弃.

4. HAVING

如果你用了 GROUP BY 分组, HAVING 会在分组完成后对结果集再次筛选。AS别名也不能在这个阶段使用.

5. SELECT

确定结果之后，SELECT用来对结果col简单筛选或计算，决定输出什么数据.

6. DISTINCT

如果数据行有重复DISTINCT 将负责排重.

7. ORDER BY

在结果集确定的情况下，ORDER BY 对结果做排序。因为SELECT中的表达式已经执行完了。此时可以用AS别名.

8. LIMIT / OFFSET

最后 LIMIT 和 OFFSET 从排序的结果中截取部分数据.

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;

💯 练习答案

SQL Lesson 0: 让我给SQL做个自我介绍

1. 【初体验】这是第一题,请你先将左侧的输入框里的内容清空，然后请输入下面的SQL，您将看到所有电影标题：
   SELECT title FROM movies
2. 【初体验】请输入如下SQL你将看到4条电影（切记先清空数据框且出错要耐心比对）：
   SELECT title,director FROM movies WHERE Id < 5
3. 【初体验】输入如下SQL你将看到电影总条数：
   SELECT count(*) FROM movies
4. 【初体验】SQL可以直接做计算，下面的SQL计算1+1的和,请输入：
   SELECT 1+1

SQL Lesson 1: SELECT 查询 101

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

SELECT title FROM movies

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

SELECT Director FROM movies

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

SELECT Director,title FROM movies

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

SELECT title,Year FROM movies

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

SELECT * FROM movies

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

SELECT title,Id,Length_minutes FROM movies

SQL Lesson 2: 条件查询 (constraints) (Pt. 1)

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

SELECT * FROM movies where Id=6

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

SELECT * FROM movies where Year>=2000 and Year<=2010

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

SELECT * FROM movies where Year<2000 or Year >2010

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

SELECT * FROM movies where Id<=5

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

SELECT * FROM movies where Year>=2010 and Length_minutes<120

SQL Lesson 3: 条件查询(constraints) (Pt. 2)

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

SELECT * FROM movies where Title LIKE “%Toy Story%”

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

SELECT * FROM movies where Director ="John Lasseter"

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

SELECT * FROM movies where Director !="John Lasseter"

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

SELECT * FROM movies where Title LIKE "WALL-%"

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

SELECT * FROM movies where Title = “A Bug’s Life”

SQL Lesson 4: 查询结果Filtering过滤 和 sorting排序

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

SELECT DISTINCT Director FROM movies ORDER BY Director ASC

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

SELECT * FROM movies ORDER BY Year DESC limit 4

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

SELECT * FROM movies ORDER BY Title ASC limit 5

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

SELECT * FROM movies ORDER BY Title ASC limit 5 OFFSET 5

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

SELECT Title FROM movies WHERE Director="John Lasseter" ORDER BY Length_minutes ASC limit 1 offset 2

SQL Lesson 5: 复习 SELECT 查询

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

SELECT * FROM north_american_cities WHERE Country = "Canada"

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

SELECT * FROM north_american_cities WHERE Longitude<-87.629798 ORDER BY Longitude DESC

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

SELECT * FROM North_american_cities WHERE Country="Mexico" ORDER BY Population ASC LIMIT 2 OFFSET 1

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

SELECT * FROM North_american_cities WHERE country = "United States" ORDER BY Population DESC LIMIT 2 OFFSET 2

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

SQL Lesson 7: 外连接（OUTER JOINs）

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

SELECT DISTINCT Employees.Building 
FROM Employees LEFT JOIN Buildings ON Employees.Building = Buildings.Building_name
WHERE Employees.Building IS NOT NULL

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

SELECT DISTINCT Buildings.Building_name , Employees.Role
FROM Buildings LEFT JOIN Employees ON Employees.Building = Buildings.Building_name

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

SELECT DISTINCT Buildings.Building_name , Buildings.Capacity
FROM Buildings LEFT JOIN Employees ON Employees.Building = Buildings.Building_name
WHERE Employees.Building IS NOT NULL

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

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

SELECT Role,Name FROM Employees 
WHERE Building IS NULL

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

SELECT Building_name
FROM Buildings LEFT JOIN Employees ON Employees.Building = Buildings.Building_name
WHERE Role IS NULL

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

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

SELECT Id , Title , (Domestic_sales+International_sales)/1000000
FROM Movies JOIN Boxoffice ON Movies.Id = Boxoffice.Movie_id

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

SELECT Id , Title , Rating*10
FROM Movies JOIN Boxoffice ON Movies.Id = Boxoffice.Movie_id

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

SELECT Id , Title , Year
FROM Movies 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 sales_value
FROM Movies JOIN Boxoffice ON Movies.Id = Boxoffice.Movie_id
WHERE Director = "John Lasseter" 
ORDER BY sales_value DESC
LIMIT 3

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

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(*) FROM Employees
WHERE  Building IS NOT NULL
GROUP BY Building
ORDER BY COUNT(*) DESC

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

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

SELECT COUNT(*) FROM Employees
WHERE Role = "Artist"

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

SELECT Role , COUNT(*) FROM Employees
GROUP BY Role

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

SELECT SUM(Years_employed) FROM Employees
WHERE Role = "Engineer"

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

SELECT COUNT(*) , Role , Building IS NOT NULL AS bn FROM Employees
GROUP BY Role , Building IS NOT NULL

SQL Lesson 12: 查询执行顺序

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

SELECT Movies.Director , COUNT(*)
FROM Movies JOIN Boxoffice ON Movies.Id = Boxoffice.Movie_id
GROUP BY Movies.Director

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

SELECT Movies.Director , SUM(Domestic_sales+International_sales)
FROM Movies JOIN Boxoffice ON Movies.Id = Boxoffice.Movie_id
GROUP BY Movies.Director

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

SELECT Movies.Director 
, COUNT(*)
, SUM(Domestic_sales+International_sales)
, SUM(Domestic_sales+International_sales)/COUNT(*)
FROM Movies JOIN Boxoffice ON Movies.Id = Boxoffice.Movie_id
GROUP BY Movies.Director 
HAVING COUNT(*) > 1
ORDER BY SUM(Domestic_sales+International_sales)/COUNT(*) DESC
LIMIT 1

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

SELECT 
Title , 
(SELECT Domestic_sales + International_sales AS Sales
FROM Movies JOIN Boxoffice ON Movies.Id = Boxoffice.Movie_id
ORDER BY Sales DESC
LIMIT 1)
- (Domestic_sales + International_sales)
AS sale_diff
FROM  Movies JOIN Boxoffice ON Movies.Id = Boxoffice.Movie_id