Day5-SQL綜合練習(Datawhale)

liying_tt發表於2020-12-28

練習1：分組求和

各部門工資最高的員工（難度：中等)

1. 建立employee 表，包含所有員工資訊，每個員工有其對應的 Id, Salary 和 DepartmentID。

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

【START】

第一步：建立employee表

這裡我將Id和DepartmentID作為字串處理。

Id VARCHAR(1) NOT NULL,
Name VARCHAR(225) NOT NULL,
Salary INT ,
DepartmentID VARCHAR(1),
PRIMARY KEY(Id)
);

第二步：插入資料

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

第三步：檢視錶及表資料

SELECT * FROM employee

【END】

2. 建立department 表，包含公司所有部門的資訊。

Id	Name
1	IT
2	Sales

【START】

參考上面的步驟，建立新的表然後插入資料

CREATE TABLE department (
Id VARCHAR(1) NOT NULL,
Name VARCHAR(225) NOT NULL,
PRIMARY KEY(Id)
);

INSERT INTO department VALUES('1','IT'),
('2','Sales');

SELECT * FROM department

【END】

3. 編寫一個 SQL 查詢，找出每個部門工資最高的員工。例如，根據上述給定的表格，Max 在 IT 部門有最高工資，Henry 在 Sales 部門有最高工資。

Department	Employee	Salary
IT	Max	90000
Sales	Henry	80000

【START】

思路：觀察資料，可以發現，Department在department 表中，其他兩個欄位在employee表中，我們需要將這兩張表通過DepartmentID關聯起來，之後再求出部門裡面最高工資即可。

SELECT dep.Name AS Department,
       emp.Name AS Employee,
       MAX(emp.Salary) AS Salary
FROM employee emp 
LEFT JOIN department dep 
ON emp.DepartmentID = dep.ID
GROUP BY dep.Name;

【END】

練習2：交換位置

換座位

縱列的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

【START】

思路：觀察上面的資料，我們可以發現奇數行的相下一位，偶數行的向上一位，只需要將奇數行並且不等於最大id，則id+1，偶數行則id-1。

此處需要注意，因為我們會對id進行求餘的運算，需要將id維護為INT型。

CREATE TABLE seat (
Id INT NOT NULL,
student VARCHAR(225) NOT NULL,
PRIMARY KEY(Id)
);

INSERT INTO seat VALUES(1,'Abbot'),
(2,'Doris'),
(3,'Emerson'),
(4,'Green'),
(5,'Jeames');

SELECT CASE WHEN id%2=0 THEN id-1
	    WHEN id%2=1 AND id!=(SELECT MAX(id) FROM seat) THEN id+1
	    ELSE id  END ID,
	    student
FROM seat
ORDER BY ID

【END】

練習3：分數排名

編寫一個 SQL 查詢來實現分數排名，如果兩個分數相同，則兩個分數排名（Rank）相同。請注意，平分後的下一個名次應該是下一個連續的整數值。換句話說，名次之間不應該有“間隔”。

建立score表：

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

查詢的結果應該為

Score	Rank
4.00	1
4.00	1
3.85	2
3.65	3
3.65	3
3.50	4

【START】

思路：按照Score降序排列，並且得到Rank後的值，值是按照無間隔排序。使用視窗函式中的ROW_NUMBER()

注意：decimal(m,n)中m是總個數；命名為Rank的時候由於rank是內建函式，需要加上引號。

CREATE TABLE score_t (
Id INT NOT NULL,
Score DECIMAL(6,2) NOT NULL,
PRIMARY KEY(Id)
);

INSERT INTO score_t VALUES (1,3.50),
(2,3.65),
(3,4.00),
(4,3.85),
(5,4.00),
(6,3.65);

SELECT Score,
       DENSE_RANK() OVER (ORDER BY Score desc) AS 'Rank'
FROM score_t

【END】

練習4：連續出現的數字

【START】

編寫一個 SQL 查詢，查詢所有至少連續出現三次的數字。

Id	Num
1	1
2	1
3	1
4	2
5	1
6	2
7	2

CREATE TABLE NUM (
Id INT NOT NULL,
Num INT NOT NULL,
PRIMARY KEY(Id)
);

INSERT INTO NUM VALUES (1,1),
(2,1),
(3,1),
(4,2),
(5,1),
(6,2),
(7,2)

【START】

思路：如果為連續出現的數，它的順序排序與它分組後的順序排序的差值是相等的(此處的排序必須為不重複排序)

SELECT Num AS ConsecutiveNums
FROM
(SELECT Num,
	diff,
	count(1) cot
FROM 
(SELECT Num,
        (row_number() OVER (ORDER BY Id)-row_number() OVER (PARTITION BY Num ORDER BY ID)) 
          AS diff
FROM NUM) t
GROUP BY Num,
	 diff) t2
WHERE cot>=3;

【END】

練習5：樹節點

對於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’是葉子節點，因為它們有父節點但沒有子節點。

下面是樹的圖形：

注意

如果一個樹只有一個節點，只需要輸出根節點屬性。

【START】

思路：首先需要拼接一張既有上節點又有下節點的表，然後再根據條件使用CASE WHEN對他們賦值。

