DW_SQL练习题

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

SELECT DP.`name` AS department_name,E.`name` AS employee_name,X.max_salary
FROM employee AS E
INNER JOIN department AS DP
ON E.departmentid=DP.id
INNER JOIN  
(SELECT departmentid,MAX(salary) AS max_salary
FROM employee
GROUP BY departmentid) AS X
ON E.departmentid=X.departmentid
WHERE E.salary= max_salary;

A-练习三: 分数排名
按降序，有三种

SELECT *
,RANK() OVER (ORDER BY score_avg DESC) AS ranking_1
,DENSE_RANK() OVER (ORDER BY score_avg DESC) AS ranking_2
,ROW_NUMBER() OVER (ORDER BY score_avg DESC) AS ranking_3
FROM score_a;

A-练习五：树节点

SELECT id
,CASE WHEN  p_id IS NULL THEN 'Root'
      WHEN  id IN (SELECT p_id FROM tree) THEN 'Inner'
      ELSE 'Leaf'
END AS type
FROM tree;

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

SELECT * FROM employee2
WHERE id=
(SELECT managerid
FROM employee2
GROUP BY managerid
HAVING COUNT(id)>=5);

A-练习七：查询回答率最高的问题

CREATE VIEW ex (question_id,rate)
AS
SELECT question_id,COUNT(answer_id)/COUNT(question_id) AS result
FROM survey_log 
GROUP BY question_id
ORDER BY result DESC;
SELECT question_id
FROM ex
WHERE rate=(SELECT MAX(rate) FROM ex);

A-练习八：各部门前3高工资的员工

CREATE VIEW ex8(Department,Employee,Salary,ranking)
AS
(SELECT p1.`name`AS Department  ,p2.`name` AS Employee,salary,ranking
FROM department AS p1
INNER JOIN 
(SELECT departmentid,`name`,salary
,RANK() OVER(PARTITION BY departmentid ORDER BY salary DESC) AS ranking
FROM employee9) AS p2
ON p1.id=p2.departmentid);
SELECT *
FROM ex8
WHERE ranking <=3
ORDER BY Department,ranking;

B-练习一：行转列

/*case表达式的运用：行列转换*/
SELECT `name`,
       SUM(CASE WHEN subject = 'chinese' THEN score ELSE null END) as chinese,
       SUM(CASE WHEN subject = 'math' THEN score ELSE null END) as math,
       SUM(CASE WHEN subject = 'english' THEN score ELSE null END) as english
  FROM score2
 GROUP BY `name`;

C-练习一：行转列

/*case表达式的运用：行列转换*/
SELECT cdate,
       COUNT(CASE WHEN result = '胜' THEN 1 ELSE null END) as 胜,
       COUNT(CASE WHEN result = '负' THEN 1 ELSE null END) as 负
 FROM row_col
 GROUP BY cdate;

本次组队学习的感受：
首先，感谢DW团队，从上次参加pandas就喜欢上了这个团队，也推荐给其他的小伙伴了；
之前还是石墨文档看打卡评语，现在用上了小程序，高级了哈；
本次学习内容基本都学会了，但是个人运用上还有欠缺，看本次打卡就明了了，当然也有周末没带电脑回家浪去了没有好好学习的原因，反思ing；
希望DW越来越好，我也不断提升自己，奥利给。

DW_SQL学习