DataWhale_sql Task06 综合练习

最新推荐文章于 2022-09-19 14:01:14 发布
最新推荐文章于 2022-09-19 14:01:14 发布
阅读量132 收藏
点赞数
分类专栏： DataWhale_sql 文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42044546/article/details/111822849
版权

练习一: 各部门工资最高的员工（难度：中等）

创建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    |
+----+----------+

编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

答：

Create table Employee(
Id Integer Not Null,
Name Varchar(32) Not Null,
Salary Integer Not Null,
DepartmentId Integer Not Null
);

Create table Department(
Id Integer Not Null,
Name Varchar(32) Not Null
);

INSERT INTO employee VALUES (1, 'Joe', 70000, 1),
			    (2, 'Henry', 80000, 2),
			    (3, 'Sam', 60000, 2),
                            (4, 'Max', 90000, 1); 
                            
INSERT INTO department VALUES (1, 'IT'),
			      (2, 'Sales');

 

select d.Name as Department,e.Name as Employee ,e.Salary from Employee e join Department d on e.DepartmentId = d.Id where (e.DepartmentId ,e.Salary) in (select DepartmentId ,max(Salary) as Salary  from Employee group by DepartmentId);

练习二: 换座位（难度：中等）

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。

其中纵列的id是连续递增的

小美想改变相邻俩学生的座位。

你能不能帮她写一个 SQL query 来输出小美想要的结果呢？

请创建如下所示seat表：

示例：

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

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

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

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

Create table If Not Exists seat(id int, studentvarchar(255));
Truncate table seat;
insert into seat (id, student) values ('1','Abbot');
insert into seat (id, student) values ('2','Doris');
insert into seat (id, student) values ('3','Emerson');
insert into seat (id, student) values ('4','Green');
insert into seat (id, student) values ('5','Jeames');

select s.id , s.student from
(
select id-1 as id ,student from seat wheremod(id,2)=0
union
select id+1 as id,student from seat wheremod(id,2)=1 and id !=(select count(*) from seat)
union
select id,student from seat where mod(id,2)=1and id = (select count(*) from seat)
) s order by id;

练习三: 分数排名（难度：中等）

编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

创建以下score表：

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

 

练习四：连续出现的数字（难度：中等）

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+

select distinct l1.Num as ConsecutiveNums
from 
Logs l1, 
Logs l2, 
Logs l3
where l1.Id = l2.Id-1
and l2.Id = l3.Id-1
and l1.Num=l2.Num
and l2.Num=l3.Num

 练习五：树节点 （难度：中等）

对于tree表，id是树节点的标识，p_id是其父节点的id。

+----+------+
| id | p_id |
+----+------+
| 1  | null |
| 2  | 1    |
| 3  | 1    |
| 4  | 2    |
| 5  | 2    |
+----+------+

每个节点都是以下三种类型中的一种：

  • Root: 如果节点是根节点。
  • Leaf: 如果节点是叶子节点。
  • Inner: 如果节点既不是根节点也不是叶子节点。

写一条查询语句打印节点id及对应的节点类型。按照节点id排序。上面例子的对应结果为：

+----+------+
| id | Type |
+----+------+
| 1  | Root |
| 2  | Inner|
| 3  | Leaf |
| 4  | Leaf |
| 5  | Leaf |
+----+------+

说明

  • 节点’1’是根节点，因为它的父节点为NULL，有’2’和’3’两个子节点。
  • 节点’2’是内部节点，因为它的父节点是’1’，有子节点’4’和’5’。
  • 节点’3’，‘4’，'5’是叶子节点，因为它们有父节点但没有子节点。

下面是树的图形：

    1         
  /   \ 
 2    3    
/ \
4  5

注意

如果一个树只有一个节点，只需要输出根节点属性。

 

CREATE TABLE tree(id INT,p_id INT);
 
INSERT INTO tree VALUES (1,NULL);
INSERT INTO tree VALUES (2,1);
INSERT INTO tree VALUES (3,1);
INSERT INTO tree VALUES (4,2);
INSERT INTO tree VALUES (5,2);

