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
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;