SQL查询练习二(From LeetCode)

请选用MySQL进行测试.

1.将男性和女性的工资互换(E)

思路:使用case when进行条件判断,在使用update进行修改

update salary
set sex = 
case sex
    when 'm' then 'f'
    else 'm'
end

2.找出description不是boring且id是奇数的电影(E)

  思路:使用where字句进行筛选,并且使用mod进行奇数偶数的判定

select id,movie,description,rating
from cinema
where description != 'boring'
and mod(id,2) = 1
order by rating desc

3.找出不销售Red厂家的推销员(E)

思路:使用子查询先找出销售RED厂家的销售员id,在将外层查询进行not in

select name
from salesperson
where sales_id not in(
    select sales_id 
    from orders o join company c
    on o.com_id = c.com_id
    and c.name = 'RED'
)

4.找出数据库中相同的行(E)

思路:使用子查询对内层查询按照Email进行分组,统计出大于1的就是重复的值

select Email from
(
    select Email,count(*)  as num from Person group by Email
) as sub
where num>1

5.删除相同的数据(E)

思路:找到两张表相同的Email但是不同的id,把这行数据进行删除

delete p1 from Person p1,Person p2
where p1.Email = p2.Email
and p1.id > p2.id

6.找出经度和纬度不同的2016年的投资金额总和

思路:找出2015年投资相同的记录数,再将经度,维度作为分组的条件进行连接,最后查出结果

select sum(insurance.TIV_2016) as tiv_2016
from insurance
where insurance.TIV_2015 in
(
    select TIV_2015 
    from insurance 
    group by TIV_2015
    having count(*) > 1
)
and concat(lat,lon) in
(
    select concat(lat,lon)
    from insurance
    group by lat,lon
    having count(*) =1
)

7.找出部门平均工资和公司平均工资的高低

　思路:计算公司每月的平均薪水,计算部门每月的平均薪水,然后进行比较

select department_sal.pay_month,department_id,
case 
    when department_avg > company_avg then 'higher'
    when department_avg < company_avg then 'lower'
    else 'same'
end as comparison
from
(
    select department_id,avg(amount) as department_avg,date_format(pay_date,'%Y-%m') as pay_month
    from salary join employee
    on salary.employee_id = employee.employee_id
    group by department_id,pay_month
) as department_sal
join
(
    select avg(amount) as company_avg,date_format(pay_date,'%Y-%m') as pay_month
    from salary
    group by pay_month
) as company_sal
on department_sal.pay_month = company_sal.pay_month

 8.找出谁是facebook上最受欢迎的人

　　思路:根据request_id和accepter_id,可知3收到两个accept,发送一个request,所以3才是社交最活跃的,采用union all将requester_id,sender_id集合起来,分组才能找到使用最频繁的用户

select ids as id,cnt as num
from
(
    select ids,count(*) as cnt
    from
    (
        select requester_id as ids from request_accepted 
        union all
        select accepter_id from request_accepted 
    ) as tb1
    group by ids
) as tb2
order by num desc
limit 1

　9.找出followee和follower

　　思路:B和D都在follower中,B的Follower是C,D,A不在follower中,D的follower是E

select f1.follower,count(distinct f2.follower) as num
from follow f1 join follow f2
on f1.follower = f2.followee
group by f1.follower

10.找出体育馆人数大于100且连续的天数大于3天的数据

　　思路:先找出所有大于100人的条件,在使用3次自连接,根据t1,t2,t3的id来进行排序

select distinct t1.*
from stadium t1,stadium t2,stadium t3
where t1.people >= 100 and t2.people >= 100 and t3.people >= 100
and
(
    (t1.id-t2.id = 1 and t1.id-t3.id = 2 and t2.id-t3.id = 1)
    or
    (t2.id-t1.id = 1 and t2.id-t3.id = 2 and t1.id-t3.id = 1)
    or
    (t3.id-t2.id = 1 and t2.id-t1.id = 1 and t3.id-t1.id = 2)
)
order by t1.id