第一章 sql刷题
1.如何查找第2高的数据?
1) limit字句的用法
2) ifnull的用法
题目:编写一个 SQL 查询,获取 Employee 表中第二高的薪水(Salary) 。
Id Salary 1 100 2 200 3 300 例如上述 Employee 表,SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水,那么查询应返回 null。
SecondHighestSalary 200 答案:
select ifNull((select Distinct Salary from Employee order by Salary Desc limit 1,1),null) as SecondHighestSalary
2.如何查找第N高的数据?
题目:编写一个 SQL 查询,获取 Employee 表中第 n 高的薪水(Salary)。
Id Salary 1 100 2 200 3 300 例如上述 Employee 表,n = 2 时,应返回第二高的薪水 200。如果不存在第 n 高的薪水,那么查询应返回 null。
getNthHighestSalary(2) 200 答案:
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN set N = N - 1; if N < 0 then RETURN null; else RETURN ( \# Write your MySQL query statement below. select ifNull((select Distinct Salary from Employee order by Salary Desc limit N,1) ,null) as getNthHighestSalary ); end if; END
3.编写一个 SQL 查询来实现分数排名。
如果两个分数相同,则两个分数排名(Rank)相同。请注意,平分后的下一个名次应该是下一个连续的整数值。换句话说,名次之间不应该有“间隔”。
Id Score 1 3.50 2 3.65 3 4.00 4 3.85 5 4.00 6 3.65 例如,根据上述给定的 Scores 表,你的查询应该返回(按分数从高到低排列):
Score Rank 4.00 1 4.00 1 3.85 2 3.65 3 3.65 3 3.50 4 select score as "Score",dense_rank() over(order by score desc) as "RANK" from Scores
4.超过经理薪资的员工
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 a.Name AS "Employee" from Employee as a,Employee as b where a.ManagerId=b.Id and a.Salary>b.Salary
5.编写一个 SQL 查询,查找 Person
表中所有重复的电子邮箱
示例:
Id 1 a@b.com 2 c@d.com 3 a@b.com 根据以上输入,你的查询应返回以下结果:
a@b.com 说明:所有电子邮箱都是小写字母。
select email from person group by email having count(email)>1
6.某网站包含两个表,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 Customers.Id not in ( select distinct CustomerId from Orders )
7.部门工资最高的员工
Employee 表包含所有员工信息,每个员工有其对应的 Id, salary 和 department Id。
Id Name Salary DepartmentId 1 Joe 70000 1 2 Jim 90000 1 3 Henry 80000 2 4 Sam 60000 2 5 Max 90000 1 Department 表包含公司所有部门的信息。
Id Name 1 IT 2 Sales 编写一个 SQL 查询,找出每个部门工资最高的员工。对于上述表,您的 SQL 查询应返回以下行(行的顺序无关紧要)。
Department Employee Salary IT Max 90000 IT Jim 90000 Sales Henry 80000 # Write your MySQL query statement below select Department.Name as "Department", Employee.Name as "Employee", Employee.Salary as "Salary" from Employee inner join Department on Employee.DepartmentId=Department.Id where (Employee.DepartmentId,Employee.Salary) in( select Employee.DepartmentId,max(Employee.Salary) from Employee GROUP BY DepartmentId )
注意两表连接的时候不是left join,应该是inner join
8.部门工资前三高的所有员工
Employee 表包含所有员工信息,每个员工有其对应的工号 Id,姓名 Name,工资 Salary 和部门编号 DepartmentId 。
Id Name Salary DepartmentId 1 Joe 85000 1 2 Henry 80000 2 3 Sam 60000 2 4 Max 90000 1 5 Janet 69000 1 6 Randy 85000 1 7 Will 70000 1 Department 表包含公司所有部门的信息。
Id Name 1 IT 2 Sales 编写一个 SQL 查询,找出每个部门获得前三高工资的所有员工。例如,根据上述给定的表,查询结果应返回:
Department Employee Salary IT Max 90000 IT Randy 85000 IT Joe 85000 IT Will 70000 Sales Henry 80000 Sales Sam 60000 SELECT B.Name AS Department, A.Name AS Employee, A.Salary FROM (SELECT DENSE_RANK() OVER (partition by DepartmentId order by Salary desc) AS ranking,DepartmentId,Name,Salary FROM Employee) AS A JOIN Department AS B ON A.DepartmentId=B.id WHERE A.ranking<=3
row_number(),rank(),dense_rank()的区别
9.删除重复的电子邮箱
编写一个 SQL 查询,来删除 Person 表中所有重复的电子邮箱,重复的邮箱里只保留 Id 最小 的那个。
Id 1 john@example.com 2 bob@example.com 3 john@example.com Id 是这个表的主键。
例如,在运行你的查询语句之后,上面的 Person 表应返回以下几行:
Id 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
10.上升的温度
编写一个 SQL 查询,来查找与之前(昨天的)日期相比温度更高的所有日期的 id 。
返回结果 不要求顺序 。
查询结果格式如下例:
Weather
id recordDate Temperature 1 2015-01-01 10 2 2015-01-02 25 3 2015-01-03 20 4 2015-01-04 30 Result table:
id 2 4 2015-01-02 的温度比前一天高(10 -> 25)
2015-01-04 的温度比前一天高(20 -> 30)select w1.id from Weather w1,Weather w2 where DateDiff(w1.recordDate,w2.recordDate)=1 and w1.Temperature>w2.Temperature
比较日期 使用DateDiff函数
11.删除重复的电子邮箱
编写一个 SQL 查询,来删除 Person 表中所有重复的电子邮箱,重复的邮箱里只保留 Id 最小 的那个。
Id 1 john@example.com 2 bob@example.com 3 john@example.com Id 是这个表的主键。
例如,在运行你的查询语句之后,上面的 Person 表应返回以下几行:
Id 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