[leetCode] # 584, 595, 1757 (coalesce를 통한 null 대체)

문제 1. Recyclable and Low Fat Products

https://leetcode.com/problems/recyclable-and-low-fat-products/

Recyclable and Low Fat Products - LeetCode

Find the ids of products that are both low fat and recyclable.

 

작성 답안

select p.product_id
from products as p
where p.recyclable ='Y' and p.low_fats = 'Y'

 

 

문제 2. Find Customer Referee

https://leetcode.com/problems/find-customer-referee/

Find Customer Referee - LeetCode

Write an SQL query to report the names of the customer that are not referred by the customer with id = 2.

 

작성 답안

select c.name
from customer c
where c.referee_id != 2 or c.referee_id is null

- 처음엔 앞의 조건문만 사용해서 오답 처리되었다.

- null인 값은 자동으로 제외하고 집계되기 때문에 referee_id가 2가 아님에도 null인 행은 제외하여 집계된다.

- 따라서 뒤의 조건문을 추가해주었다.

 

리뷰

- null은 자동으로 제외됨

- COALESCE(<column>, <replace_value>)

    <column> 에서 NULL인값들을 <replace_value>로 바꿔줄 수 있다.

    해당 함수를 통해 NULL을 0으로 바꿔주면 조건을 하나만 걸어도 된다.

SELECT name
FROM Customer
WHERE COALESCE(referee_id,0) <> 2;

 

문제 3. big countries

https://leetcode.com/problems/big-countries/

Big Countries - LeetCode

A country is big if:

• it has an area of at least three million (i.e., 3000000 km2), or
• it has a population of at least twenty-five million (i.e., 25000000).

Find the name, population, and area of the big countries.

 

작성답안

select w.name, w.population, w.area
from world as w
where w.area>= 3000000 or w.
population >= 25000000