Review: SQL Problems
SQL Demands Mindsets Beyond Of Our Intuitive Thinkings:
The most impressive problems to me:
Common SQL Requirements:
- Use having with group-by: Classes More Than 5 Students
## Blog link: https://code.dennyzhang.com/classes-more-than-5-students select class from courses group by class having count(distinct student)>=5;
- Get maximum of multiple columns: Triangle Judgement
## 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
- Use case: Swap Salary
## Blog link: https://code.dennyzhang.com/swap-salary update salary set sex = case when sex = 'm' then 'f' when sex = 'f' then 'm' end;
- Use subquery: Investments in 2016
## 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)
- Use isnull: Tree Node
## 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
- Use tuple for in Clause: Investments in 2016
## 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)
- Use float and round: Friend Requests I: Overall Acceptance Rate
## 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
- union vs union all: Friend Requests II: Who Has the Most Friends
## 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
- 3 Tables join: Sales Person
## 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
- If no match show null: Second Highest Salary
## 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
- nth element in each group: Nth Highest Salary
## 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
- Select statement add an incremental id: Students Report By Geography
## 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
- 3 virtual tables left join: Students Report By Geography
## 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.
- Review: SQL Problems
- Leetcode: Winning Candidate
- Leetcode: Trips and Users
- Leetcode: Triangle Judgement
- Leetcode: Tree Node
- Leetcode: Swap Salary
- Leetcode: Students Report By Geography
- Leetcode: Shortest Distance in a Plane
- Leetcode: Shortest Distance in a Line
- Leetcode: Second Highest Salary
- Leetcode: Second Degree Follower
- Leetcode: Sales Person
- Leetcode: Rising Temperature
- Leetcode: Rank Scores
- Leetcode: Nth Highest Salary
- Leetcode: Not Boring Movies
- Leetcode: Median Employee Salary
- Leetcode: Managers with at Least 5 Direct Reports
- Leetcode: Investments in 2016
- Leetcode: Human Traffic of Stadium
- Leetcode: Get Highest Answer Rate Question
- Leetcode: Friend Requests II: Who Has the Most Friends
- Leetcode: Friend Requests I: Overall Acceptance Rate
- Leetcode: Find Median Given Frequency of Numbers
- Leetcode: Find Customer Referee
- Leetcode: Find Cumulative Salary of an Employee
- Leetcode: Exchange Seats
- Leetcode: Employees Earning More Than Their Managers
- Leetcode: Employee Bonus
- Leetcode: Department Top Three Salaries
- Leetcode: Department Highest Salary
- Leetcode: Delete Duplicate Emails
- Leetcode: Customers Who Never Order
- Leetcode: Customer Placing the Largest Number of Orders
- Leetcode: Count Student Number in Departments
- Leetcode: Consecutive Numbers
- Leetcode: Consecutive Available Seats
- Leetcode: Combine Two Tables
- Leetcode: Classes More Than 5 Students
- Leetcode: Big Countries
- Leetcode: Average Salary: Departments VS Company
See more blog_posts.
Share It, If You Like It.