굉장히 엄청나!

문제 1 - 자동차 대여 기록에서 장기/단기 대여 구분하기

1. 문제 

https://school.programmers.co.kr/learn/courses/30/lessons/151138

2. 작성한 코드

• DATEDIFF () : 두개의 날짜값의 차이를 int로 반환하는 내장함수이다.
  
• 마자막 날짜에서 시작 날짜의 차이를 구하면 하루를 포함하지 않는 값이 나오므로 +1 해줘야 한다.

SELECT HISTORY_ID
        , CAR_ID
        , DATE_FORMAT(START_DATE, "%Y-%m-%d") AS START_DATE
        , DATE_FORMAT(END_DATE, "%Y-%m-%d") AS END_DATE
        , (CASE 
            WHEN (DATEDIFF(END_DATE, START_DATE) + 1) >= 30 THEN "장기 대여"
            ELSE "단기 대여"
            END) as RENT_TYPE
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where START_DATE like '2022-09%'
order by HISTORY_ID desc

문제 2 - 특정 옵션이 포함된 자동차 리스트 구하기

1. 문제 

https://school.programmers.co.kr/learn/courses/30/lessons/157343

2. 작성한 코드

SELECT CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS
from CAR_RENTAL_COMPANY_CAR
where OPTIONS LIKE '%네비게이션%'
order by CAR_ID DESC

문제 3 - 과일로 만든 아이스크림 고르기

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/133025

2. 작성한 코드

SELECT A.FLAVOR
FROM FIRST_HALF A, ICECREAM_INFO B
WHERE (A.FLAVOR = B.FLAVOR 
       AND B.INGREDIENT_TYPE = "fruit_based"
 AND A.TOTAL_ORDER > 3000)
ORDER BY A.TOTAL_ORDER DESC

문제 4 - 인기있는 아이스크림

1. 문제 

https://school.programmers.co.kr/learn/courses/30/lessons/133024

2. 작성한 코드

SELECT FLAVOR
FROM FIRST_HALF
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID

문제 5 - 흉부외과 또는 일반외과 의사 목록 출력하기

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/132203

2. 작성한 코드

SELECT DR_NAME
        , DR_ID
        , MCDP_CD
        , DATE_FORMAT(HIRE_YMD, '%Y-%m-%d') AS HIRE_YMD
FROM DOCTOR
where MCDP_CD IN ("CS", "GS")
order by HIRE_YMD desc, DR_NAME

문제 6 - 12세 이하인 여자 환자 목록 출력하기

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/132201

2. 작성한 코드

SELECT PT_NAME,	PT_NO, GEND_CD,	AGE, IFNULL(TLNO, "NONE") AS TLNO
FROM PATIENT
WHERE GEND_CD = "W"
    AND AGE <= 12
ORDER BY AGE DESC, PT_NAME

문제 7 - 가장 비싼 상품 구하기

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/131697

2. 작성한 코드

SELECT MAX(PRICE) AS MAX_PRICE
FROM PRODUCT

문제 8 - 조건에 맞는 회원수 구하기

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/131697

2. 작성한 코드

SELECT COUNT(USER_ID) AS USERS
FROM USER_INFO
WHERE JOINED LIKE "2021%"
    AND AGE BETWEEN 20 AND 29

문제 9 - 나이 정보가 없는 회원 수 구하기

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/131528

2. 작성한 코드

SELECT COUNT(USER_ID) AS USERS
FROM USER_INFO
WHERE AGE IS NULL

문제 10 - 경기도에 위치한 식품창고 목록 출력하기

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/131114

2. 작성한 코드

SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, IFNULL(FREEZER_YN, "N")
FROM FOOD_WAREHOUSE
WHERE ADDRESS LIKE "경기도%"
ORDER BY  WAREHOUSE_ID

문제 11 - 강원도에 위치한 생산공장 목록 출력하기

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/131112

2. 작성한 코드

