[6주차]리트코드: day1 ~ day4

문제1. (595)Big Countries

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

 

문제2. (1757)Recyclable and Low Fat Products

select product_id
from Products
where low_fats = 'Y' and recyclable = 'Y'

 

문제3. (584)Find Customer Referee

select name
from Customer
where id not in (select id from Customer where referee_id = 2)

 

문제4. (183)Customers Who Never Order

select name as Customers
from Customers
where id not in (select customerId from Orders)

 

문제5. (1873) Calculate Special Bonus

select employee_id,
       case
            when employee_id % 2 = 1 and left(name, 1) <> 'M' then salary 
            else 0
        end as bonus
from Employees 
order by employee_id

 

 

문제6. (627)Swap Salary

update Salary
set sex = case
               when sex = 'f' then 'm'
               when sex = 'm' then 'f'
           end;

 

 

문제7. (196)Delete Duplicate Emails

delete p2
from Person p1
inner join Person p2 on p1.email = p2.email
where p1.id < p2.id

 

문제8. (1667)Fix Names in a Table #substr() #concat #lower #upper

select user_id,
       concat(upper(left(name,1)), lower(substring(name,2))) as name
from Users 
order by user_id

substr은 지정한 곳부터 모든 문자열을 불러올 수 있다!

 

문제9. (1484)Group Sold Products By The Date #group_concat()

select sell_date,
       count(distinct product) as num_sold,
       group_concat(distinct product) as products                     
from Activities
group by 1
order by 1

 

문제10. (1527)Patients With a Condition #특정문자열찾기 like '%'

select patient_id,
       patient_name,
       conditions   
from Patients 
where conditions like '%DIAB1%'

 

문제11. (1965)Employees With Missing Information #union

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

 

 

문제12. (1795)Rearrange Products Table #union

select db.product_id,
       db.store,
       db.price
from (
select product_id,
       case when store1 is not null then 'store1' end as store,
       store1 as price
from Products
union
select product_id,
       case when store2 is not null then 'store2' end as store,
       store2 as price
from Products
union
select product_id,
       case when store3 is not null then 'store3' end as store,
       store3 as price
from Products
) db
where db.price is not null or db.price <> 0

 

 

문제13. (608) Tree Node

select id,
       case 
            when p_id is null then 'Root'
            when id in (select p_id from tree where p_id is not null) then 'Inner'
            else 'Leaf' 
        end as type
from tree
group by id

 

문제14. (176) Second Highest Salary #ifnull #offset

SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary