SQL : JOIN 실습

🍀 JOIN 실습

---

❔ 문제 1: 'customers' 테이블과 orders 테이블을 사용하여, 모든 고객의 이름과 주문 번호를 조회하라.

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

• customerNumber : 두 테이블을 연결하는 key
• 고객의 이름과 주문번호를 합침, customerNumber가 같을 시.
• 더 실무로 들어가면, 여기서 발생한 수익이 얼만지? 주문 빈도가 어떤지? 관심 있는 제품이 뭔지? 고객이 겨울에만 주문하는지?

❔ 문제 2: products 테이블과 orderdetails 테이블을 사용하여 상품 이름과 주문된 수량을 조회하라.

SELECT p.productCode, o.quantityOrdered
FROM products p
JOIN orderdetails o ON p.productCode = o.productCode;

• products와 orderdetails를 연결하는 key : productCode
• 상품이름 : product.productName, 주문된 수량 : orderdetails.quantityOrdered
• 처음에는 SELECT * 로 조회를 하고 잘 JOIN 되었으면 select하는 것이 좋음.

❔ 문제 3: employees 테이블과 customers 테이블을 사용하여, Leslie 이라는 이름을 가진 직원이 담당하는 모든 고객의 이름을 조회하라.

SELECT c.customerName
FROM employees e
JOIN customers c ON e.employeeNumber = c.salesRepEmployeeNumber
WHERE e.firstName = 'Leslie';

• employees와 customers 테이블을 연결하는 key : employees.employeeNumber = customers.salesRepEmployeeNumber
• 시작할 때 단순하게 시작하고, 점점 구문을 붙이는 게 쉽다

❔ 문제 4: employees 테이블과 offices 테이블을 사용하여, San Francisco 사무실에서 근무하는 모든 직원의 이름을 조회하라.

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

• employees와 offices 테이블을 연결하는 key : officeCode
• offices.city = 'San Francisco'

❔ 문제 5: orderdetails 테이블과 products 테이블을 사용하여, 주문 가격이 상품의 구매 가격보다 2.5배 높은 상품의 이름, 코드, 판매가격, 주문가격, 주문수량을 조회하라.

SELECT p.productName, p.productCode, p.buyPrice, o.priceEach, o.quantityOrdered
FROM products p
JOIN orderdetails o ON p.productCode = o.productCode
WHERE 2.5 * p.buyPrice < o.priceEach;

• orderdetails와 products 테이블을 연결하는 key : productCode
• 주문가격 orderdetails.priceEach, 구매가격 products.buyPrice
• 이름 products.productName, 코드 products.productCode, 판매가격 products.buyPrice, 주문가격 orderdetails.priceEach, 주문수량 orderdetails.quantityOrdered
• 마진이 높은 상품은 무엇인가!

❔ 문제 6: customers 테이블과 orders 테이블을 join하여, 2003년에 주문한 고객의 이름과 주문 번호를 조회하라.

SELECT c.customerName, o.orderNumber
FROM customers c
JOIN orders o ON c.customerNumber = o.customerNumber
WHERE YEAR(o.orderDate) = 2003 ;

• customers와 orders 테이블을 연결하는 key : customerNumber
• YEAR(orers.orderDate) = 2003
• 고객이름 customers.customerName, 주문번호 orders.orderNumber

❔ 문제 7: customers 테이블과 payments 테이블을 join하여, 2004년에 결제한 고객의 이름과 결제 금액을 조회하라.

SELECT c.customerName, p.amount
FROM customers c
JOIN payments p ON c.customerNumber = p.customerNumber
WHERE YEAR(p.paymentDate) = 2004;

• customers와 payments를 연결하는 key : customerNumber
• YEAR(payments.paymentDate) = 2004
• 고객이름 customers.customerName, 결제금액 payments.amount

⭐ 문제 8: employees 테이블과 customers 테이블을 join하여, 각 직원별로 담당한 고객의 수를 조회하라.

SELECT CONCAT(e.firstName," ", e.lastName) AS employeeName, COUNT(c.customerNumber) AS countC
FROM employees e
JOIN customers c ON e.employeeNumber = c.salesRepEmployeeNumber
GROUP BY e.employeeNumber;

• employees과 customer테이블을 연결하는 key : employees.employeeNumber = customers.salesRepEmployeeNumber
• concat : 합치기

⭐ 문제 9: orders 테이블, orderdetails 테이블, prooducts 테이블을 join하여, 2003년에 주문된 상품 이름과 해당 주문의 수량을 조회하라.

SELECT p.productName, od.quantityOrdered
FROM orders o
JOIN orderdetails od ON o.orderNumber = od.orderNumber
JOIN products p ON od.productCode = p.productCode
WHERE YEAR(o.orderDate) = 2003 ;

• orders - orderdetails, orderdetails - products 연결하는 key : orderNumber, productCode
• 먼저 2개의 테이블을 조인한 다음에 나머지 한 테이블을 조인하는 방법

❔ 문제 10: customers 테이블, orders 테이블, orderdetails 테이블을 조인하여, 각 고객별 총 주문 금액을 조회하라.

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

• customers - orders, orders - orderdetails 연결하는 key : customerNumber, orderNumber
• 고객별 GROUP BY customerNumber 이나 customerName
• 데이터가 너무 크면 LIMIT을 사용하여 적절히 조회할 것! 안그럼 컴퓨터 멈춤

❔ 문제 11: 각 직원별로 담당한 고객들의 총 결제 금액을 조회하라.

SELECT e.lastName, e.firstName, sum(p.amount) AS Total
FROM employees e
JOIN customers c ON e.employeeNumber = c.salesRepEmployeeNumber
JOIN payments p ON c.customerNumber = p.customerNumber
GROUP BY e.employeeNumber;

• employees - customers : employeeNumber, salesRepEmployeeNumber
• customers - payments : customerNumber

❔ 문제 12: 각 상품 라인별로 주문된 상품의 총 수량을 조회하라.

SELECT p.productLine, SUM(od.quantityOrdered) AS TotalQuantity
FROM products p
JOIN orderdetails od ON p.productCode = od.productCode
GROUP BY p.productLine;

• products - orderdetails : productCode

⭐ 문제 13: 2004년에 가장 많이 판매된 상위 5개 상품 이름과 해당 상품의 총 판매 수량을 조회하라.

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

• orders - orderdetails : orderNumber (2004-판매수량)
• orderdetails - products : productCode (상품이름)
• WHERE - GROUP BY - ORDER BY - LIMIT