[SQL] LeetCode Easy Database Problem (11~20)

11. Classes More Than 5 Students (COUNT DISTINCT)

SELECT class
FROM Courses
GROUP BY class
HAVING COUNT(**Distinct**(student)) >= 5

12. Not Boring Movies

//boring이 아닌 odd넘버 정렬은 rating desc
SELECT id, movie, description, rating
FROM cinema
WHERE description != 'boring' AND id%2 = 1
ORDER BY rating desc

13. Swap Salary (UPDATE)

UPDATE salary SET sex = IF(sex= 'm', 'f', 'm')

UPDATE salary SET sex = CASE WHEN sex = 'm' THEN 'f' ELSE 'm' END

14. Product Sales Analysis

JUST JOIN

15. Product Sales Analysis2

SELECT product_id, SUM(quantity) AS total_quantity
FROM Sales 
GROUP BY product_id

16. Sales Analysis

SELECT seller_id
FROM Sales
GROUP BY seller_id
HAVING SUM(price) = 
(SELECT  SUM(price) as total_price
FROM Sales
GROUP BY seller_id
ORDER BY total_price desc LIMIT 1)

17. Sales Analysis 2

SELECT buyer_id
FROM Product
JOIN Sales
ON Product.product_id = Sales.product_id
GROUP BY buyer_id 
HAVING SUM(product_name = 'S8') > 0 AND SUM(product_name = 'iPhone') = 0

18. Sales Analysis 3 

SELECT Product.product_id, Product.product_name
FROM Product
JOIN Sales
ON Product.product_id = Sales.product_id
GROUP BY product_id 
HAVING MIN(sale_date) >= "2019-01-01"
AND MAX(sale_date) <= "2019-03-31"
//GROUP BY해서 중복 없음 2053MS

//틀린 이유 : 봄에만! 팔린거 다른계절에 팔리면 안됨
SELECT Product.product_id, Product.product_name
FROM Product
WHERE Product.product_id IN (SELECT product_id
	FROM Sales
	WHERE sale_date >= "2019-01-01"
	AND sale_date <= "2019-03-31" )

19. Reformat Department Table (UNPIVOT)

SELECT id, 
IF(month = "Jan", revenue, NULL) AS Jan_Revenue,
IF(month = "Feb", revenue, NULL) AS Feb_Revenue,
IF(month = "Mar", revenue, NULL) AS Mar_Revenue,
...

IF(month = "Dec", revenue, NULL) AS Dec_Revenue
FROM Department
GROUP BY id

20. Product’s Price for Each Store

SELECT product_id, 
MIN(IF(store = "store1", price, NULL)),
...min max avg sum,, 하나의 value를 구하기 위해,,
FROM Products
GROUP BY product_id