数据蛙就业班sql第一阶段测试题

• 基础测试

1 Employee 表(表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id):

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

Department 表(包含公司所有部门的信息):

Id	Name
1	IT
2	Sales

-- 创建 Employee 表并插入数据
create table Employee (
id int(20),
name varchar(20),
salary int(20),
departmentid int(20)
);

insert into Employee values
(1,"Joe",70000,1),
(2,"Henry",80000,2),
(3,"Sam",60000,2),
(4,"Max",90000,1);

-- 创建 department 表
create table Department (
id int(20),
name varchar(20)
);

insert into Department values
(1,"IT"),
(2,"Sales");

编写一个 SQL 查询，找出每个部门工资最高的员工。例如：根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。
(知识点:group by ,max函数)

查询结果应为:

Department	Employee	Salary
IT	Max	90000
Sales	Henry	80000

2 编写一个 SQL 查询，查找 customer 表中所有重复的电子邮箱（所有电子邮箱都是小写字母）。
(知识点:group by ,count())

customer表:

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

--创建customer表并插入数据
create table customer(
Id int(10),
Email varchar(20)
);

insert into Person values
(1,'a@b.com'),
(2,'c@d.com'),
(3,'a@b.com');

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

Email
a@b.com

3 某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。
(知识点:可用子查询、where子句)

Customers 表：

Id	Name
1	Joe
2	Henry
3	Sam
4	Max

Orders 表：

Id	CustomerId
1	3
2	1

--创建Customers表
create table Customers( 
Id int(10),
 Name varchar(20)
);

insert into Customers values 
(1,'Joe'),
(2,'Henry'),
(3,'Sam'),
(4,'Max'); 

--创建Orders表
create table Orders(
 Id int(10),
CustomerId int(10)
);

insert into Orders values 
(1,3),
(2,1);

根据上述数据表，你的查询应返回：

Customers
Henry
Max

4 编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。
(知识点:可用case when、order by)

Scorses 表:

Id	Score
1	3.50
2	3.65
3	4.00
4	3.85
5	4.00
6	3.65

--创建Scores表
create table Scores( 
id varchar(20), 
score float(4,2) 
);

insert into scores values 
(1,3.5), 
(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

5 小美是一所中学的老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。
其中纵列的 id 是连续递增的
小美想改变相邻俩学生的座位。
(知识点:可用case when 分析学生人数为奇、偶的两种条件)

请编写一个 SQL 查询语句 来输出小美想要的结果:
seat 表：

id	student
1	Abbot
2	Doris
3	Emerson
4	Green
5	Jeames

--创建seat表
create table seat 
( id int(20),
 student varchar(20)); 

insert into seat values 
(1,'Abbot'),
(2,'Doris'),
(3,'Emerson'),
(4,'Green'),
 (5,'Jeames');

假如数据输入的是上表，则输出结果如下：

id	student
1	Doris
2	Abbot
3	Green
4	Emerson
5	Jeames

注意：如果学生人数是奇数，则不需要改变最后一个同学的座位。

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

Employee 表:

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

--创建Employee表
create table Employee
( Id int(10), 
Name varchar(20),
Salary int(20), 
ManagerId int(10));

insert into Employee values 
(1,'Joe',70000,3), 
(2,'Henry',80000,4), 
(3,'Sam',60000,Null), 
(4,'Max',90000,Null);

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

Employee
Joe

• 进阶测试:

X 市建了一个新的体育馆，每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、 人流量 (people)。
请编写一个查询语句，找出人流量的高峰期。高峰期时，至少连续三行记录中的人流量不少于100。
例如，表 stadium：

id	visit_date	people
1	2017-01-01	10
2	2017-01-02	109
3	2017-01-03	150
4	2017-01-04	99
5	2017-01-05	145
6	2017-01-06	1455
7	2017-01-07	199
8	2017-01-08	188

--创建表
create table stadium 
( id int(20), 
visit_date date, 
people int(20)); 

insert into stadium values 
(1,'2017-01-01',10), 
(2,'2017-01-02',109), 
(3,'2017-01-03',150), 
(4,'2017-01-04',99), 
(5,'2017-01-05',145), 
(6,'2017-01-06',1455), 
(7,'2017-01-07',199), 
(8,'2017-01-08',188);

对于上面的示例数据，输出为：

id	visit_date	people
5	2017-01-05	145
6	2017-01-06	1455
7	2017-01-07	199
8	2017-01-08	188

提示：每天只有一行记录，日期随着 id 的增加而增加。

• 挑战面试题:

数据集：https://pan.baidu.com/s/1seBnmi9t4_tlJn0yDSAiDw&shfl=sharepset

-- 创建 userinfo表
create table userinfo(
uid varchar(10),
app_name varchar(20),
duration int(10),
times int(10),
dayno varchar(30)
);

--导入数据
load data infile 'D:/d.csv'
into table userinfo
fields terminated by ','
ignore  1 lines;

(知识点:date_format用法，sum()结合条件语句的用法，百分比符号concat添加)

参考答案