굉장히 엄청나!

Inner Join 복습 문제

문제 

• 회원아이디 b001 회원의 마일리지 값보다 큰 회원정보 조회
• 조회칼럼 : 회원아이디, 이름

-- 1
SELECT mem_id, mem_name
FROM member
WHERE mem_mileage > (SELECT mem_mileage
FROM member
WHERE mem_id ='b001') ;

-- 2
SELECT A.mem_id, A.mem_name
FROM (SELECT mem_id, mem_name
FROM member
WHERE mem_mileage > (SELECT mem_mileage FROM member WHERE mem_id ='b001')) A ;

-- 3
SELECT mem_id, mem_name
FROM member, 
(SELECT mem_mileage
FROM member
WHERE mem_id ='b001') MEM
WHERE member.mem_mileage > MEM.mem_mileage ;

결과는 모두 동일하다.

🎀 Self Join

• Self Join으로 처리하기
• 셀프조인 시 : 조건은 한쪽 m2 테이블에 적용
• 조회 시 : m1 테이블에서 조회

Self Join 문제

문제 1번

• Inner Join 문제 1번에 Self join을 사용해 보기

SELECT m1.mem_id, m1.mem_name
FROM member m1, member m2
WHERE m2.mem_id = 'b001'
AND m1.mem_mileage > m2.mem_mileage;

Inner Join 문제 1번과 결과가 동일하다.

🎀 Outer Join

• 특정 테이블 전체에 대한 집계를 하고자 할 때 사용
• OUTER JOIN 종류
  1. LEFT OUTER JOIN : 테이블 순서 중 왼쪽에 위치한 테이블 전체
  2. RIGHT OUTER JOIN : 테이블 순서 중 오른쪽에 위치한 테이블 전체
  3. FULL OUTER JOIN : 왼쪽, 오른쪽 전체 테이블 (Oracle DB만 사용 가능)
• OUTER JOIN 개념
  1. Outer Join은 표준방식으로만 사용해야 하며 일반 조건은 무조건 On() 내부에 작성해야 한다
  2. 일반 조건을 where 절을 사용하면 Outer 전체 계념이 적용이 안된다
  3. Inner Join을 만족해야 한다(Inner Join 개념을 그대로 적용)
  4. Left or Right 기준으로 같으면 같은 조건대로 조회하고 다르면 null로 조회된다

Outer join 문제

문제 1번

SELECT lprod_nm, COUNT(prod_lgu) AS cnt
FROM lprod 
	LEFT JOIN prod
		ON (lprod_gu = prod_lgu)
GROUP BY lprod_gu
ORDER BY cnt;

문제 2 - 1번

• 회원별 구매금액의 총액 조회
• 2005년 5월에 구매한 내역
• 조회칼럼 : 회원이름, 구매금액 총액
  

  -- 일반 방식
  SELECT mem_name, SUM(cart_qty*prod_sale) AS total
  FROM member, prod, cart
  WHERE mem_id=cart_member
  AND cart_prod = prod_id
  AND substring(cart_no,1,6) = '200505'
  group BY mem_id, mem_name
  ORDER BY total desc;
  
  -- 표준 방식
  SELECT mem_name, SUM(cart_qty*prod_sale) AS total
  FROM member INNER join cart
  ON (mem_id=cart_member AND substring(cart_no,1,6) = '200505')
  INNER JOIN prod
  ON (cart_prod = prod_id)
  group BY cart_member
  ORDER BY total desc;​

찾은 행 13개

5월에 구매한 내역이 있는 고객만 조회됨

문제 2 - 2번

• 문제 2 - 1에 이어서 회원 전체에 대해서 문제 2 - 1조건으로 조회하기
• 총회원은 28명

SELECT mem_name, SUM(nvl(cart_qty,0)*nvl(prod_sale,0)) AS total
FROM member left join cart
ON (mem_id=cart_member)
left JOIN prod
ON (cart_prod = prod_id)
group BY mem_id, mem_name
ORDER BY total desc;

찾은 행 28개

구매이력이 없는 회원도 모두 조회되며

NULL값을 가지면 0으로 대체됨

문제 3 - 1번

• 2005년도 월별 판매 현황 검색하기
• 조회칼럼 : 판매월, 월별 총판매수량, 월별 총판매금액

SELECT substring(cart_no,1,6) AS cart_no, sum(cart_qty) AS cart_qty, sum(cart_qty*prod_sale) AS total
	FROM cart 
		left join prod
			ON (cart_prod = prod_id
					AND SUBSTRING(cart_no,1,4) = '2005')
	GROUP BY substring(cart_no,1,6);

문제 3 - 2번

• 문제 3 - 1번 조건에서 1월 ~ 12월까지 모두 표현하고자 한다면?
  
• 방법 1 - UNION 사용

SELECT
    months.month,
    NVL(SUM(A.cart_qty), 0) AS total_sales,
    NVL(SUM(A.cart_qty*B.prod_sale),0) AS total
