WK6 Practice Continuing

練習(xí)一: 各部門工資最高的員工（難度：中等）

創(chuàng)建Employee 表，包含所有員工信息，每個(gè)員工有其對應(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 表，包含公司所有部門的信息。

+----+----------+

| Id | Name? ? |

+----+----------+

| 1? | IT? ? ? |

| 2? | Sales? ? |

+----+----------+

編寫一個(gè) SQL 查詢，找出每個(gè)部門工資最高的員工。例如，根據(jù)上述給定的表格，Max 在 IT 部門有最高工資，Henry 在 Sales 部門有最高工資。

+------------+----------+--------+

| Department | Employee | Salary |

+------------+----------+--------+

| IT? ? ? ? | Max? ? ? | 90000? |

| Sales? ? ? | Henry? ? | 80000? |

+------------+----------+--------+

練習(xí)二: 換座位（難度：中等）

小美是一所中學(xué)的信息科技老師，她有一張 seat 座位表，平時(shí)用來儲存學(xué)生名字和與他們相對應(yīng)的座位 id。

其中縱列的id是連續(xù)遞增的

小美想改變相鄰倆學(xué)生的座位。

你能不能幫她寫一個(gè) SQL query 來輸出小美想要的結(jié)果呢？

請創(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é)的座位。

練習(xí)三: 分?jǐn)?shù)排名（難度：中等）

編寫一個(gè) SQL 查詢來實(shí)現(xiàn)分?jǐn)?shù)排名。如果兩個(gè)分?jǐn)?shù)相同，則兩個(gè)分?jǐn)?shù)排名（Rank）相同。請注意，平分后的下一個(gè)名次應(yīng)該是下一個(gè)連續(xù)的整數(shù)值。換句話說，名次之間不應(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? ? |

+-------+------+

練習(xí)四：連續(xù)出現(xiàn)的數(shù)字（難度：中等）

編寫一個(gè) SQL 查詢，查找所有至少連續(xù)出現(xiàn)三次的數(shù)字。

+----+-----+

| Id | Num |

+----+-----+

| 1? |? 1? |

| 2? |? 1? |

| 3? |? 1? |

| 4? |? 2? |

| 5? |? 1? |

| 6? |? 2? |

| 7? |? 2? |

+----+-----+

例如，給定上面的 Logs 表， 1 是唯一連續(xù)出現(xiàn)至少三次的數(shù)字。

+-----------------+

| ConsecutiveNums |

+-----------------+

| 1? ? ? ? ? ? ? |

+-----------------+

練習(xí)五：樹節(jié)點(diǎn) （難度：中等）

對于tree表，id是樹節(jié)點(diǎn)的標(biāo)識，p_id是其父節(jié)點(diǎn)的id。

+----+------+

| id | p_id |

+----+------+

| 1? | null |

| 2? | 1? ? |

| 3? | 1? ? |

| 4? | 2? ? |

| 5? | 2? ? |

+----+------+

每個(gè)節(jié)點(diǎn)都是以下三種類型中的一種：

Root: 如果節(jié)點(diǎn)是根節(jié)點(diǎn)。

Leaf: 如果節(jié)點(diǎn)是葉子節(jié)點(diǎn)。

Inner: 如果節(jié)點(diǎn)既不是根節(jié)點(diǎn)也不是葉子節(jié)點(diǎn)。

寫一條查詢語句打印節(jié)點(diǎn)id及對應(yīng)的節(jié)點(diǎn)類型。按照節(jié)點(diǎn)id排序。上面例子的對應(yīng)結(jié)果為：

+----+------+

| id | Type |

+----+------+

| 1? | Root |

| 2? | Inner|

| 3? | Leaf |

| 4? | Leaf |

| 5? | Leaf |

+----+------+

說明

節(jié)點(diǎn)’1’是根節(jié)點(diǎn)，因?yàn)樗母腹?jié)點(diǎn)為NULL，有’2’和’3’兩個(gè)子節(jié)點(diǎn)。

節(jié)點(diǎn)’2’是內(nèi)部節(jié)點(diǎn)，因?yàn)樗母腹?jié)點(diǎn)是’1’，有子節(jié)點(diǎn)’4’和’5’。

節(jié)點(diǎn)’3’，‘4’，'5’是葉子節(jié)點(diǎn)，因?yàn)樗鼈冇懈腹?jié)點(diǎn)但沒有子節(jié)點(diǎn)。

下面是樹的圖形：

? ? 1? ? ? ?

? /? \

2? ? 3? ?

/ \

4? 5

注意

如果一個(gè)樹只有一個(gè)節(jié)點(diǎn)，只需要輸出根節(jié)點(diǎn)屬性。

練習(xí)六：至少有五名直接下屬的經(jīng)理 （難度：中等）

Employee表包含所有員工及其上級的信息。每位員工都有一個(gè)Id，并且還有一個(gè)對應(yīng)主管的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個(gè)下屬的主管。對于上面的表，結(jié)果應(yīng)輸出：

+-------+

| Name? |

+-------+

| John? |

+-------+

注意:

