Task06：综合练习

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

练习一 各部门工资最高的员工

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

使用代码如下：

CREATE TABLE Employee
(Id CHAR(1) not NUll,
NAME VARCHAR(8) not null,
Salary char(5) not null,
DeparmenrId INTEGER not NULL
);
INSERT INTO employee VALUES('1', 'Joe','70000',1),
('2','Henry','80000',2),
('3','Sam','60000',2),
('4','Max','90000',1);

得到：
在这里插入图片描述

创建Department 表，包含公司所有部门的信息。

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

使用代码如下：

CREATE TABLE Department
(Id CHAR(1) NOT NULL,
NAME VARCHAR(8) NOT NULL
);
INSERT INTO department VALUES('1','IT'),
('2','Sales');

得到如图：
在这里插入图片描述

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

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

代码如下：

SELECT Department.Name as Department, 
Employee.Name as Employee, 
MAX(Employee.Salary) as Salary 
FROM Employee 
LEFT JOIN Department 
ON Employee.DeparmenrId = Department.Id 
GROUP BY Employee.DeparmenrId;

另一种代码：

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

得到：
在这里插入图片描述

练习二: 换座位

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。
其中纵列的id是连续递增的
小美想改变相邻俩学生的座位。
你能不能帮她写一个 SQL query 来输出小美想要的结果呢？
请创建如下所示seat表：

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

使用代码：

CREATE TABLE seat
(id char(1) NOT NULL,
student VARCHAR(8) NOT NULL
);
INSERT into seat VALUES('1','Abbot'),
('2','Doris'),
('3','Emersom'),
('4','Green'),
('5','Jeames');

得到：
在这里插入图片描述
假如数据输入的是上表，则输出结果如下：

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

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

select 
(case
      when mod(id,2)!=0 and id!=counts then id+1
      when mod(id,2)!=0 and id=counts then id
      else id-1 end)
			as id,student
      from seat,
			(select count(*)as counts from seat)as seat_counts
                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  |
+----+-------+

使用代码