FROM (
    SELECT '200501' AS month
    UNION SELECT '200502'
    UNION SELECT '200503'
    UNION SELECT '200504'
    UNION SELECT '200505'
    UNION SELECT '200506'
    UNION SELECT '200507'
    UNION SELECT '200508'
    UNION SELECT '200509'
    UNION SELECT '200510'
    UNION SELECT '200511'
    UNION SELECT '200512'
) AS months
LEFT JOIN cart A ON (SUBSTRING(A.cart_no, 1, 6) = months.month)
LEFT join prod B ON (A.cart_prod = B.prod_id)
GROUP BY months.month
ORDER BY months.month;

• 방법 2 - 새로운 날짜 테이블 생성

-- 테이블 생성
CREATE TABLE date_mm (
	mm CHAR(2) NOT NULL
);

-- 값 입력
INSERT INTO date_mm VALUES('01');
INSERT INTO date_mm VALUES('02');
INSERT INTO date_mm VALUES('03');
INSERT INTO date_mm VALUES('04');
INSERT INTO date_mm VALUES('05');
INSERT INTO date_mm VALUES('06');
INSERT INTO date_mm VALUES('07');
INSERT INTO date_mm VALUES('08');
INSERT INTO date_mm VALUES('09');
INSERT INTO date_mm VALUES('10');
INSERT INTO date_mm VALUES('11');
INSERT INTO date_mm VALUES('12');

-- 조회 코드 작성
SELECT date_mm.mm,
	SUM(nvl(cart_qty,0)) AS total_qty,
	SUM(nvl(cart_qty*prod_sale,0)) AS total
FROM date_mm
	LEFT JOIN cart
		ON(date_mm.mm = SUBSTRING(cart_no,5,2))
	LEFT JOIN prod
		ON(cart_prod = prod_id
			AND substring(cart_no,1,4) = '2005')
GROUP BY date_mm.mm;

결과는 동일하다.

문제 4번

• 2005년도에 대한 전체 거래처별 총매출금액 조회하기
• 조회칼럼 : 거래처 코드, 거래처명, 총매출금액
• 정렬 : 총매출금액 - 내림차순

SELECT buyer_id, buyer_name, SUM(nvl(cart_qty*prod_sale,0)) AS total
FROM buyer 
	LEFT join prod
		ON (buyer_id = prod_buyer)
	LEFT join cart
		ON(prod_id = cart_prod AND substring(cart_no,1,4) = '2005' )
GROUP BY buyer_id
ORDER BY total DESC;

찾은 행 13개

⛄ JOIN

• 조인(join) 구문 작성하는 방법
• Inner Join 방식은 일반방식 or 표준방식 모두 표준처럼 사용됨
• Inner Join - 일반방식
  - 두 테이블 간에 PK = FK인 조건을 제시
  - from절 뒤에 테이블들을 ,로 구분하여 사용
  - where절에 관계 조건식(PK=FK)을 추가함
  - 관계조건식은 최소한 (사용된 테이블의 갯수 -1) 만큼 제시되어야 함

select 테이블1.컬럼1...테이블n.컬럼1
 From 테이블1, 테이블2 ... 테이블n
 Where 관계조건(PK = FK)
   And 일반조건

SELECT mem_id, mem_name, cart_prod, cart_qty
FROM member, cart
-- 관계조건식 추가(PK = FK)
WHERE mem_id = cart_member
	-- 일반조건 추가
	AND mem_add1 LIKE '서울%';

• Inner Join - 표준방식
  - 일반 조건을 where절에 작성하는 것도 가능하다.

select 테이블1.컬럼1...테이블n.컬럼1
 From 테이블1 Inner Join 테이블2
    On(관계조건(PK = FK)
     And 일반조건)
     Inner join 테이블N
    On(관계조건(PK = FK)
      And 일반조건)
 Group by ... 그룹이 있는 경우
 Having ...그룹 조건이 있는 경우
 Order By ...정렬이 있는 경우

SELECT mem_id, mem_name, cart_prod, cart_qty
FROM member INNER JOIN cart
		on (mem_id = cart_member
       	 		-- 일반조건 추가
			AND mem_add1 LIKE '서울%')
	    INNER join prod
		on (cart_prod = prod_id);

• cross join
  - 행의 갯수 = 테이블행 전체갯수 * 테이블행 전체갯수
  - 아래와 같이 두 테이블 간의 관계 조건 없이 사용한 경우

SELECT mem_id, cart_member
FROM member, cart;

JOIN 문제

📝문제 1 

• 조회컬럼 : 회원아이디, 회원이름, 주문번호, 주문수량, 상품명 조회
• 조건
  1. 주문내역이 있는 회원이어야 하고 회원 거주지가 서울에 거주하는 회원이어야 한다.
  2. 상품명에 컴퓨터라는 단어가 포함되어있어야 한다.
  3. 일반방식과 표준방식으로 작성하기
  4. 주문수량을 기준으로 내림차순

✏️일반방식 

