SQL综合练习草稿

-- 练习一

DROP TABLE Employee;
CREATE TABLE If Not Exists Employee
(id INT, 
name VARCHAR(255),
salary INT,
departmentid INT);

INSERT INTO Employee(id,name,salary,departmentid) VALUES (1,'Joe',70000,1);
INSERT INTO Employee(id,name,salary,departmentid) VALUES (2,'Henry',80000,2);
INSERT INTO Employee(id,name,salary,departmentid) VALUES (3,'Sam',60000,2);
INSERT INTO Employee(id,name,salary,departmentid) VALUES (4,'Max',90000,1);

DROP TABLE Department;
Create table If Not Exists Department 
(id INT, name VARCHAR(255));

INSERT INTO Department (Id, name) VALUES ('1', 'IT');
INSERT INTO Department (Id, name) VALUES ('2', 'Sales');


SELECT d.name AS 'Department',sa.Employee AS 'Employee',sa.Salary AS 'Salary'
FROM  
-- 自链接找出个部门工资的最大值，并以工资金额作为连接条件
(SELECT e.departmentid AS 'Department', e.name AS 'Employee',m.s AS 'Salary'
FROM Employee AS e
INNER JOIN 
(SELECT *,max(salary) AS  's'
FROM Employee AS e
GROUP BY e.departmentid)AS m
ON e.salary = m.s) AS sa
-- 再把部门表里的部门名字连接进来
INNER JOIN Department AS d
ON sa.Department = d.id;

-- 练习二
Create table If Not Exists seat(id int NOT NULL PRIMARY KEY AUTO_INCREMENT, student varchar(255));

insert into seat (id, student) values ('1', 'Abbot');
insert into seat (id, student) values ('2', 'Doris');
insert into seat (id, student) values ('3', 'Emerson');
insert into seat (id, student) values ('4', 'Green');
insert into seat (id, student) values ('5', 'Jeames');

-- 条件语句，如果是因为最后一位是5（单数），所以若余数为1且ID不为5，则退一位，余数为零则进一位，余数为一且id=5则不动。
SELECT (CASE WHEN mod(id,2) = 1 AND id = max(id) THEN id
							WHEN mod(id,2) = 0 THEN id-1
							ELSE id +1 END) AS 'id', student
FROM seat
ORDER BY id;

-- 练习三
DROP TABLE score;
CREATE table if not EXISTS score(id int not null primary key auto_increment,
																Score DECIMAL(11,2));

insert into score (id, Score) values ('1', 3.50);
insert into score (id, Score) values ('2', 3.65);
insert into score (id, Score) values ('3', 4.00);
insert into score (id, Score) values ('4', 3.85);
insert into score (id, Score) values ('5', 4.00);
insert into score (id, Score) values ('6', 3.65);

SELECT Score ,DENSE_RANK() OVER (ORDER BY Score DESC) AS 'RANK'
FROM score;

-- 练习七

SELECT Score ,RANK() OVER (ORDER BY Score DESC) AS 'RANK'
FROM score;

SELECT count(id) FROM seat;

-- 练习四
CREATE TABLE if not EXISTS Log(id int not null primary key auto_increment,
																num int);
INSERT INTO Log(id, num) VALUES (1,1);
INSERT INTO Log(id, num) VALUES (2,1);
INSERT INTO Log(id, num) VALUES (3,1);
INSERT INTO Log(id, num) VALUES (4,2);
INSERT INTO Log(id, num) VALUES (5,1);
INSERT INTO Log(id, num) VALUES (6,2);
INSERT INTO Log(id, num) VALUES (7,2);

SELECT l.num as 'ConsecutiveNums ' 
FROM
-- 找出出现过的数字
(SELECT num 
FROM Log
GROUP BY num) AS l
INNER JOIN 
-- 数一下出现过的数字分别出现过几次
(SELECT num, COUNT(*) AS times
FROM Log
GROUP BY num) AS c
-- 连接条件为出现过数字，且出现次数大于3次的
ON l.num = c.num
WHERE c.times >3;

-- 练习六

CREATE TABLE if not EXISTS emp_manag(id int not null PRIMARY KEY,
															name VARCHAR(255),
															department VARCHAR(255) not null,
															managerid int DEFAULT NULL);
INSERT INTO emp_manag(id, name, department, managerid) VALUES (101,'John','A',NULL);
INSERT INTO emp_manag(id, name, department, managerid) VALUES (102,'Dan','A',101);
INSERT INTO emp_manag(id, name, department, managerid) VALUES (103,'James','A',101);
INSERT INTO emp_manag(id, name, department, managerid) VALUES (104,'Amy','A',101);
INSERT INTO emp_manag(id, name, department, managerid) VALUES (105,'Anne','A',101);
INSERT INTO emp_manag(id, name, department, managerid) VALUES (106,'Ron','B',101);



SELECT em.name 
FROM emp_manag AS em
INNER JOIN
-- 选出带五个员工的管理者
(SELECT id, name, managerid,count(managerid) AS workers
FROM emp_manag
GROUP BY managerid
HAVING workers =5) AS l
-- 连接管理员名字与id
ON em.id = l.managerid;

SELECT id, name 
FROM emp_manag;

-- 练习九
INSERT INTO Employee(id,name,salary,departmentid) VALUES (5,'Janet',69000,1);
INSERT INTO Employee(id,name,salary,departmentid) VALUES (6,'Randy',85000,1);

SELECT department.name AS 'Department',d.r_name AS 'Employee', d.r_salary AS 'Salary'
FROM department
INNER JOIN 
-- 连接部门名称表
(SELECT r.departmentid AS r_id ,r.name AS r_name, r.salary AS r_salary
FROM
-- 用窗口函数进行分组排序
(SELECT id,name, salary,departmentid, RANK() OVER (PARTITION BY departmentid ORDER BY salary DESC) AS ranking
FROM employee) AS r
-- 把每组前三选出
WHERE r.ranking < 4) AS d
ON d.r_id = department.id;

-- 练习十
CREATE TABLE if not EXISTS shortest(x int,
															y int);
INSERT INTO shortest (x,y) VALUES (-1,-1);
INSERT INTO shortest (x,y) VALUES (0,0);	
INSERT INTO shortest (x,y) VALUES (-1,-2);	


SELECT MIN(    
	ROUND(        
		sqrt(            
			power((p1.x-p2.x),2)+power((p1.y-p2.y),2)
			)        
		,2)    
	) AS shortest
FROM shortest as p1
INNER JOIN shortest as p2
on (p1.x,p1.y)<>(p2.x,p2.y);

-- 练习十一
-- 暂未写完，后面再补