Review: SQL Problems

## Blog link: https://code.dennyzhang.com/classes-more-than-5-students

select class from courses
group by class having count(distinct student)>=5;

## Blog link: https://code.dennyzhang.com/triangle-judgement
SELECT CASE WHEN Col1 >= Col2 AND Col1 >= Col3 AND Col1 >= Col4 THEN Col1
            WHEN Col2 >= Col1 AND Col2 >= Col3 AND Col2 >= Col4 THEN Col2
            WHEN Col3 >= Col1 AND Col3 >= Col2 AND Col3 >= Col4 THEN Col3
            WHEN Col4 >= Col1 AND Col4 >= Col2 AND Col4 >= Col3 THEN Col4
        END As Max
FROM TableName
WHERE Col2 = 120

## Blog link: https://code.dennyzhang.com/swap-salary
update salary
set sex =
    case
    when sex = 'm' then 'f'
    when sex = 'f' then 'm'
    end;

## Blog link: https://code.dennyzhang.com/investments-in-2016
select sum(TIV_2016) as TIV_2016
from insurance
where concat(LAT, ',', LON)
    in (select concat(LAT, ',', LON)
       from insurance
       group by LAT, LON
       having count(1) = 1)
and TIV_2015 in
    (select TIV_2015
    from insurance
    group by TIV_2015
    having count(1)>1)

## Blog link: https://code.dennyzhang.com/tree-node
select t1.id, 
    case
        when isnull(t1.p_id) then 'Root'
        when isnull(max(t2.id)) then 'Leaf'
        else 'Inner'
    end as Type
from tree as t1 left join tree as t2
on t1.id = t2.p_id
group by t1.id, t1.p_id

## Blog link: https://code.dennyzhang.com/investments-in-2016
select sum(TIV_2016) as TIV_2016
from insurance
where concat(LAT, ',', LON)
    in (select concat(LAT, ',', LON)
       from insurance
       group by LAT, LON
       having count(1) = 1)
and TIV_2015 in
    (select TIV_2015
    from insurance
    group by TIV_2015
    having count(1)>1)

## Blog link: https://code.dennyzhang.com/friend-requests-i-overall-acceptance-rate
select ifnull((round(accepts/requests, 2)), 0.0) as accept_rate
from
    (select count(distinct sender_id, send_to_id) as requests from friend_request) as t1,
    (select count(distinct requester_id, accepter_id) as accepts from request_accepted) as t2

## Blog link: https://code.dennyzhang.com/friend-requests-ii-who-has-the-most-friends
select t.id, sum(t.num) as num
from (
      (select requester_id as id, count(1) as num
       from request_accepted
       group by requester_id)
      union all
       (select accepter_id as id, count(1) as num
        from request_accepted
        group by accepter_id)) as t
group by t.id
order by num desc
limit 1

## Blog link: https://code.dennyzhang.com/sales-person
select distinct salesperson.name
    from salesperson, orders, company
    where company.name = 'RED'
    and salesperson.sales_id = orders.sales_id
    and orders.com_id = company.com_id

## Blog link: https://code.dennyzhang.com/second-highest-salary
select ifnull((
       select Salary from Employee
       group by Salary order by Salary desc limit 1,1), null) as SecondHighestSalary

## Blog link: https://code.dennyzhang.com/nth-highest-salary
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  DECLARE M INT;
  DECLARE column_name varchar(100);
  set M=N-1;
  set column_name = CONCAT('getNthHighestSalary', N, ')');
  RETURN (
     select DISTINCT Salary as column_name
     from Employee
     order by Salary desc
     LIMIT M, 1
   );
END

## Blog link: https://code.dennyzhang.com/students-report-by-geography
select t1.name as America, t2.name as Asia, t3.name as Europe
from
    (select (@cnt1 := @cnt1 + 1) as id, name
    from student
    cross join (select @cnt1 := 0) as dummy
    where continent='America'
    order by name) as t1 
    left join
    (select (@cnt2 := @cnt2 + 1) as id, name
    from student
    cross join (select @cnt2 := 0) as dummy
    where continent='Asia'
    order by name) as t2
    on t1.id = t2.id
    left join
    (select (@cnt3 := @cnt3 + 1) as id, name
    from student
    cross join (select @cnt3 := 0) as dummy
    where continent='Europe'
    order by name) as t3
    on t1.id = t3.id

## Blog link: https://code.dennyzhang.com/students-report-by-geography
select t1.name as America, t2.name as Asia, t3.name as Europe
from
    (select (@cnt1 := @cnt1 + 1) as id, name
    from student
    cross join (select @cnt1 := 0) as dummy
    where continent='America'
    order by name) as t1 
    left join
    (select (@cnt2 := @cnt2 + 1) as id, name
    from student
    cross join (select @cnt2 := 0) as dummy
    where continent='Asia'
    order by name) as t2
    on t1.id = t2.id
    left join
    (select (@cnt3 := @cnt3 + 1) as id, name
    from student
    cross join (select @cnt3 := 0) as dummy
    where continent='Europe'
    order by name) as t3
    on t1.id = t3.id