SQL exercise from leetcode Database category at level of Easy and Medium
Outer join & inner join
Inner join -> intersect
Select from a INNER JOIN b on a.a= b.b;
Select a., b. from a, b where a.a =b.b;
Outer join -> union by condition
Left outer join -> all rows in left combine common rows in right
select from a LEFT OUTER JOIN b on a.a = b.b;
select a., b. from a,b where a.a = b.b(+);
Right outer join -> all rows in right combine common rows in left
select from a RIGHT OUTER JOIN b on a.a = b.b;
select a., b. from a,b where a.a(+) = b.b;
Full outer join -> all rows in right combin all rows in left
select from a FULL OUTER JOIN b on a.a = b.b;LIMIT,OFFSET
Choose how many elements return by limit, OFFSET is optional as it indicates to skip number of elements
Must use ORDER BY when use LIMIT
Return smallest/biggest 5 elements
Select from a ORDER BY (DESC) salary LIMIT 5;
skip number of elements to return number elements
Select from a ORDER BY (DESC) salary LIMIT 5 OFFSET 1; == Select from a ORDER BY (DESC) salary LIMIT 1, 5;
Use OFFSET, need to take care if there is not enough previous elements to skip, it will be an issue.
Select (Select from a ORDER BY (DESC) salary LIMIT 5 OFFSET 1) as secondhighestsalary;
OR use IFNULL function to solve
Select IFNULL((Select * from a ORDER BY (DESC) salary LIMIT 5 OFFSET 1), NULL) AS secondhighestsalary;
CREATE FUNCTION
CREATE FUNCTION function_name(parameter datatype) RETURNS return_datatype
BEGIN
Declaration_section
Executable_section
END;
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
);
ENDThe Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.
SELECT a.Name as Employee FROM Employee AS a JOIN Employee AS b ON a.ManagerId = b.Id AND a.Salary > b.Salary;
== SELECT a.Name as Employee FROM Employee AS a, Employee AS b WHERE a.ManagerId=b.Id AND a,Salary >b.Salary;Duplicate Emails - 3 ways
Simple join: select DISTINCT p1.Email from Person as p1, Person as p2 where p1.Email = p2.Email AND p1.Id != p2.Id; ⇐> select DISTINCT p1.Email from Person as p1 join Person as p2 on p1.Email=p2.Email where p1.Id <> p2.Id;
Groupby+temporary table(super fast): select Email from (select Email, count(Email) as num from Person Group by Email) as statistic where num>1;
Groupby+having condition: select Email from Person Group by Email Having count(Email)>1;
Customers Who Never Order -2 ways
Simple left join + IS NULL: select name as Customers from Customers c left outer join Orders o on c.Id = o.CustomerId where o.CustomerId IS NULL;
Sub query + NOT IN: select c.Name as Customers from Customers where c.Id not in (select CustomerId from Orders);Delete Duplicate Emails
DELETE clause: DELETE p1 from Person p1, Person p2 where p1.Email = p2.Email AND p1.Id>p2.Id;Rising compare to previous date
DATEDIFF clause and JOIN: datediff to compare two dates data type
SELECT Weather.Id as ‘Id’ FROM Weather JOIN Weather w ON DATEDIFF(Weather.RecordDate, w.RecordDate) = 1 WHERE Weather.Temperature > w.Temperature;Big countries
OR clause or UNION(全包) clause: select name, population, area from World
where population > 25000000 OR area > 3000000;Classes more than 5 students
Count num of students in each class, and select more than 5
GROUP BY and COUNT clause with sub-query(add condition): SELECT class FROM (
SELECT class, COUNT(DISTINCT student) AS num FROM courses GROUP BY class ) AS temp_table WHERE num >=5;
GROUP BY and HAVING condition: SELECT class FROM courses GROUP BY class HAVING COUNT(DISTINCT student) >=5;Not boring movies
MOD function: SELECT FROM cinema WHERE MOD(id,2) <> 0 AND description <> ‘boring’ GROUP BY rating DESC; == SELECT FROM cinema WHERE id % 2 <> 0 AND description <> ‘boring’ GROUP BY rating DESC;
<> operator is faster than != operatorSwap salary - Single update statement
IF clause IF(condition, value if true, value if false) + UPDATE clause fast:
UPDATE salary SET sex = IF (sex = ‘m’, ‘f’, ‘m’);
CASE WHEN clause slowest: UPDATE salary SET sex = CASE sex WHEN ‘m’ THEN ‘f’ ELSE ‘m’ END;
Fastest: UPDATE salary SET sex = CASE sex WHEN ‘m’ THEN ‘f’ WHEN ‘f’ THEN ‘m’ ELSE sex END;
Fast: UPDATE salary SET sex = CASE sex WHEN sex =’m’ THEN ‘f’ WHEN sex=’f’ THEN ‘m’ END;Rank Scores
Two tables compare + count func: SELECT s1.score, count(s2.score) AS Rank FROM Scores s1, (select distinct score from Scores) s2 WHERE s1.score <= s2.score GROUP BY s1.id ORDER BY s1.score DESC
Sort table + set variable rank, prev: SELECT score, CONVERT(Rank, UNSIGNED) AS Rank
FROM ( SELECT score, @rank:= CASE WHEN score=@pre THEN @rank ELSE @rank+1 END AS Rank, @pre:= score FROM Scores, (SELECT @pre:=-1, @rank:=0) AS INITIAL
ORDER BY score DESC) AConsecutive Numbers
distinct + where clause: SELECT DISTINCT l1.Num AS ConsecutiveNums
FROM Logs l1, Logs l2, Logs l3
WHERE l1.Id = l2.Id -1 AND l2.Id = l3.Id -1 AND l1.Num = l2.Num AND l2.Num = l3.Num;Department Highest Salary
Join: select d.name as Department, e.Name as Employee, e.Salary as Salary
from Employee e join Department d on e.DepartmentId = d.Id
WHERE (e.DepartmentId , e.Salary ) In
(select DepartmentId, MAX(Salary)
from Employee
group by DepartmentId)Exchange Seats
Next seat change to prev, if total is odd, last seat remain same->odd+1, even-1:
select
(case when mod(id, 2)<>0 and id <> num then id+1
when mod(id, 2)<>0 and id = num then id
else id - 1
end) as id,student
from seat, (select count(*) as num
from seat) as seat_counts
order by id