개요
본 강의는 메타코드M에서 진행되는 이상훈 강사님의 실무에 적합한 SQL역량을 키우기 위해 준비된 강의입니다.
강의 정보는 아래 이미지 링크를 클릭해 주세요
https://mcode.co.kr/video/list2?viewMode=view&idx=53
1.번문제풀이
문제 : employees 테이블과 customers테이블을 Join한 수 각 직원별이 담당하는 고객 수가 5명 이상인 직원들의 이름과 담당 고객 수 조회
첫번째로 employees, customers테이블을의 구성을 각각 조사한 뒤, employees 테이블에서 customers 테이블로 연결 가능한Froigen Key를 찾는다.
여기서는 employees.employNumber 컬럼 항목이 Customers.salesRepEmployeeNumber 컬럼 항목과 연결 가능한 Froigen Key 이다.
다음으로 문제에 맞게 employees의 firstName 항목과 LastName 항목을 Concat함수로 텍스트 연결을 하여 깔금하게 컬럼을 합치고, 합친 컬럼 employ_name에서 직원 별로 담당하는 고객의 수를 Group by : Count 를 통해 데이터를 묶어준다.
마지막으로 고객 수 5명 이상의 값만 색인되게 Having을 적용한다.
2.번문제풀이
products 테이블에서 각 제품 라인별로 제품 가격의 평균을 계산한다. 이후 평균 가격이 50달러 이상인 제품 라인만 조회한다.
product 테이블을 불러온 뒤 '의미 있는 컬럼'인 productName, productLine, buyPrice 컬럼만 선택 색인
그 다음 buyPrice의 평균값으로 group by : AVG를 적용 후 50달러 이상인 조건을 Having을 적용하여 색인
3.번문제풀이
products 테이블과 orderdetails 테이블을 join 한 후, 제품별로 총 주문량을 계산하시오.
이때 총 주문량이 1000개 이상인 제품만 조회하시오
products 테이블과 ordertails 테이블을 색인한 후 products 테이블의 forigen key인 products.productCode를 ordertails 테이블의 primary key인 ordertails.productCode와 join
이후 의미있는 칼럼 products.productName, ordertials.quantityOrdered 컬럼만 선택 색인 후 group by : sum 함수로 ordertials.quantityOrdered값을 묶음
group by : sum 함수로 ordertials.quantityOrdered값을 묶인 컬럼을 SUM_order로 재 명명한 후 해당 컬럼에 Having조건문을 통해 1000개 이상 조건적용, 이후 Order by구문으로 내림차순 정렬
4.번문제풀이
문제 4: orders 테이블에서 각 연도별 주문 건수를 계산하시오, 그리고 연도별 주문건수가 100건 이상인 연도만 조회하시오
order 테이블에서 의미 있는 컬럼 orderdate, ordernumber 선택 색인
orderdate에서 YEAR 조건으로 연도만 추출한 뒤 이를 Group by : count으로 요약정리
마지막 Having구문 적용을 통해 주문건수 100건 이상 데이터셋 도출
5.번문제풀이
customers 테이블에서 각 나라별 고객 수를 조회하시오, 이때 고객 수가 5명 이상인 나라만 조건 색인하시오.
customers 테이블에서 의미 있는 컬럼 country, customerName 선택 색인
이후 customerName을 group by : count로 그룹화 한 뒤 해당 그룹에서 고객 수 5명 이상되는 값만 Having으로 데이터셋 추출
6.번문제풀이
customers 테이블과 orders 테이블을 Join 한 후 각 고객별 최근 주문 날짜 조회
customers 테이블과 orders 테이블을 Join
Join 테이블에서 의미있는 컬럼(customer.customerNumber, order.orderDate)를 선택 색인 후 group by : max 항목으로 가장 최신 날짜 데이터셋만 그룹 추출
이후 order by 함수로 보기 좋게 오름차순 정렬
참고로 문제풀이와 다르게 해당 문제는 Windows 함수로도 계산 중간과정을 보면서 하는게 더 편하긴 하다..
7.번문제풀이
products 테이블에서 각 제품 라인별로 최고가/최저가 제품의 가격차이 조사
product테이블을 색인 후 group by : min, max이중 조건 그룹화를 적용 하여 그룹 색인 후
해당 컬럼을 기반으로 최고가 - 최저가의 차이인 price_diff 컬럼을 생성한다.
(이때 price_diff 컬럼은 min, max 조건을 적용한 컬럼의 재 명기 col명 : m_buy, M_buy를 쓸 수는 없다)
8.번문제풀이
orders 테이블과 orderdetails 테이블을 join한 후 2004년에 주문된 제품들 중 가장 많이 주문된 제품의 코드와 이름을 조회하시오
우선 orders, orderdetails을 Join한다.
그런데 두 테이블을 Join해도 '제품의 이름'은 알 수 없다.
따라서 product 테이블을 한번 더 Join 해준 뒤
의미 있는 컬럼 products.productCode, products.productName, order.orderDate, orderdetails.quantityOrdered 컬럼을 선택 색인한다.
orderdetails.quantityOrdered 컬럼을 기반으로 group by : sum 조건으로 그룹화 한 뒤, YEAR(orders.orderData) = 2004 조건으로 조건에 맞는 데이터셋만 추출한다.
이후 보기좋게 내림차순 정렬
9.번문제풀이
employees 테이블과 customers 테이블을 join하여, 각 직원별 담당 고객의 평균 신용 한도를 조회하시오. 이때 평균 신용 한도가 5000이상이 고객그룹을 담당하는 직원만 조회사시오
SELECT CONCAT(employees.firstName, ' ', employees.lastName) AS employee_name,
customers.customerNume, customers.creditlimit
FROM employees
JOIN customers on employees.employeeNumber = customers.salesRepEmployeeNumber