[LeetCode] SQL 50문제 (진행중)

찾아주셔서 감사드립니다. 

 

본 글에서는 LeetCode에서 선별한 SQL 50문제에 대해 풀이를 간략하게 적어보고자 합니다.

문의 사항이 있으시다면, 언제든지 댓글 남겨주세요! 

 

 

SQL 50 - Study Plan - LeetCode

 

 

SELECT 

1. Recycable and Low Fat Products 

SELECT product_id FROM Products
WHERE low_fats = 'Y' and recyclable ='Y'
ORDER BY 1 ASC

• 너무 간단한 문제이므로, 상세한 설명은 생략하겠습니다. 

 

2. Find Customer Referee

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

• CALESCE() 함수는 병합이라고 생각하시면 될거 같습니다. COALECSE(referee_id, 0) 사용하게 될 경우, 컬럼 값의 NULL이 존재한다면 "0"으로 대체할 수 있습니다. 

referee_id	COALECSE(referee_id, 0)
Null	0
Null	0
2	2
Null	0
1	1
2	2

 

3. Big Countries 

SELECT name, population, area 
FROM World 
WHERE population >= 25000000  or area >= 3000000

#UNION (성능 훨씬 빠름) 
SELECT name, population, area 
FROM World 
area >= 3000000
UNION 
SELECT name, population, area 
FROM World 
WHERE population >= 25000000

• 간단하게 작성할 수 있지만, UNION 사용하여 작성이 가능한점 참고 부탁드립니다! 4

4. Article View I

SELECT DISTINCT author_id as id
FROM Views
WHERE author_id = viewer_id
ORDER BY author_id

• DISTINCT X → 4,4,7 반환하게 되는데, DISTINCT 사용하면 4,7 반환 (중복 제거) 

5. Invalid Tweets 

SELECT tweet_id 
FROM Tweets
WHERE LENGTH(content) > 15

• 설명은 생략하도록 하겠습니다. 

JOIN 

6. Replace Employee ID With The Unique Identifier 

SELECT unique_id, name FROM Employees a
LEFT JOIN EmployeeUNI b ON a.id = b.id

 

7. Product Sales Analysis I 

SELECT product_name, year, price FROM Sales 
LEFT JOIN Product 
ON Sales.product_id = Product.product_id

 

 

8. Customer Who visited but Did not Make Any Transactions 

SELECT customer_id, COUNT(*) as count_no_trans
FROM Visits
WHERE visit_id NOT IN (SELECT DISTINCT visit_id FROM Transactions)
GROUP BY customer_id;