Section A
练习一: 各部门工资最高的员工（难度：中等）
创建Employee 表，包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
创建Employee 表，包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
+----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1 | Joe | 70000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 | +----+-------+--------+--------------+
创建Department 表，包含公司所有部门的信息。
+----+----------+ | Id | Name | +----+----------+ | 1 | IT | | 2 | Sales | +----+----------+
编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。
create table Employee
(id int not null ,
name varchar(100) not null,
salary int ,
departmentid int,
primary key(id));
create table department
(id int not null,
name varchar(32) not null,
primary key(id));
insert into Employee values('1','Joe',70000,'1'),
insert into Employee values('2','Henry',80000,'2'),
('3','Sam',60000,'2'),
('4','Max',90000,'1');
insert into department values('1','IT'),
('2','Sales');
select *from Employee
select *from department
select d.name,namee,max_salary
from department d
inner join
(select departmentid,name as namee,max(salary) as max_salary
from employee
group by departmentid) as e
on d.id = e.departmentid;
练习二: 换座位（难度：中等）
小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。
其中纵列的id是连续递增的
小美想改变相邻俩学生的座位。
你能不能帮她写一个 SQL query 来输出小美想要的结果呢？
请创建如下所示seat表：
+---------+---------+ | id | student | +---------+---------+ | 1 | Abbot | | 2 | Doris | | 3 | Emerson | | 4 | Green | | 5 | Jeames | +---------+---------+
假如数据输入的是上表，则输出结果如下：
+---------+---------+ | id | student | +---------+---------+ | 1 | Doris | | 2 | Abbot | | 3 | Green | | 4 | Emerson | | 5 | Jeames | +---------+---------+
注意： 如果学生人数是奇数，则不需要改变最后一个同学的座位。
create table seat
(id int not null,
student varchar(32),
primary key(id));
insert into seat values('1','Abbot'),
('2','Doris'),('3','Emerson'),('4','Green'),('5','Jeames')
select *from seat
select s.id , s.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;
练习三: 分数排名（难度：中等）
假设在某次期末考试中，二年级四个班的平均成绩分别是 93、93、93、91，请问可以实现几种排名结果？分别使用了什么函数？排序结果是怎样的？（只考虑降序）
+-------+-----------+ | class | score_avg | +-------+-----------+ | 1 | 93 | | 2 | 93 | | 3 | 93 | | 4 | 91 | +-------+-----------+
create table score
(class int not null,
score_avg int not null,
primary key(class));
insert into score values('1',93),('2',93),('3',93),('4',91);
--使用窗口函数，降序排列--
select class,score_avg,
rank() over w as 'standard_rank',
dense_rank() over w as 'dense_rank',
row_number() over w as 'row_number_rank'
from score
window w as (order by score_avg DESC);
练习四：连续出现的数字（难度：中等）
编写一个 SQL 查询，查找所有至少连续出现三次的数字。
+----+-----+ | Id | Num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。
+-----------------+ | ConsecutiveNums | +-----------------+ | 1 | +-----------------+
create table logs
(id int not null,
num int not null,
primary key(id));
insert into logs values('1',1),
('2',1),('3',1),('4',2),('5',1),('6',2),('7',12);
select p.num
from(select id, num,
lead(num,1) over () as num2,
lead(num,2) over () as num3
from logs) as p
where p.num=p.num2
and p.num=p.num3;
练习六：至少有五名直接下属的经理 （难度：中等）
Employee表包含所有员工及其上级的信息。每位员工都有一个Id，并且还有一个对应主管的Id（ManagerId）。
+------+----------+-----------+----------+ |Id |Name |Department |ManagerId | +------+----------+-----------+----------+ |101 |John |A |null | |102 |Dan |A |101 | |103 |James |A |101 | |104 |Amy |A |101 | |105 |Anne |A |101 | |106 |Ron |B |101 | +------+----------+-----------+----------+
针对Employee表，写一条SQL语句找出有5个下属的主管。对于上面的表，结果应输出：
+-------+ | Name | +-------+ | John | +-------+
CREATE TABLE Employee1
(Id VARCHAR(6) NOT NULL,
Name VARCHAR(5) NOT NULL,
Department CHAR(1) NOT NULL,
ManagerId VARCHAR(6),
PRIMARY KEY (Id));
INSERT INTO Employee1 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 Employee1
WHERE Id IN (SELECT ManagerID
FROM (SELECT Id,
Name,
ManagerId,
COUNT(Id) OVER (PARTITION BY ManagerId ORDER BY ManagerId) AS num_same_manager
FROM Employee1) AS temp
WHERE num_same_manager >= 5); 
Section B
练习一：行转列
假设 A B C 三位小朋友期末考试成绩如下所示：
+-----+-----------+------| | name| subject |score | +-----+-----------+------| | A | chinese | 99 | | A | math | 98 | | A | english | 97 | | B | chinese | 92 | | B | math | 91 | | B | english | 90 | | C | chinese | 88 | | C | math | 87 | | C | english | 86 | +-----+-----------+------|
请使用 SQL 代码将以上成绩转换为如下格式：
+-----+-----------+------|---------| | name| chinese | math | english | +-----+-----------+------|---------| | A | 99 | 98 | 97 | | B | 92 | 91 | 90 | | C | 88 | 87 | 86 | +-----+-----------+------|---------|
CREATE TABLE score1
(
name VARCHAR(10) NOT NULL,
subject VARCHAR(32) NOT null,
score int
);
insert into score1 values('A','chinese',99),
('A','math',98),('A','english',97),
('B','chinese',92),('B','math',91),('B','english',90),
('C','chinese',88),('C','math',87),('C','english',86);
select *from score1
select name,
sum(case when subject='chinese' then score else null end) as chinese1,
sum(case when subject='math' then score else null end) as math1,
sum(case when subject='english' then score else null end) as english1
from score1
group by name;
练习二：列转行
假设 A B C 三位小朋友期末考试成绩如下所示：
+-----+-----------+------|---------| | name| chinese | math | english | +-----+-----------+------|---------| | A | 99 | 98 | 97 | | B | 92 | 91 | 90 | | C | 88 | 87 | 86 | +-----+-----------+------|---------|
请使用 SQL 代码将以上成绩转换为如下格式：
+-----+-----------+------| | name| subject |score | +-----+-----------+------| | A | chinese | 99 | | A | math | 98 | | A | english | 97 | | B | chinese | 92 | | B | math | 91 | | B | english | 90 | | C | chinese | 88 | | C | math | 87 | | C | english | 86 | +-----+-----------+------|
create table score3
(name varchar(10) not null,
chinese int,
math int,
english int);
insert into score3 values('A',99,98,97),
('B',92,91,90),('C',88,87,86);
select name,
'chinese' as subject,
chinese as score
from score3
union all
select name,
'math' as subject,
math as score
from score3
union all
select name,
'english' as subject,
english as score
from score3
order by name;
练习三：谁是明星带货主播？
假设，某平台2021年主播带货销售额日统计数据如下：
表名 anchor_sales
+-------------+------------+---------| | anchor_name | date | sales | +-------------+------------+---------| | A | 20210101 | 40000 | | B | 20210101 | 80000 | | A | 20210102 | 10000 | | C | 20210102 | 90000 | | A | 20210103 | 7500 | | C | 20210103 | 80000 | +-------------+------------+---------|
定义：如果某主播的某日销售额占比达到该平台当日销售总额的 90% 及以上，则称该主播为明星主播，当天也称为明星主播日。
请使用 SQL 完成如下计算：
a. 2021年有多少个明星主播日？
b. 2021年有多少个明星主播？
create table anchor_sales
(name varchar(10) not null,
datee date,
sale int);
insert into anchor_sales values('A',20210101,40000),
('B',20210101,80000),('A',20210102,20000),
('C',20210102,90000),('A',20210103,7500),
('C',20210103,80000);
select * from anchor_sales
--B.3.1--
select datee,name,
(sum(sale)/sum(sale) over (partition by datee))as sales_rate
from anchor_sales
group by datee,name
order by datee,sales_rate desc;
--B.3.2--
select *
from (select datee, name,
(sum(sale)/sum(sale) over (partition by datee))as sales_rate
from anchor_sales
group by datee,name
order by datee,sales_rate desc)as p1
where sales_rate >= 0.9
练习一：行转列
假设有如下比赛结果：
+--------------+-----------+ | cdate | result | +--------------+-----------+ | 2021-01-01 | 胜 | | 2021-01-01 | 胜 | | 2021-01-01 | 负 | | 2021-01-03 | 胜 | | 2021-01-03 | 负 | | 2021-01-03 | 负 | +------------+-------------+
请使用 SQL 将比赛结果转换为如下形式：
+--------------+-----+-----| | 比赛日期 | 胜 | 负 | +--------------+-----------+ | 2021-01-01 | 2 | 1 | | 2021-01-03 | 1 | 2 |
create table compition1
(cdate date not null,
resultt varchar(32) not null );
insert into compition1 values('2021-01-01','win'),
('2021-01-01','win'),('2021-01-01','lose'),
('2021-01-03','win'),('2021-01-03','lose'),
('2021-01-03','lose');
select *from compition1
select cdate,
count(case when resultt='win' then 1 else null end)as win1,
count(case when resultt='lose' then 1 else null end)as lose1
from compition1
group by cdate;
练习二：列转行
假设有如下比赛结果：
+--------------+-----+-----| | 比赛日期 | 胜 | 负 | +--------------+-----------+ | 2021-01-01 | 2 | 1 | | 2021-01-03 | 1 | 2 | +------------+-----------+
请使用 SQL 将比赛结果转换为如下形式：
+--------------+-----------+ | cdate | result | +--------------+-----------+ | 2021-01-01 | 胜 | | 2021-01-01 | 胜 | | 2021-01-01 | 负 | | 2021-01-03 | 胜 | | 2021-01-03 | 负 | | 2021-01-03 | 负 | +------------+-------------+
create table compition
(datee date not null,
win int,
lose int);
insert into compition values('2021-01-01',2,1),
('2021-01-03',1,2);
select * from compition
select datee,
'win' as resultt,
win as resulttt
from compition
union all
select datee,
'lose' as resultt,
lose as resulttt
from compition; 
371
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
