数据库 CURD测试题【简单】

1.组合两个表

基本信息

表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 Person.FirstName,Person.LastName,Address.City,Address.State  
from Person left join Address on  Person.PersonId = Address.PersonId;

2.第二高的薪水

基本信息

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

Id	Salary
1	100
2	200
3	300

要求

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

SecondHighestSalary
200

答案

select (
    select DISTINCT  Salary 
    from Employee 
    ORDER BY Salary DESC limit 1 offset 1
) as SecondHighestSalary;

3.查找重复的电子邮箱

基本信息

编写一个 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;

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 e1.Name as Employee 
from Employee e1 join Employee e2  on e1.ManagerId = e2.Id and e1.Salary > e2.Salary

5.超过5名学生的课

信息：

有一个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;

6.有趣的电影

信息

某城市开了一家新的电影院，吸引了很多人过来看电影。该电影院特别注意用户体验，专门有个 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 id,movie, description, rating
from cinema
where description  !='boring' and id %2=1
order by rating desc;

7.交换工资（updeta,条件判断）

基本信息

给定一个 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=IF(sex='f','m','f');

8从不订购的客户

信息

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

Customers 表：

Id	Name
1	Joe
2	Henry
3	Sam
4	Max

Orders 表：

Id	CustomerId
1	3
2	1

要求

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

Customers
Henry
Max

答案

使用的not in

select Name as Customers
from Customers
where Customers.Id not in (select Customers.Id from Orders where Customers.Id = Orders.CustomerId);

使用的not exists

select Name as Customers
from Customers
where  not exists (select * from Orders where Customers.Id = Orders.CustomerId);

9.上升的温度(mysql函数to_days)

信息

给定一个 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 w1.Id 
from weather as w1,weather as w2
where w1.Temperature > w2.Temperature and to_days(w1.RecordDate)-to_days(w2.RecordDate)=1;

10.删除重复的邮箱

信息

编写一个 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.Id>p2.Id and p1.Email = p2.Email;

11.员工奖金(null的应用)

信息

select all employee’s name and bonus whose bonus is < 1000.

Table:Employee

empId	name	supervisor	salary
1	John	3	1000
2	Dan	3	2000
3	Brad	null	4000
4	Thomas	3	4000

empId is the primary key column for this table.
Table: Bonus

empId	bonus
2	500
4	2000

• empId is the primary key column for this table.

要求

Example ouput:

name	bonus
John	null
Dan	500
Brad	null

选出所有奖金<1000元的雇员姓名及奖金数额

答案：

select name, bonus
from Employee e left join Bonus b
on e.empId = b.empId
where bonus < 1000 or bonus is null

12.查找订单最多的用户