项目七:部门最高工资员工
1),创建表格:Employee 、Department 并插入数据:
create table Employee(
-> id intauto_increment primary key,
-> namevarchar(8) not null,
-> salaryint not null,
-> department_id int not null);
2),编写一个 SQL 查询,找出每个部门工资最高的员工。例如,根据上述给定的表格,Max在 IT 部门有最高工资,Henry 在 Sales 部门有最高工资:
select max(d.name) as Department,max(e.name) as Employee, max(e.salary) assalary from Employee e left join Department d on e.department_id = d.id groupby e.department_id;
项目八:换座位
1),创建seat表:
create table seat(
-> id int auto_increment primary key,
-> student varchar(18) notnull);
2),插入数据:
insert into seat (student) values ('Abbot'),
-> ('Doris'),
-> ('Emerson'),('Green'),('Jeames');
3),改变相邻俩学生的座位,如果学生人数是奇数,则不需要改变最后一个同学的座位:
SELECT(CASE
WHEN MOD(id,2) = 1 AND id = (SELECTCOUNT(*) FROM seat) THEN id
WHEN MOD(id,2) = 1 THEN id+1
ElSE id-1
END) AS id, student
FROM seat
ORDER BY id;
项目九:分数排名(连续:当存在相同分数时,名次数小于班级人数。)
1),创建score表,并插入数据:
createtable score(
-> id int auto_increment primary key,
-> Score float not null);
2),修改Score数据类型:
altertable score modify column Score decimal(10, 2) not null comment '数学成绩';
3),SQL 查询来实现分数排名,名次之间没有“间隔”:
select Score, (select count(distinct Score) from scorewhere Score >= s.Score) as Rank from score s order by Score desc;
项目十:行程与用户
1),创建表格Users、Trips(包含外键),并插入数据,注意:Trips创建的外键在Users中,因此需要先创建Users表格:
CREATETABLE Users (
Users_Id INT NOT NULL PRIMARY KEY,
Banned varchar(5) NOT NULL,
Role ENUM('client', 'driver','partner')
);
CREATETABLE Trips (
Id INT NOT NULL PRIMARY KEY,
Client_Id int NOT NULL,
Driver_Id int not null,
City_Id int not null,
Status ENUM('completed','cancelled_by_driver','cancelled_by_client'),
Request_at date,
foreign key(Client_Id) referencesUsers(Users_Id),
foreign key(Driver_Id) referencesUsers(Users_Id)
);
2),写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率:
SELECT T2.DAY,IFNULL(ROUND((T1.num/T2.num),2),0) AS'Cancellation Rate'
FROM
(SELECT Request_at as Day,count(*) as num
FROM Tripst
LEFT JOINUsers u
ONt.Client_Id = u.Users_Id
WHERE u.Banned!= 'Yes'
AND t.status!= 'completed'
AND Request_at>='2013-10-01' AND Request_at <= '2013-10-03'
GROUP BYDay) AS T1
RIGHT JOIN
(SELECTRequest_at as Day,count(*) as num
FROM Tripst
LEFT JOINUsers u
ONt.Client_Id = u.Users_Id
WHERE u.Banned!= 'Yes'
AND Request_at>='2013-10-01' AND Request_at <= '2013-10-03'
GROUP BYDay) AS T2
ON T1.DAY =T2.DAY;
项目十一:查询部门前3高工资的员工:
1),清空Employee表:
TRUNCATE TABLE employee;
2),重新插入数据:
insert into Employee (name,salary,department_id)values
('Joe','70000','1'),
('Henry','80000','2'),
('Sam','60000','2'),
('Max','90000','1'),
('Janet','69000','1'),
('Randy','85000','1');
3),编写一个 SQL 查询,找出每个部门工资前三高的员工:
SELECT
any_value(d.name) AS Department, any_value(e.name) AS Employee,any_value(e.salary) as salary
FROM
Employee e
leftJOIN
Department dON e.department_id = d.id
WHERE
(SELECT
COUNT(DISTINCT em.salary)
FROM
Employee em
WHERE
em.salary >= e.salary
AND em.department_id = e.department_id) <= 3
GROUP BY department , salary DESC;
项目十二:分数排名
1),score表,排名非连续:
SELECT
s.Score,
(SELECT
COUNT(*) + 1
FROM
score AS s1
WHERE
s1.Score > s.Score) AS Rank
FROM
score s
ORDER BY Score DESC;
本文分享自 MiningAlgorithms 微信公众号,前往查看
如有侵权,请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体同步曝光计划 ,欢迎热爱写作的你一起参与!