SELECT FACTORY_ID, FACTORY_NAME, ADDRESS
FROM FOOD_FACTORY
WHERE ADDRESS LIKE "강원도%"
ORDER BY FACTORY_ID

문제 12 - 경기도에 위치한 식품창고 목록 출력하기

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/59415

2. 작성한 코드

SELECT MAX(DATETIME) AS 시간
FROM ANIMAL_INS

문제 13 - 이름이 있는 동물의 아이디

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/59407

2. 작성한 코드

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
ORDER BY ANIMAL_ID

문제 14 - 상위 n개 레코드

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/59405

2. 작성한 코드

• MySQL에서 상위 N개의 데이터를 출력하기 위해선 LIMIT N 함수를 사용한다.
• ORACLE : ROWNUM < N
• MS - SQL : TOP N, TOP N WITH TIES 

SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME
LIMIT 1

문제 15 - 여러 기준으로 정렬하기

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/59404

2. 작성한 코드

SELECT ANIMAL_ID, NAME, DATETIME
FROM ANIMAL_INS
ORDER BY NAME, DATETIME DESC

문제 16 - 동물의 아이디와 이름

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/59403

2. 작성한 코드

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

문제 17- 이름이 없는 동물의 아이디

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/59039

2. 작성한 코드

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NULL
ORDER BY ANIMAL_ID

문제 18 - 어린 동물 찾기

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/59037#fn1

2. 작성한 코드

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION != "Aged"
ORDER BY ANIMAL_ID

문제 19 - 아픈 동물 찾기

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/59036

2. 작성한 코드

SELECT ANIMAL_ID, NAME
from ANIMAL_INS
where INTAKE_CONDITION = "Sick"
order by ANIMAL_ID

문제 20 - 역순 정렬하기

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/59035

2. 작성한 코드

SELECT NAME, DATETIME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID DESC

문제 21 - 모든 레코드 조회하기

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/59034

2. 작성한 코드

SELECT ANIMAL_ID
        , ANIMAL_TYPE
        , DATETIME
        , INTAKE_CONDITION
        , NAME
        , SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

🎀 Union / Union All

Union

• 조회결과와 조회결과를 행단위로 합치는 기능
• 조회결과들 간의 칼럼의 개수는 동일해야 함
• 조회결과들 간의 데이터 타입을 동일해야 함
• 예시
  - 'member'와 'cart'는 구분자로 쓰임

SELECT 'member', mem_id, mem_name
FROM member
UNION
SELECT 'cart', cart_member, cart_prod
FROM cart;

찾은 행  28개

UNION ALL

• 회원 28명, 주문정보 135건 일 때 아래 코드 실행 시 결과
  UNION : 155건
  UNION ALL : 163건

SELECT 'member', mem_id, mem_name
FROM member
UNION all
SELECT 'cart', cart_member, cart_prod
FROM cart;

• UNION : 중복제거
• UNION ALL : 중복포함
• MINUS : 차집합
• INTERSECT : 교집합

Union 문제

문제 1 - 1번

