hackerrank-sql 刷題-medium部分 ?hard(16題,17題)

1.The PADS

image.png

image.png

image.png
select concat(name,'(',left(OCCUPATION,1),')')
from OCCUPATIONS
order by name;

select concat('There are a total of ',count(OCCUPATION),' ',lower(OCCUPATION),'s.')
from OCCUPATIONS
group by OCCUPATION
order by count(OCCUPATION) asc,OCCUPATION asc;

2.Occupations

image.png

image.png

image.png
select min(Doctor), min(Professor),min(Singer),  min(Actor)
from(
select ROW_NUMBER() OVER(PARTITION By Doctor,Actor,Singer,Professor order by name asc) AS Rownum, 
case when Doctor=1 then name else Null end as Doctor,
case when Actor=1 then name else Null end as Actor,
case when Singer=1 then name else Null end as Singer,
case when Professor=1 then name else Null end as Professor
from occupations
pivot
( count(occupation)
for occupation in(Doctor, Actor, Singer, Professor)) as p

) temp

group by Rownum  ;

mysql字符串大小比較:使用MAX()查詢一個字符串類型的字段時漫谷,字符串類型大小比較是先比較首字符的ASCII碼的大小仔雷,然后依次往后進行比較的。
對字符型數(shù)據(jù)的最大值舔示,是按照首字母由A~Z的順序排列碟婆,越往后,其值越大惕稻。當然竖共,對于漢字則是按照其全拼拼音排列的,若首字符相同俺祠,則比較下一個字符公给,以此類推。
當然锻煌,對與日期時間類型的數(shù)據(jù)也可以求其最大/最小值妓布,其大小排列就是日期時間的早晚姻蚓,越早認為其值越小.

講解:
可以使用用戶定義的變量輔助創(chuàng)建新表宋梧。RowLine 表示這個名字應(yīng)該被放在結(jié)果的第幾行。因為結(jié)果希望將姓名按字母表順序排列狰挡,所以可以先按名字排序捂龄。暫時把這張表叫做t。當?shù)玫絫這樣的表加叁,就可以把查詢語句先寫成"SELECT MIN(Doctor), MIN(Professor), MIN(Singer), MIN(Actor) FROM t GROUP BY RowLine"倦沧。

為了生成表格t,可以定義變量和使用CASE語句它匕。創(chuàng)建四個變量來記錄對應(yīng)行數(shù)(RowLine)展融,一個職業(yè)一個變量。使用CASE來針對不同的職業(yè)進行不同的操作豫柬。

變量的設(shè)置

參考

3.Binary Tree Nodes

image.png

image.png

image.png
select N, 
case when P is NULL then 'Root' 
when N in (select P from BST) then 'Inner' 
else 'Leaf' 
end as Node 
from BST 
order by N;
SELECT N, 
IF(P IS NULL,'Root',IF((SELECT COUNT(*) FROM BST WHERE P=B.N)>0,'Inner','Leaf')) 
FROM BST AS B 
ORDER BY N;

if的這個答案有點蒙圈告希,不太懂為什么要count扑浸。。燕偶。

4.New Companies

image.png

image.png

image.png
SELECT c.company_code, c.founder, 
       COUNT(DISTINCT l.lead_manager_code), COUNT(DISTINCT s.senior_manager_code),
       COUNT(DISTINCT m.manager_code), COUNT(DISTINCT e.employee_code)
FROM Company c, Lead_Manager l, Senior_Manager s, Manager m, Employee e
WHERE c.company_code = l.company_code AND 
      l.lead_manager_code = s.lead_manager_code AND
      s.senior_manager_code = m.senior_manager_code AND
      m.manager_code = e.manager_code
GROUP BY c.company_code, c.founder ORDER BY c.company_code;

5.Contest Leaderboard

image.png

image.png

image.png

image.png
SELECT h.hacker_id, h.name, SUM(score) FROM (
    SELECT hacker_id, challenge_id, MAX(score) AS score FROM SUBMISSIONS
    GROUP BY hacker_id, challenge_id
)t 
JOIN Hackers h on t.hacker_id = h.hacker_id
GROUP BY h.hacker_id, h.name
HAVING SUM(score) > 0
ORDER BY SUM(score) desc, h.hacker_id

6.Weather Observation Station 19

image.png
select ROUND(sqrt(pow(abs(Max(Lat_n)-min(Lat_n)),2)+pow(abs(Max(long_w)-min(long_w)),2)),4) 
from station;

