Task06：综合练习

练习题1

CREATE TABLE shop.employee(id int not null);
     alter table shop.employee add column name varchar(45) null;
     alter table shop.employee add column salary float null;
     alter table shop.employee add column departmentid int not null;
     insert into shop.employee(id, name, salary,departmentid)values(1,'Joe',70000,1);
     insert into shop.employee(id, name, salary,departmentid)values(2,'Henry' ,80000,2);
     insert into shop.employee(id, name,salary,departmentid)values(3,'Sam',60000,2);
     insert into shop.employee(id, name, salary,departmentid)values(4,'Max',90000,1)
create table shop.department(
     id int not null,
     name varchar(45) null);
     insert into shop.department(id,name)  values (1,'IT');
     insert into shop.department(id,name)  values (2,'Sales')
 select d.Name as department,e.Name as employee,e.salary
    -> from department d,employee e
    -> where e.departmentId=d.id and e.salary=(Select max(salary) from employee where departmentId=d.id);
+------------+----------+--------+
| department | employee | salary |
+------------+----------+--------+
| Sales      | Henry    |  80000 |
| IT         | Max      |  90000 |
+------------+----------+--------+

练习题2

create table shop.seat(id int not null,student varchar(45) null);
insert into seat (id,student) value (1,'Abbot');
insert into seat (id,student) value (2,'Doris');
 insert into seat (id,student) value (3,'Emerson');
 insert into seat (id,student) value (4,'Green');
 insert into seat (id,student) value (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;
+----+---------+
| id | student |
+----+---------+
|  1 | Doris   |
|  2 | Abbot   |
|  3 | Green   |
|  4 | Emerson |
|  5 | Jeames  |
+----+---------+

练习题3

select score,dense_rank() over(order by score desc) as `Rank` from scores;

练习题4

select distinct l1.Num as ConsecutiveNums
from 
number l1, 
number l2, 
number l3
where l1.Id = l2.Id-1
and l2.Id = l3.Id-1
and l1.Num=l2.Num
and l2.Num=l3.Num

练习题5

SELECT p_id AS f,COUNT(id) AS branch
FROM tree
GROUP BY p_id;
  DELETE FROM tree WHERE id in (2,3,4,5);

SELECT T.id,(CASE WHEN T.p_id IS NULL THEN 'Root' WHEN P.branch >0 THEN 'Leaf' ELSE 'Inner' END) AS 'Type'
FROM tree T LEFT JOIN (SELECT p_id AS f,COUNT(id) AS branch FROM tree GROUP BY p_id) P
ON T.Id = P.f;

练习题6

SELECT
	a.Name
FROM
	Employee a
JOIN
	(SELECT
			ManagerId
		FROM
			Employee
		GROUP BY ManagerId
		HAVING COUNT(*) >= 5) b
ON a.Id = b.ManagerId;

练习题7

select score,rank() over(order by score desc) as `Rank` from scores;

练习题8

SELECT question_id AS 'survey_log'
FROM (SELECT question_id,SUM(CASE WHEN answer_id IS NOT NULL THEN 1 ELSE 0 END)/COUNT(1) AS sv
FROM survey_log 
GROUP BY question_id) t2
WHERE t2.sv =
(SELECT MAX(sv)
FROM
(SELECT question_id,SUM(CASE WHEN answer_id IS NOT NULL THEN 1 ELSE 0 END)/COUNT(1) AS sv
FROM survey_log 
GROUP BY question_id) t);

练习题9

SELECT d.Name AS Department, e1.Name AS Employee, e1.Salary
FROM Employee e1 JOIN Department d ON e1.DepartmentId = d.Id
WHERE 3 > (SELECT COUNT(DISTINCT e2.Salary) FROM Employee e2
           WHERE e2.Salary > e1.Salary AND e1.DepartmentId = e2.DepartmentId);

练习题10

SELECT MIN(distance) as shortest
FROM(
			SELECT POW ((p1.x - p2.x),2) + POW((p1.y - p2.y), 2)  as distance
			FROM point_2d as p1
			INNER JOIN point_2d as p2
			ON !(p1.x = p2.x and p1.y = p2.y)
) as p;

练习题11

SELECT
    Request_at AS DAY,
    ROUND( SUM( CASE WHEN `Status` LIKE 'cancelled%' THEN 1 ELSE 0 END ) / COUNT( * ), 2 ) AS cancelled_rate 
FROM
    trips
    LEFT JOIN ( SELECT Users_Id, Banned FROM users ) client ON trips.Client_Id = client.Users_Id
    LEFT JOIN ( SELECT Users_Id, Banned FROM users ) driver ON trips.Driver_Id = driver.Users_Id
    WHERE client.Banned = 'NO' AND driver.Banned = 'NO'
    GROUP BY Request_at
    ORDER BY Request_at;