SQL : CTE(Common Table Expression) 실습

실습

❔ 문제 1: products 테이블에서 각 제품 라인별로 평균 제품 가격을 계산하라. 그리고 이 평균 가격보다 높은 가격을 가진 제품들만을 해당 제품 라인별로 조회하라. (55row)

WITH AveragePrice AS (
	SELECT productLine, AVG(buyPrice) AS avgPrice
	FROM products
	GROUP BY productLine
)
SELECT p.productName, p.productLine, p.buyPrice, a.avgPrice
FROM products p
JOIN AveragePrice a ON a.productLine = p.productLine
WHERE p.buyPrice > a.avgPrice;

❔ 문제 2: 각 제품 라인별로 제품의 평균 가격과 전체 제품의 평균 가격을 비교하여 전체 평균 가격보다 높은 제품 라인만 조회하라.

WITH ProductLineAvgPrice AS (
    SELECT productLine, AVG(buyPrice) AS avgPrice
    FROM products
    GROUP BY productLine
),
TotalAvgPrice AS (
	SELECT AVG(buyPrice) AS totalAvgPrice
    FROM products
) #스칼라값
SELECT plap.productLine, plap.avgPrice, plap.totalAvgPrice
FROM ProductLineAvgPrice plap, TotalAvgPrice tap
WHERE plap.avgPrice > tap.totalAvgPrice #조건
;

• 문제를 위한 문제임. 실무에서는 전체 제품의 평균 가격을 구할 일이 없음
• WITH 두 개 이상 써보는 문제.
• 같은 키가 없어서 JOIN하기가 쉽지 않은데 위 방법 사용

# 서브쿼리로도 가능한 문제
SELECT 
    pl.productLine,
    AVG(pl.buyPrice) AS avgPrice,
    (SELECT AVG(buyPrice) FROM products) AS totalAvgPrice)
FROM products pl
GROUP BY pl.productLine
HAVING avgPrice > (SELECT AVG(buyPrice) FROM products);

❔ 문제 3: 각 직원별로 맡은 고객들의 평균 크레딧 한도를 계산하고, 크레딧 한도의 평균이 $100,000 이상인 직원만 조회하라.

WITH EmployeeAvgCredit AS (
    SELECT salesRepEmployeeNumber, AVG(creditLimit) as avgCredit
    FROM customers
    GROUP BY salesRepEmployeeNumber
)
SELECT e.employeeNumber, e.fistName, e.lastName, eac.avgCredit
FROM employees e
JOIN EmployeeAvgCredit eac ON e.employeeNumber = eac.salesRepEmployeeNumber
WHERE eac.avgCredit >= 100000
;

# 서브쿼리로 가능
SELECT e.employeeNumber, e.firstName, e.lastName, eac.avgCredit
FROM employees e
JOIN (
	SELECT salesRepEmployeeNumber, AVG(creditLimit) AS avgCredit
    FROM customers
    GROUP BY salesRepEmployeeNumber
) eac ON e.employeeNumber = eac.salesRepEmployeeNumber
WHERE eac.avgCredit >= 100000;

• 테이블 형태의 서브쿼리는 꼭! 별칭이 필요함 (eac)

# join으로 해결하기! 복잡하게 생각할 것 없이
SELECT e.employeeNumber, e.firstName, e.lastName, AVG(c.creditLimit) AS avgcredit
FROM customers c
JOIN employees e ON c.salesRepEmployeeNumber = e.employeeNumber
GROUP BY e.employeeNumber
HAVING avgcredit >=100000;