mysql database exercises:

Leetcode database exercises

Easy difficulty

196. Delete duplicate emails (delete, self-join table)

Insert picture description here
delete p1
    from Person p1,Person p2
    where p1.Email = p2.Email and p1.Id > p2.Id;

620. Interesting movies (bitwise and (&)|mod(num,2) to determine parity; order by defaults in ascending order, and can only be placed behind)

Insert picture description here
select * from cinema 
where id&1 and description!='boring' 
order by rating desc ;

Difficulty

185. The top three employees with the highest salary in the department (window function)

Insert picture description here


Use window function:

Insert picture description here


first use the window function to find a new table sorted by department and salary, and then use the intermediate table for query

select t2.Name as dname,t1.Name as ename,Salary,dense_rank() over(partition by DepartmentId order by Salary desc) as rn
from Employee as t1, Department as t2 where t1.DepartmentId=t2.Id;
dnameenameSalaryrn
ITMax900001
ITJoe850002
ITRandy850002
ITWill700003
ITJanet690004
SalesHenry800001
SalesSam600002

Final query:

select dname as `Department` ,ename as `Employee`,Salary from
(select t2.Name as dname,t1.Name as ename,Salary,dense_rank() over(partition by DepartmentId order by Salary desc) as rn
from Employee as t1, Department as t2 where t1.DepartmentId=t2.Id) temp
where rn <=3;
DepartmentEmployeeSalary
ITMax90000
ITRandy85000
ITJoe85000
ITWill70000
SalesHenry80000
SalesSam60000

Supplement: rank(), dense_rank(), row_num()

Insert picture description here