题目一:组合两个表
题目要求:
编写解决方案,报告
Person
表中每个人的姓、名、城市和州。如果personId
的地址不在Address
表中,则报告为null
。以 任意顺序 返回结果表。
表结构:
运行结果示例:
思路:
使用左连接连接两张表即可。
运行代码示例:
select firstName,lastName,city,state
from Person
left join Address
using(personId)
题目二:第二高的薪水
题目要求:
查询并返回
Employee
表中第二高的薪水 。如果不存在第二高的薪水,查询应该返回null(Pandas 则返回 None)
。
表结构:
运行结果示例:
思路:
拆解题目,题目要求找到第二高的薪水,那么我们需要使用order by子句对salary字段进行倒序排序,再使用limit子句限定找到拍第二个的数据。此外,需要注意题目要求如果不存在则返回null,因此我们需要使用ifnull()函数再次处理一下结果,如果结果为空则返回null。
运行代码示例:
select ifnull((select distinct salary from Employee order by salary desc limit 1,1),null) as SecondHighestSalary
题目三:分数排名
题目要求:
编写一个解决方案来查询分数的排名。排名按以下规则计算:
- 分数应按从高到低排列。
- 如果两个分数相等,那么两个分数的排名应该相同。
- 在排名相同的分数后,排名数应该是下一个连续的整数。换句话说,排名之间不应该有空缺的数字。
按
score
降序返回结果表。
表结构:
运行结果示例:
思路:
本题考察窗口函数。排名问题是SQL中经常遇到的一类问题,关于窗口函数的使用可以参考【SQL】一张学生表带你学会开窗函数-CSDN博客
根据题目要求,本题需要使用的是dense_rank()函数。需要注意的是,这里的别名rank需要用‘’括起来,因为rank是SQL中的关键字之一。
运行代码示例:
select score,dense_rank() over (order by score desc) as 'rank'
from scores
题目四:超过经理收入的员工
题目要求:
编写解决方案,找出收入比经理高的员工。
以 任意顺序 返回结果表。
表结构:
运行结果示例:
思路:
遇到复杂的题目可以先拆分题目,首先我们要找到员工与经理直接的对应关系,我们可以直接使用e1.managerId = e2.id这个条件进行表自连接,获得一张含有员工、员工收入、对应经理、经理收入的表,再使用where子句限定条件即可。
运行代码示例:
select e1.name as Employee
from Employee e1
join Employee e2
on e1.managerId = e2.id
where e1.salary > e2.salary
题目五:查找重复的电子邮件
题目要求:
编写解决方案来报告所有重复的电子邮件。 请注意,可以保证电子邮件字段不为 NULL。
以 任意顺序 返回结果表。
表结构:
运行结果示例:
思路:
本题考察了分组子句以及having子句的使用方法。我们按照email字段进行分组,统计每组的数量,将大于1的数据筛选出来即可。
注:该题限定了前提条件“电子邮件不包含大写字母”,若无此条件则不能直接使用该方法。
运行代码示例:
select email as Email
from Person
group by email
having count(email) > 1
题目六:从不订购的客户
题目要求:
找出所有从不点任何东西的顾客。
以 任意顺序 返回结果表。
表结构:
运行结果示例:
思路:
观察示例,可知本题我们直接去找不在orders表里的用户id对应的用户即可。
运行代码示例:
select name as Customers
from Customers
where id not in (select customerId from Orders)