SQL 피드백

SQL Server 기본SQL문 교육 - 0203.Second Level SQL(QUIZ02).sql
결과는 틀리지 않았으나 드라이빙 테이블 선정은 중요합니다.
CUSTOMERS 에서부터 데이터가 시작하니 드라이빙 테이블은 CUSTOMERS 가 되어야 합니다.

⭐ 문제에서 요구하는 테이블부터 조인하기
: 누구부터 조회하지? 생각 후 대상 테이블을 생각하기

---

11.Orders테이블에서 OrderDate가 1996년7월부터 1996년 12월까지의 데이터를 대상으로
Orders와 [Order Details]와 연결하고(OrderID사용)
Orders와 Employees를 연결해서(EmployeeID사용)
EmployeeID의 Title별로, 각 월별 OrderAmount를 구하시오
OrderAmount = [Order Details].UnitPrice * [Order Details].Quanty

SELECT	E.Title,
		ISNULL(SUM(CASE WHEN O.OrderDate >= '1996-07-01' AND O.OrderDate < '1996-08-01' THEN D.UnitPrice * D.Quantity END), 0) AS OrderAmount_07,
		ISNULL(SUM(CASE WHEN O.OrderDate >= '1996-08-01' AND O.OrderDate < '1996-09-01' THEN D.UnitPrice * D.Quantity END), 0) AS OrderAmount_08,
		ISNULL(SUM(CASE WHEN O.OrderDate >= '1996-09-01' AND O.OrderDate < '1996-10-01' THEN D.UnitPrice * D.Quantity END), 0) AS OrderAmount_09,
		ISNULL(SUM(CASE WHEN O.OrderDate >= '1996-10-01' AND O.OrderDate < '1996-11-01' THEN D.UnitPrice * D.Quantity END), 0) AS OrderAmount_10,
		ISNULL(SUM(CASE WHEN O.OrderDate >= '1996-11-01' AND O.OrderDate < '1996-12-01' THEN D.UnitPrice * D.Quantity END), 0) AS OrderAmount_11,
		ISNULL(SUM(CASE WHEN O.OrderDate >= '1996-12-01' AND O.OrderDate < '1997-01-01' THEN D.UnitPrice * D.Quantity END), 0) AS OrderAmount_12
FROM	Orders O
		INNER JOIN [Order Details] D 
			ON O.OrderID = D.OrderID
		INNER JOIN Employees E
			ON O.EmployeeID = E.EmployeeID
WHERE	O.OrderDate >= '1996-07-01' 
		AND O.OrderDate < '1997-01-01'
GROUP BY E.Title
ORDER BY E.Title

답본

SELECT T3.Title,
SUM(CASE WHEN DATEPART(month, T1.OrderDate) = 7 THEN T2.UnitPrice * T2.Quantity ELSE 0 END) OrderAmount_07,
SUM(CASE WHEN DATEPART(month, T1.OrderDate) = 8 THEN T2.UnitPrice * T2.Quantity ELSE 0 END) OrderAmount_08,
SUM(CASE WHEN DATEPART(month, T1.OrderDate) = 9 THEN T2.UnitPrice * T2.Quantity ELSE 0 END) OrderAmount_09,
SUM(CASE WHEN DATEPART(month, T1.OrderDate) = 10 THEN T2.UnitPrice * T2.Quantity ELSE 0 END) OrderAmount_10,
SUM(CASE WHEN DATEPART(month, T1.OrderDate) = 11 THEN T2.UnitPrice * T2.Quantity ELSE 0 END) OrderAmount_11,
SUM(CASE WHEN DATEPART(month, T1.OrderDate) = 12 THEN T2.UnitPrice * T2.Quantity ELSE 0 END) OrderAmount_12

FROM Orders T1

INNER JOIN [Order Details] T2
ON T1.OrderID = T2.OrderID
INNER JOIN Employees T3
ON T3.EmployeeID = T1.EmployeeID

WHERE T1.OrderDate >= '1996-07-01'
AND T1.OrderDate < '1997-01-01'

GROUP BY T3.Title

⭐ 월별로 집계 해야할 때
: O.OrderDate >= '1996-07-01' AND O.OrderDate < '1996-08-01'
대신 DATEPART(month, T1.OrderDate) = 7 사용하기

⭐ISNULL 최소한으로 사용하기
: ISNULL(SUM(CASE WHEN ... THEN 금액 END), 0)
대신 SUM(CASE WHEN ... THEN 금액 ELSE 0 END)
ELSE를 이용하기

---

요일 표현
SELECT datename(dw, T1.OrderDate) Weekday, COUNT(*) OrderCount

⭐ DATENAME(weekday, variable) DATENAME(dw, variable)
: 둘 다 같은 표현

---

COUNT(CASE DATEPART(qq, OrderDate) WHEN 1 THEN 1 END) as First_Quarter

⭐ COUNT 를 할 때는 THEN 부분에 1 사용하기 (가시성)

CONVERT(nchar(6), T1.OrderDate, 112)

⭐ LEFT 대신 CONVERT 만 사용
:LEFT(CONVERT(nchar(8), OrderDate, 112)
대신 CONVERT(nchar(6), T1.OrderDate, 112)

---

SELECT T1.DataType, T1.EmployeeID, T1.OrderDate, T1.Freight

FROM (

SELECT 0 DATA_ORDER, 'Data' DataType, T1.EmployeeID, T1.OrderDate, T1.Freight
FROM Orders T1
WHERE T1.OrderDate >= '1996-07-01'
AND T1.OrderDate < '1996-08-01'

UNION ALL

SELECT 1 DATA_ORDER, 'Sub TTL' DataType, T1.EmployeeID, NULL OrderDate, SUM(T1.Freight) Freight
FROM Orders T1
WHERE T1.OrderDate >= '1996-07-01'
AND T1.OrderDate < '1996-08-01'

GROUP BY T1.EmployeeID

) T1

ORDER BY T1.EmployeeID, T1.DATA_ORDER, T1.OrderDate

⭐정렬값을 따로 할당하여 정렬하기
: 0 DATA_ORDER, 1 DATA_ORDER 을 통해서 정렬함
인라인뷰에서 가상으로 설정한 값을 이용해 정렬
-> 하지만 SELECT 하지 않아 보이지 않게 설정

---

⭐ 소수점이 안 나올 때,
: CONVERT(numeric(18,2), VAR)