LeetCode数据库刷题记录 简单部分

第一题 组合两个表|Combine Two Tables

表1: Person

列名	类型
PersonId	int
FirstName	varchar
LastName	varchar

PersonId 是上表主键
表2: Address

列名	类型
AddressId	int
PersonId	int
City	varchar
State	varchar

AddressId 是上表主键

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

解答：
select a.FirstName, a.LastName, b.City, b.State
from Person a
left join Address b
on a.PersonId = b.PersonId;

第二题 第二高的薪水|Second Highest Salary

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

Id	Salary
1	100
2	200
3	300

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

SecondHighestSalary
200

解答：
此题有两种解法。
需要注意到的是，如果没有这样的第二最高工资，简单把salary降序排序再用limit选出第二高的薪水被判断为 “错误答案”，因为本表可能只有一项记录。
由此有以下两种解法：

解法一：
把
SELECT DISTINCT
Salary AS SecondHighestSalary
FROM
Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1；

作为临时表，则答案为：

SELECT
(SELECT DISTINCT
Salary
FROM
Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1) AS SecondHighestSalary;

解法二：
使用 “IFNULL” 函数
SELECT
IFNULL(
(SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1),
NULL) AS SecondHighestSalary;

第三题 超过经理收入的员工|Employees Earning More Than Their Managers

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

Id	Name	Salary	ManagerId
1	Joe	70000	3
2	Henry	80000	4
3	Sam	60000	NULL
4	Max	90000	NULL

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

Employee
Joe

解法一：使用内连接查询
select
b.Name as Employee
from Employee a inner join Employee b
where a.Id=b.ManagerId
and
b.Salary>a.Salary;

解法二：套用子查询
select
a.Name as Employee
from Employee a
where a.Salary >
(
select
b.Salary
from Employee b
where b.Id = a.ManagerId
);

第四题 查找重复的电子邮箱|Duplicate Emails

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

示例：

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

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

Email
a@b.com

说明：所有电子邮箱都是小写字母。

解答：
解法一：
select
Email
from Person
group by Email
having count(Email)>1;
解法二：
将
select Email, count(Email) as num
from Person
group by Email;
作为临时表，可得

select Email from
(
select Email, count(Email) as num
from Person
group by Email
) as statistic
where num > 1

第五题 从不订购的客户|Customers Who Never Order

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

Customers 表：

Id	Name
1	Joe
2	Henry
3	Sam
4	Max

Orders 表：

Id	CustomerId
1	3
2	1

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

Customers
Henry
Max

解答：
解法一：
select
Name as Customers
from Customers
where Id
not in(
select
CustomerId
from Orders
);
解法二：
select
Name as Customers
from Customers
left join Orders
on Customers.Id = Orders.CustomerId
where Orders.CustomerId is null;

第六题 删除重复的电子邮箱|Delete Duplicate Emails

编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。

Id	Email
1	john@example.com
2	bob@example.com
3	john@example.com

Id 是这个表的主键。
例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:

Id	Email
1	john@example.com
2	bob@example.com

解答：
DELETE p1 FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id;

第七题 上升的温度|Rising Temperature

给定一个 Weather 表，编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 Id。

Id(INT)	RecordDate(DATE)	Temperature(INT)
1	2015-01-01	10
2	2015-01-02	25
3	2015-01-03	20
4	2015-01-04	30

例如，根据上述给定的 Weather 表格，返回如下 Id:

Id
2
4

解答：
select b.Id
from Weather a inner join Weather b
on DATEDIFF(b.RecordDate,a.RecordDate)=1
and a.Temperature<b.Temperature;

第八题 大的国家|Big Countries

这里有张 World 表

name	continent	area	population	gdp
Afghanistan	Asia	652230	25500100	20343000
Albania	Europe	28748	2831741	12960000
Algeria	Africa	2381741	37100000	188681000
Andorra	Europe	468	78115	3712000
Angola	Africa	1246700	20609294	100990000

如果一个国家的面积超过300万平方公里，或者人口超过2500万，那么这个国家就是大国家。

编写一个SQL查询，输出表中所有大国家的名称、人口和面积。

例如，根据上表，我们应该输出:

name	population	area
Afghanistan	25500100	652230
Algeria	37100000	2381741

解答：
select
name,population,area
from World
where population>25000000
or area>3000000;

第九题 超过5名学生的课|Classes More Than 5 Students

有一个courses 表 ，有: student (学生) 和 class (课程)。

请列出所有超过或等于5名学生的课。

例如,表:

student	class
A	Math
B	English
C	Math
D	Biology
E	Math
F	Computer
G	Math
H	Math
I	Math

应该输出:

class
Math

Note:
学生在每个课中不应被重复计算。
解答：
SELECT CLASS
FROM COURSES
GROUP BY CLASS
HAVING COUNT(DISTINCT STUDENT) >= 5;

第十题 有趣的电影|Not Boring Movies

某城市开了一家新的电影院，吸引了很多人过来看电影。该电影院特别注意用户体验，专门有个 LED显示板做电影推荐，上面公布着影评和相关电影描述。

作为该电影院的信息部主管，您需要编写一个 SQL查询，找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片，结果请按等级 rating 排列。

例如，下表 cinema:

id	movie	description	rating
1	War	great 3D	8.9
2	Science	fiction	8.5
3	irish	boring	6.2
4	Ice song	Fantacy	8.6
5	House card	Interesting	9.1

对于上面的例子，则正确的输出是为：

id	movie	description	rating
5	House card	Interesting	9.1
1	War	great 3D	8.9

解答：
select *
from cinema
where description != ‘boring’
and mod(id,2)=1
order by rating
desc;

第十一题 交换工资|Swap Salary

给定一个 salary 表，如下所示，有 m = 男性 和 f = 女性 的值。交换所有的 f 和 m 值（例如，将所有 f 值更改为 m，反之亦然）。要求只使用一个更新（Update）语句，并且没有中间的临时表。

注意，您必只能写一个 Update 语句，请不要编写任何 Select 语句。

例如：

id	name	sex	salary
1	A	m	2500
2	B	f	1500
3	C	m	5500
4	D	f	500

运行你所编写的更新语句之后，将会得到以下表:

id	name	sex	salary
1	A	f	2500
2	B	m	1500
3	C	f	5500
4	D	m	500

解答：
解法一：
UPDATE salary
SET
sex = CASE sex
WHEN ‘m’ THEN ‘f’
ELSE ‘m’
END;
解法二：
update salary
set
sex = if(sex = ‘m’,‘f’,‘m’);