7.Weather Observation Station 20

image.png

求中位數(shù)

SET @r = -1; 
SELECT ROUND(AVG(Temp.L), 4) 
FROM 
(SELECT @r := @r + 1 AS r, Lat_N as L 
 FROM Station 
 ORDER BY Lat_N) Temp 
 WHERE Temp.r = ceil(@r/2) and temp.r = floor(@r/2);

參考

8.The Report

image.png

image.png

image.png
select (case when grade <8 THEN NULL ELSE name END) name, grade, marks 
from students,grades 
where marks between min_Mark and Max_Mark 
order by grade desc, coalesce(name,marks);

9.Top Competitors

image.png

image.png

我的錯誤答案

select Hackers.hacker_id,Hackers.name
from Hackers
inner join Submissions on Hackers.hacker_id=Submissions.hacker_id
inner join Challenges on Challenges.hacker_id= Hackers.hacker_id
inner join Difficulty on Challenges.difficulty_level = Difficulty.difficulty_level
where Submissions.score in(select max(Submissions.score) from Submissions)
having count(Challenges.challenge_id) as cc >1
order by cc desc,Hackers.hacker_id;

正確答案

select h.hacker_id, h.name
from submissions s
inner join challenges c
on s.challenge_id = c.challenge_id
inner join difficulty d
on c.difficulty_level = d.difficulty_level 
inner join hackers h
on s.hacker_id = h.hacker_id
where s.score = d.score and c.difficulty_level = d.difficulty_level
group by h.hacker_id, h.name
having count(s.hacker_id) > 1
order by count(s.hacker_id) desc, s.hacker_id asc

10.Ollivander's Inventory

image.png

image.png

image.png

image.png
SELECT temp2.I, temp2.A, temp2.WNN, temp2.P FROM (SELECT MIN(W1.COINS_NEEDED) AS WN, WP1.AGE as AG, W1.POWER AS PW FROM WANDS W1 INNER JOIN WANDS_PROPERTY WP1 ON W1.CODE=WP1.CODE 
 GROUP BY W1.POWER, WP1.AGE ORDER BY W1.POWER DESC, WP1.AGE DESC) temp1
INNER JOIN
(SELECT W.ID AS I, MIN(W.COINS_NEEDED) AS WNN, WP.AGE as A, W.POWER AS P  FROM WANDS W INNER JOIN WANDS_PROPERTY WP ON W.CODE=WP.CODE 
WHERE WP.IS_EVIL=0
GROUP BY W.POWER, WP.AGE, W.ID ORDER BY W.POWER DESC, WP.AGE DESC) temp2
ON temp1.WN=temp2.WNN AND temp1.PW=temp2.P AND temp1.AG=temp2.A;

11.Challenges

Julia asked her students to create some coding challenges. Write a query to print the hacker_id, name, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by hacker_id. If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.


image.png

image.png

image.png

image.png
SELECT c.hacker_id, h.name, COUNT(c.challenge_id) AS cnt 
FROM Hackers AS h JOIN Challenges AS c ON h.hacker_id = c.hacker_id
GROUP BY c.hacker_id, h.name HAVING
cnt = (SELECT COUNT(c1.challenge_id) FROM Challenges AS c1 GROUP BY c1.hacker_id ORDER BY COUNT(*) DESC LIMIT 1) OR
cnt NOT IN (SELECT COUNT(c2.challenge_id) FROM Challenges AS c2 GROUP BY c2.hacker_id HAVING c2.hacker_id <> c.hacker_id)
ORDER BY cnt DESC, c.hacker_id;

參考答案

12.Projects

You are given a table, Projects, containing three columns: Task_ID, Start_Date and End_Date. It is guaranteed that the difference between the End_Date and the Start_Date is equal to 1 day for each row in the table.


image.png

image.png

image.png
SET sql_mode = '';
SELECT Start_Date, End_Date
FROM 
    (SELECT Start_Date FROM Projects WHERE Start_Date NOT IN (SELECT End_Date FROM Projects)) a,
    (SELECT End_Date FROM Projects WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)) b 
WHERE Start_Date < End_Date
GROUP BY Start_Date 
ORDER BY DATEDIFF(End_Date, Start_Date), Start_Date

13. Placements

image.png

image.png

image.png

image.png
select s.name 
from students s 
inner join friends f 
on s.id=f.id 
inner join packages p 
on p.id=s.id 
inner join packages p1 
on p1.id=f.friend_id 
where (p1.salary-p.salary)>0 
order by p1.salary;

