개인 공부

기본 SQL 연습하기 2

orin602 2025. 1. 19. 16:49

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