目录
SQL编程语言课程学习心得
第一次参加datawhale的课程学习，这种线上组队、打卡自学的模式对我来说挺合适的，也让我开始尝试写自己的博客来输出内容（虽然现在的博客基本上是练习题，但也是一个新的开始）。比较可惜的是，我与其他组员的交流不够多，有点自闭（以后肯定要尝试多与小伙伴交流）。
课程的内容以及安排还是很合理的，就本次SQL的学习来说，我确实是零基础开始，一步步跟着课程任务进行学习，目前也算是掌握了一定的SQL知识，可以编程解决一些简单的问题。task01搭建环境时，由于我对各种客户端的使用方式不够了解，还是花了一定的功夫才上手，后续除了部分难点可能还未理解透彻，其他的内容基本可以跟上。
在此还是特别感谢datawhale团队的成员，提供了优质的开源学习内容，学习模式也很棒！以后会继续参加其他课程的学习。如果有什么建议的话，希望能营造一个更加积极的交流氛围吧。
以下练习题仅含sectionA的十道题，由于时间问题，剩余的练习无法在限定时间内完成，但是后续我还会继续练习，争取能够熟练运用基础的SQL语句。部分题目有一定难度，参考了网络。
练习一: 各部门工资最高的员工（难度：中等）
#1.创建Employee 表，包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
#创建表
CREATE TABLE employee
(Id INT NOT NULL,
NAME VARCHAR(8),
salary INT,
Department_ID INT NOT NULL);#插入数值
INSERT INTO Employee
VALUES (1, "joe", 70000, 1),
(2, "Henry", 80000, 2),
(3, "Sam", 60000, 2),
(4, "Max", 90000, 1);#2.创建Department 表，包含公司所有部门的信息。
CREATE TABLE Department
(Id INT NOT NULL,
NAME VARCHAR(20));
INSERT INTO Department
VALUES (1, "IT"),
(2, "Sales");#3.编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。
#思路：先找到各部门最高薪水，然后连接两张表并根据两张表的关系进行合并。
SELECT D.name AS Department, e.name AS Employee, salary
FROM Employee AS e
LEFT JOIN Department D
ON e.Department_ID = D.Id
WHERE (e.Department_ID, e.salary)
IN (SELECT Department_ID, MAX(salary) AS "max_slary"
FROM Employee
GROUP BY Department_ID)
练习二: 换座位（难度：中等）
/*小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。
其中纵列的id是连续递增的
小美想改变相邻俩学生的座位。
你能不能帮她写一个 SQL query 来输出小美想要的结果呢？
注意：如果学生人数是奇数，则不需要改变最后一个同学的座位。*/
#1.创建座位表，插入数值
CREATE TABLE seat
(id INT NOT NULL,
student VARCHAR(10));
INSERT INTO seat
VALUES (1, "Abbot"),
(2, "Doris"),
(3, "Emerson"),
(4, "Green"),
(5, "jeames");#2.改变相邻俩学生的座位
#关键点：学生人数是奇数不改变最后一个同学的座位，需要增加条件id != (SELECT COUNT(*) FROM seat)。
SELECT CASE
WHEN MOD(id, 2) != 0 AND id != (SELECT COUNT(*) FROM seat) THEN id + 1
WHEN MOD(id, 2) = 0 THEN id - 1
ELSE id
END AS "id", student
FROM seat ORDER BY id;
练习三: 分数排名（难度：中等）
#假设在某次期末考试中，二年级四个班的平均成绩分别是 93、93、93、91，请问可以实现几种排名结果？分别使用了什么函数？排序结果是怎样的？（只考虑降序）
CREATE TABLE Score
(class INT NOT NULL,
score_avg INT);
INSERT INTO Score
VALUES (1, 93),(2, 93),(3, 93),(4, 91),(5, 96);#法一、RANK函数：计算排序时，如果存在相同位次的记录，则会跳过之后的位次。
SELECT class, score_avg, RANK() OVER (ORDER BY score_avg DESC) AS ranking
FROM Score;
#法二：DENSE_RANK函数：同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。
SELECT class, score_avg, DENSE_RANK() OVER (ORDER BY score_avg DESC) AS ranking
FROM Score;
#法三：ROW_NUMBER函数：赋予唯一的连续位次。
SELECT class, score_avg, ROW_NUMBER() OVER (ORDER BY score_avg DESC) AS ranking
FROM Score;
练习四：连续出现的数字（难度：中等）
#编写一个 SQL 查询，查找所有至少连续出现三次的数字。
CREATE TABLE nums_arr
(Id INT NOT NULL,
Num INT);
INSERT INTO nums_arr
VALUES (1, 1),(2, 1),(3, 1),(4, 2),(5, 1),(6, 2),(7, 2)#思路：构建新列cut，为原id与根据数字排序建立的新id的差值，当相同的差值大于等于3时，即数字连续出现三次以上。
#id: 1 2 3 4 5 6 7
#num: 1 1 1 2 1 2 2
#new_id: 1 2 3 1 4 2 3
#cut: 0 0 0 3 1 4 4
#注：PARTITON BY分组时不会改变原始表中记录的行数。
SELECT Num AS ConsecutiveNums
FROM (SELECT Num, COUNT(*) FROM
(SELECT Id, Num,
id - (ROW_NUMBER() OVER (PARTITION BY Num ORDER BY id)) AS cut
FROM nums_arr) AS sub
GROUP BY cut
HAVING COUNT(*) >= 3) AS Result;
练习五：树节点 （难度：中等）
/*对于tree表，id是树节点的标识，p_id是其父节点的id。
每个节点都是以下三种类型中的一种：
Root: 如果节点是根节点。
Leaf: 如果节点是叶子节点。
Inner: 如果节点既不是根节点也不是叶子节点。
节点’1’是根节点，因为它的父节点为NULL，有’2’和’3’两个子节点。
节点’2’是内部节点，因为它的父节点是’1’，有子节点’4’和’5’。
节点’3’，’4’，’5’是叶子节点，因为它们有父节点但没有子节点。*/
DROP TABLE tree
CREATE TABLE tree
(id INT NOT NULL,
p_id CHAR(8));
INSERT INTO tree
VALUES (1, NULL),(2, 1),(3, 1),(4, 2),(5, 2);#法一、CASE WHEN语句
SELECT id, CASE
WHEN p_id IS NULL THEN "root"
WHEN id IN (SELECT DISTINCT p_id FROM tree) THEN "inner"
ELSE "leat"
END AS "type"
FROM tree;#法二、if语句：SELECT IF(判别表达式,'yes','no')
SELECT id,
IF(ISNULL(p_id), "root",
IF(id IN (SELECT DISTINCT p_id FROM tree),"inner", "leaf")) AS "Type"
FROM tree;
练习六：至少有五名直接下属的经理 （难度：中等）
#Employee表包含所有员工及其上级的信息。每位员工都有一个Id，并且还有一个对应主管的Id（ManagerId）。
CREATE TABLE employee2
(id INT NOT NULL,
NAME VARCHAR(10),
department VARCHAR(10),
manager_id INT,
PRIMARY KEY (id));
INSERT INTO employee2
VALUES (101, "John", "A", NULL),
(102, "Dan", "A", 101),
(103, "James", "A", 101),
(104, "Amy", "A", 101),
(105, "Ron", "B", 101),
(106, "Anne", "B", 101);#内连接：INNER JOIN
SELECT t2.name
FROM employee2 t1
INNER JOIN employee2 AS t2
ON t1.manager_id = t2.id
GROUP BY t2.name
HAVING COUNT(1) >= 5;
练习七：查询回答率最高的问题 （难度：中等）
/*求出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语句找出回答率（show 出现次数 / answer 出现次数）最高的 question_id。*/
CREATE TABLE survey_log
(uid CHAR(4) NOT NULL,
ACTION VARCHAR(32) NOT NULL,
question_id INTEGER,
answer_id INTEGER,
q_num INTEGER,
TIMESTAMP INTEGER);
INSERT INTO survey_log
VALUES ('5', 'show', 285, NULL, 1, 123),
('5', 'answer', 285, 1, 1, 124),
('5', 'show', 369, NULL, 2, 125),
('5', 'skip', 369, NULL, 2, 126);#注：limit可以选择哪一行的数据，1就是前一行，（1,10就是从2开始选择10行数据），10就是前10行数据
SELECT question_id FROM
(SELECT question_id,
SUM(CASE WHEN ACTION = 'show' THEN 1
ELSE 0
END) / SUM(CASE
WHEN ACTION = 'answer' THEN 1
ELSE 0
END) AS ratio
FROM survey_log
GROUP BY question_id
ORDER BY ratio DESC
LIMIT 1) AS a;
#问题285的回答率为1/1，然而问题369的回答率是0/1，所以输出是285。
练习八：各部门前3高工资的员工（难度：中等）
#复制练习一中的 employee 表，再插入第5、第6行数据
INSERT INTO Employee
VALUES (5, "Janet", 69000, 1),
(6, "Randy", 85000, 1);#思路：先拼接Employee表和Department表，然后根据Department_ID分组后进行排序，最后找出前3名。
SELECT Department, NAME AS Employee, salary FROM
(SELECT salary, E.Name AS NAME,
D.Name AS Department,
DENSE_RANK() OVER (PARTITION BY Department_ID ORDER BY salary DESC) AS ranking
FROM Employee AS E
LEFT JOIN Department AS D
ON E.Department_ID = D.Id) AS sub
WHERE ranking <= 3;
#此外，请考虑实现各部门前N高工资的员工功能。
#答：只需修改最后一个语句的参数即可，WHERE ranking <= N。
练习九：平面上最近距离 (难度: 困难）
#point_2d表包含一个平面内一些点（超过两个）的坐标值（x，y）。写一条查询语句求出这些点中的最短距离并保留2位小数。
CREATE TABLE point_2d
(X INT,
Y INT);
INSERT INTO point_2d
VALUES (-1, -1),(0, 0),(-1, -2);#计算距离，采用交叉连结：CROSS JOIN
#ROUND：保留小数的位数；SQRT：开方；POWER(X,Y) 返回底数为X，指数为Y的值
SELECT ROUND(MIN(SQRT(POW(p1.x - p2.x, 2) + POW((p1.y - p2.y), 2))), 2) AS shortest
FROM point_2d AS p1
CROSS JOIN point_2d AS p2
WHERE p1.x < p2.x OR p1.y < p2.y;
练习十：行程和用户（难度：困难）
/*数据背景：
Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。
Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。
Users 表存所有用户。每个用户有唯一键 Users_Id。
Banned 表示这个用户是否被禁止，Role 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。
题目：写一段 SQL 语句查出2013年10月1日至2013年10月3日期间非禁止用户的取消率。
基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。*/
#1.创建Trips表，插入数据
CREATE TABLE Trips
(id INTEGER NOT NULL,
client_id CHAR(4) NOT NULL,
driver_id CHAR(4) NOT NULL,
city_id CHAR(4) NOT NULL,
STATUS VARCHAR(32),
request_at DATE,
PRIMARY KEY (id));
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');#2.创建Users表，插入数据
CREATE TABLE Users
(users_id INTEGER NOT NULL,
banned VARCHAR(32) NOT NULL,
ROLE VARCHAR(32) NOT NULL,
PRIMARY KEY (users_id));
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');#3.连结两表
SELECT *
FROM Trips AS t
LEFT JOIN Users AS u
ON u.users_id = t.client_id
WHERE banned != "Yes" AND request_at BETWEEN 20131001 AND 20131003;#4.查出指定期间非禁止用户的取消率
SELECT request_at AS "Day", ROUND(AVG(STATUS != "completed"), 2) AS 'Cancellation Rate'
FROM Trips AS t
LEFT JOIN Users AS u
ON u.users_id = t.client_id
WHERE banned != "Yes" AND request_at BETWEEN 20131001 AND 20131003
GROUP BY request_at
ORDER BY request_at;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
