[Database][SQL] 데이터 조회 문제 만들어보기

문제

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

마일리지	등급
~ 999	아이언
1000 ~ 1999	브론즈
2000 ~ 2999	실버
3000 ~ 3999	골드
4000 ~ 4999	플래티넘
5000 ~	다이아

• 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);

결과