🍀 GROUP BY 실습2
❔ 문제 1: customers 테이블에서 국가(country)가 'USA', 'Canada', 'France'인 도시(city)별로 평균 크레딧 한도(creditlimit)을 계산하라.
SELECT city, AVG(creditLimit) AS avgCreditLimit
FROM customers
WHERE country IN ('usa' , 'canada', 'france')
GROUP BY city
;
- WHERE country IN ('USA' , 'Canada', 'France')
- WHERE절 다음에 GROUP BY절
❔ 문제 2: products 테이블에서 제품 라인(productLine)이 'Ships'나 'Trains'가 아닌 제품 라인별로 평균 가격(buyPrice)를 계산하라.
SELECT productLine, AVG(buyPrice) AS avgBuyPrice
FROM products
WHERE productLine NOT IN ('ships', 'trains')
GROUP BY productLine
;
- NOT IN
- WHERE절을 빼고 GROUP BY 후, HAVING productLine NOT IN ('ships', 'trains');
❔ 문제 3: orders 테이블에서 2003년부터 2005년까지 주문된 상태(status)별로 주문 번호(orderNumber)의 수를 계산하라.
SELECT status, COUNT(orderNumber) AS countOrderNumber
FROM orders
WHERE orderDate BETWEEN '2003-01-01' AND '2005-12-31'
GROUP BY status ;
- WHERE 컬럼 BETWEEN ^ and ^ 형태
- WHERE YEAR(orderDate) BETWEEN 2003 AND 2005;
❔ 문제 4: employees 테이블에서 보고받는 매니저(reportsTo)가 NULL이 아닌 직원들의 사무실 코드(officeCode)별로 직원의 수를 계산하라.
SELECT officeCode, COUNT(employeeNumber) AS CountEmployee
FROM employees
WHERE reportsTo IS NOT NULL
GROUP BY officeCode;
- WHERE 컬럼 IS NOT NULL
- 숫자를 셀때는 primary key를 세주는 것이 좋음.
❔ 문제 5: orderdetails 테이블에서 주문 수량(quantityOrdered)이 30개 이상 50개 이하인 제품 코드(productCode)별로 평균 주문 가격(priceEach)를 계산하라.
SELECT productCode, AVG(priceEach) AS avgPriceEach
FROM orderdetails
WHERE quantityOrdered BETWEEN 30 AND 50
GROUP BY productCode;
- WHERE 컬럼 BETWEEN 30 AND 50
- WHERE 컬럼 >=30 AND 컬럼 <=50
❔ 문제 6: products 테이블에서 제품 라인(productLine)이 'Ships'나 'Trains'인 제품 라인별로 평균 재고 수량(quantityInStock)을 계산하라.
SELECT productLine, AVG(quantityInStock) AS avgInStock
FROM products
WHERE productLine IN ('Ships', 'Trains')
GROUP BY productLine;
- WHERE 컬럼 IN ( )
❔ 문제 7: orders 테이블에서 코멘트(comments)가 NULL인 주문 상태(status)별로 주문 번호(orderNumber)의 수를 계산하라.
SELECT status, COUNT(orderNumber) AS countOrderNumber
FROM orders
WHERE comments IS NULL
GROUP BY status;
- 코멘트가 없는 = 주문자들의 요구사항이 없는.
❔ 문제 8: orderdetails 테이블에서 주문 가격(priceEach)이 $50 이상 $100 이하인 제품 코드(productCode)별로 평균 주문 수량(quantityOrdered)를 계산하라.
SELECT productCode, AVG(quantityOrdred) AS avgOrdered
FROM orderdetails
WHERE priceEach BETWEEN 50 AND 100
GROUP BY productCode;
❔ 문제 9: customers 테이블에서 국가(country)가 'USA'나 'Canada'가 아닌 국가별로 평균 크레딧 한도(creditLimit)을 계산하라.
SELECT country, AVG(creditLimit) AS avgCreditLimit
FROM customers
WHERE country NOT IN ('USA' ,'Canada')
GROUP BY country;
❔ 문제 10: employees 테이블에서 보고받는 매니저(reportsTo)가 NULL인 사무실 코드(officeCode)별로 직원의 수를 계산하라.
SELECT officeCode, COUNT(employeeNumber)
FROM employees
WHERE reportsTo IS NULL
GROUP BY officeCode;
