SQL专项练习

LeetCode-SQL入门专题

595. 大的国家

考点：简单的where条件筛选

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

1757. 可回收且低脂的产品

考点：where的特殊语法：where (low_fats, recyclable) = ('Y', 'Y')

select product_id  from Products where (low_fats, recyclable) = ('Y', 'Y');

584. 寻找用户推荐人

考点：where条件逻辑表达式，is与null的搭配使用

select name from customer where referee_id <> 2 or referee_id is null;

183. 从不订购的客户

考点：子查询，左外链接；左外链接右表无数据会直接为null（不管什么类型，都是显示为null）

select `Name` as `Customers` from  Customers c where c.Id not in (select distinct CustomerId from Orders);
select Name Customers From Customers left join Orders on Customers.id = Orders.CustomerId where Orders.id is null;

1873. 计算特殊奖金

考点：流程函数：if(boolean, value1, value2)，case where exp then value1 else default value end, case exp where value1 then result1 else default result end在select语句中的使用

select employee_id , if((name like 'M%') or (employee_id % 2 = 0), 0,  salary) bonus from Employees order by employee_id;
select employee_id, (case when name like 'M%' then 0 when employee_id%2=0 then 0 else salary end) bonus from Employees order by employee_id;
select employee_id, (case (name like 'M%') or (employee_id % 2 = 0) when true then 0 else salary end) bonus from Employees order by employee_id;

627. 变更性别

考点：流程函数：if(boolean, value1, value2)，case where exp then value1 else default value end, case exp where value1 then result1 else default result end在update语句中的使用

update Salary set sex = if(sex='m', 'f', 'm');
update Salary set sex = (case sex when 'm' then 'f' when 'f' then 'm' else null end);

196. 删除重复的电子邮箱

考点：1.在from后使用了别名时，delete语句后面必须跟上要删除的表的别名，否则报错，可参考 Delete争取使用别名的方式；2.from后面的派生表必须有别名

-- 解法1
DELETE p1 FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id

-- 解法2
delete from Person where id not in
(
    select t.id from 
    (
        select min(id) as id from Person group by Email
    ) t
)