task06综合练习

综合练习

练习1：分组求和

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

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;

练习2：交换位置

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

练习3：分数排名

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

练习4：连续出现的数字

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)


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;

练习5：树节点

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 

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

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

练习7：分数排名

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

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

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

练习9：各部门前3高工资的员工

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;

练习10：平面上最近距离

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：行程和用户

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

---

完结撒花！！！

---

下次datawhale继续！！！

---