【DataBase】XueSQL Training

地址：

http://xuesql.cn/

Lesson0

-- 认识SQL

-- 【初体验】这是第一题,请你先将左侧的输入框里的内容清空，然后请输入下面的SQL，您将看到所有电影标题：
SELECT title FROM movies

-- 【初体验】请输入如下SQL你将看到4条电影（切记先清空数据框且出错要耐心比对）：
SELECT title,director FROM movies WHERE Id < 5

-- 【初体验】输入如下SQL你将看到电影总条数：
SELECT count(*) FROM movies

--【初体验】SQL可以直接做计算，下面的SQL计算1+1的和,请输入：
SELECT 1+1

Lesson1

-- 基础查询

-- 【简单查询】找到所有电影的名称title
SELECT Title FROM movies

-- 【简单查询】找到所有电影的导演
SELECT Director FROM movies

-- 【简单查询】找到所有电影的名称和导演
SELECT Title, Director FROM movies

-- 【简单查询】找到所有电影的名称和上映年份
SELECT Title, Year FROM movies

-- 【简单查询】找到所有电影的所有信息
SELECT * FROM movies

-- 【简单查询】找到所有电影的名称,Id和播放时长
SELECT Title, Id, Length_minutes FROM movies

Lesson2

-- 条件查询1

-- 【简单条件】找到id为6的电影
SELECT * FROM Movies WHERE Id = 6

-- 【简单条件】找到在2000-2010年间year上映的电影
SELECT * FROM Movies WHERE Year BETWEEN 2000 AND 2010

-- 【简单条件】找到不是在2000-2010年间year上映的电影
SELECT * FROM Movies WHERE Year  NOT BETWEEN 2000 AND 2010

-- 【简单条件】找到头5部电影
SELECT * FROM Movies LIMIT 5

-- 【简单条件】找到2010（含）年之后的电影里片长小于两个小时的片子
SELECT * FROM Movies WHERE Year > 2009 AND Length_minutes < 120

Lesson3

-- 【复杂条件】找到所有Toy Story系列电影
SELECT * FROM Movies WHERE Title LIKE "%Toy Story%"

-- 【复杂条件】找到所有John Lasseter导演的电影
SELECT * FROM Movies WHERE Director = "John Lasseter"

-- 【复杂条件】找到所有不是John Lasseter导演的电影
SELECT * FROM Movies WHERE Director <> "John Lasseter"

-- 【复杂条件】找到所有电影名为 "WALL-" 开头的电影
SELECT * FROM Movies WHERE Title LIKE "WALL-%"

-- 【复杂条件】有一部98年电影中文名《虫虫危机》请给我找出来
SELECT * FROM Movies WHERE Year = 1998 AND Title = "A Bug's Life"

Lesson4

-- 【结果排序】按导演名排重列出所有电影(只显示导演)，并按导演名正序排列
SELECT DISTINCT Director FROM Movies ORDER BY Director ASC

-- 【结果排序】列出按上映年份最新上线的4部电影
SELECT * FROM Movies ORDER BY Year DESC LIMIT 4

-- 【结果排序】按电影名字母序升序排列，列出前5部电影
SELECT * FROM Movies ORDER BY Title ASC LIMIT 5

-- 【结果排序】按电影名字母序升序排列，列出上一题之后的5部电影
SELECT * FROM Movies ORDER BY Title ASC LIMIT 5 OFFSET 5

-- 【结果排序】如果按片长排列，John Lasseter导演导过片长第3长的电影是哪部，列出名字即可
SELECT Title FROM Movies WHERE Director = "John Lasseter" ORDER BY Length_minutes DESC LIMIT 1 OFFSET 2

Lesson5

-- 【复习】列出所有加拿大人的Canadian信息(包括所有字段)
SELECT * FROM north_american_cities WHERE Country = "Canada"

-- 【复习】列出所有在Chicago西部的城市，从西到东排序(包括所有字段)
SELECT * FROM north_american_cities WHERE Longitude < (SELECT Longitude FROM north_american_cities WHERE City = "Chicago") ORDER BY Longitude ASC

-- 【复习】用人口数population排序,列出墨西哥Mexico最大的2个城市(包括所有字段)
SELECT * FROM north_american_cities WHERE Country = "Mexico" ORDER BY Population DESC LIMIT 2

-- 【复习】列出美国United States人口3-4位的两个城市和他们的人口(包括所有字段)
SELECT * FROM north_american_cities WHERE Country = "United States" ORDER BY Population DESC LIMIT 2 OFFSET 2

Lesson6

-- 联表查询1

-- 【联表】找到所有电影的国内Domestic_sales和国际销售额
SELECT * FROM Movies A LEFT JOIN Boxoffice B ON A.Id = B.Movie_id

-- 【联表】找到所有国际销售额比国内销售大的电影
SELECT * FROM Movies A LEFT JOIN Boxoffice B ON A.Id = B.Movie_id WHERE B.International_sales > B.Domestic_sales

-- 【联表】找到所有国际销售额比国内销售大的电影
SELECT * FROM Movies A LEFT JOIN Boxoffice B ON A.Id = B.Movie_id ORDER BY Rating DESC

