2021-04-05

2024-01-23 16:36:52

## **多表练习**

![img](https://pic1.zhimg.com/80/v2-943e3c507bb66832445e887246ca2f20_720w.jpg)

**SQL Lesson 6: 用JOINs进行多表联合查询**

Table: Movies (Read-Only)

| Id | Title | Director | Year | Length_minutes |
| ---- | ------------------- | -------------- | ---- | -------------- |
| 1 | Toy Story | John Lasseter | 1995 | 81 |
| 2 | A Bug's Life | John Lasseter | 1998 | 95 |
| 3 | Toy Story 2 | John Lasseter | 1999 | 93 |
| 4 | Monsters, Inc. | Pete Docter | 2001 | 92 |
| 5 | Finding Nemo | Finding Nemo | 2003 | 107 |
| 6 | The Incredibles | Brad Bird | 2004 | 116 |
| 7 | Cars | John Lasseter | 2006 | 117 |
| 8 | Ratatouille | Brad Bird | 2007 | 115 |
| 9 | WALL-E | Andrew Stanton | 2008 | 104 |
| 10 | Up | Pete Docter | 2009 | 101 |
| 11 | Toy Story 3 | Lee Unkrich | 2010 | 103 |
| 12 | Cars 2 | John Lasseter | 2011 | 120 |
| 13 | Brave | Brenda Chapman | 2012 | 102 |
| 14 | Monsters University | Dan Scanlon | 2013 | 110 |

Table: Boxoffice (Read-Only)

| Movie_id | Rating | Domestic_sales | International_sales |
| -------- | ------ | -------------- | ------------------- |
| 5 | 8.2 | 380843261 | 555900000 |
| 14 | 7.4 | 268492764 | 475066843 |
| 8 | 8 | 206445654 | 417277164 |
| 12 | 6.4 | 191452396 | 368400000 |
| 3 | 7.9 | 245852179 | 239163000 |
| 6 | 8 | 261441092 | 370001000 |
| 9 | 8.5 | 223808164 | 297503696 |
| 11 | 8.4 | 415004880 | 648167031 |
| 1 | 8.3 | 191796233 | 170162503 |
| 7 | 7.2 | 244082982 | 217900167 |
| 10 | 8.3 | 293004164 | 438338580 |
| 4 | 8.1 | 289916256 | 272900000 |
| 2 | 7.2 | 162798565 | 200600000 |
| 13 | 7.2 | 237283207 | 301700000 |

Table(表）: Movies 全表查看

1. 找到所有电影的国内`Domestic_sales`和国际销售额

```sql
SELECT * FROM movies
inner join Boxoffice
on Movies.id=Boxoffice.Movie_id
```

\2. 找到所有国际销售额比国内销售大的电影

```sql
SELECT * FROM movies
inner join Boxoffice
on Movies.id=Boxoffice.Movie_id
where International_sales > Domestic_sales
```

\3. 找出所有电影按市场占有率`rating`倒序排列

```sql
SELECT * FROM movies
inner join Boxoffice
on Movies.id=Boxoffice.Movie_id
order by Rating desc
```

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

```sql
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`)名字

```sql
SELECT DISTINCT building FROM employees WHERE Building
```

\2. 找到所有办公室和它们的最大容量

```sql
SELECT * FROM Buildings
```

\3. 找到所有办公室里的所有角色（包含没有雇员的）,并做唯一输出(`DISTINCT`)

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

\4. 找到所有有雇员的办公室(`buildings`)和对应的容量

```sql
SELECT DISTINCT Building,Capacity FROM Employees INNER JOIN Buildings
ON employees.Building=Buildings.Building_name;
```

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

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

```sql
SELECT Role, Name FROM Employees
WHERE Building IS NULL;
```

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

```sql
SELECT DISTINCT Building_name
FROM Buildings LEFT JOIN Employees
ON Building_name = Building
WHERE Role IS NULL;
```

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

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

```sql
SELECT Id,Title,
(b.domestic_sales+b.international_sales)/1000000 AS Total
FROM Movies m
LEFT JOIN Boxoffice b
ON m.id=b.movie_id;
```

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

```sql
SELECT Id,Title,Rating*10 AS Market_indices
FROM Movies m
LEFT JOIN Boxoffice b
ON m.id=b.movie_id;
```

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

```sql
SELECT Id,Title,Year
FROM Movies
WHERE Year%2=0
```

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

```sql
SELECT Title,(b.Domestic_sales+b.International_sales)/m.Length_minutes
AS Per_sales
FROM Movies m LEFT JOIN Boxoffice b on m.Id=b.Movie_id
WHERE Director='John Lasseter'
ORDER BY Per_sales DESC LIMIT 3
```

\5. 电影名最长的3部电影和他们的总销量是多少，使用函数（length()）

```sql
SELECT m.title,length(Title),
(b.domestic_sales+b.international_sales) as Sum_sales
FROM Movies m
LEFT JOIN Boxoffice b
ON m.id=b.movie_id
ORDER BY length(Title) DESC
LIMIT 3;
```

**SQL Lesson 10: 在查询中进行统计I (Pt. 1)，常用函数有count,MAX,MIN,AVG,SUM**

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

```sql
SELECT Name, MAX(Years_employed) FROM Employees;
```

\2. 按角色(`Role`)统计一下每个角色的平均就职年份

```sql
SELECT Role,AVG(Years_employed)
FROM Employees GROUP BY Role;
```

\3. 按办公室名字总计一下就职年份总和

```sql
SELECT Building, SUM(Years_employed)
FROM Employees GROUP BY Building;
```

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

```sql
SELECT Building,COUNT(*)
FROM Employees
WHERE Building GROUP BY Building;
```

\5. 就职1,3,5,7年的人分别占总人数的百分比率是多少(给出年份和比率"50%" 记为 50)

```sql
SELECT Years_employed,count()*100 /
(SELECT count(Name) FROM Employees) as Ratio
FROM employees
WHERE Years_employed in(1,3,5,7)
GROUP BY Years_employed
```

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

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

```sql
SELECT COUNT() FROM Employees WHERE Role='Artist';
```

\2. 按角色统计一下每个角色的雇员数量

```sql
SELECT Role, COUNT() FROM Employees GROUP BY Role;
```

\3. 计算出Engineer角色的就职年份总计

```sql
SELECT SUM(Years_employed) FROM Employees
WHERE Role='Engineer';
```

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

```sql
SELECT Role,CASE when Building is NOT NULL
THEN '1' ELSE '0' END
AS Have,COUNT(Name)
FROM Employees
GROUP BY Role,Have;
```

\5. 按角色和就职年份统计人数,年份按0-3，3-6，6-9这种阶梯分组，最后按角色+阶梯分组排序

```sql
SELECT Role,Years_employed/3 AS Year,
COUNT(Name) FROM Employees
GROUP BY Role,Year
ORDER BY Year ASC;
```

**SQL Lesson 12: 查询执行顺序**

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

```sql
SELECT Director,COUNT(Title)
FROM Movies GROUP BY Director;
```

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

```sql
SELECT Director,SUM(b.Domestic_sales+b.International_sales)
AS Sum_Sales
FROM Movies m LEFT JOIN Boxoffice b
ON m.Id = b.Movie_id
GROUP BY Director;
```

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

```sql
SELECT director,SUM(b.domestic_sales+b.international_sales)
as Sum_sales,
count(title) as Total,
sum(b.domestic_sales+b.international_sales)/count(title)
AS average FROM Movies m
LEFT JOIN Boxoffice b
ON m.Id = b.Movie_id
GROUP BY Director
HAVING COUNT(Title) > 1
ORDER BY Average DESC
LIMIT 1;
```

\4. 找出每部电影和单部电影销售冠军之间的销售差，列出电影名，销售额差额

```sql
SELECT Title, (SELECT MAX(Domestic_sales+International_sales)
FROM Boxoffice) - SUM(Domestic_sales+International_sales)
AS Diff FROM Movies INNER JOIN Boxoffice
ON Movies.ID = Boxoffice.Movie_id
GROUP BY Title;
```

码农公寓

相关文章