Easy Difficulty
- Combine Two Tables - LeetCode
SELECT FirstName, LastName, City, State
FROM Person
LEFT JOIN Address ON Person.PersonId = Address.PersonId;
- Second Highest Salary - LeetCode
SELECT distinct(Salary) AS SecondHighestSalary
FROM Employee
UNION
SELECT NULL
ORDER BY SecondHighestSalary DESC LIMIT 1, 1;
- 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;
- Duplicate Emails
Using GROUP BY and COUNT to count down duplicates
SELECT Email
FROM Person
GROUP BY Email
HAVING count(*) > 1;
- 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);
- Delete Duplicate Emails
DELETE p1
FROM Person p1, Person p2
WHERE p1.Email = p2.Email AND
p1.Id > p2.Id;
- 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;
- 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
- Not Boring Movies
SELECT id, movie, description, rating
FROM cinema
WHERE id%2 = 1 AND description != 'boring'
ORDER BY rating DESC;
- 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
- 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;
- 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
- 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;