MySQL简单实践（三）

项目七：部门最高工资员工

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;