SELECT p_id AS f,COUNT(id) AS branch
FROM tree
GROUP BY p_id;
DELETE FROM tree WHERE id in (2,3,4,5);

SELECT T.id,(CASE WHEN T.p_id IS NULL THEN 'Root' WHEN P.branch >0 THEN 'Leaf' ELSE 'Inner' END) AS 'Type'
FROM tree T LEFT JOIN (SELECT p_id AS f,COUNT(id) AS branch FROM tree GROUP BY p_id) P
ON T.Id = P.f;

练习六：至少有五名直接下属的经理 （难度：中等）

Employee表包含所有员工及其上级的信息。每位员工都有一个Id，并且还有一个对应主管的Id（ManagerId）。

+------+----------+-----------+----------+
|Id    |Name 	  |Department |ManagerId |
+------+----------+-----------+----------+
|101   |John 	  |A 	      |null      |
|102   |Dan 	  |A 	      |101       |
|103   |James 	  |A 	      |101       |
|104   |Amy 	  |A 	      |101       |
|105   |Anne 	  |A 	      |101       |
|106   |Ron 	  |B 	      |101       |
+------+----------+-----------+----------+

针对Employee表，写一条SQL语句找出有5个下属的主管。对于上面的表，结果应输出：

+-------+
| Name  |
+-------+
| John  |
+-------+

注意:

没有人向自己汇报。

 

练习七: 分数排名 （难度：中等）

练习三的分数表，实现排名功能，但是排名需要是非连续的，如下：

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 3    |
| 3.65  | 4    |
| 3.65  | 4    |
| 3.50  | 6    |
+-------+------

练习八：查询回答率最高的问题 （难度：中等）

求出survey_log表中回答率最高的问题，表格的字段有：uid, action, question_id, answer_id, q_num, timestamp。

uid是用户id；action的值为：“show”， “answer”， “skip”；当action是"answer"时，answer_id不为空，相反，当action是"show"和"skip"时为空（null）；q_num是问题的数字序号。

写一条sql语句找出回答率最高的问题。

举例：

输入

uidactionquestion_idanswer_idq_numtimestamp
5show285null1123
5answer2851241241124
5show369null2125
5skip369null2126

输出

survey_log
285

说明

问题285的回答率为1/1，然而问题369的回答率是0/1，所以输出是285。

**注意：**最高回答率的意思是：同一个问题出现的次数中回答的比例。

练习九：各部门前3高工资的员工（难度：中等）

将项目7中的employee表清空，重新插入以下数据（其实是多插入5,6两行）：

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
+----+-------+--------+--------------+

编写一个 SQL 查询，找出每个部门工资前三高的员工。例如，根据上述给定的表格，查询结果应返回：

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

此外，请考虑实现各部门前N高工资的员工功能。

CREATE TABLE IF NOT EXISTS Employee(
		Id INT PRIMARY KEY,
		Name varchar(10) NOT NULL,
		Salary FLOAT NOT NULL,
		DepartmentId INT NOT NULL
)Engine=Innodb;
INSERT INTO Employee VALUES(1,'Joe',70000,1);
INSERT INTO Employee VALUES(2,'Henry',80000,2);
INSERT INTO Employee VALUES(3,'Sam',60000,2);
INSERT INTO Employee VALUES(4,'Max',90000,1);
INSERT INTO Employee VALUES(5,'Janet',69000,1);
INSERT INTO Employee VALUES(6,'Randy',85000,1);

CREATE TABLE IF NOT EXISTS Department(
		Id INT PRIMARY KEY,
		Name varchar(10) NOT NULL
)Engine=Innodb;
INSERT INTO Department VALUES(1,'IT');
INSERT INTO Department VALUES(2,'Sales');

