SQL
选择
595.大的国家
如果一个国家满足下述两个条件之一，则认为该国是 大国 ： 面积至少为 300 平方公里（即，3000000 km2），或者 人口至少为 2500 万（即 25000000） 编写一个 SQL 查询以报告 大国 的国家名称、人口和面积。
select name,population,area from World
where area >= 3000000 or population >= 25000000;
183.从不订购的用户
某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。
select `Name` as Customers from Customers
where Id Not in(select CustomerID from Orders);
select C.Name as Customers from Customers C
left join Orders O
on C.Id = O.CustomerId
where O.CustomerId is Null;
584. 寻找用户推荐人
select name from customer
where ifnull(referee_id,0) != 2;
select name from customer
where id not in
(select id from customer where referee_id =2)
1757. 可回收且低脂的产品
select product_id from Products
where low_fats = 'Y' and recyclable = 'Y';
排序＆修改
1873. 计算特殊奖金
SELECT employee_id,
CASE
WHEN employee_id%2 = 1 AND name NOT LIKE 'M%'
THEN salary
ELSE 0
END AS bonus
FROM Employees
select employee_id,
if(employee_id%2 = 1 && left(name,1) != 'M',salary,0) as bonus
from Employees;
627.变更性别
update salary
set sex = case sex when 'm' then 'f' else 'm'
end;
update salary
set sex = if(sex = 'm','f','m');
196.删除重复的电子邮件
delete p1 from Person p1,Person p2
where p1.Email = p2.Email and p1.Id>p2.Id;
字符串处理函数
1667修复表中的名字
select user_id,
concat(upper(left(name, 1)),lower(right(name, length(name) - 1))) as name
from users
order by user_id;
select user_id,
concat(upper(left(name, 1)), lower(substring(name, 2))) as name
from users
order by user_id;
1484. 按日期分组销售产品
select
sell_date,
count(distinct(product)) as num_sold,
group_concat(distinct(product)) as products
from Activities
group by sell_date
order by sell_date;
1527.患某种疾病的患者
select patient_id, patient_name, conditions
from patients
where conditions like "DIAB1%" or conditions like "% DIAB1%";
组合查询＆指定选取
1965.丢失信息的雇员
select employee_id from employees
where employee_id not in (select employee_id from salaries)
union
select employee_id from salaries
where employee_id not in (select employee_id from employees)
order by employee_id
1795.每个产品在不同商店的价格
select product_id,'store1' store, store1 price from Products where store1 is not null
union all
select product_id,'store2', store2 from Products where store2 is not null
union all
select product_id,'store3', store3 from Products where store3 is not null
608.树节点
select id,
case when t.p_id is null then 'Root'
when t.id in (select p_id from tree ) then 'Inner'
else 'Leaf'
end as Type
from tree t
176.第二高的薪水
select max(Salary) SecondHighestSalary
from employee
where salary < (select max(salary) from employee)
select (select distinct salary from Employee order by salary desc limit 1,1) as SecondHighestSalary