CREATE TABLE scroe
(Id CHAR(1) NOT NULL,
Score FLOAT(3,2) NOT NULL
);
INSERT INTO scroe VALUES('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,
    dense_rank() over(order by Score desc) as 'Rank'
from Scroe

得到：
在这里插入图片描述

练习四：连续出现的数字

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

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

使用代码

Create table  Logs 
(Id int, Num int);
insert into Logs  values ('1', '1'),
('2', '1'),
('3', '1'),
('4', '2'),
('5', '1'),
('6', '2'),
('7', '2');

得到
在这里插入图片描述
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

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

使用代码：

select distinct Num as ConsecutiveNums
from (
  select Num,
    case
      when @prev = Num then @count := @count + 1
      when (@prev := Num) is not null then @count := 1
    end
    as CNT
  from Logs, (select @prev := null,@count := null)as t
    )
as temp
where
    temp.CNT >= 3

得到
在这里插入图片描述

练习五：树节点

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

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

使用代码

CREATE TABLE tree
(id char(1) not null,
p_id CHAR(1)
);
INSERT INTO tree VALUES('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 |
+----+------+

代码如下：

SELECT
   DISTINCT t1.id AS "id",
   CASE WHEN t1.p_id IS NULL THEN "Root"
        WHEN t2.id IS NULL THEN "Leaf"
   ELSE "Inner"
   END
       AS "Type"
FROM
   tree t1
LEFT JOIN tree t2 ON t1.id=t2.p_id
ORDER BY id;

得到：
在这里插入图片描述

说明

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

    1         
  /   \ 
 2    3    
/ \
4  5

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

练习六：至少有五名直接下属的经理

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

注意:

没有人向自己汇报。

SELECT
   name
FROM employee
JOIN
   (SELECT managerID,
          COUNT(DISTINCT id) as cnt
   FROM employee
   GROUP BY managerID
   HAVING cnt > 4) temp
ON id = temp.managerID;

练习七: 分数排名

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

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

select Score,rank() over(order by Score desc) as Rank
from Scores

练习八：查询回答率最高的问题

求出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语句找出回答率最高的问题。

举例：

uid	  action	question_id	answer_id	q_num	timestamp
5	    show	285	             null	   1	123
5	   answer	285	           124124	   1	124
5	    show	369	             null	   2	125
5	    skip	369	             null	   2	126


survey_log
285

说明

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

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

SELECT question_id as survey_log
FROM
(
SELECT question_id,
SUM(case when action = “answer” THEN 1 ELSE 0 END) as num_answers,
SUM(case when action = “show” THEN 1 ELSE 0 END) as num_shows
FROM survey_log
GROUP BY question_id
) as tbl
ORDER BY (num_answers / num_shows) DESC
LIMIT 1;

练习九：各部门前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高工资的员工功能。

select Department.Name,t.Name,t.Salary from
(select Id,Name,DepartmentId,salary,row_number() over (partition by DepartmentId order by Salary DESC) as ‘rank’
from employee) t
left join Department
on t.DepartmentID = Department.id
where t.rank<=3

练习十：平面上最近距离

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。

select min(round(sqrt(pow(d1.x-d2.x,2)+pow(d1.y-d2.y,2)),2)) as shortest
from point_2d d1,point_2d d2
where concat(d1.x,d1.y)!= concat(d2.x,d2.y)

练习十一：行程和用户

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

Id	Client_Id	Driver_Id	City_Id		     	Status			         Request_at
1	    1	          10		1			     completed			     2013-10-1
2   	2	          11		1			     cancelled_by_driver	 2013-10-1
3	    3	          12		6			     completed			     2013-10-1
4   	4	          13		6			     cancelled_by_client	 2013-10-1
5	    1	          10		1			     completed			     2013-10-2
6   	2		      11		6			     completed				 2013-10-2
7   	3		      12		6			     completed				 2013-10-2
8   	2		      12	    12			     completed			     2013-10-3
9   	3		      10		12			     completed				 2013-10-3
10	    4		      13	    12			     cancelled_by_driver	 2013-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        |
+------------+-------------------+

代码如下：

SELECT T.request_at AS Day,
ROUND(
SUM(IF(T.STATUS = ‘completed’,0,1))
/
COUNT(T.STATUS),2) AS Cancellation Rate
FROM Trips AS T
JOIN Users AS U1 ON (T.client_id = U1.users_id AND U1.banned =‘No’)
JOIN Users AS U2 ON (T.driver_id = U2.users_id AND U2.banned =‘No’)
WHERE T.request_at BETWEEN ‘2013-10-01’ AND ‘2013-10-03’
GROUP BY T.request_at
鲍Wei
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
task06任务打卡
qq_41834327的博客
07-05 254
实现基于图同构网络的图标正网络的实现的步骤主要是：首先计算得到节点特征，然后需要对图上各个节点的表征作图池化来得到图的表征。 #图表征模块：通过对图上的节点作节点嵌入从而得到节点表征，然后通过对节点表征作图池化来得到图的表征信息，最后则通过使用一层线性变换来获取图的表征信息。 import torch from torch import nn from torch_geometric.nn import global_add_pool, global_mean_pool, global_max_pool
nnUnet使用
qq_62798397的博客
01-19 817
paddleSeg的MedicalSeg组件里nnUnet使用基础方法
SQL语句
Marlborough
03-16 659
sql join默认是inner join182. Duplicate Emails统计输出重复的Emailselect Email from Person group by Email having count(*) > 1;181. Employees Earning More Than Their Managers一个表格,让员工比老板拿的多，//Where: select e1.Name
Leetcode SQL
kelly_fumiao的博客
05-01 373
Content1. Rank problem 1. Rank problem 177. Nth Highest Salary CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN # DECLARE M INT; # SET M = N-1; RETURN ( # Write your MySQL qu...
Pandas数据分析 ——Task06：综合练习
nanashi_F的博客
05-01 639
Pandas数据分析 ——Task06：综合练习 | 一、2002 年-2018 年上海机动车拍照拍卖 | 二、2007 年-2019 年俄罗斯机场货运航班运载量 | 三、新冠肺炎在美国的传播
【学习笔记】阿里云天池龙珠计划SQL训练营-Task06：综合练习题-10道经典题目
zhang0yi8的博客
06-26 258
学习阶段习题集
Task05：综合练习
fire_i_ce的博客
07-01 288
问题1 (1) 请删除最后一列为缺失值的行，并求所有在杭州发货的商品单价均值。 (2) 商品标题带有“嘉兴”但发货地却不在嘉兴的商品有多少条记录? (3) 请按照分位数将价格分为“高、较高、中、较低、低”5 个类别，再将 类别结果插入到标题一列之后，最后对类别列进行降序排序。 (4) 付款人数一栏有缺失值吗?若有则请利用上一问的分类结果对这些缺失 值进行合理估计并填充。 (5) 请将数据后四列合并为如下格式的 Series:商品发货地为 ××，店铺为 ××，共计 ×× 人付款，单价为 ××。 (
Pandas_task06 综合练习
01-20
第六次-大作业 一、2002 年-2018 年上海机动车拍照拍卖 df_car = pd.read_csv('数据集/2002年-2018年上海机动车拍照拍卖.csv') ##1 df = df_car.copy() df['percent'] = df.apply(lambda x:x['Total number of ...
组队学习6：综合题（一）（基于Python）
01-20
组队学习介绍   最近事情太多（以及人太懒），好久没有空更新博客，遂报名公众号Datawhale的Pandas学习...Task06：综合练习（1天）   学习小组的参考资料是Datawhale-GYH助教撰写的1。 本博文的笔记概览如下所示：
【JDBC】JDBC和连接池的关系
pangpd的博客
09-07 2249
1. 前言 一直以为连接池是对JDBC的封装，今天再次回顾Java基础的时，才发现并非完全如此。连接池只是包装/操作了JDBC API 接口,然后再实际运行的时候,这些接口的具体实现则 下面是知乎上的一个图：JDBC和连接池的关系 2. JDBC和连接池的关系 JDBC（Java Data Base Connectivity,java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。简单说可以通过JDBC来访问数据库， 而连
【第六期论文复现赛】nnUNet
m0_63642362的博客
07-02 3247
复现赛nnUNet，肺部肿瘤分割，要求3DUnet-cascade Avg Dice = 66.85%，ensemble 2DUnet+ 3DUnet Avg Dice = 61.18%。
MYSQL习题
Shirley_L77的博客
08-18 798
Day1 1.1 编写一条 CREATE TABLE 语句，用来创建一个包含表 1-A 中所列各项的表 Addressbook （地址簿），并为 regist_no （注册编号）列设置主键约束 表1-A　表 Addressbook （地址簿）中的列。 CREATE TABLE Addressbook (regist_no INTEGER NOT NULL , name VARCHAR(128) NOT NULL , address VARCHAR(256) NOT NULL , tel_no CHAR(1
mysql大作业题目_数据库大作业题目，，零售企业管理系统的实施
weixin_35160938的博客
02-08 1270
数据库设计：使用MySQL和PHP零售企业管理系统的实施[私以为，原文比较Nice~]本项目是利用MySQL和PHP来实现RBMS应用。1。准备(5分)我们将使用以下表格：员工(EID，EnMead，城市)客户(CID，CNED，城市，VISITSH制造，LASTYVISITITYTIME)产品(PID，PNED，QOH，QOHOHEAST，原价，DelntTyRead，SID)供应商(SID，S...
面试 SQL整理 常见的SQL面试题：经典50题
热门推荐
sh_c1314
09-13 26万+
目录 ​ SQL基础知识整理: 常见的SQL面试题：经典50题 三、50道面试题 2.汇总统计分组分析 3.复杂查询 sql面试题：topN问题 4.多表查询 【面试题类型总结】这类题目属于行列如何互换，解题思路如下： 其他面试题: SQL基础知识整理: select 查询结果 如:[学号,平均成绩：组函数avg(成绩)]from 从哪张表中查找数...
深大数据库实验3之销售管理系统 -- php与mysql之后台编写与接口
AKGWSB 's blog
12-07 6257
⚠ 完整代码在这里 ⚠ https://github.com/AKGWSB/SZU-DBSC-LAB3 目录前言要求项目结构规划数据库创建与数据准备编写 show_xxxx 过程以全表打印编写 report_monthly_sale 函数以打印报表编写 add_purchase 函数以实现添加一笔购买记录为 purchases 表的 insert 操作注册触发器以实现业务逻辑注册触发器以记录 logs为增删改操作编写 sql 接口编写数据管理模块编写下单模块编写报表模块主页快速部署 前言 唔 销售管理系统
【LeetCode】177.第N高的薪水
wal1314520的博客
04-27 4565
177.第N高的薪水编写一个 SQL 查询语句，获取Employee表中第n高的薪水（Salary）。用到的表和数据SQL：Create table If Not Exists Employee (Idint, Salary int); Truncate table Employee; insert into Employee (Id, Salary) values('1', '100'); in...
pandas读取xlsx文件使用sqlachemy写到数据库
最新发布
ithongchou的博客
09-10 330
如果你使用的是特定的数据库（如 SQLite、PostgreSQL、MySQL），你还需要安装相应的数据库驱动。通过这些步骤，你可以方便地将 Excel 文件中的数据写入数据库。连接字符串的格式取决于你使用的数据库。（用于读取 Excel 文件）。方法将 DataFrame 数据写入数据库。读取 Excel 文件。
【网络安全】空字节绕过：URL回调+XSS+SQL绕WAF
等风来
09-07 320
经过进一步测试，我发现WAF在解析HTTP请求时，如果发现恶意查询，会导致服务器挂起。如果我连续提交3个请求且服务器挂起，它就会阻止我的IP地址。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值