HackerRank SQL练习题答案大全

太弱智的就不写了

Weather Observation Station 5

select city,length(city) from station order by length(city),city limit 1;
select city,length(city) from station order by length(city) desc,city limit 1;

Weather Observation Station 8

select city from station where city REGEXP ^[AEIOU].*[aeiou]$;

Higher Than 75 Marks

select name from students where marks > 75 order by right(name,3),id asc;

Type of Triangle

select
case when(a+b>c and a+c>b and c+b>a) then (
case when(a=b and b=c) then Equilateral
when(a=b or b=c or a=c) then Isosceles
else Scalene end
)
else Not A Triangle end
from triangles;

The Blunder

select ceil(AVG(salary) - AVG(replace(salary,0,))) from employees;

Top Earners

select max(salary*months),count(employee_id) from employee where salary*months = (select max(salary*months) from employee);

Weather Observation Station 18

set @a =(select min(lat_n) from station);
set @b = (select min(long_w) from station);
set @c = (select max(lat_n) from station);
set @d = (select max(long_w) from station);
select round(@c+@d-@a-@b,4);

Draw The Triangle 1

set @i = 21;
select repeat(* ,@i:=@i-1) from information_schema.tables limit 20;

Binary Tree Nodes

/*
Enter your query here.
*/
select B2.N,
case when ((select count(*) from BST B1 where B1.P = B2.N)=0) then Leaf
when (isnull(B2.P) = 1) then Root
else Inner end
from BST B2 order by B2.N;

Placements

/*
Enter your query here.
*/
select
T.name
from (
    select 
    S.Id Id,
    S.name name,
    P.Salary Salary,
    F.Friend_Id FId,
    (select Packages.Salary from Packages where Packages.Id=FId) FSalary
    FROM Students S, Friends F, Packages P
    where F.Id=S.Id
    and P.Id=S.Id
) T
where FSalary>Salary
order by FSalary;