作業(yè)#
項(xiàng)目七: 各部門(mén)工資最高的員工（難度：中等）
創(chuàng)建Employee 表陆蟆，包含所有員工信息，每個(gè)員工有其對(duì)應(yīng)的 Id, salary 和 department Id戏锹。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
+----+-------+--------+--------------+
創(chuàng)建Department 表妄田，包含公司所有部門(mén)的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
編寫(xiě)一個(gè) SQL 查詢沥邻，找出每個(gè)部門(mén)工資最高的員工。例如羊娃，根據(jù)上述給定的表格唐全，Max 在 IT 部門(mén)有最高工資，Henry 在 Sales 部門(mén)有最高工資蕊玷。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
- 創(chuàng)建表：
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
`Id` int(11) NOT NULL,
`Name` varchar(255) NOT NULL,
`Salary` int(11) NOT NULL,
`DepartmentId` int(11) NOT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
`Id` int(11) NOT NULL,
`Name` varchar(255) NOT NULL,
PRIMARY KEY (`Id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO Employee VALUE('1','Joe','70000','1');
INSERT INTO Employee VALUE('2','Henry','80000','2');
INSERT INTO Employee VALUE('3','Sam','60000','2');
INSERT INTO Employee VALUE('4','Max','90000','1');
INSERT INTO Department VALUE('1','IT');
INSERT INTO Department VALUE('2','Sales');
SELECT
d.Name AS Department,
e.Name AS Employee,
e.Salary AS Salary
FROM
Employee AS e,
Department AS d
WHERE e.DepartmentId = d.Id AND
e.Salary >= (SELECT MAX(Salary) FROM Employee WHERE DepartmentId=d.Id)
ORDER BY Salary DESC;
項(xiàng)目八: 換座位（難度：中等）
小美是一所中學(xué)的信息科技老師邮利，她有一張 seat 座位表，平時(shí)用來(lái)儲(chǔ)存學(xué)生名字和與他們相對(duì)應(yīng)的座位 id垃帅。
其中縱列的 id 是連續(xù)遞增的
小美想改變相鄰倆學(xué)生的座位延届。
你能不能幫她寫(xiě)一個(gè) SQL query 來(lái)輸出小美想要的結(jié)果呢？
請(qǐng)創(chuàng)建如下所示seat表：
示例：
+---------+---------+
| id | student |
+---------+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+---------+---------+
假如數(shù)據(jù)輸入的是上表贸诚，則輸出結(jié)果如下：
+---------+---------+
| id | student |
+---------+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+---------+---------+
注意：
如果學(xué)生人數(shù)是奇數(shù)方庭，則不需要改變最后一個(gè)同學(xué)的座位。
CREATE TABLE seat(
id INT ,
student VARCHAR(20)
)
#插入數(shù)據(jù)
INSERT INTO seat VALUE('1','Abbot');
INSERT INTO seat VALUE('2','Doris');
INSERT INTO seat VALUE('3','Emerson');
INSERT INTO seat VALUE('4','Green');
INSERT INTO seat VALUE('5','Jeames');
SELECT *
FROM (
-- 遇到偶數(shù)往上移一個(gè)位置
SELECT id-1 AS id,student
FROM seat
WHERE id%2=0
UNION
-- 遇到奇數(shù)往下移一個(gè)位置
SELECT id+1 AS id,student
FROM seat
WHERE id%2=1 AND (id+1)<=(SELECT COUNT(*) FROM seat)
UNION
-- 處理最后一個(gè)位置赦颇，這里只考慮奇數(shù)情況二鳄，保持不變（偶數(shù)已經(jīng)在第一步里處理了）
SELECT id AS id,student
FROM seat
WHERE id%2=1 AND(id+1)>(SELECT COUNT(*) FROM seat)
) AS c1
ORDER BY id ASC;
項(xiàng)目九: 分?jǐn)?shù)排名（難度：中等）
編寫(xiě)一個(gè) SQL 查詢來(lái)實(shí)現(xiàn)分?jǐn)?shù)排名赴涵。如果兩個(gè)分?jǐn)?shù)相同媒怯，則兩個(gè)分?jǐn)?shù)排名（Rank）相同。請(qǐng)注意髓窜，平分后的下一個(gè)名次應(yīng)該是下一個(gè)連續(xù)的整數(shù)值扇苞。換句話說(shuō)，名次之間不應(yīng)該有“間隔”寄纵。
創(chuàng)建以下score表：
+----+-------+
| Id | Score |
+----+-------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+
例如鳖敷，根據(jù)上述給定的 Scores 表，你的查詢應(yīng)該返回（按分?jǐn)?shù)從高到低排列）：
+-------+------+
| Score | Rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+------+
CREATE TABLE scores(
ID INT,
Score FLOAT);
INSERT INTO scores VALUE('1','3.50');
INSERT INTO scores VALUE('2','3.65');
INSERT INTO scores VALUE('3','4.00');
INSERT INTO scores VALUE('4','3.85');
INSERT INTO scores VALUE('5','4.00');
INSERT INTO scores VALUE('6','3.65');
SELECT score,(SELECT COUNT(DISTINCT score)
FROM scores
WHERE score>=s.score) AS ran_k
FROM scores AS s
ORDER BY score DESC;
4.2 MySQL 實(shí)戰(zhàn) - 復(fù)雜項(xiàng)目
作業(yè)#
項(xiàng)目十：行程和用戶（難度：困難）
Trips 表中存所有出租車的行程信息程拭。每段行程有唯一鍵 Id定踱，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外鍵。Status 是枚舉類型恃鞋，枚舉成員為 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)崖媚。
+----+-----------+-----------+---------+--------------------+----------+
| Id | Client_Id | Driver_Id | City_Id | Status |Request_at|
+----+-----------+-----------+---------+--------------------+----------+
| 1 | 1 | 10 | 1 | completed |2013-10-01|
| 2 | 2 | 11 | 1 | cancelled_by_driver|2013-10-01|
| 3 | 3 | 12 | 6 | completed |2013-10-01|
| 4 | 4 | 13 | 6 | cancelled_by_client|2013-10-01|
| 5 | 1 | 10 | 1 | completed |2013-10-02|
| 6 | 2 | 11 | 6 | completed |2013-10-02|
| 7 | 3 | 12 | 6 | completed |2013-10-02|
| 8 | 2 | 12 | 12 | completed |2013-10-03|
| 9 | 3 | 10 | 12 | completed |2013-10-03|
| 10 | 4 | 13 | 12 | cancelled_by_driver|2013-10-03|
+----+-----------+-----------+---------+--------------------+----------+
Users 表存所有用戶。每個(gè)用戶有唯一鍵 Users_Id。Banned 表示這個(gè)用戶是否被禁止挨稿，Role 則是一個(gè)表示（‘client’, ‘driver’, ‘partner’）的枚舉類型严拒。
+----------+--------+--------+
| Users_Id | Banned | Role |
+----------+--------+--------+
| 1 | No | client |
| 2 | Yes | client |
| 3 | No | client |
| 4 | No | client |
| 10 | No | driver |
| 11 | No | driver |
| 12 | No | driver |
| 13 | No | driver |
+----------+--------+--------+
寫(xiě)一段 SQL 語(yǔ)句查出 2013年10月1日 至 2013年10月3日 期間非禁止用戶的取消率≤牛基于上表赛蔫，你的 SQL 語(yǔ)句應(yīng)返回如下結(jié)果，取消率（Cancellation Rate）保留兩位小數(shù)泥张。
+------------+-------------------+
| Day | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 | 0.33 |
| 2013-10-02 | 0.00 |
| 2013-10-03 | 0.50 |
+------------+-------------------+
- 創(chuàng)建表
CREATE TABLE trips(
Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
Client_Id INT NOT NULL,
Driver_Id INT NOT NULL,
City_Id INT NOT NULL,
Status ENUM(‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’) NOT NULL,
Request_at DATE DEFAULT NULL);
CREATE TABLE Users(
Users_Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
Banned VARCHAR(10) NOT NULL,
Role ENUM(‘client’, ‘driver’, ‘partnet’) NOT NULL);
- 插入數(shù)據(jù)
NSERT INTO trips
VALUES (1,1,10,1,‘completed’,‘2013-10-01’),
(2,2,11,1, ‘cancelled_by_driver’,‘2013-10-01’),
(3,3,12,6,‘completed’,‘2013-10-01’),
(4,4,13,6,‘cancelled_by_client’,‘2013-10-01’),
(5,1,10,1,‘completed’,‘2013-10-02’),
(6,2,11,6,‘completed’,‘2013-10-02’),
(7,3,12,6,‘completed’,‘2013-10-02’),
(8,2,12,12,‘completed’,‘2013-10-03’),
(9,3,10,12,‘completed’,‘2013-10-03’),
(10,4,13,12, ‘cancelled_by_driver’,‘2013-10-03’);
INSERT INTO Users
VALUES (1, ‘No’, ‘client’),
(2, ‘Yes’, ‘client’),
(3, ‘No’, ‘client’),
(4, ‘No’, ‘client’),
(10, ‘No’, ‘driver’),
(11, ‘No’, ‘driver’),
(12, ‘No’, ‘driver’),
(13, ‘No’, ‘driver’);
- 查詢表:
SELECT t.Request_at AS ‘Day’,
ROUND((SUM(CASE WHEN t.Status LIKE ‘cancelled%’ THEN 1 ELSE 0 END))/COUNT(*),2) AS ‘Cancellation Rate’
FROM Trips AS t INNER JOIN Users AS u
ON u.Users_Id = t.Client_Id AND u.Banned = ‘No’
GROUP BY t.Request_at ;
項(xiàng)目十一：各部門(mén)前3高工資的員工（難度：中等）
將項(xiàng)目7中的employee表清空呵恢，重新插入以下數(shù)據(jù)（其實(shí)是多插入5,6兩行）：
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
+----+-------+--------+--------------+
編寫(xiě)一個(gè) SQL 查詢，找出每個(gè)部門(mén)工資前三高的員工媚创。例如瑰剃，根據(jù)上述給定的表格，查詢結(jié)果應(yīng)返回：
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
此外筝野，請(qǐng)考慮實(shí)現(xiàn)各部門(mén)前N高工資的員工功能晌姚。
項(xiàng)目十二 分?jǐn)?shù)排名 - （難度：中等）
依然是昨天的分?jǐn)?shù)表，實(shí)現(xiàn)排名功能歇竟，但是排名是非連續(xù)的挥唠，如下：
+-------+------+
| Score | Rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 3 |
| 3.65 | 4 |
| 3.65 | 4 |
| 3.50 | 6 |
+-------+------