14.Symmetric Pairs

image.png
SELECT f1.X, f1.Y FROM Functions f1
INNER JOIN Functions f2 ON f1.X=f2.Y AND f1.Y=f2.X
GROUP BY f1.X, f1.Y
HAVING COUNT(f1.X)>1 or f1.X<f1.Y
ORDER BY f1.X 

15.Print Prime Numbers

image.png
SET @potential_prime = 1;
SET @divisor = 1;

SELECT GROUP_CONCAT(POTENTIAL_PRIME SEPARATOR '&') FROM
    (SELECT @potential_prime := @potential_prime + 1 AS POTENTIAL_PRIME FROM
    information_schema.tables t1,
    information_schema.tables t2
    LIMIT 1000) list_of_potential_primes
WHERE NOT EXISTS(
    SELECT * FROM
        (SELECT @divisor := @divisor + 1 AS DIVISOR FROM
        information_schema.tables t4,
        information_schema.tables t5
        LIMIT 1000) list_of_divisors
    WHERE MOD(POTENTIAL_PRIME, DIVISOR) = 0 AND POTENTIAL_PRIME <> DIVISOR);

16. Interviews

Samantha interviews many candidates from different colleges using coding challenges and contests. Write a query to print the contest_id, hacker_id, name, and the sums of total_submissions, total_accepted_submissions, total_views, and total_unique_views for each contest sorted by contest_id. Exclude the contest from the result if all four sums are .

Note: A specific contest can be used to screen candidates at more than one college, but each college only holds screening contest.


image.png

image.png

image.png

image.png
select con.contest_id,
        con.hacker_id, 
        con.name, 
        sum(total_submissions), 
        sum(total_accepted_submissions), 
        sum(total_views), sum(total_unique_views)
from contests con 
join colleges col on con.contest_id = col.contest_id 
join challenges cha on  col.college_id = cha.college_id 
left join
(select challenge_id, sum(total_views) as total_views, sum(total_unique_views) as total_unique_views
from view_stats group by challenge_id) vs on cha.challenge_id = vs.challenge_id 
left join
(select challenge_id, sum(total_submissions) as total_submissions, sum(total_accepted_submissions) as total_accepted_submissions from submission_stats group by challenge_id) ss on cha.challenge_id = ss.challenge_id
    group by con.contest_id, con.hacker_id, con.name
        having sum(total_submissions)!=0 or 
                sum(total_accepted_submissions)!=0 or
                sum(total_views)!=0 or
                sum(total_unique_views)!=0
            order by contest_id;

17.15 Days of Learning SQL

image.png

image.png

image.png
select 
submission_date ,

( SELECT COUNT(distinct hacker_id)  
 FROM Submissions s2  
 WHERE s2.submission_date = s1.submission_date AND    (SELECT COUNT(distinct s3.submission_date) FROM      Submissions s3 WHERE s3.hacker_id = s2.hacker_id AND s3.submission_date < s1.submission_date) = dateDIFF(s1.submission_date , '2016-03-01')) ,

(select hacker_id  from submissions s2 where s2.submission_date = s1.submission_date 
group by hacker_id order by count(submission_id) desc , hacker_id limit 1) as shit,
(select name from hackers where hacker_id = shit)
from 
(select distinct submission_date from submissions) s1
group by submission_date

reference:
https://nifannn.github.io/2018/06/01/SQL-%E7%AC%94%E8%AE%B0-Hackerrank-Occupations/
https://blog.csdn.net/qqxyy99/article/details/79980005

