Database - Chapter 4-22. 2) 실습 - mysqlsample data 분석(1/2)

 

MySQL Sample Database 분석 및 실습 가이드

1. 개요

• MySQL 샘플 데이터베이스(mysqlsample)를 활용하여 SQL의 다양한 기능을 실습합니다.
• 테이블 간 관계를 이해하고 기본 조회, 조인, 그룹 쿼리, 서브쿼리, 데이터 수정 등을 학습합니다.

2. ERD (Entity Relationship Diagram)

아래 ERD(개체 관계 다이어그램)는 mysqlsample 데이터베이스의 테이블 구조와 관계를 나타냅니다.

3. 기본 조회 및 필터링

• 고객 목록 조회: 모든 고객의 정보를 조회합니다.

  SELECT * FROM customers;
  

• 특정 제품 라인의 제품 조회 (Classic Cars)

  SELECT * FROM products WHERE productLine = 'Classic Cars';
  

• 최근 10개의 주문 조회 (orderDate 기준 최신순)

  SELECT * FROM orders ORDER BY orderDate DESC LIMIT 10;
  

• 100달러 이상 결제된 거래 조회

  SELECT * FROM payments WHERE amount >= 100;
  

4. JOIN을 활용한 테이블 결합

• 고객과 주문 정보 조합

  SELECT o.orderNumber, c.customerName
  FROM orders o
  JOIN customers c ON o.customerNumber = c.customerNumber;
  

• 제품과 제품 라인 정보 조회

  SELECT p.productName, pl.textDescription
  FROM products p
  JOIN productlines pl ON p.productLine = pl.productLine;
  

• 직원과 직속 상사(매니저) 정보 조회

  SELECT e1.employeeNumber, e1.firstName, e1.lastName,
         e2.firstName AS ManagerFirstName, e2.lastName AS ManagerLastName
  FROM employees e1
  LEFT JOIN employees e2 ON e1.reportsTo = e2.employeeNumber;
  

• 특정 사무실에서 근무하는 직원 목록 (San Francisco)

  SELECT e.employeeNumber, e.firstName, e.lastName
  FROM employees e
  JOIN offices o ON e.officeCode = o.officeCode
  WHERE o.city = 'San Francisco';
  

5. 그룹 쿼리 (GROUP BY 활용)

• 제품 라인별 제품 수 조회

  SELECT productLine, COUNT(*) AS productCount FROM products GROUP BY productLine;
  

• 고객별 총 주문 금액 계산

  SELECT c.customerNumber, c.customerName, SUM(od.quantityOrdered * od.priceEach) AS totalAmount
  FROM customers c
  JOIN orders o ON c.customerNumber = o.customerNumber
  JOIN orderdetails od ON o.orderNumber = od.orderNumber
  GROUP BY c.customerNumber, c.customerName;
  

• 가장 많이 팔린 제품 조회

  SELECT p.productName, SUM(od.quantityOrdered) AS totalQuantity
  FROM orderdetails od
  JOIN products p ON od.productCode = p.productCode
  GROUP BY p.productName
  ORDER BY totalQuantity DESC
  LIMIT 1;
  

• 매출이 가장 높은 사무실 조회

  SELECT o.city, SUM(od.quantityOrdered * od.priceEach) AS totalSales
  FROM orders ord
  JOIN orderdetails od ON ord.orderNumber = od.orderNumber
  JOIN customers c ON ord.customerNumber = c.customerNumber
  JOIN employees e ON c.salesRepEmployeeNumber = e.employeeNumber
  JOIN offices o ON e.officeCode = o.officeCode
  GROUP BY o.city
  ORDER BY totalSales DESC
  LIMIT 1;
  

6. 서브쿼리 활용

• 500달러 이상의 총 주문 금액을 기록한 주문 조회

  SELECT orderNumber, SUM(quantityOrdered * priceEach) AS totalAmount
  FROM orderdetails
  GROUP BY orderNumber
  HAVING totalAmount > 500;
  

• 평균 이상 결제 고객 조회

  SELECT customerNumber, SUM(amount) AS totalPayment
  FROM payments
  GROUP BY customerNumber
  HAVING totalPayment > (SELECT AVG(amount) FROM payments);
  

• 주문이 없는 고객 조회

  SELECT customerName FROM customers WHERE customerNumber NOT IN (SELECT customerNumber FROM orders);
  

• 최대 매출 고객 조회

  SELECT c.customerName, SUM(od.quantityOrdered * od.priceEach) AS totalSpent
  FROM customers c
  JOIN orders o ON c.customerNumber = o.customerNumber
  JOIN orderdetails od ON o.orderNumber = od.orderNumber
  GROUP BY c.customerName
  ORDER BY totalSpent DESC
  LIMIT 1;
  

7. 데이터 수정 및 관리 (INSERT, UPDATE 활용)

• 신규 고객 추가

  INSERT INTO customers (customerName, contactLastName, contactFirstName, phone, addressLine1, city, country, salesRepEmployeeNumber, creditLimit)
  VALUES ('New Customer', 'Doe', 'John', '123-456-7890', '123 Main St', 'New York', 'USA', 1002, 50000.00);
  

• Classic Cars 제품 라인의 모든 제품 가격 10% 인상

  UPDATE products SET buyPrice = buyPrice * 1.10 WHERE productLine = 'Classic Cars';
  

• 특정 고객 이메일 업데이트

  UPDATE customers SET email = 'newemail@example.com' WHERE customerNumber = 103;
  

• 특정 직원을 다른 사무실로 이동

  UPDATE employees SET officeCode = '2' WHERE employeeNumber = 1002;