SELECT mem_id, mem_name, cart_no, cart_qty, prod_name
FROM member, cart, prod
WHERE mem_id = cart_member
	AND cart_prod = prod_id
   	AND mem_add1 LIKE '서울%'
	AND prod_name like '%컴퓨터'
order by cart_qty DESC;

✏️표준방식 

SELECT mem_id, mem_name, cart_no, cart_qty, prod_name
FROM member 
	INNER JOIN cart
 		ON (mem_id = cart_member AND mem_add1 LIKE '서울%')
  	INNER JOIN prod
 		ON (cart_prod = prod_id AND prod_name like '%컴퓨터')
order by cart_qty DESC;

조회되는 데이터 없음

📝문제 2 

• 조회 컬럼 : 상품분류코드, 상품분류명, 분류별 상품의 수
• 조건
  1. 상품 분류별 상품의 수를 조회
  2. 분류별 상품의 수 내림차순

✏️일반방식 

SELECT lprod_gu, lprod_nm, COUNT(prod_lgu) AS cnt
FROM lprod, prod
WHERE lprod_gu = prod_lgu
GROUP BY lprod_gu
ORDER BY cnt DESC;

✏️표준방식 

SELECT lprod_gu, lprod_nm, COUNT(lprod_gu) AS cnt
FROM lprod
	INNER JOIN prod
		ON (lprod_gu = prod_lgu)
GROUP BY lprod_gu
ORDER BY cnt DESC;

📝문제 3

• 조회 컬럼 : 회원아이디, 회원이름, 주문 수량, 상품명
• 조건
  1. 구매상품의 거래처 주소가 서울, 대전, 광주
  2. 상품 분류명에 전자가 포함
  3. 주문수량이 5이상
  4. 아이디 오름차순, 주문수량 내림차순

✏️일반방식 

SELECT mem_id, mem_name, cart_qty, prod_name
FROM member, cart, prod, lprod, buyer
WHERE mem_id = cart_member
	AND cart_prod = prod_id
	AND prod_lgu  = lprod_gu
	AND Prod_buyer = buyer_id
	AND substring(buyer_add1,1,2) IN ('서울', '대전','광주')
	AND lprod_nm LIKE '%전자%'
	AND cart_qty >= 5
ORDER BY mem_id, cart_qty DESC;

✏️표준방식 

SELECT mem_id, mem_name, cart_qty, prod_name
FROM member
		INNER JOIN cart
 			ON(mem_id = cart_member AND cart_qty >= 5)
 		INNER JOIN prod
 			ON(cart_prod = prod_id)
		INNER JOIN lprod
			ON(prod_lgu  = lprod_gu AND lprod_nm LIKE '%전자%')
		INNER JOIN buyer
		 	ON(Prod_buyer = buyer_id AND substring(buyer_add1,1,2) IN ('서울', '대전','광주'))
ORDER BY mem_id, cart_qty DESC;

조회되는 데이터 없음

📝문제 4

• 주문내역이 있는 회원별로 지금까지 총 지출한 총액 조회
• 조회컬럼 : 회원아이디, 회원이름, 총지출액

✏️일반방식 

SELECT mem_id, mem_name, SUM(prod_sale*cart_qty) AS 'total'
FROM member, cart, prod
WHERE mem_id = cart_member
	AND cart_prod = prod_id
GROUP BY cart_member
ORDER BY total desc;

✏️표준방식 

SELECT mem_id, mem_name, SUM(prod_sale*cart_qty) AS 'total'
FROM member 
	inner join cart
		ON mem_id = cart_member
	inner join prod
		ON cart_prod = prod_id
GROUP BY cart_member
ORDER BY total desc;

⛄ Inline View

Inline View 문제

✏️문제 1

• JOIN 문제 4번에 이어서 지출이 가장 큰 값과, 가장 작은 값을 조회하기
• 조회 컬럼 : 최댓값, 최솟값

SELECT MAX(A.total) AS total_max
			, MIN(A.total) AS total_min
FROM (
    SELECT SUM(prod_sale * cart_qty) AS total
    FROM member
        INNER JOIN cart 
		  ON mem_id = cart_member
        INNER JOIN prod 
		  ON cart_prod = prod_id
    GROUP BY cart_member) A;

✏️문제 2 

- Inline View 1번 문제의 결과에서 최대값을 가지는 회원아이디와 이름 조회

SELECT AA.mem_id, AA.mem_name, AA.total
FROM(
	SELECT mem_id, mem_name, SUM(prod_sale * cart_qty) AS total
    			FROM member, cart, prod
				WHERE mem_id = cart_member
					AND cart_prod = prod_id
			GROUP BY cart_member) AA,
	(
	SELECT MAX(A.total) AS total_max, MIN(A.total) AS total_min
		FROM (SELECT mem_id, mem_name, SUM(prod_sale * cart_qty) AS total
    			FROM member, cart, prod
				WHERE mem_id = cart_member
					AND cart_prod = prod_id
				GROUP BY cart_member) A) BB
WHERE AA.total = BB.total_max;