[프로그래머스 SQL/DB 스터디 1기 4주차] 후기&TIL

전체적인 4주차 스터디 후기 

4주차는 온라인 세션을 수행하기 조금 빠듯한 시간이였다. 그래서 코테 과제 뒤에 3문제 정도를... 완벽히 풀어내지 못했다. SELF JOIN에 대해 충분히 익혀야겠다.

스터디는 매주차마다 점점 난이도가 올라간다. 그래서 SQL 문법도 다지고, 응용하는데 적절한 강의이다. 다만, 직딩분들은 직장과, 학생분들은 수업과 병행하기 양이 많다는 점이 힘들다. 다 이뤄낸 주는 뿌듯하다. :D

---

1. JOIN 연산자

🙄 문제

총주문액과 총결재액이 같은 고객을 검색하세요.

출력 컬럼은 customerId, name(고객명), 총주문액, 총결재액 순으로 합니다.

결과는 customerId의 오름차순으로 정렬합니다.

 

⛔ 나의 코드 

WITH temp AS
(
	SELECT O.orderNo, SUM(Od.priceEach * Od.quantity) 주문액합계, O.customerId
	FROM orders O JOIN orderdetails Od USING (orderNo)
	GROUP BY O.orderNo
)
SELECT C.customerId, C.name 고객명, SUM(T.주문액합계) 총주문액, SUM(P.amount) 총결제액
FROM customers C JOIN payments P USING (customerId) JOIN temp T USING (customerId)
GROUP BY C.customerId
HAVING 총주문액 = 총결제액
ORDER BY 1;
-- 틀림.

 

✅ 피드벡 

WITH orderTotal AS
(
		SELECT	customerId, SUM(quantity * priceEach) AS 총주문액
		FROM	orders, orderDetails
		WHERE	orders.orderNo = orderDetails.orderNo
		GROUP 	BY customerId
),
paymentTotal AS
(
		SELECT	customers.customerId, name, SUM(amount) AS 총결제액
		FROM	customers, payments
        WHERE	customers.customerId = payments.customerId
		GROUP 	BY customerId
)
SELECT	O.customerId, name, 총주문액, 총결제액
FROM	orderTotal O, paymentTotal P
WHERE	O.customerId = P.customerId AND
		총주문액 = 총결재액
ORDER 	BY O.customerId;

-- 총주문액을 구하는 orderTotal 테이블과 총결제액을 구하는 paymentTotal 테이블 두 개 생성
-- 두 테이블을 조인하여야 원하는 결과 도출

 

---

2. FROM 절 조인

🙄 문제

각 지점(soffices.city) 별로, 소속 직원이 담당하는 고객수를 고객의 국가별로 검색하세요.

출력 컬럼은 city (offices.city), country (customers.country), 고객수 순으로 합니다.

결과는 city의 오름차순, country의 오름차순으로 정렬합니다.

 

⛔ 나의 코드 

-- 1
SELECT O.city, C.country, COUNT(C.customerId) 고객수
FROM offices O JOIN employees E USING (officeCode)
	JOIN customers C ON E.employeeId = C.salesRepId
GROUP BY O.city AND C.country;
SELECT O.city, O.officeCode
FROM offices O JOIN employees E USING (officeCode);

-- 2
WITH temp1 AS
(
	SELECT O.city, O.officeCode
    FROM offices O JOIN employees E USING (officeCode)
    GROUP BY O.city
),
temp2 AS
(
	SELECT E.officeCode, E.employeeId, C.country, COUNT(C.customerId) 직원별고객수
    FROM employees E JOIN customers C ON E.employeeId = C.salesRepId
    GROUP BY C.salesRepId
)
SELECT  T1.city, T2.country, COUNT(직원별고객수) 고객수
FROM temp1 T1 JOIN temp2 T2 USING (officeCode)
GROUP BY T2.country
ORDER BY 1, 2 DESC;

 

✅ 피드벡 

SELECT	O.city, C.country, COUNT(C.country) 고객수
FROM 	offices O JOIN employees E USING (officeCode)
		JOIN customers C ON E.employeeId = C.salesRepId
GROUP 	BY O.city, C.country
ORDER 	BY 1, 2;

-- 필요한 테이블 모두 JOIN 하여 원하는 데이터인 고객수 구하기

 

---

 

🙄 문제

각 직원에 대해, 본인이 관리하는 고객의 2004년 주문액 합계를 검색하세요.

단, 관리하는 고객사가 없는 직원도 결과에 포함합니다.