CREATE TABLE tree (
id VARCHAR(4) NOT NULL,
p_id VARCHAR(4) ,
PRIMARY KEY(Id)
); 


INSERT INTO tree VALUES (1,null),
(2,1),
(3,1),
(4,2),
(5,2)

SELECT  DISTINCT id,
  	CASE WHEN p_id IS NULL AND t_id IS NOT NULL THEN 'Root'
    	     WHEN p_id IS NOT NULL AND t_id IS NOT NULL THEN 'Inner'
	     WHEN p_id IS NOT NULL AND t_id IS NULL THEN 'Leaf'
        END Type
FROM
(SELECT t1.id,
	t1.p_id,
        t2.id t_id
FROM tree t1
LEFT JOIN tree t2
ON t1.id = t2.p_id) t

【END】

練習6：至少有五名直接下屬的經理

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個下屬的主管。對於上面的表，結果應輸出：

注意:

沒有人向自己彙報。

【START】

思路：找到managerid的名稱，並統計managerid出現的次數，之後再做篩選。

CREATE TABLE employee_t (
id VARCHAR(3) NOT NULL,
name VARCHAR(25) NOT NULL,
department VARCHAR(1),
managerid VARCHAR(3),
PRIMARY KEY(id)
);


INSERT INTO employee_t VALUES (101,'John','A',null),
(102,'Dan','A',101),
(103,'James','A',101),
(104,'Amy','A',101),
(105,'Anne','A',101),
(106,'Ron','A',101)

SELECT managername AS name
FROM
SELECT  t1.name,
        t1.managerid,
	t2.name AS managername,
	COUNT(1) AS num
FROM employee_t t1
LEFT JOIN employee_t t2
ON t1.managerid = t2.id
GROUP BY t1.name,
	 t1.managerid) t3
GROUP BY managername
HAVING SUM(num) >= 5

結果：

【END】

練習7：分數排名

練習三的分數表，實現排名功能，但是排名需要是非連續的

【START】

思路：將練習3中的desen_rank()改為rank即可。

SELECT Score,
       RANK() OVER (ORDER BY Score desc) AS 'Rank'
FROM score_t

【END】

練習8：查詢回答率最高的問題

求出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	285	null	2	125
5	skip	285	null	2	126

【SATRT】

思路：找到回到問題的次數以及問題的次數，之後再計算回答率並求出最大回答率。

CREATE TABLE survey_log (
    uid VARCHAR(1) NOT NULL,
    action VARCHAR(25) NOT NULL,
    question_id VARCHAR(3),
    answer_id VARCHAR(25),
    q_num INT,
    timestamp VARCHAR(3)
    );

INSERT INTO survey_log VALUES (5,'show',285,null,1,123),
(5,'answer',285,124124,1,124),
(5,'show',369,null,2,125),
(5,'skip',369,null,2,126)

SELECT CASE WHEN MAX(answer_num/quest_num) THEN question_id END  survey_log			 
FROM(
SELECT question_id,
       1 quest_num,
       count(answer_id) answer_num
FROM survey_log
GROUP BY question_id) t1

【END】

練習9：各部門前3高工資的員工

編寫一個 SQL 查詢，找出每個部門工資前三高的員工。此外，請考慮實現各部門前N高工資的員工功能

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

id	name
1	IT
2	Sales

【START】

思路：根據departmentid以及name進行分組排序，然後找到排序前三的，這裡使用的是視窗的函式ROW_NUMBER()，對於找前N高工資的功能只需要將最後where語句的3改為n即可。

CREATE TABLE employee_n (
id VARCHAR(3) NOT NULL,
name VARCHAR(25) NOT NULL,
salary INT,
departmentid VARCHAR(1),
PRIMARY KEY(id)
);
CREATE TABLE department (
Id VARCHAR(1) NOT NULL,
Name VARCHAR(225) NOT NULL,
PRIMARY KEY(Id)
);


INSERT INTO employee_n VALUES(1,'Joe',70000,1),
(2,'Henry',80000,2),
(3,'Sam',60000,2),
(4,'Max',90000,1),
(5,'Janet',69000,1),
(6,'Randy',85000,1);

INSERT INTO department VALUES('1','IT'),
('2','Sales');

SELECT Department,
       Emplyee,
       Salary
FROM
(SELECT dep.name Department,
	emp.Name Emplyee,
	emp.Salary,
	ROW_NUMBER() OVER(PARTITION BY dep.name ORDER BY Salary) AS ranking
FROM employee_n emp
LEFT JOIN department dep 
ON emp.departmentid = dep.id
)t1 
WHERE ranking <= 3;

【END】

練習10：平面上最近距離

point_2d表包含一個平面內一些點（超過兩個）的座標值（x，y）

寫一條查詢語句求出這些點中的最短距離並保留2位小數。(注意：所有點的最大距離小於10000)

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

最短距離是1，從點（-1，-1）到點（-1，-2)。

結果為：

shortest

1.00

【START】