最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末喝噪,一起剝皮案震驚了整個濱河市,隨后出現(xiàn)的幾起案子指么,更是在濱河造成了極大的恐慌酝惧,老刑警劉巖,帶你破解...
    沈念sama閱讀 222,464評論 6 517
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件伯诬,死亡現(xiàn)場離奇詭異晚唇,居然都是意外死亡,警方通過查閱死者的電腦和手機盗似,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 95,033評論 3 399
  • 文/潘曉璐 我一進店門缺亮,熙熙樓的掌柜王于貴愁眉苦臉地迎上來,“玉大人桥言,你說我怎么就攤上這事萌踱。” “怎么了号阿?”我有些...
    開封第一講書人閱讀 169,078評論 0 362
  • 文/不壞的土叔 我叫張陵并鸵,是天一觀的道長。 經(jīng)常有香客問我扔涧,道長园担,這世上最難降的妖魔是什么? 我笑而不...
    開封第一講書人閱讀 59,979評論 1 299
  • 正文 為了忘掉前任枯夜,我火速辦了婚禮弯汰,結(jié)果婚禮上,老公的妹妹穿的比我還像新娘湖雹。我一直安慰自己咏闪,他們只是感情好,可當我...
    茶點故事閱讀 69,001評論 6 398
  • 文/花漫 我一把揭開白布摔吏。 她就那樣靜靜地躺著鸽嫂,像睡著了一般。 火紅的嫁衣襯著肌膚如雪征讲。 梳的紋絲不亂的頭發(fā)上据某,一...
    開封第一講書人閱讀 52,584評論 1 312
  • 那天蛛芥,我揣著相機與錄音蒋困,去河邊找鬼。 笑死程拭,一個胖子當著我的面吹牛,可吹牛的內(nèi)容都是我干的筷狼。 我是一名探鬼主播橱夭,決...
    沈念sama閱讀 41,085評論 3 422
  • 文/蒼蘭香墨 我猛地睜開眼,長吁一口氣:“原來是場噩夢啊……” “哼桑逝!你這毒婦竟也來了棘劣?” 一聲冷哼從身側(cè)響起,我...
    開封第一講書人閱讀 40,023評論 0 277
  • 序言:老撾萬榮一對情侶失蹤楞遏,失蹤者是張志新(化名)和其女友劉穎茬暇,沒想到半個月后,有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體寡喝,經(jīng)...
    沈念sama閱讀 46,555評論 1 319
  • 正文 獨居荒郊野嶺守林人離奇死亡糙俗,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 38,626評論 3 342
  • 正文 我和宋清朗相戀三年,在試婚紗的時候發(fā)現(xiàn)自己被綠了预鬓。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片巧骚。...
    茶點故事閱讀 40,769評論 1 353
  • 序言:一個原本活蹦亂跳的男人離奇死亡,死狀恐怖格二,靈堂內(nèi)的尸體忽然破棺而出劈彪,到底是詐尸還是另有隱情,我是刑警寧澤顶猜,帶...
    沈念sama閱讀 36,439評論 5 351
  • 正文 年R本政府宣布沧奴,位于F島的核電站,受9級特大地震影響长窄,放射性物質(zhì)發(fā)生泄漏滔吠。R本人自食惡果不足惜,卻給世界環(huán)境...
    茶點故事閱讀 42,115評論 3 335
  • 文/蒙蒙 一挠日、第九天 我趴在偏房一處隱蔽的房頂上張望疮绷。 院中可真熱鬧,春花似錦嚣潜、人聲如沸冬骚。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,601評論 0 25
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽唉韭。三九已至,卻和暖如春犯犁,著一層夾襖步出監(jiān)牢的瞬間,已是汗流浹背女器。 一陣腳步聲響...
    開封第一講書人閱讀 33,702評論 1 274
  • 我被黑心中介騙來泰國打工酸役, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留,地道東北人。 一個月前我還...
    沈念sama閱讀 49,191評論 3 378
  • 正文 我出身青樓涣澡,卻偏偏與公主長得像贱呐,于是被迫代替她去往敵國和親。 傳聞我的和親對象是個殘疾皇子入桂,可洞房花燭夜當晚...
    茶點故事閱讀 45,781評論 2 361

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

  • 官網(wǎng) 中文版本 好的網(wǎng)站 Content-type: text/htmlBASH Section: User ...
    不排版閱讀 4,407評論 0 5
  • 第5章 引用類型(返回首頁) 本章內(nèi)容 使用對象 創(chuàng)建并操作數(shù)組 理解基本的JavaScript類型 使用基本類型...
    大學(xué)一百閱讀 3,238評論 0 4
  • ORA-00001: 違反唯一約束條件 (.) 錯誤說明:當在唯一索引所對應(yīng)的列上鍵入重復(fù)值時奄薇,會觸發(fā)此異常。 O...
    我想起個好名字閱讀 5,341評論 0 9
  • Lua 5.1 參考手冊 by Roberto Ierusalimschy, Luiz Henrique de F...
    蘇黎九歌閱讀 13,836評論 0 38
  • 訓(xùn)練抗愁,尤其是對學(xué)習(xí)材料中最困難的部分更要刻意訓(xùn)練馁蒂,這樣才能讓那些擁有普通智力的人有機會上升到“天賦異稟”的境界。就...
    iCloudEnd閱讀 168評論 0 1