고객사의 2004년 주문액은 orderDate 기준입니다.

상품의 주문액은 주문단가(priceEach)와 주문개수(quantity)의 곱으로 계산합니다.

출력 컬럼은 직원의 근무지점(offices.city), 성명, 주문액합계 순으로 합니다.

성명은 firstName과 lastName으로 구성되며, 사이에 공백 문자(space)가 하나 들어갑니다.

결과는 주문액합계의 내림차순으로 정렬합니다.

 

⛔ 나의 코드 

WITH orders_temp AS 
(
	SELECT * 
	FROM orders
	WHERE YEAR(orderDate) = 2004
) 
SELECT Ofi.city 근무지점, CONCAT(E.firstName, ' ', E.lastName) 성명, 
	SUM(Od.priceEach * Od.quantity) 주문액합계
FROM employees E LEFT JOIN customers C ON E.employeeId = C.salesRepId
	JOIN offices Ofi USING (officeCode)
	JOIN orders_temp O USING (customerId)
    JOIN orderdetails Od USING (orderNo)
GROUP BY E.employeeId
ORDER BY 3 DESC;

 

✅ 피드벡 

WITH temp AS(
	SELECT	*
        FROM	orders JOIN orderDetails USING (orderNo)
        WHERE	YEAR(orderDate) = 2004
)
SELECT	O.city 근무지점, CONCAT(firstName, ' ', lastName) 성명, 
	SUM(priceEach * quantity) 주문액합계
FROM	offices O JOIN employees USING (officeCode)
		LEFT JOIN customers ON employeeId = salesRepId
		LEFT JOIN temp USING (customerId)
GROUP 	BY employeeId
ORDER 	BY 3 DESC;
-- LEFT JION 적절히 진행

 

---

🙄 문제

고객의 담당직원(salesRepId) 성명과 결재액을 검색하세요.

단, 담당직원이 없는 고객도 결과에 포함합니다.

또, 결재액이 없는 고객도 결과에 포함하며, 이 경우 결재액은 0.00으로 출력합니다.

출력 컬럼은 고객명(name), 담당직원의 성명, 결재액 순으로 합니다.

결과는 고객명의 오름차순으로 정렬합니다.

 

⛔ 나의 코드 

SELECT C.name 고객명, CONCAT(E.firstName, ' ', E.lastName) 담당직원명, 
	ROUND(SUM(P.amount),2) 결제액
FROM employees E RIGHT JOIN customers C ON E.employeeId = C.salesRepId
	JOIN payments P USING (customerId)
GROUP BY C.name    
ORDER BY 1;

 

✅ 피드벡 

SELECT	name 고객명, CONCAT(firstName, ' ', lastName) 담당직원성명, 
	COALESCE(SUM(amount),0) 결재액
FROM	employees RIGHT JOIN customers ON employeeId = salesRepId	/* 담당직원이 없는 고객 포함 */
		LEFT JOIN payments USING (customerId)			/* 결재가 없는 고객 포함 */
GROUP 	BY customerId
ORDER 	BY 1;
-- LEFT JOIN 과 COALESCE() 사용

 

---

3. SELF JOIN

🙄 문제

자신의 매니저와 다른 지점(s_offices.city)에서 근무하는 직원을 검색하세요.

출력 컬럼은 직원의 성명, jobTitle, 근무 지점, 매니저 성명, jobTitle, 매니저 근무지점 순으로 합니다.

결과는 직원 성명의 오름차순으로 정렬합니다.

 

 

⛔ 나의 코드 

 

✅ 피드벡 

WITH emp AS
(
		SELECT	employeeId, CONCAT(firstName, ' ', lastName) name, jobTitle, managerId, city
        FROM	employees JOIN offices USING (officeCode)
),
mgr AS
(
		SELECT	employeeId, CONCAT(firstName, ' ', lastName) name, jobTitle, city
        FROM	employees JOIN offices USING (officeCode)
)
SELECT	emp.name 직원, emp.jobTitle, emp.city,
		mgr.name 매니저, mgr.jobTitle, mgr.city
FROM  	emp JOIN mgr ON emp.managerId = mgr.employeeId
WHERE	emp.city <> mgr.city
ORDER 	BY 1;

---

프로그래머스 스쿨 [스터디/1기] 학교 밖에서 듣는 전공필수 SQL/DB Essential 바로가기

[스터디/1기] 학교 밖에서 듣는 전공필수 SQL/DB Essentials