4.1 MySQL 實(shí)戰(zhàn)

作業(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 |
+-------+------

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個(gè)濱河市焕议,隨后出現(xiàn)的幾起案子宝磨,更是在濱河造成了極大的恐慌,老刑警劉巖盅安,帶你破解...
    沈念sama閱讀 223,002評(píng)論 6 519
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件唤锉,死亡現(xiàn)場(chǎng)離奇詭異,居然都是意外死亡别瞭,警方通過(guò)查閱死者的電腦和手機(jī)窿祥,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 95,357評(píng)論 3 400
  • 文/潘曉璐 我一進(jìn)店門(mén),熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)蝙寨,“玉大人晒衩,你說(shuō)我怎么就攤上這事∏酵幔” “怎么了听系?”我有些...
    開(kāi)封第一講書(shū)人閱讀 169,787評(píng)論 0 365
  • 文/不壞的土叔 我叫張陵,是天一觀的道長(zhǎng)虹菲。 經(jīng)常有香客問(wèn)我靠胜,道長(zhǎng),這世上最難降的妖魔是什么? 我笑而不...
    開(kāi)封第一講書(shū)人閱讀 60,237評(píng)論 1 300
  • 正文 為了忘掉前任浪漠,我火速辦了婚禮菠赚,結(jié)果婚禮上,老公的妹妹穿的比我還像新娘郑藏。我一直安慰自己衡查,他們只是感情好,可當(dāng)我...
    茶點(diǎn)故事閱讀 69,237評(píng)論 6 398
  • 文/花漫 我一把揭開(kāi)白布必盖。 她就那樣靜靜地躺著拌牲,像睡著了一般。 火紅的嫁衣襯著肌膚如雪歌粥。 梳的紋絲不亂的頭發(fā)上塌忽,一...
    開(kāi)封第一講書(shū)人閱讀 52,821評(píng)論 1 314
  • 那天,我揣著相機(jī)與錄音失驶,去河邊找鬼土居。 笑死,一個(gè)胖子當(dāng)著我的面吹牛嬉探,可吹牛的內(nèi)容都是我干的擦耀。 我是一名探鬼主播,決...
    沈念sama閱讀 41,236評(píng)論 3 424
  • 文/蒼蘭香墨 我猛地睜開(kāi)眼涩堤,長(zhǎng)吁一口氣:“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼眷蜓!你這毒婦竟也來(lái)了?” 一聲冷哼從身側(cè)響起胎围,我...
    開(kāi)封第一講書(shū)人閱讀 40,196評(píng)論 0 277
  • 序言:老撾萬(wàn)榮一對(duì)情侶失蹤吁系,失蹤者是張志新(化名)和其女友劉穎,沒(méi)想到半個(gè)月后白魂,有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體汽纤,經(jīng)...
    沈念sama閱讀 46,716評(píng)論 1 320
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡,尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 38,794評(píng)論 3 343
  • 正文 我和宋清朗相戀三年福荸,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了蕴坪。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點(diǎn)故事閱讀 40,928評(píng)論 1 353
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡逞姿,死狀恐怖辞嗡,靈堂內(nèi)的尸體忽然破棺而出,到底是詐尸還是另有隱情滞造,我是刑警寧澤,帶...
    沈念sama閱讀 36,583評(píng)論 5 351
  • 正文 年R本政府宣布栋烤,位于F島的核電站谒养,受9級(jí)特大地震影響,放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜买窟,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 42,264評(píng)論 3 336
  • 文/蒙蒙 一丰泊、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧始绍,春花似錦瞳购、人聲如沸。這莊子的主人今日做“春日...
    開(kāi)封第一講書(shū)人閱讀 32,755評(píng)論 0 25
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)。三九已至吞杭,卻和暖如春盏浇,著一層夾襖步出監(jiān)牢的瞬間,已是汗流浹背芽狗。 一陣腳步聲響...
    開(kāi)封第一講書(shū)人閱讀 33,869評(píng)論 1 274
  • 我被黑心中介騙來(lái)泰國(guó)打工绢掰, 沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留,地道東北人童擎。 一個(gè)月前我還...
    沈念sama閱讀 49,378評(píng)論 3 379
  • 正文 我出身青樓滴劲,卻偏偏與公主長(zhǎng)得像,于是被迫代替她去往敵國(guó)和親顾复。 傳聞我的和親對(duì)象是個(gè)殘疾皇子哑芹,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 45,937評(píng)論 2 361

推薦閱讀更多精彩內(nèi)容

  • 4.1 MySQL 實(shí)戰(zhàn) 感謝https://blog.csdn.net/u011490595/article/d...
    假如時(shí)光不完美閱讀 268評(píng)論 0 0
  • 183. Customers Who Never Order Problem Suppose that a web...
    olivia_ong閱讀 379評(píng)論 0 0
  • 1.部門(mén)工資前3高的員工Employee 表包含所有員工信息,每個(gè)員工有其對(duì)應(yīng)的 Id, salary 和 dep...
    katelin閱讀 586評(píng)論 0 2
  • 一. Java基礎(chǔ)部分.................................................
    wy_sure閱讀 3,815評(píng)論 0 11
  • 有些人注定是為了路過(guò)而出現(xiàn)的在我心的沙地上留下一點(diǎn)點(diǎn)足跡浪過(guò)捕透,洗凈一地歡笑從此聪姿,一切只存留于昨天 后來(lái),記憶的浪花...
    易耳閱讀 55評(píng)論 0 0