SELECT D1.Name Department, E1.Name Employee,  E1.Salary  
FROM Employee E1, Employee E2, Department D1  
WHERE E1.DepartmentID = E2.DepartmentID  
AND E2.Salary >= E1.Salary   
AND E1.DepartmentID = D1.ID        
GROUP BY E1.Name  
HAVING COUNT(DISTINCT E2.Salary) <= 3  
ORDER BY D1.Name, E1.Salary DESC;

 

练习十：平面上最近距离 (难度: 困难）

point_2d表包含一个平面内一些点（超过两个）的坐标值（x，y）。

写一条查询语句求出这些点中的最短距离并保留2位小数。

|x   | y  |
|----|----|
| -1 | -1 |
|  0 |  0 |
| -1 | -2 |

最短距离是1，从点（-1，-1）到点（-1，-2）。所以输出结果为：

| shortest |

1.00

+--------+
|shortest|
+--------+
|1.00    |
+--------+

注意： 所有点的最大距离小于10000。

练习十一：行程和用户（难度：困难）

Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。

IdClient_IdDriver_IdCity_IdStatusRequest_at
11101completed2013-10-1
22111cancelled_by_driver2013-10-1
33126completed2013-10-1
44136cancelled_by_client2013-10-1
51101completed2013-10-2
62116completed2013-10-2
73126completed2013-10-2
821212completed2013-10-3
931012completed2013-10-3
1041312cancelled_by_driver2013-10-3

Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，Role 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。

+----------+--------+--------+
| Users_Id | Banned |  Role  |
+----------+--------+--------+
|    1     |   No   | client |
|    2     |   Yes  | client |
|    3     |   No   | client |
|    4     |   No   | client |
|    10    |   No   | driver |
|    11    |   No   | driver |
|    12    |   No   | driver |
|    13    |   No   | driver |
+----------+--------+--------+

写一段 SQL 语句查出2013年10月1日至2013年10月3日期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。

+------------+-------------------+
|     Day    | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 |       0.33        |
| 2013-10-02 |       0.00        |
| 2013-10-03 |       0.50        |
+------------+-------------------+
那一夜见红
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL任务3
linjiawen1996的博客
08-12 465
学习内容 数据导入导出 将之前创建的任意一张MySQL表导出,需要是CSV格式 再将CSV表导入数据库 作业 项目十: 各部门工资最高的员工（难度：中等） 创建Employee 表，包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。 ±—±------±-------±-------------+ | Id | Name | Salary | Dep...
mysql查询未讲课教师_mysql综合性练习
weixin_42208552的博客
01-28 2056
题目描述设定有一个数据库，里面有4张表：mysql学生表(student)课程表(course)成绩表(score)教师信息表(teacher)表结构以下：sql表一_学生表(student)属性名数据类型含 义snoint学号(主键)snamevarchar学生姓名ssexvarchar学生性别sbirthdaydate学生出生年月classidvarchar学生所在班级表二_课程表(cours...
MySQL 综合分析题_MySQL练习题：综合面试题
weixin_42395171的博客
01-27 229
student学生表sid 学生编号，字符串，主键sname 学生姓名，字符串sage 年龄，数值ssex 性别，字符串teacher教师表tid 教师编号，字符串，主键tname 教师姓名，字符串course课程表cid 课程编号，字符串，主键cname 课程名称，字符串tid 教师编号，字符串，外键sc成绩表sid 学生编号，字符串，外键cid 课程编号，字符串，外键score 成绩，数值1、...
MySQL经典练习50题
热门推荐
csdnluolei
10-29 1万+
这50道查询练习确实很经典，题是我在网上找的，SQL是我自己写的。发现问题的欢迎提出，有更好方法的，可以提出来大家共同学习。做完后你的SQL编写能力肯定有一个提升。 ---------创建数据库、表、插入数据---------------------- -- 建表 -- 学生表 CREATE TABLE Student( s_id VARCHAR(20) COMMENT '学生编号'...
综合练习打卡
u013084111的博客
12-27 201
题目：http://datawhale.club/t/topic/471 练习一: 各部门工资最高的员工（难度：中等） SELECT d.department_name, emplyee_name, max_salary FROM employee JOIN department d ON employee.department_id = d.department_id JOIN (SELECT department_id, MAX(salary) as max_salary FROM e
SQL经典练习题_sql练习_
10-03
本资料包中的"SQL经典练习题.sql练习."包含了个人收藏的一系列SQL题目和案例，这些内容常常在面试中出现，对提升SQL技能和准备面试具有很高的价值。 首先，我们要了解SQL的基本概念。SQL主要包括以下几个部分： 1....
SQL_sql语言练习_源码
10-02
【SQL_sql语言练习_源码】这个压缩包文件主要涵盖了SQL语言的学习与实践内容，尤其适合正在学习数据库的初学者进行参考。SQL，全称Structured Query Language，即结构化查询语言，是用于管理关系数据库的标准语言。...
PL_SQL经典练习.pdf
08-12
PL_SQL经典练习
3-SQL.rar_sql语句练习
最新发布
09-20
SQL语句练习、学生可以查看自学SQL相关语句的练习
My_SQL天池学校task_02
03-22
My_SQL天池学校task_02
MySQL管理器（练手项目十三）
11-27
实现了一个MySQL管理器，这个管理器中有多个功能，包括数据库元素的管理、数据浏览与SQL文件的导出和导出。 实现MySQL管理器功能并不困难，困难的是一些界面的交互，特别是表管理界面。 与一些流行的管理器有着部分的区别，需要用户选择MySQL的安装目录，目的为了使用MySQL的一些内置命令。 当用户需要导出SQL文件的时候，也可以使用程序来对数据库进行分析，再写到SQL文件中，这样也是一种实现途径。
SSM+MySQL小项目练习增删改查
08-04
整合spring+springMVC+mybatis+MySQL，练习框架，建议练习时间2小时
MySQL综合练习
Own_April的博客
06-04 433
MySQL综合练习 学生表：Students (Sno, Sname, Ssex , Sage, Sdept) ​ 学号，姓名，性别，年龄，所在系 Sno为主键 课程表：Course (Cno, Cname,) ​ 课程号，课程名 Cno为主键 学生选课表：SC (Sno, Cno, Score) ​ 学号，课程号，成绩 Sno,Cno为主键 用SQL语句创建学生表students，定义主键，姓名不能重名，性别只能输入男或女，所在系的
mysql 综合练习
m0_71917549的博客
09-19 430
(112,'高分子化学','化学','1996',25),(102,'微电子','计算机','1996',30),(111,'无机化学','化学','1995',29),(121,'统计数学','数学','1995',20),(131,'现代语言','中文','1996',20),(141,'国际贸易','经济','1997',30),(101,'软件','计算机','1995',20),('001','数学'),('002','计算机'),('003','化学'),('004','中文'),
mysql 综合练习（一）
m0_48930261的博客
08-07 1628
-- 创建部门表 DROP TABLE IF EXISTS dept; CREATE TABLE dept( -- 部门编号 deptno int PRIMARY KEY, -- 部门名称 dname VARCHAR(14), -- 部门所在地 loc VARCHAR(13) ); -- 向部门表插入数据 INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK'); INSERT INTO dept V
mysql项目练习_mysql练习项目
weixin_29353431的博客
01-21 630
SELECT * FROM `category`//最新外卖显示SELECT max(Goodsld)FROM goods ;SELECT * FROM goods WHERE Goodsld in (SELECT max(Goodsld)FROM goods);//畅销外卖显示SELECT Goodsid FROM orderdetails WHERE qty in(SELECT MAX(qty...
达梦数据库DM_SQL语言全面指南：数据操作与管理详解
《达梦数据库_SQL语言手册》是一份全面指南，介绍了达梦数据库管理系统（DM）所使用的SQL语言特性、语法和功能。该手册覆盖了从基本概念到高级操作的广泛内容，包括： 1. **DM_SQL语言介绍**：阐述了DM_SQL的特点，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值