模板

表 1: Person

1

表 2: Address

1

编写一个 SQL 查询

1
FirstName, LastName, City, State

[ 解题 ]

1

经典题目

组合两个表

表 1: Person

1
2
3
4
5
6
7
8
+-------------+---------+
| 列名         | 类型     |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId 是上表主键

表 2: Address

1
2
3
4
5
6
7
8
9
+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId 是上表主键

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：

1
FirstName, LastName, City, State

[ 解题 ]

1
2
3
4
select FirstName, LastName, City, State from Person
left join Address on Address.PersonId = Person.PersonId;

-- left join Address using(PersonId)

第二高的薪水

编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。

1
2
3
4
5
6
7
+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

例如上述 Employee 表，SQL 查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

1
2
3
4
5
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

[ 解题 ]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# Write your MySQL query statement below

# 方法一
select MAX(Salary) as SecondHighestSalary from Employee WHERE Salary < (select max(Salary) from Employee)；

# 方法二

-- SELECT
--     IFNULL(
--       (SELECT DISTINCT Salary
--        FROM Employee
--        ORDER BY Salary DESC
--         LIMIT 1 OFFSET 1),
--     NULL) AS SecondHighestSalary；

超过经理收入的员工

Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。

1
2
3
4
5
6
7
8
+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+

给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。

1
2
3
4
5
+----------+
| Employee |
+----------+
| Joe      |
+----------+

[ 解题 ]

1
2
3
4
# Write your MySQL query statement below

select A.Name as Employee  from Employee AS A,Employee AS B 
WHERE A.ManagerId = B.Id And A.Salary > B.Salary;

查找重复的电子邮箱

编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。

1
2
3
4
5
6
7
8
9
示例：

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

根据以上输入，你的查询应返回以下结果：

1
2
3
4
5
6
7

+---------+
| Email   |
+---------+
| a@b.com |
+---------+
说明：所有电子邮箱都是小写字母。

[ 解题 ]

1
2
3
4
5
6
7
# Write your MySQL query statement below

-- select Email, count(*) as Num  from Person group by Email;

-- select Email from t where Num >1

select Email from (select Email, count(*) as Num  from Person group by Email) t where t.Num > 1;

从不订购的客户

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

Customers 表：

1
2
3
4
5
6
7
8
+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Orders 表：

1
2
3
4
5
6
+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

例如给定上述表格，你的查询应返回：

1
2
3
4
5
6
+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

[ 解题 ]

1
2
3
4
5
6
7
8
9
# Write your MySQL query statement below

-- select Name as Customers from Customers Where Id not in (select Orders.CustomerId from Orders );

select c.Name as Customers from Customers c

left join Orders o on c.Id = O.CustomerId
Where o.CustomerId is null;

取得每个部门最高薪水的人员名称

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 分析
-- 第一步： 求出每个部门最高的薪水
SELECT 
	e.deptno,MAX(e.sal) as maxsal
FROM emp e
GROUP BY 
	e.deptno;
-- 将以上查询结果当成一个临时表 t(deptno,maxsal)

-- 最高薪水的人员名称（两张表的连接）
SELECT 
	e.deptno,e.ename,t.maxsal,e.sal
FROM 
	(SELECT e.deptno,MAX(e.sal) AS maxsal 
     FROM emp e 
     GROUP BY e.deptno;) t
JOIN emp e ON t.deptno = e.deptno
WHERE t.maxsal = e.sal 
ORDER BY e.deptno;

那些人的薪水在部门的平均薪水之上

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 分析 
-- 第一步，求出部门的平均薪水
SELECT 
	e.deptno, AVG(e.sal) AS avgsal
FROM 
	emp e
GROUP BY e.deptno; 
-- 将以上查询结果当成一个临时表 t(deptno,avgsal)

-- 薪水在部门的平均薪水之上的人员（两张表的连接）
SELECT e.deptno,e.ename,e.sal,t.avgsal 
FROM 
	(SELECT e.deptno, AVG(e.sal) AS avgsal 
	 FROM emp e 
     GROUP BY e.deptno;) t
JOIN emp e ON t.deptno = e.deptno
WHERE e.sal > t.avgsal
ORDER BY e.deptno;