LeetCode Database(175-180)

175. Combine Two Tables

select FirstName,LastName,City,State
from Person
left join Address
on Person.PersonId = Address.PersonId;

select FirstName,LastName,City,State
from Person
left join Address
using(PersonId);

select FirstName,LastName,City,State
from Person
natural left join Address;

176. Second Highest Salary

select max(Salary) SecondHighestSalary
from Employee
where Salary < (select max(Salary) from Employee);

select max(Salary) SecondHighestSalary
from Employee
where Salary not in (select max(Salary) from Employee);

select(
    select distinct Salary
    from Employee order by Salary desc
    limit 1 offset 1
)as SecondHighestSalary;

177. Nth Highest Salary

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    set n = n-1;
    RETURN (
        # Write your MySQL query statement below.
        select distinct e.Salary from Employee e order by Salary Desc limit n,1 
    );
END

178. Rank Scores

select Scores.Score, count(Ranking.Score) as Rank
from Scores, (select distinct Score from Scores) Ranking
where Scores.Score <= Ranking.Score
group by Scores.ID, Scores.Score
order by Scores.Score desc;

select Score,Rank from
(select Score,
@Rank := IF(@prevScore = Score,@Rank,@Rank+1) as Rank,
@prevScore := Score from Scores
order by Score desc) s,
(select @Rank := 0,@prevScore := NULL) r;

180. Consecutive Numbers

select distinct l1.Num as ConsecutiveNums
from Logs l1, Logs l2, Logs l3
where l1.Id = l2.Id-1 and l2.Id = l3.Id-1 and l1.Num = l2.Num and l2.Num = l3.Num;

select distinct Num as ConsecutiveNums
from(
    select Num,
    case
        when @prev = Num then @count := @count+1
        when (@prev := Num) is not NULL then @count := 1
    END cnt
    from Logs,(select @prev := NULL) x
    order by Id
) as T
where T.cnt >= 3;