# Review: SQL Problems

Review: SQL Problems

SQL Demands Mindsets Beyond Of Our Intuitive Thinkings:

The most impressive problems to me:

Common SQL Requirements:

```## 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
```

See all sql problems: #sql.

See more blog_posts.

Share It, If You Like It.