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码的大小，然后依次往后进行比较的。
对字符型数据的最大值，是按照首字母由A～Z的顺序排列，越往后，其值越大。当然，对于汉字则是按照其全拼拼音排列的，若首字符相同，则比较下一个字符，以此类推。
当然，对与日期时间类型的数据也可以求其最大/最小值，其大小排列就是日期时间的早晚，越早认为其值越小.
讲解：
可以使用用户定义的变量辅助创建新表。RowLine 表示这个名字应该被放在结果的第几行。因为结果希望将姓名按字母表顺序排列，所以可以先按名字排序。暂时把这张表叫做t。当得到t这样的表，就可以把查询语句先写成"SELECT MIN(Doctor), MIN(Professor), MIN(Singer), MIN(Actor) FROM t GROUP BY RowLine"。
为了生成表格t，可以定义变量和使用CASE语句。创建四个变量来记录对应行数（RowLine），一个职业一个变量。使用CASE来针对不同的职业进行不同的操作。
变量的设置
参考
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
求中位数
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