-- 【联表】每部电影按国际销售额比较,排名最靠前的导演是谁,国际销量多少
SELECT Director, International_sales FROM Movies A LEFT JOIN Boxoffice B ON A.Id = B.Movie_id ORDER BY International_sales DESC LIMIT 1

Lesson7

-- 【复习】找到所有有雇员的办公室(buildings)名字 ✓
SELECT DISTINCT B.Building_name FROM Employees A INNER JOIN Buildings B ON A.Building = B.Building_name

-- 【复习】找到所有办公室里的所有角色（包含没有雇员的）,并做唯一输出(DISTINCT)
SELECT 
 DISTINCT A.Building_name,
 B.Role
FROM
 Buildings A
 LEFT JOIN  Employees B ON A.Building_name = B.Building

-- 【难题】找到所有有雇员的办公室(buildings)和对应的容量
-- 先求去重的building
SELECT DISTINCT Building  FROM Employees WHERE Building  IS NOT NULL
-- 然后虚拟表联表即可
SELECT 
 A.Building,
 B.Capacity
FROM 
 (SELECT DISTINCT Building  FROM Employees WHERE Building  IS NOT NULL) A 
 INNER JOIN Buildings B ON A.Building = B.Building_name

Lesson8

-- 【复习】找到雇员里还没有分配办公室的(列出名字和角色就可以)
SELECT Role, Name FROM Employees WHERE Building IS NULL
-- 【难题】找到还没有雇员的办公室
SELECT Building_name FROM Buildings WHERE Building_name NOT IN(SELECT DISTINCT Building  FROM Employees WHERE Building IS NOT NULL) 

Lesson9

-- 【计算】列出所有的电影ID,名字和销售总额(以百万美元为单位计算)
SELECT Id, Title, (B.Domestic_sales + B.International_sales) / 1000000 TotalSales FROM Movies A LEFT JOIN Boxoffice B ON A.Id = Movie_id
-- 【计算】列出所有的电影ID,名字和市场指数(Rating的10倍为市场指数)
SELECT Id, Title, B.Rating * 10 FROM Movies A LEFT JOIN Boxoffice B ON A.Id = Movie_id
-- 【计算】列出所有偶数年份的电影，需要电影ID,名字和年份
SELECT Id, Title, Year FROM Movies WHERE Year % 2 = 0
-- 【难题】John Lasseter导演的每部电影每分钟值多少钱,告诉我最高的3个电影名和价值就可以
SELECT 
 A.Title, (B.Domestic_sales + B.International_sales) / A.Length_minutes Value
FROM 
 Movies A 
 LEFT JOIN Boxoffice B ON A.Id = Movie_id
WHERE
 Director = "John Lasseter"
ORDER BY 
 Value DESC 
LIMIT 3

Lesson10

-- 【统计】找出就职年份最高的雇员(列出雇员名字+年份）
SELECT Name, Years_employed  FROM Employees WHERE Years_employed = (SELECT MAX(Years_employed) 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(1) FROM Employees WHERE Building IS NOT NULL GROUP BY Building

Lesson11

-- 【统计】统计一下Artist角色的雇员数量 ✓
SELECT COUNT(1) FROM Employees WHERE Role = "Artist"

-- 【分组】按角色统计一下每个角色的雇员数量
SELECT COUNT(1), Role FROM Employees GROUP BY Role

-- 【分组】算出Engineer角色的就职年份总计
SELECT SUM(Years_employed) FROM Employees WHERE Role = "Engineer"

-- 【难题】按角色分组算出每个角色按有办公室和没办公室的统计人数(列出角色，数量，有无办公室,注意一个角色如果部分有办公室，部分没有需分开统计）
SELECT 
 Role,
 CASE WHEN Building IS NULL  THEN 0 ELSE 1 END AS have_b,
 COUNT(Name)
FROM Employees
GROUP BY Role, have_b;

Lesson12

--【复习】统计出每一个导演的电影数量（列出导演名字和数量）
SELECT Director, COUNT(1) FROM Movies GROUP BY Director

--【复习】统计一下每个导演的销售总额(列出导演名字和销售总额)
SELECT A.Director, SUM(B.Domestic_sales + B.International_sales) FROM Movies A LEFT JOIN Boxoffice B ON A.Id = B.Movie_id GROUP BY A.Director

--【难题】按导演分组计算销售总额,求出平均销售额冠军（统计结果过滤掉只有单部电影的导演，列出导演名，总销量，电影数量，平均销量)
SELECT 
 SUM(B.Domestic_sales + B.International_sales) sum_sale,
 A.Director, 
 COUNT(1) count,
 (SUM(B.Domestic_sales + B.International_sales) / COUNT(1)) avg_sale
FROM
 Movies A LEFT JOIN Boxoffice B ON A.Id = B.Movie_id GROUP BY A.Director
HAVING count > 1 
ORDER BY avg_sale DESC 
LIMIT 1

--【变态难】找出每部电影和单部电影销售冠军之间的销售差，列出电影名，销售额差额
-- 先求销售冠军
SELECT MAX(Domestic_sales + International_sales) FROM Boxoffice

-- 列处理
SELECT 
 (
 (SELECT MAX(Domestic_sales + International_sales) FROM Boxoffice) - 
 (B.Domestic_sales + B.International_sales)
 ) sale_diff,
 A.Title
FROM  Movies A LEFT JOIN Boxoffice B ON A.Id = B.Movie_id