14주차 목 1123(데이터 분석, 조인(Join)

조인이다!

-- join
/*
<조인(join) 구문 작성하는 방법>
 - Inner Join 방식은 : 인반방식 or 표준방식 모두 표준처럼 사용됨
(Inner Join - 일반방식)
	Select 테이블1. 컬럼1...테이블n.컬럼1
	From 테이블1, 테이블2... 테이블n
	Where 관계조건(PK = FK)
		And 일반조건
		
(Inner Join - 표준방식)
	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, cart_member
FROM MEMBER, cart;

-- 28
SELECT COUNT(*)
FROM MEMBER;
-- 135
SELECT COUNT(*)
FROM cart;

SELECT 28 * 135;

-- Inner Join
-- : 두 테이블간에 PK = FK 인 조건을 제시
-- (일반방식)
-- : From 절 뒤에 테이블을 콤마(,)로 구분하여 사용
-- : Where절에 관계조건식 (PK = FK)을 추가함
-- : 관계조건식은 최소한 (사용된 테이블의 갯수 -1) 만큼 제시 해야함
-- : 제일 많은 값을 기준으로 체크한다.
SELECT mem_id, mem_name, cart_prod, cart_qty, prod_name
FROM MEMBER, cart, prod
-- 관계조건식 추가(PK = FK)
WHERE mem_id = cart_member
	AND prod_id = cart_prod
	-- 일반조건 추가
	AND mem_add1 LIKE '서울%';
	
-- (Inner Join Ansi 표준방식)
SELECT mem_id, mem_name, cart_prod, cart_qty, prod_name
FROM MEMBER
		INNER JOIN cart
			ON(mem_id = cart_member)
				-- 일반조건 추가
				AND mem_add1 LIKE '서울%'
		INNER Join prod
			ON(prod_id = cart_prod);
			
-- 일반조건을 where절에 추가
SELECT mem_id, mem_name, cart_prod, cart_qty, prod_name
FROM MEMBER
		INNER JOIN cart
			ON(mem_id = cart_member)
		INNER Join prod
			ON(prod_id = cart_prod)
-- 일반조건 추가
where mem_add1 LIKE '서울%';

-- [문제]
-- 조회컬럼 : 회원아이디, 회원이름, 주문번호, 주문수량, 상품명 조회 
-- 조건 : 주문내역이 있는 회원이어야 함
-- 	  : 주문한 회원 거주지가 서울에 거주하는 회원이어야 함
--      : 상품명에 컴퓨터라는 단어가 포함
-- 정렬 : 주문수량을 기준으로 내림차순
-- (일반방식)
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 ASC; 
-- 컴파일 순서 select > from > where > select 조회 > order by

-- (표준방식)
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 asc;
-- 컴파일 순서 select > from > select 조회 > order by

-- [문제]
-- 상품분류별 상품의 갯수를 조회
-- 조회컬럼 : 상품분류코드, 상품분류명, 분류별갯수
-- 정렬 : 분류별갯수 내림차순
-- (일반방식)
SELECT prod_lgu, lprod_nm, COUNT(lprod_nm) AS lprod_cnt
FROM prod, lprod
where prod_lgu = lprod_gu
GROUP BY prod_lgu, lprod_nm
ORDER BY lprod_cnt DESC;

-- (표준방식)
SELECT prod_lgu, lprod_nm, COUNT(lprod_nm) AS lprod_cnt
FROM prod
		INNER JOIN lprod
			ON(prod_lgu = lprod_gu)
GROUP BY prod_lgu, lprod_nm
ORDER BY lprod_cnt DESC;

-- [문제]
/*
 - 회원아이디, 회원이름, 주문수량, 상품명 조회
 - 단, 구매상품의 거래처 주소가 서울, 대전, 광주인 경우
 -     상품분류명에 "전자"가 포함된 경우
 -     주문수량이 5이상인 경우
 - 정렬은 아이디 오름차순, 주문수량 내림차순
*/
-- (일반방식)
SELECT mem_id, mem_name, cart_qty, prod_name
FROM MEMBER, cart, prod, buyer, lprod
WHERE mem_id = cart_member
 AND cart_prod = prod_id
 AND prod_buyer = buyer_id
 AND prod_lgu = lprod_gu
	AND SUBSTR(buyer_add1,1,2) IN ('서울','대전','광주') 
	AND lprod_nm LIKE '%전자%'
	AND cart_qty >= 5
ORDER BY mem_id ASC, 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 buyer
			ON(prod_buyer = buyer_id
				AND SUBSTR(buyer_add1,1,2) IN ('서울','대전','광주') )
		INNER Join lprod
			ON(prod_lgu = lprod_gu
				AND lprod_nm LIKE '%전자%' )
ORDER BY mem_id ASC, cart_qty DESC;

-- [문제]
-- 주문내역이 있는 회원별로 지금까지 총 지출한 총액 조회하기
-- 조회컬럼 : 회원아이디, 회원이름, 총 지출액
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 mem_id
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 mem_id
ORDER BY total DESC;

-- 위 문제에 이어서...
-- 위 결과에서 가장 지출이 큰 값과, 가장 작은 값을 조회
-- 조회 값 : 최대값, 최소값 2개만..

-- 가상테이블 개념이 적용됨(Inline View)
-- : From절 뒤에 테이블 대신 Sub Query를 사용함
-- : Sub Query의 Select한 행렬 결과를 메모리에 적재시켜서 사용
-- : 행렬의 데이터를 테이블과 동일한 개념으로 사용하기 때문에
--   가상테이블이라고 칭합니다.
-- : 가상테이블은 별칭을 이용해서 테이블 이름처럼 사용합니다.
SELECT MAX(total) AS max_total,
		MIN(total) AS min_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 mem_id) Subquery;

-- [문제]
-- 위에 결과에서 최대값을 가지는 회원아이디와 이름을 조회
SELECT B.mem_id, B.mem_name, B.total
FROM (select MAX(total) AS max_total,
				 MIN(total) AS min_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 mem_id) Subquery) A,
				
	  (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 mem_id
		) B
WHERE A.max_total = B.total;