굉장히 엄청나!

📝 문제

✏️ 작성한 코드

 for문을 사용하여 풀었지만 데이터가 많을 경우 효율적이지 못한 코드가 될 수 있으므로 HashMap을 사용하는 것을 추천한다.

• int[] answer = new int[photo.length]
  - int[]: 정수형 배열을 나타내는 자료형
  - new int[photo.length]: photo 배열의 길이와 같은 크기를 가진 새로운 정수형 배열을 생성

class Solution {
    public int[] solution(String[] name, int[] yearning, String[][] photo) {
        int[] answer = new int[photo.length];
        for (int i = 0; i < name.length ;i++) {
            for (int j = 0; j < photo.length; j++) {
                for (int k = 0; k < photo[j].length; k++){
                    if (photo[j][k].equals(name[i])) {
                            answer[j] += yearning[i];
                  }
                }
              }       
            }
        return answer;
   	   }
       }

✏️ 코딩짱 풀이 

HashMap 사용

import java.util.*;

class Solution {
    public int[] solution(String[] name, int[] yearning, String[][] photo) {
        int[] answer = new int[photo.length];

        HashMap<String, Integer> map = new HashMap<>();
        for(int i = 0; i < name.length; i++) {
            map.put(name[i], yearning[i]);
        }

        int sum;
        for(int i = 0; i < photo.length; i++) {
            sum = 0;
            for(int j = 0; j < photo[i].length; j++) {
                if(map.containsKey(photo[i][j])) 
                    sum += map.get(photo[i][j]);
            }   
            answer[i] = sum;
        }   
        
        return answer;
    }
}

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개

문제

• 가장 최근에 입고된 상품의 상품명, 거래처명
  
• 가장 최근에 입고된 상품을 가장 많이 구매한 회원의 아이디, 이름, 마일리지, 해당 상품 구매수량, 회원등급 구하기
• 조회 컬럼 : 상품명, 거래처명, 회원아이디, 회원이름, 마일리지, 해당 상품 구매수량, 회원 등급
• 등급 조건

• ERD

작성한 코드

코드 1

SELECT  AA.prod_name, AA.buyer_name, AA.mem_id, AA.mem_name, AA.mem_mileage, AA.cart_qty,
(CASE
        WHEN AA.mem_mileage BETWEEN 1000 AND 2000 THEN '브론즈'
        WHEN AA.mem_mileage BETWEEN 2000 AND 3000 THEN '실버'
        WHEN AA.mem_mileage BETWEEN 3000 AND 4000 THEN '골드'
        WHEN AA.mem_mileage BETWEEN 4000 AND 5000 THEN '플래티넘'
        WHEN AA.mem_mileage >= 5000 THEN '다이아'
    	ELSE '아이언'
	 END) AS grade 
FROM (
	SELECT prod_name, buyer_name, mem_id, mem_name, mem_mileage, cart_qty, cart_prod
   	FROM member, cart, prod, buyer
    		WHERE mem_id = cart_member
            AND cart_prod = prod_id
            AND prod_buyer = buyer_id 
      ) AA,
     (
	SELECT cart_prod, MAX(cart_qty) AS cart_qty
		FROM cart
    		WHERE cart_prod IN (
           				 SELECT B.buy_prod
           					 FROM (SELECT MAX(buy_date) AS max_date
                  					FROM buyprod) A,
                 					(SELECT buy_prod, buy_date
                  					FROM buyprod) B
            					WHERE A.max_date = B.buy_date)
    			GROUP BY cart_prod ) BB
	WHERE AA.cart_prod = BB.cart_prod 
      AND AA.cart_qty = BB.cart_qty
	ORDER BY prod_name;

코드 2

출처 미니 조코딩 님

https://sujakjil.tistory.com/54

SELECT prod_name ,buyer_name ,mem_id , mem_name , mem_mileage , cart_qty ,
      (case 
      when mem_mileage BETWEEN 1000 AND 1999 then '브론즈'
      when mem_mileage BETWEEN 2000 AND 2999 then '실버'
      when mem_mileage BETWEEN 3000 AND 3999 then '골드'
      when mem_mileage BETWEEN 4000 AND 4999 then '플래티넘'
      ELSE '다이아' 
      END) AS 'grade'
FROM member , cart , prod , buyer
WHERE prod_id = cart_prod
AND mem_id = cart_member
AND prod_buyer = buyer_id
AND prod_id IN (SELECT prod_id FROM buyprod , prod 
               WHERE prod_id = buy_prod
               AND buy_date = (SELECT MAX(buy_date)
               FROM buyprod))
AND cart_qty in (SELECT  MAX(cart_qty)
FROM cart , prod
WHERE cart_prod = prod_id
and prod_id IN (SELECT prod_id FROM buyprod , prod 
               WHERE prod_id = buy_prod
               AND buy_date = (SELECT MAX(buy_date)
               FROM buyprod))
GROUP BY prod_id);

결과

📝문제

✏️작성한 코드

Java

class Solution {
    public int solution(int n, int m, int[] section) {
        int answer = 1;
        int start = section[0];
        for (int i = 0; i < section.length; i++) {
                if(section[i] > start + m - 1) {
                    answer += 1;
                    start = section[i];
                }
            }
        return answer;
    }
}

✒️ 코딩짱 풀이

미니 조코딩 님 - Python

https://sujakjil.tistory.com/53

def solution(n, m, section):
    a = section[0]
    cnt = 1
    for i,s in enumerate(section) :
        if s > a+m-1:
            cnt += 1
            if i != len(section)-1:
                a = section[i]
    return cnt

cow1 님 - Python

https://codingwithyou.tistory.com/

def solution(n, m, section):
    answer = 1
    start = section[0]
    for i in section:
        
        # start = section[i][j]
        if i > start+(m - 1):
            answer += 1
            start = i
    return answer

⛄ 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;

📝문제

✏️작성한 코드

 CREATED_DATE를 어느 테이블에서 가져와야 하는지 헷갈렸다. 문제에서 2022년 10월에 작성된 게시글 제목이라고 했기 때문에 WHERE 절에서 쓴 CREATED_DATE는 USED_GOODS_BOARD에서 가져와야 했고, SELECT 절에서 쓴 CREATED_DATE는 댓글 작성일을 조회하는 것이기 때문에 USED_GOODS_REPLY 테이블에서 가져와 조회를 해야 한다.

SELECT A.TITLE
       , A.BOARD_ID
       , B.REPLY_ID
       , B.WRITER_ID
       , B.CONTENTS
       , DATE_FORMAT(B.CREATED_DATE, '%Y-%m-%d') AS CRAETED_DATE
FROM USED_GOODS_BOARD A 
INNER JOIN USED_GOODS_REPLY B
ON A.BOARD_ID = B.BOARD_ID
WHERE SUBSTRING(A.CREATED_DATE,1, 7) = '2022-10'
ORDER BY B.CREATED_DATE, A.TITLE;