SQL查询练习二(From LeetCode)

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

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

1 update salary
2 set sex = 
3 case sex
4     when ‘m‘ then ‘f‘
5     else ‘m‘
6 end

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

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

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

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

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

1 select name
2 from salesperson
3 where sales_id not in(
4     select sales_id 
5     from orders o join company c
6     on o.com_id = c.com_id
7     and c.name = ‘RED‘
8 )

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

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

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

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

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

1 delete p1 from Person p1,Person p2
2 where p1.Email = p2.Email
3 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

SQL查询练习二(From LeetCode)

标签：splay   esc   lap   name   ase   dep   不同的   des   inf