SQL Coding Challenge – Leetcode

Easy Difficulty

1. Combine Two Tables - LeetCode

SELECT FirstName, LastName, City, State
FROM Person
LEFT JOIN Address ON Person.PersonId = Address.PersonId;

2. Second Highest Salary - LeetCode

SELECT distinct(Salary) AS SecondHighestSalary
FROM Employee
UNION
SELECT NULL
ORDER BY SecondHighestSalary DESC LIMIT 1, 1;

3. Employee Earning More Than Their Managers
   Assign two different names for single table and do filtering

SELECT a.name AS "Employee" FROM employee a, employee b
    WHERE a.managerid = b.id AND a.salary > b.salary;

4. Duplicate Emails
   Using GROUP BY and COUNT to count down duplicates

SELECT Email
FROM Person
GROUP BY Email
HAVING count(*) > 1;

5. Customers Who Never Order

SELECT A.Name from Customers A
WHERE NOT EXISTS (SELECT 1 FROM Orders B WHERE A.Id = B.CustomerId);

SELECT A.Name from Customers A
LEFT JOIN Orders B on  a.Id = B.CustomerId
WHERE b.CustomerId is NULL;

SELECT A.Name AS Customers
FROM Customers A
WHERE A.Id NOT IN (SELECT B.CustomerId from Orders B);

6. Delete Duplicate Emails

DELETE p1
FROM Person p1, Person p2
WHERE p1.Email = p2.Email AND
p1.Id > p2.Id;

7. Rising Temperature

SELECT A.Id 
FROM Weather A, Weather B
WHERE A.Temperature > B.Temperature
AND TO_DAYS(A.RecordDate) - TO_DAYS(B.RecordDate) = 1;

8. Classes More Than 5 Students
   Use subquery; pay attention that the sub table should be assigned a name.

SELECT class
FROM (SELECT class, COUNT(DISTINCT student) AS CT FROM courses GROUP BY class) AS A
WHERE CT >= 5

9. Not Boring Movies

SELECT id, movie, description, rating
FROM cinema
WHERE id%2 = 1 AND description != 'boring'
ORDER BY rating DESC;

10. Swap Salary
    Use both update function and if-else set

UPDATE salary
SET sex = (CASE WHEN sex = 'f' THEN 'm' ELSE 'f' END);

Medium Difficulty

11. Consecutive Numbers

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

12. nth highest salary

CREATE FUNCTION getNthHighestSalary(N INT) Returns INT
BEGIN
declare m INT;
set m = N-1;
return (select distinct Salary 
from Employee 
order by Salary desc
limit m, 1);
end

13. Exchange Seat

SELECT IF(id < (SELECT COUNT(*) FROM seat), IF(id mod 2=0, id-1, id+1), IF(id mod 2=0, id-1, id)) AS id, student
FROM seat
ORDER BY id;