🌟 CASE
SELECT CASE
WHEN CategoryID = 1 THEN '음료'
WHEN CategoryID = 2 THEN '조미료'
ELSE '기타'
END AS 'CategoryNAME', Products.*
FROM Products
WHEN절에 두가지의 조건
SELECT CASE
WHEN CategoryID = 1 AND SupplierID = 1 THEN '음료'
WHEN CategoryID = 2 THEN '조미료'
ELSE '기타'
END AS 'CategoryNAME', Products.*
FROM Products
만든 컬럼으로 GROUP BY
SELECT CASE
WHEN CategoryID = 1 THEN '음료'
WHEN CategoryID = 2 THEN '소스'
ELSE '이외'
END AS new_category
, AVG(Price)
FROM Products
GROUP BY new_category
💭 문제풀이
[해커랭크_문제] Type Of Triangle
WHEN절의 순서를 신경써야한다.
📌첫 조건에 해당한 값들은 제외하고 다음 조건에서 충족하는지 안하는지 확인하며 넘어간다.
SELECT CASE
WHEN A = B AND B = C THEN 'Equilateral'
WHEN A + B <= C OR A + C <= B OR B + C <= A THEN 'Not A Triangle'
WHEN A = B OR A = C OR B = C THEN 'Isosceles'
ELSE 'Scalene'
END
FROM TRIANGLES
🌟 CASE를 활용한 테이블 PIVOT
사진 속 위의 내용을 아래처럼 추출하는 쿼리
SELECT AVG(CASE WHEN categoryid = 1 THEN PRICE ELSE 'NULL' END) AS category1_price
, AVG(CASE WHEN categoryid = 2 THEN PRICE ELSE 'NULL' END) AS category2_price
, AVG(CASE WHEN categoryid = 3 THEN PRICE ELSE 'NULL' END) AS category3_price
FROM Products
💭 문제풀이
SELECT id
, sum(CASE WHEN month = 'Jan' THEN revenue ELSE NULL END) AS Jan_Revenue
, sum(CASE WHEN month = 'Feb' THEN revenue ELSE NULL END) AS Feb_Revenue
, sum(CASE WHEN month = 'Mar' THEN revenue ELSE NULL END) AS Mar_Revenue
, sum(CASE WHEN month = 'Apr' THEN revenue ELSE NULL END) AS Apr_Revenue
, sum(CASE WHEN month = 'May' THEN revenue ELSE NULL END) AS May_Revenue
, sum(CASE WHEN month = 'Jun' THEN revenue ELSE NULL END) AS Jun_Revenue
, sum(CASE WHEN month = 'Jul' THEN revenue ELSE NULL END) AS Jul_Revenue
, sum(CASE WHEN month = 'Aug' THEN revenue ELSE NULL END) AS Aug_Revenue
, sum(CASE WHEN month = 'Sep' THEN revenue ELSE NULL END) AS Sep_Revenue
, sum(CASE WHEN month = 'Oct' THEN revenue ELSE NULL END) AS Oct_Revenue
, sum(CASE WHEN month = 'Nov' THEN revenue ELSE NULL END) AS Nov_Revenue
, sum(CASE WHEN month = 'Dec' THEN revenue ELSE NULL END) AS Dec_Revenue
FROM department
group by id
🌟 JOIN
두개의 테이블을 연결해서 데이터를 추출하기 위해
모든 경우의 숫자를 다 만들어 줌
SELECT * FROM Users,Orders
🌟 INNER JOIN
두 테이블에 모두 포함되는 데이터를 추출 (교집합)
Old
SELECT *
FROM Users,Orders
WHERE Users.Id = Orders.userId
New
SELECT *
FROM Users
INNER JOIN Orders ON Users.Id = Orders.userId
🌟 OUTER JOIN
OUTER를 적지 않아도 무관
LEFT JOIN (주로)
SELECT *
FROM Users
LEFT (OUTER) JOIN Orders ON Users.Id = Orders.userId
RIGHT JOIN = LEFT JOIN의 반대
💭 문제풀이
[해커랭크_문제]
African Cities
SELECT CITY.NAME
FROM CITY
INNER JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE
WHERE COUNTRY.CONTINENT = 'Africa'
Population Census
SELECT SUM(CITY.POPULATION)
FROM CITY
INNER JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE
WHERE COUNTRY.CONTINENT = 'Asia'
Average Population of Each Continent
📌 Continent별로 평균 인구를 구해야하니까 GROUP BY 해야함
SELECT COUNTRY.CONTINENT
, FLOOR(AVG(CITY.POPULATION))
FROM CITY
INNER JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE
GROUP BY COUNTRY.CONTINENT
[LEETCODE 문제] 183. Customers Who Never Order
SELECT Customers.NAME AS Customers
FROM Customers
LEFT JOIN Orders ON Customers.Id = Orders.CustomerId
WHERE Orders.ID IS NULL
🌟 SELF JOIN
💭 문제풀이
[LEETCODE 문제] 181. Employees Earning More Than Their Managers
A 테이블에 A를 JOIN하는 경우
SELECT Employee.NAME AS Employee
FROM Employee
INNER JOIN Employee AS Manager ON Employee.managerId = Manager.id
WHERE Employee.SALARY > Manager.SALARY
[LEETCODE 문제] 197. Rising Temperature
SELECT TODAY.Id AS TODAY_Id
, TODAY.recordDate AS TODAY_DATE
, TODAY.temperature AS TODAY_TEP
, YESTERDAY.Id AS YESTERDAY_Id
, YESTERDAY.recordDate AS YESTERDAY_DATE
, YESTERDAY.temperature AS YESTERDAY_TEP
FROM Weather AS TODAY
INNER JOIN Weather AS YESTERDAY ON TODAY.ID = YESTERDAY.ID+1
WHERE TODAY.temperature > YESTERDAY.temperature
Submit을 하면 틀림
📌 ID가 1이 오늘 2가 전날인 경우가 있음
SELECT TODAY.Id AS Id
FROM Weather AS TODAY
INNER JOIN Weather AS YESTERDAY ON TODAY.ID = YESTERDAY.ID+1
WHERE TODAY.temperature > YESTERDAY.temperature
DATE_ADD를 이용해서 데이터 추출
SELECT TODAY.Id AS Id
FROM Weather AS TODAY
INNER JOIN Weather AS YESTERDAY ON TODAY.recordDate = DATE_ADD(YESTERDAY.recordDate,INTERVAL 1 DAY)
WHERE TODAY.temperature > YESTERDAY.temperature
📌 DATE_ADD(기준날짜,INTERVAL) : 더하기
SELECT DATE_ADD(SYSDATE, INTERVAL 1 SECOND)
SELECT DATE_ADD(SYSDATE, INTERVAL 1 MINUTE)
SELECT DATE_ADD(SYSDATE, INTERVAL 1 HOUR)
SELECT DATE_ADD(SYSDATE, INTERVAL 1 DAY)
SELECT DATE_ADD(SYSDATE, INTERVAL 1 MONTH)
SELECT DATE_ADD(SYSDATE, INTERVAL 1 YEAR)
SELECT DATE_ADD(SYSDATE, INTERVAL -1 YEAR)
📌 DATE_SUB(기준날짜,INTERVAL) : 빼기
SELECT DATE_SUB(SYSDATE, INTERVAL 1 SECOND)
🌟 UNION & UNION ALL
중복값 제외 (UNION) / 중복값까지 다 포함 (UNION ALL)
Product 테이블에서 Price가 5이하 또는 200이상인 상품들만 출력
SELECT *
FROM Products
WHERE price <= 5
OR price >= 200
위의 쿼리를 UNION을 이용해서 쿼리 작성
SELECT *
FROM Products
WHERE price <= 5
UNION
SELECT *
FROM Products
WHERE price >= 200
위의 쿼리를 UNION ALL을 이용해서 쿼리 작성
SELECT *
FROM Products
WHERE price <= 5
UNION ALL
SELECT *
FROM Products
WHERE price >= 200
🌟 FULL OUTER JOIN
MYSQL에선 지원을 안하기 때문에 FULL OUTER JOIN처럼 하고 싶은 경우
비회원의 주문정보까지도 다 보고 싶은 쿼리
SELECT *
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomersID
UNION
SELECT *
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomersID
💭 문제풀이
[해커랭크_문제] Symmetric Pairs
ORDER BY는 UNION아래의 SELECT절만 가능
SELECT X,Y
FROM Functions
WHERE X = Y
GROUP BY X,Y
HAVING COUNT(*) = 2
UNION
SELECT F1.X,F1.Y
FROM Functions AS F1
INNER JOIN Functions AS F2 ON F1.X = F2.Y AND F2.X = F1.Y
WHERE F1.X < F1.Y
ORDER BY X