SELECT mem_id, mem_name, SUM(nvl(cart_qty*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
UNION
SELECT '', '', SUM(nvl(cart_qty*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 '', '';

찾은 행 29개

- 그룹화 하지 않은 SUM은 전체 행의 합이 구해진다.

- Group by ' ', ' ' 는 아무 의미가 없지만 빈 문자열로 컬럼을 지정하면 오류가 나기 때문에 문법상 적어준 것이다.

문제 1 - 2번

• Union 문제 1 - 1번 에서 총 구매금액 순을 내림차순으로 정렬
• 단, 총 구매금액의 총합은 마지막 행에 위치

SELECT A.mem_id, A.mem_name, A.total
FROM 
(SELECT mem_id, mem_name, SUM(nvl(cart_qty*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
ORDER BY total DESC) A
UNION
SELECT '', '', SUM(nvl(cart_qty*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 '', '';

🎀 Exists / Not Exists

Exists

• 조회결과가 1건이라도 있으면 true, 0건이면 false
• 서브쿼리를 적용(다중컬럼의 다중행 모두 가능)
• 예시
  - 구매내역이 있는 회원만 조회
  - 아래 쿼리 실행시 회원 28명 중 19명이 조회 된다.
  

  SELECT mem_id, mem_name
  FROM member
  WHERE EXISTS(SELECT *
  FROM cart
  WHERE cart_member = mem_id);​

Not Exists

• 예시
  - 구매 (주문) 내역이 없는 회원만 조회
  - 아래 쿼리 실행시 회원 28명 중 9명이 조회 된다.

SELECT mem_id, mem_name
FROM member
WHERE NOT EXISTS (SELECT * FROM cart WHERE mem_id = cart_member);

Exists / Not Exists 문제

문제 1번

• 2005년도 구매내역이 있는 회원 조회하기
• 단, 구매내역이 있는 회원의 총구매금액이 3천만원 이상인 데이터에 대해서만 조회
• 조회컬럼 : 회원아이디, 회원이름, 마일리지
• 방법 1 - Exists 사용

SELECT mem_id, mem_name, mem_mileage
FROM member
WHERE EXISTS (
		SELECT SUM(cart_qty*prod_sale)
			FROM cart, prod
			WHERE cart_member = mem_id
				AND cart_prod = prod_id
				AND left(cart_no,4) = '2005'
		HAVING SUM(cart_qty*prod_sale) >= 30000000);

• 방법 2 - Inline View 사용

SELECT A.mem_id, A.mem_name, A.mem_mileage
FROM(
	SELECT mem_id, mem_name, mem_mileage, SUM(nvl(cart_qty*prod_sale,0)) AS total
		FROM member 
        	LEFT JOIN cart
				ON ( mem_id = cart_member AND left(cart_no,4) = '2005')
			LEFT JOIN prod
				ON(cart_prod = prod_id)
		GROUP BY mem_id) A
WHERE A.total >= 30000000;

결과는 동일하다.

🎀 UPDATE

• 데이터 수정하기
• UPDATE 할 땐 수정하려는 값이 문제에서 제시한 수정 대상이 맞는지  SELECT ~ WHERE 절로 확인하기
• 수정 및 삭제 시 기준 컬럼은 PK값을 기준으로 행을 조건 처리 해야 함
  
• 예시 1
  - 회원아이디 a001, b001인 회원의 마일리지 값을 2로 수정

UPDATE member set mem_mileage = 2
WHERE mem_id IN ('a001','b001') ;

• 예시 2
  - 구매내역이 있는 회원의 마일리지 값을 3으로 일괄 수정처리하기

UPDATE member SET mem_mileage =3
WHERE EXISTS(SELECT *
FROM cart
WHERE cart_member = mem_id);

UPDATE 문제

문제 1

• Exists 문제 1번 조건에 만족하는 회원에 대해서 마일리지 값을 1000점 부여하기
• 방법 1 - Exists 사용

UPDATE member SET mem_mileage = 1000
WHERE EXISTS (
			SELECT SUM(cart_qty*prod_sale)
				FROM cart, prod
					WHERE cart_member = mem_id
						AND cart_prod = prod_id
						AND left(cart_no,4) = '2005'
					HAVING SUM(cart_qty*prod_sale) >= 30000000);

• 방법 2 - Inline View 사용

UPDATE member SET mem_mileage = (mem_mileage + 1000)
	WHERE mem_id IN(
		SELECT A.mem_id
		FROM (
			SELECT mem_id, mem_name, mem_mileage, SUM(nvl(cart_qty*prod_sale,0)) AS total
			FROM member 
				LEFT JOIN cart
					ON ( mem_id = cart_member AND left(cart_no,4) = '2005')
				LEFT JOIN prod
					ON(cart_prod = prod_id)
			GROUP BY mem_id) A
	WHERE A.total >= 30000000);

문제

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

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

결과

📝문제

✏️작성한 코드

 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;