思路：我們需要去遍歷所有的x,y的組合，所以我們考慮用笛卡爾積，但是我們不需要自身到自身的距離，所以引入id序號，將id序號相同的排除，然後使用歐式距離計算公式sqrt((x1-x2)^2+(y1-y1)^2)得到最小距離。

CREATE TABLE point_2d
(id INT,
x INT, 
y INT)

INSERT INTO point_2d VALUES 
(1,-1,-1),
(2,0,0),
(3,-1,-2)

SELECT  t1.x x1,
	t1.y y1,
	t2.x x2,
	t2.y y2,
	MIN(SQRT(POW((t1.x-t2.x),2)+POW((t1.y-t2.y),2))) shortest
FROM 
point_2d t1,point_2d t2
WHERE t1.id <> t2.id

SELECT MIN(SQRT(POW((t1.x-t2.x),2)+POW((t1.y-t2.y),2))) shortest
FROM 
point_2d t1,point_2d t2
WHERE t1.id <> t2.id

練習11：行程和使用者

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’）的列舉型別。

user_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

【START】

思路：

第一，我們查的是request_at在2013-10-1至2013-10-3範圍；

第二，banned要為Yes，一個是client_id需要匹配Users表，一個是driver_id需要匹配Users表

第三，Status包含cancelled的為取消訂單。合計訂單包含取消訂單數，取消率為取消訂單數除以合計訂單數

CREATE TABLE trips
(id INT NOT NULL,
client_id INT, 
driver_id INT,
city_id INT,
status VARCHAR(60),
request_at DATE);

INSERT INTO trips VALUES
(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');

CREATE TABLE users
(user_id  INT NOT NULL,
banned VARCHAR(4),
role VARCHAR(60));

INSERT INTO users VALUES
(1,'No','client'),
(2,'YES','client'),
(3,'No','client'),
(4,'No','client'),
(10,'No','driver'),
(11,'No','driver'),
(12,'No','driver'),
(13,'No','driver');

SELECT request_at Day, 
       ROUND(sum(cancelled_num)/sum(all_num),2) Cancellation_Rate
FROM			 
(SELECT request_at,
        status,
	count(1) all_num,
	CASE WHEN t1.status <> 'completed' THEN 1 ELSE 0 END cancelled_num			 
FROM trips t1
LEFT JOIN users u1
ON t1.client_id = u1.user_id 
LEFT JOIN users u2
ON t1.driver_id = u2.user_id 
WHERE u1.banned  = 'No'
AND u2.banned  = 'No'
AND request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY request_at,
         status) tp
GROUP BY request_at

【END】

ADMIN07 - 綜合練習
2018-03-17
Java基礎 --- 綜合練習
2024-03-13
Java
SparkSql與Redis綜合練習
2020-12-08
SparkSQLRedis
迴圈高階綜合練習
2024-03-03
綜合練習——洗牌發牌Easy版
2021-09-09
程式設計實踐（Pandas）綜合練習1
2021-01-01
程式設計
U7-11課綜合練習+12課階段測評練習——複習練習題目
2024-07-12
物件導向綜合訓練
2024-10-20
物件
二維陣列和函式綜合程式設計練習
2018-11-30
陣列函式程式設計
『政善治』Postman工具 — 13、Postman介面測試綜合練習
2021-05-24
Postman
Day8-綜合作業1(DataWhale)
2021-01-01
知識學習綜合
2018-08-08
Java基礎 --- 物件導向綜合訓練
2024-03-19
Java物件
【綜合複習_網路部分】
2020-12-01
綜合練習——尋找有潛力的bilibili百大UP主（1）
2021-08-27
Java攻城獅第二季綜合練習——初學者好懂版
2021-09-09
Java
Java入門第二季 6-1綜合練習程式碼
2021-09-09
Java
C++遠征之起航篇第5章綜合練習程式碼參考
2021-09-09
C++
【pandas學習筆記】綜合整理
2018-07-13
筆記
python綜合學習七之TensorFlow初識
2018-09-06
Python
Spark學習進度7-綜合案例
2021-01-10
Spark
python綜合學習四之Numpy和Pandas(下)
2019-02-16
Python
綜合約束
2024-03-23
綜合實驗
2020-09-28
Steam教育在新時代中綜合學習論
2022-07-13
Linq 綜合寫法
2018-03-20
教資 - 綜合（4）
2024-10-09
教資 - 綜合（1）
2024-10-07
綜合掃描工具
2024-09-10
模糊綜合評價
2024-07-14
教資 - 綜合（6）
2024-10-29
2020綜合知識
2024-05-24
OSPF 綜合實驗
2021-04-28
OSPF綜合實驗
2020-12-24
BGP綜合實驗
2020-12-24
【綜合評價方法】常見綜合評價方法及其實現
2024-08-26
第 10 節：複合型別小練習！
2019-12-03
型別
Datawhale
2024-08-23

Day5-SQL綜合練習(Datawhale)

練習1：分組求和

練習2：交換位置

練習3：分數排名

練習4：連續出現的數字

練習5：樹節點

練習6：至少有五名直接下屬的經理

練習7：分數排名

練習8：查詢回答率最高的問題

練習9：各部門前3高工資的員工

練習10：平面上最近距離

練習11：行程和使用者

相關文章