1. JOIN과 서브쿼리 활용 : employees 테이블과 orders 테이블을 사용해 각 직원이 담당한 주문의 수를 직원의 이름과 함께 출력하기.(주문이 없는 직원도 포함)
SELECT e.first_name || ' ' || e.last_name AS employee_name, COUNT(o.order_id) AS order_count
FROM employees e
LEFT JOIN orders o ON e.employee_id = o.order_id
GROUP BY e.first_name, e.last_name
ORDER BY order_count DESC;
- LEFT JOIN : 주문이 없는 직원도 결과에 포함.
- COUNT(o.order_id)는 주문 수 계산, GROUP BY로 직원별 집계.
2. 집계 함수와 서브쿼리
모든 직원의 평균 급여보다 높은 급여를 받는 직원들의 이름과 급여 출력하기.
SELECT first_name || ' ' || last_name AS employee_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;
- 서브쿼리 (SELECT AVG(salary) FROM employees)는 전체 평균 급여를 반환.
3. 직원별 총 주문 금액 계산
직원별로 담당한 주문에서 발생한 총 매출액 출력하기
SELECT e.employee_id, e.first_name || ' ' || e.last_name,
SUM(oi.quantity * oi.unit_price) AS total_sales
FROM employees e
JOIN orders o ON e.employee_id = o.salesman_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY e.employee_id, e.first_name, e.last_name
ORDER BY total_sales DESC;
- 매출액 계산 : quantity * unit_price = 각 주문 항목의 매출액
- JOIN 처리 : emlpoyees(직원)테이블과 orders(주문)테이블, order_item(주문 항목)테이블을 JOIN해 매출 데이터와 연결.
- SUM(...): 직원별 매출액 집계.
- ORDER BY total_sales DESC : 매출액 기준 내림차순 정렬.
4. 제품 카테고리별 재고 상황 분석
제품 카테고리(product_categories)별 재고(inventories)에 남아있는 총 제품 수량 출력하기.
SELECT pc.category_name, p.product_name, SUM(i.quantity) AS total_inventory
FROM product_categories pc
JOIN products p ON pc.category_id = p.category_id
JOIN inventories i ON p.product_id = i.product_id
GROUP BY pc.category_name, p.product_name
ORDER BY total_inventory DESC;
5. 고객별 주문 제품의 총 매출, 담당 직원, 고객 연락처 정보 조회.
SELECT cu.customer_id, cu.name, co.first_name || ' ' || co.last_name AS contact_name,
co.phone, e.first_name || ' ' || e.last_name AS salesman_name,
SUM(oi.quantity * oi.unit_price) AS total_sales
FROM customers cu
JOIN contacts co ON cu.customer_id = co.customer_id
JOIN orders o ON cu.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN employees e ON o.order_id = e.employee_id
GROUP BY cu.customer_id, cu.name, co.phone, co.first_name, co.last_name, e.first_name, e.last_name
ORDER BY total_sales DESC;
- cu.customer_id : 고객 고유 ID
- cu.name : 고객 이름
- co.first_name || ' ' || co.last_name AS contact_name: 연락처 담당자의 이름 선택 >> first_name과 last_name을 합쳐서 전체 이름을 만듭니다.
- co.phone : 연락처 담당자의 전화번호
- e.first_name || ' ' || e.last_name AS salesman_name: 판매원의 이름을 선택.
- SUM(oi.quantity * oi.unit_price) AS total_sales: 각 고객의 총 매출을 계산 >> order_items 테이블에서 주문 수량(quantity)과 단가(unit_price)를 곱하여 매출을 구하고, SUM() 함수를 사용하여 총합을 계산합니다.
- JOIN contacts co ON cu.customer_id = co.customer_id: contacts 테이블과 customers 테이블을 customer_id를 기준으로 조인
- JOIN orders o ON cu.customer_id = o.customer_id: orders 테이블과 customers 테이블을 customer_id를 기준으로 조인
- JOIN order_items oi ON o.order_id = oi.order_id: order_items 테이블과 orders 테이블을 order_id를 기준으로 조인
'개인 공부' 카테고리의 다른 글
[Python] Numpy(1) (0) | 2025.01.25 |
---|---|
데이터베이스?? (0) | 2025.01.19 |
기본 SQL 연습하기 (0) | 2025.01.18 |
객체지향 프로그래밍 OOP (0) | 2025.01.15 |
JWT를 사용한 Spring Security 기반 인증 시스템 구현 (1) | 2024.12.27 |