数据库刷题

第一章 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	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

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	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

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	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

第二章 算法与数据结构刷题