mysql基礎實戰第三部分

• mysql實戰六:找到各部門工資最高的員工（難度：中等）

  建立 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 部門有最高工資。

  ±-----------±---------±-------+

  | Department | Employee | Salary |

  ±-----------±---------±-------+

  | IT | Max | 90000 |

  | Sales | Henry | 80000 |

  ±-----------±---------±-------+

建立表及插入資料:

表一:

表二:

代碼:

mysql> select d.name as Department,
    -> e.name as Employee,salary
    -> from Employee e join Department d
    -> on e.departmentid = d.id
    -> where salary in(
    -> select max(salary)
    -> from Employee group by departmentid)
    -> order by salary desc;
           

結果如下:

• mysql實戰七:換座位（難度：中等）

  小美是一所中學的資訊科技老師，她有一張 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 |

  ±--------±--------+

  注意：

  如果學生人數是奇數，則不需要改變最後一個同學的座位。

  建立表及插入資料如下:

  

  代碼:

mysql> select (case when mod (id, 2)= 1 AND id = (select count(*) from seat)THEN id
    -> when mod (id, 2)=1 THEN id + 1
    -> else id-1
    -> end)
    -> as id, student
    -> from seat order by id;
           

結果如下:

• mysql實戰八:分數排名（難度：中等）

  編寫一個 SQL 查詢來實作分數排名。如果兩個分數相同，則兩個分數排名（Rank）相同。請注意，平分後的下一個名次應該是下一個連續的整數值。換句話說，名次之間不應該有“間隔”。

  建立以下 score 表：

  ±—±------+

  | Id | Score |

  ±—±------+

  | 1 | 3.50 |

  | 2 | 3.65 |

  | 3 | 4.00 |

  | 4 | 3.85 |

  | 5 | 4.00

  | 6 | 3.65 |

  ±—±------+

  例如，根據上述給定的 scores 表，你的查詢應該傳回（按分數從高到低排列）：

  ±------±-----+

  | Score | Rank |

  ±------±-----+

  | 4.00 | 1 |

  | 4.00 | 1 |

  | 3.85 | 2 |

  | 3.65 | 3 |

  | 3.65 | 3 |

  | 3.50 | 4 |

  ±------±-----+

  建立表及插入資料如下:

  

  結果如下:

  

• mysql實戰九:行程和使用者（難度：困難）

  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 表存所有使用者。每個使用者有唯一鍵 Users_Id。Banned 表示這個使用者是否被禁止，Role 則是一個表示（‘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 |

  ±---------±-------±-------+

  寫一段 SQL 語句查出 2013年10月1日 至 2013年10月3日 期間非禁止使用者的取消率。基于上表，你的 SQL 語句應傳回如下結果，取消率（Cancellation Rate）保留兩位小數。

  ±-----------±------------------+

  | Day | Cancellation Rate |

  ±-----------±------------------+

  | 2013-10-01 | 0.33 |

  | 2013-10-02 | 0.00 |

  | 2013-10-03 | 0.50 |

  ±-----------±------------------+

  建立表:

  

  Usets表插入資料:

  

  Trips表插入資料:

  

  結果:

  

• mysql實戰十:各部門前三的高工資（難度：中等）

  将項目7中的 employee 表清空，重新插入以下資料（其實是多插入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 |

  ±—±------±-------±-------------+

  編寫一個 SQL 查詢，找出每個部門工資前三高的員工。例如，根據上述給定的表格，查詢結果應傳回：

  ±-----------±---------±-------+

  | Department | Employee | Salary |

  ±-----------±— ------±-------+

  | IT | Max | 90000 |

  | IT | Randy | 85000 |

  | IT | Joe | 70000 |

  | Sales | Henry | 80000 |

  | Sales | Sam | 60000 |

  ±-----------±---------±-------+

此外，請考慮實作各部門前N高工資的員工功能。

删除原有的表,并插入資料:

表一:

表二:

結果:

• mysql實戰十一:分數排名（難度：中等）

  依然是昨天的分數表，實作排名功能，但是排名是非連續的，如下：

  ±------±-----+

  | Score | Rank |

  ±------±-----+

  | 4.00 | 1 |

  | 4.00 | 1 |

  | 3.85 | 3 |

  | 3.65 | 4 |

  | 3.65 | 4 |

  | 3.50 | 6 |

  ±------±-----

  結果如下: