1.The PADS
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
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è)進行不同的操作豫柬。
參考
3.Binary Tree Nodes
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
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
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
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
求中位數(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
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
我的錯誤答案
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
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.
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.
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
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
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
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.
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
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
