leetcode数据库部分刷题

刷题地址

1. 组合两个表

select person.firstname,person.lastname, address.city, address.state
from person left join address 
on person.personid=address.personid;

2.第二薪水

select 
ifnull((select distinct(salary) 
from employee
order by salary desc
limit 1,1),null) as secondhighestsalary;

3、超过经理收入的员工

select e1.name as employee 
from employee as e1 ,employee as e2 
where e1.managerid=e2.id
and e1.salary>e2.salary;

4、查找重复邮箱

select email
from person
group by email
having count(email) > 1;

5、从不订购的用户

select c.name as customers
from customers c
where c.id not in (select o.customerid from orders o);

6、部门最高工资的员工

select d.name as department, e.name as employee, e.salary 
from department d inner join employee e 
on d.id = e.departmentid 
and e.salary >= (select max(salary) from employee where departmentid = d.id);

7、删除重复的电子邮件

delete p1 
from person p1,person p2
where p1.email = p2.email 
and p1.id > p2.id;

8、上升的温度

select weather.id as id
from weather join weather w 
on datediff(weather.recorddate, w.recorddate) = 1
and weather.temperature > w.temperature;

9、大的国家

select name,population,area
from world
where area>3000000
or population>25000000;

10、超过5名学生的课

select class
from courses
group by class
having count(distinct(student)) >=5

11、换座位

select * from(
    select id-1 as id,student from seat where id%2=0
    union
    select id+1 as id,student from seat where id%2=1 and (id+1) <= (select count(*) from seat)
    union
    select id as id,student from seat where id%2=1 and (id+1) > (select count(*) from seat)
) as t1
order by id asc

12、交换工资

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

13、分数排名

select score, (select count(distinct score) from scores where score >= s.score) as rank 
from scores s 
order by score desc