[leetcode] SELECT

584. Find Customer Referee

• referee의 id가 2가 아닌 row만 반환한다.

<hide/>
Customer table:
+----+------+------------+
| id | name | referee_id |
+----+------+------------+
| 1  | Will | null       |
| 2  | Jane | null       |
| 3  | Alex | 2          |
| 4  | Bill | null       |
| 5  | Zack | 1          |
| 6  | Mark | 2          |
+----+------+------------+
Output: 
+------+
| name |
+------+
| Will |
| Jane |
| Bill |
| Zack |
+------+

 출처 - https://leetcode.com/problems/find-customer-referee/submissions/

 

  Sol)

• ISNULL() 괄호 안이 NULL 인 경우에만 실행한다.
• '<>'를 이용해서 id 2가 아닌 경우만 출력하면 NULL인 경우는 출력되지 않는다. 따라서 ISNULL()을 이용한다.

<hide/>
SELECT name
FROM Customer
WHERE referee_id <> 2  OR ISNULL(referee_id);

 

 

 

183. Customers Who Never Order

<hide/>
Input: 
Customers table:
+----+-------+
| id | name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+
Orders table:
+----+------------+
| id | customerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+
Output: 
+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

 출처 -  https://leetcode.com/problems/customers-who-never-order/?envType=study-plan&id=sql-i 

 

 

 Sol)

• 출력하는 테이블의 이름도 맞춰야한다.
• "필드명" IS NULL 이라고 쓸 수도 있고 ISNULL(필드명)이라고 쓸 수도 있다.

<hide/>
SELECT C.name "Customers"
FROM Customers C LEFT JOIN Orders O ON C.id = O.customerId
WHERE O.customerId  IS NULL;

 

• ISNULL(필드명)

<hide/>
SELECT C.name "Customers"
FROM Customers C LEFT JOIN Orders O ON C.id = O.customerId
WHERE ISNULL(O.customerId);

 

• WHERE절에 서브쿼리를 이용한 방식
  • CustomersId (1, 2, 3, 4) 중에서 Orders.customerId (1, 3)에 해당하지 않는 2, 4번에 대한 필드만 출력되도록 한다.
  • JOIN을 쓰지 않고도 풀 수 있고 직관적이다.

<hide/>
SELECT name "Customers"
FROM Customers
WHERE Customers.Id NOT IN 
(
    SELECT customerId FROM Orders
);

 

 

 

595. Big Contries

출처 -  https://leetcode.com/problems/big-countries/?envType=study-plan&id=sql-i

 

<hide/>
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).
Write an SQL query to report the name, population, and area of the big countries.
Return the result table in any order.
The query result format is in the following example.



Input: 
World table:
+-------------+-----------+---------+------------+--------------+
| name        | continent | area    | population | gdp          |
+-------------+-----------+---------+------------+--------------+
| Afghanistan | Asia      | 652230  | 25500100   | 20343000000  |
| Albania     | Europe    | 28748   | 2831741    | 12960000000  |
| Algeria     | Africa    | 2381741 | 37100000   | 188681000000 |
| Andorra     | Europe    | 468     | 78115      | 3712000000   |
| Angola      | Africa    | 1246700 | 20609294   | 100990000000 |
+-------------+-----------+---------+------------+--------------+
Output: 
+-------------+------------+---------+
| name        | population | area    |
+-------------+------------+---------+
| Afghanistan | 25500100   | 652230  |
| Algeria     | 37100000   | 2381741 |
+-------------+------------+---------+

• 당연하게 AND 연산인 줄 알았는데 OR이었다.
• 둘 중 하나의 조건만 만족하면 출력한다.
• 면적이 300만 이상 또는 인구가 25000000이상인 지역 정보를 출력하면 된다.

  Sol) 

<hide/>
SELECT name, population, area
FROM  World
WHERE  population >= 25000000 OR area >= 3000000;

 

 

 

1757. Recycleable and Low Fat Products

출처 - https://leetcode.com/problems/recyclable-and-low-fat-products/?envType=study-plan&id=sql-i 

<hide/>

Write an SQL query to find the ids of products that are both low fat and recyclable.
Return the result table in any order.
The query result format is in the following example.

Input: 
Products table:
+-------------+----------+------------+
| product_id  | low_fats | recyclable |
+-------------+----------+------------+
| 0           | Y        | N          |
| 1           | Y        | Y          |
| 2           | N        | Y          |
| 3           | Y        | Y          |
| 4           | N        | N          |
+-------------+----------+------------+
Output: 
+-------------+
| product_id  |
+-------------+
| 1           |
| 3           |
+-------------+
Explanation: Only products 1 and 3 are both low fat and recyclable.

  

  Sol)

• low fat이면서 recycle = 'y'인 상품의 id를 불러온다.

<hide/>
SELECT product_id
FROM Products
WHERE low_fats = 'Y' AND recyclable = 'Y'