SQL : 실습(GROUP BY)

실습

---

❔ 문제 1: employees 테이블과 customers 테이블을 JOIN하여 각 직원별로 담당하는 고객 수가 5명 이상인 직원들의 이름과 담당 고객 수를 조회하라.

SELECT e.employeeNumber, e.lastName, e.firstName, COUNT(c.customerNumber) AS cnt_customerNumber
FROM employees e
JOIN customers c ON c.salesRepEmployeeNumber = e.employeeNumber
GROUP BY e.employeeNumber
HAVING cnt_customerNumber >= 5
;

❔ 문제 2: products 테이블에서 각 제품 라인별로 제품 가격의 평균을 계산하라. 그리고 평균 가격이 50달러 이상인 제품 라인만 조회하라.

SELECT productLine, AVG(buyPrice) AS avg_prc
FROM products
GROUP BY productLine
HAVING avg_prc >= 50;

❔ 문제 3: products 테이블과 orderdetails 테이블을 join하여, 제품별로 총 주문량을 계산하라. 그리고 총 주문량이 1000개 이상인 제품만 조회하라.

SELECT p.productcode, SUM(od.quantityOrdered) AS sum_ordered
FROM products p
JOIN orderdetails od ON p.productCode = od.productCode
GROUP BY p.productcode
HAVING sum_ordered >= 1000;

❔ 문제 4: orders 테이블에서 각 연도별 주문 건수를 계산하라. 그리고 연도별 주문 건수가 100건 이상인 연도만 조회하라.

SELECT YEAR(orderDate), COUNT(orderNumber)
FROM orders
GROUP BY YEAR(orderDate)
HAVING COUNT(orderNumber) >= 100;

❔ 문제 5: customers 테이블에서 각 나라별 고객 수를 계산하라. 그리고 고객 수가 5명 이상인 나라만 조회하라.

SELECT country, COUNT(customerNumber) AS cnt_customer
FROM customers
GROUP BY country
HAVING cnt_customer >= 5;

❔ 문제 6: customers 테이블과 orders 테이블을 join하여, 각 고객별 최근 주문 날짜를 조회하라.

SELECT c.customerNumber, MAX(o.orderDate)
FROM customers c
JOIN orders o ON c.customerNumber = o.customerNumber
GROUP BY c.customerNumber;

❔ 문제 7: products 테이블에서 각 제품 라인별로 최고가와 최저가 제품의 가격 차이를 조회하라.

SELECT productLine, MAX(buyPrice) - MIN(buyPrice)
FROM products
GROUP BY productLine;

⭐ 문제 8: orders 테이블과 orderdetails 테이블을 join하여 2004년에 주문된 제품들 중 가장 많이 주문된 제품의 코드와 이름을 조회하라.

SELECT p.productCode, p.productName, SUM(od.quantityOrdered) AS sum_quantity
FROM orders o
JOIN orderdetails od ON o.orderNumber = od.orderNumber
JOIN products p ON p.productCode = od.productCode
WHERE YEAR(orderDate) = 2004
GROUP BY p.productCode, p.productName
ORDER BY sum_quantity DESC
LIMIT 1;

❔ 문제 9: employees 테이블과 customers 테이블을 조회하여, 각 직원별 담당 고객의 평균 신용 한도를 조회하라. 평균 신용 한도가 50000보다 높은 직원만 조회하라.

SELECT e.employeeNumber, e.lastName, e.firstName, AVG(c.creditLimit)
FROM employees e
JOIN customers c ON e.employeeNumber = c.salesRepEmployeeNumber
GROUP BY e.employeeNumber
HAVING AVG(c.creditLimit) > 50000;