沒有人向自己匯報(bào)。

練習(xí)七: 分?jǐn)?shù)排名 （難度：中等）

練習(xí)三的分?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? ? |

+-------+------

練習(xí)八：查詢回答率最高的問題 （難度：中等）

求出survey_log表中回答率最高的問題，表格的字段有：uid, action, question_id, answer_id, q_num, timestamp。

uid是用戶id；action的值為：“show”， “answer”， “skip”；當(dāng)action是"answer"時(shí)，answer_id不為空，相反，當(dāng)action是"show"和"skip"時(shí)為空（null）；q_num是問題的數(shù)字序號。

寫一條sql語句找出回答率最高的問題。

舉例：

輸入

uidactionquestion_idanswer_idq_numtimestamp

5show285null1123

5answer2851241241124

5show369null2125

5skip369null2126

輸出

survey_log

285

說明

問題285的回答率為1/1，然而問題369的回答率是0/1，所以輸出是285。

**注意：**最高回答率的意思是：同一個(gè)問題出現(xiàn)的次數(shù)中回答的比例。

練習(xí)九：各部門前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? ? ? ? ? ? |

+----+-------+--------+--------------+

編寫一個(gè) SQL 查詢，找出每個(gè)部門工資前三高的員工。例如，根據(jù)上述給定的表格，查詢結(jié)果應(yīng)返回：

+------------+----------+--------+

| Department | Employee | Salary |

+------------+----------+--------+

| IT? ? ? ? | Max? ? ? | 90000? |

| IT? ? ? ? | Randy? ? | 85000? |

| IT? ? ? ? | Joe? ? ? | 70000? |

| Sales? ? ? | Henry? ? | 80000? |

| Sales? ? ? | Sam? ? ? | 60000? |

+------------+----------+--------+

此外，請考慮實(shí)現(xiàn)各部門前N高工資的員工功能。

練習(xí)十：平面上最近距離 (難度: 困難）

point_2d表包含一個(gè)平面內(nèi)一些點(diǎn)（超過兩個(gè)）的坐標(biāo)值（x，y）。

寫一條查詢語句求出這些點(diǎn)中的最短距離并保留2位小數(shù)。

|x? | y? |

|----|----|

| -1 | -1 |

|? 0 |? 0 |

| -1 | -2 |

最短距離是1，從點(diǎn)（-1，-1）到點(diǎn)（-1，-2）。所以輸出結(jié)果為：

| shortest |

1.00

+--------+

|shortest|

+--------+

|1.00? ? |

+--------+

注意：?所有點(diǎn)的最大距離小于10000。

練習(xí)十一：行程和用戶（難度：困難）

Trips 表中存所有出租車的行程信息。每段行程有唯一鍵 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外鍵。Status 是枚舉類型，枚舉成員為 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。

IdClient_IdDriver_IdCity_IdStatusRequest_at

11101completed2013-10-1

22111cancelled_by_driver2013-10-1

33126completed2013-10-1

44136cancelled_by_client2013-10-1

51101completed2013-10-2

62116completed2013-10-2

73126completed2013-10-2

821212completed2013-10-3

931012completed2013-10-3

1041312cancelled_by_driver2013-10-3

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 |

+----------+--------+--------+

寫一段 SQL 語句查出2013年10月1日至2013年10月3日期間非禁止用戶的取消率?；谏媳?，你的 SQL 語句應(yīng)返回如下結(jié)果，取消率（Cancellation Rate）保留兩位小數(shù)。

+------------+-------------------+

|? ? Day? ? | Cancellation Rate |

+------------+-------------------+

| 2013-10-01 |? ? ? 0.33? ? ? ? |

| 2013-10-02 |? ? ? 0.00? ? ? ? |

| 2013-10-03 |? ? ? 0.50? ? ? ? |

+------------+-------------------+

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • 1 安裝MySQL 進(jìn)入MySQL官網(wǎng)，登錄 如何在Macbook上安裝MySQL_百度經(jīng)驗(yàn) 我之后改了個(gè)，怕忘記...
    喬大葉_803e閱讀 769評論 0贊 0
  • 第一次玩簡書，格式還不太會設(shè)置，大家見諒，排版好的文章在公眾號“八哥的成長心路札記”上有，微信號是bager191...
    albert184閱讀 791評論 0贊 0
  • 力扣題目資源來自“熊大的數(shù)據(jù)分析之路”，寫下這系列練習(xí)記錄是為了自己實(shí)踐一遍，非商業(yè)用途。 1. 組合兩個(gè)表 表1...
    元寶2020閱讀 897評論 1贊 0
  • 作業(yè)# 項(xiàng)目七: 各部門工資最高的員工（難度：中等）創(chuàng)建Employee 表，包含所有員工信息，每個(gè)員工有其對應(yīng)的...
    jangohe閱讀 284評論 0贊 0
  • 177、第二高薪水編寫一個(gè) SQL 查詢，獲取 Employee 表中第二高的薪水（Salary） 。 +----...
    BigBigFlower閱讀 308評論 0贊 1

友情鏈接更多精彩內(nèi)容