2020-12-29

task06大作业
来不及了，回头再补上😭

# 如果表存在,那么删除
drop table if exists Employee;

# 创建Employee表
create table Employee
(Id varchar(100) not null,
Name varchar(100) not null,
Salary  varchar(100) not null,
DepartmentID varchar(100) not null,
primary key(Id));

# 插入Employee的数据
insert into Employee values ('1', 'joe', 7000, '1'),
('2', 'henry', 80000, '2'),
('3', 'sam', 60000, '2'),
('4', 'max', 90000, '1');

# 看一下Employee的数据
select *  from  Employee;



# 如果表存在,那么删除
drop table if exists Department;

# 创建Department表
create table Department
(Id varchar(100) not null,
Name varchar(100) not null,
primary key (Id));

# 插入Department的数据
insert into Department values
('1', 'IT'), ('2', 'Sales');

# 看一下Department的数据
select * from Department;


# 题目一:
# 编写一个 SQL 查询，找出每个部门工资最高的员工。
# 例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资

# 先尝试将每个部门薪水最高的数据挑出
select DepartmentId ,max(Salary) as Salary  
from Employee group by DepartmentId;

# 尝试关联两张表
select * from Employee a 
left join 
Department b 
on a.DepartmentId = b.Id; 

# 在表关联的时候加入where条件,选择各个部门薪水最高的数据做表的左关联
select b.Name as Department, a.Name as Employee, a.Salary from Employee a 
left join 
Department b 
on a.DepartmentId = b.Id
where (a.DepartmentId, a.Salary) 
in (
select DepartmentId ,max(Salary) as Salary  from Employee group by DepartmentId
)
order by a.Salary 
Desc;

# 题目二:
# 小美想改变相邻俩学生的座位。你能不能帮她写一个 SQL query 来输出小美想要的结果吗？

show databases;
use shop;

# 创建表seat
create table seat
(id varchar(100) not null,
student varchar(100) not null,
primary key (id) );

insert into seat values
(1, 'abbot'), 
(2, 'doris'), 
(3, 'emerson'), 
(4, 'green'), 
(5, 'jeames');

select * from seat;

drop table if exists seat;

select id, student from 
(select id - 1 as id, student from seat
where mod(id,2) = 0
union
select id + 1 as id, student from seat
where mod(id,2) = 1 and id <> (select count(*) from seat)
union
select id, student from seat
where mod(id,2) = 1 and id = (select count(*) from seat)) s
order by id;

# 偶数序号调整为基数序号
SELECT id - 1 AS id, student FROM seat 
 WHERE MOD(id,2) = 0;
# 奇数序号调整为偶数序号(但不为最后一个数序列)
SELECT id + 1 AS id, student FROM seat
 WHERE MOD(id,2) = 1 AND id <> (SELECT COUNT(*) FROM seat);
# 为最后一列奇数时序号不变
 SELECT id, student FROM seat
 WHERE MOD(id,2) = 1 AND id = (SELECT COUNT(*) FROM seat);
 
# 题目三
# 编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

# 创建表score
create table score
(id varchar(100) not null,
score varchar(100) not null);

# 插入数据
insert into score values
(1, 3.50),
(2, 3.65),
(3, 4.00),
(4, 3.85),
(5, 4.00),
(6, 3.65);

select * from score;

# 排序不跳过,用dense_rank() over(order by...)
select score.score, dense_rank() over (order by score.score) as ranking
from score
order by ranking;

# 题目四：
# 编写一个 SQL 查询，查找所有至少连续出现三次的数字。
create table logs
(id varchar(100) not null,
num varchar(100) not null);

insert into logs values
(1, 1),
(2, 1),
(3, 1),
(4, 2),
(5, 1),
(6, 2),
(7, 2);
 
select * from logs;
 
 
select num as consecutitivenums from logs
where (num)
in(
select num = avg(num) over (order by num rows between 1 preceding and 1 following)  from logs
);

# 题目五:
努力破解中

# 题目六
create table employee
(id varchar(100) not null,
name varchar(100) not null,
department varchar(100) not null,
managerid varchar(100) not null);

insert into employee values
(101, 'john', 'a', null),
(102, 'dan', 'a', 101),
(103, 'james', 'a', 101),
(104, 'amy', 'a', 101),
(105, 'anne', 'a', 101),
(106, 'ron', 'b', 101);

select name from
(select name, managerid from employee where count(managerid) >= 5;

# 练习题七
# 练习三的分数表，实现排名功能，但是排名需要是非连续的
select score.score, rank() over (order by score.score) as ranking
from score
order by ranking;

# 练习题八
努力破解中

# 练习题九/十十一努力破解中
truncate table employee；