ep5-1. Database 3

Tessssssssy·2023년 11월 9일
@백엔드 부트캠프mysqlsql백엔드코딩테스트코테프로그래머스프로그래머스 MYSQL프로그래머스 문제풀이한화시스템 부트캠프
0

한화시스템 BEYOND SW 백엔드 부트캠프 series

목록 보기
7/16

프로그래머스 문제 풀이

MYSQL LEVEL 1

  • 중고 거래
    https://school.programmers.co.kr/learn/courses/30/lessons/164673 

    // ver 1
SELECT board.TITLE, board.BOARD_ID, reply.REPLY_ID, reply.WRITER_ID,
       reply.CONTENTS, DATE_FORMAT(reply.CREATED_DATE, "%Y-%m-%d") AS CREATED_DATE
FROM USED_GOODS_BOARD AS board
INNER JOIN USERD_GOODS_REPLY AS reply
ON board.BOARD_ID=reply.BOARD_ID
WHERE MONTH(board.CREATED_DATE)=10
ORDER BY reply.CREATED_DATE ASC, board.TITLE ASC;
    // ver 2
SELECT board.TITLE, board.BOARD_ID, reply.REPLY_ID, reply.WRITER_ID,
       reply.CONTENTS, DATE_FORMAT(reply.CREATED_DATE, "%Y-%m-%d") AS CREATED_DATE
FROM USED_GOODS_BOARD AS board
INNER JOIN USERD_GOODS_BOARD AS board
WHERE board.CREATED_DATE LIKE '2022-10%'
ORDER BY reply.CREATED_DATE ASC, board.TITLE ASC;
    // my ver
SELECT BOARD.TITLE, BOARD.BOARD_ID, REPLY.REPLY_ID, REPLY.WRITER_ID,
       REPLY.CONTENTS, DATE_FORMAT(REPLY.CREATED_DATE, '%Y-%m-%d') AS CREATED_DATE
FROM USED_GOODS_BOARD AS BOARD
JOIN USED_GOODS_REPLY AS REPLY ON BOARD.BOARD_ID=REPLY.BOARD_ID
WHERE DATE_FORMAT(BOARD.CREATED_DATE, '%Y-%m') = '2022-10'
ORDER BY REPLY.CREATED_DATE ASC, BOARD.TITLE ASC;

  • 여자 환자 목록
    https://school.programmers.co.kr/learn/courses/30/lessons/132201 

    SELECT PT_NAME, PT_NO, GEND_CD, AGE, IF(TLNO IS NULL, 'NONE', TLNO) AS TLNO
FROM PATIENT
WHERE (AGE <= 12) AND (GEND_CD = 'W')
ORDER BY AGE DESC, PT_NAME ASC


MYSQL LEVEL 2

  • 중고거래 조건 부합
    https://school.programmers.co.kr/learn/courses/30/lessons/164672 

    SELECT BOARD_ID, WRITER_ID, TITLE, PRICE,
	CASE STATUS
 	WHEN 'SALE' THEN '판매중'
    WHEN 'RESERVED' THEN '예약중'
    WHEN 'DONE' THEN '거래완료'
    END AS STATUS
FROM USED_GOODS_BOARD
WHERE DATE_FORMAT(CREATED_DATE, '%Y-%m-%d') = '2022-10-05'
ORDER BY BOARD_ID DESC;

  • 가격대 별 상품 개수
    https://school.programmers.co.kr/learn/courses/30/lessons/131530 

    SELECT
	CASE
   	WHEN PRICE >=0 AND PRICE < 10000 THEN 0
       WHEN PRICE >=10000 AND PRICE < 20000 THEN 10000
       WHEN PRICE >=20000 AND PRICE < 30000 THEN 20000
       WHEN PRICE >=30000 AND PRICE < 40000 THEN 30000
       WHEN PRICE >=40000 AND PRICE < 50000 THEN 40000
       WHEN PRICE >=50000 AND PRICE < 60000 THEN 50000
       WHEN PRICE >=60000 AND PRICE < 70000 THEN 60000
       WHEN PRICE >=70000 AND PRICE < 80000 THEN 70000
       WHEN PRICE >=80000 AND PRICE < 90000 THEN 80000
       END AS PRICE_GROUP,
       COUNT(PRODUCT_ID) AS PRODUCTS
 FROM PRODUCT
 GROUP BY PRICE_GROUP
 ORDER BY PRICE_GROUP;

  • 재구매 회원
    https://school.programmers.co.kr/learn/courses/30/lessons/131536 

    SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID

	// USER_ID와 PRODUCT_ID 열을 기준으로 그룹화하여 
	// 동일한 USER_ID 및 PRODUCT_ID를 가진 레코드를 하나의 그룹으로 묶다.
   
HAVING COUNT(*) > 1

	// 이 부분은 그룹화된 결과에 필터를 적용합니다. 
	// COUNT(*)는 각 그룹에 속한 레코드 수를 나타내며, 
	// 이 부분은 레코드 수가 1보다 큰 그룹만 선택합니다. 
	// 즉, 중복된 조합을 가진 그룹만 선택됩니다.

ORDER BY USER_ID ASC, PRODUCT_ID DESC;

 /*
 ONLINE_SALE 테이블에서 중복된 USER_ID와 PRODUCT_ID 조합을 찾아서 해당 조합을 그룹화하고, 
 중복된 조합을 가진 그룹만 선택한 후, 
 사용자 ID는 오름차순으로 정렬하고 제품 ID는 내림차순으로 정렬하여 결과를 반환한다.
 */


MYSQL LEVEL 3

  • 대여 횟수가 많은 자동차들의 월별 대수
    https://school.programmers.co.kr/learn/courses/30/lessons/151139 

    // 1. 8-10월까지 총 대여 횟수가 5회 이상인 자동차들에서 (서브 쿼리로 처리)

 SELECT CAR_ID, COUNT(CAR_ID)
 FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
 WHERE START_DATE >= '2022-08-01' AND START_DATE < '2022-11-01'
 GROUP BY CAR_ID
 HAVING COUNT(CAR_ID) >= 5;

// 2. 위 결과에 대해 월별 자동차 ID 별 총 대여 횟수
// 3. 서브 쿼리에서 CAR_ID만 뽑은 거니까 밖에서 한번 더 날짜 filtering해야 함.

 SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(CAR_ID) AS RECORDS
 FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
 WHERE START_DATE >= '2022-08-01' AND START_DATE < '2022-11-01'
       AND CAR_ID IN 
	        (
	            SELECT CAR_ID
	            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
	            WHERE START_DATE >= '2022-08-01' AND START_DATE < '2022-11-01'
	            GROUP BY CAR_ID
	            HAVING COUNT(CAR_ID) >= 5
	        )
 GROUP BY CAR_ID, MONTH
 HAVING RECORDS >= 1
 ORDER BY MONTH ASC, CAR_ID DESC;

  • 조회수가 가장 많은 중고 거래 게시물
    https://school.programmers.co.kr/learn/courses/30/lessons/164671

    SELECT CONCAT('/home/grep/src/', board.BOARD_ID, '/', file.FILE_ID,
       file.FILE_NAME, file.FILE_EXT) AS FILE_PATH
FROM USED_GOODS_BOARD AS board
LEFT JOIN USED_GOODS_FILE AS file ON board.BOARD_ID = file.BOARD_ID 
WHERE VIEWS = (SELECT VIEWS
               FROM USED_GOODS_BOARD
               ORDER BY VIEWS DESC 
               LIMIT 1)
ORDER BY FILE_ID DESC;

  • 있었는데요 없었습니다
    https://school.programmers.co.kr/learn/courses/30/lessons/59043 

    SELECT INS.ANIMAL_ID, INS.NAME
FROM ANIMAL_INS AS INS
LEFT OUTER JOIN ANIMAL_OUTS AS OUTS ON INS.ANIMAL_ID=OUTS.ANIMAL_ID
WHERE INS.DATETIME > OUTS.DATETIME
ORDER BY INS.DATETIME;


MYSQL LEVEL 4

  • 보호소 중성화 동물
    https://school.programmers.co.kr/learn/courses/30/lessons/59045

    SELECT INS.ANIMAL_ID, INS.ANIMAL_TYPE, INS.NAME
FROM ANIMAL_INS AS INS
LEFT JOIN ANIMAL_OUTS AS OUTS ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE (INS.SEX_UPON_INTAKE LIKE 'Intact%') 
      AND (OUTS.SEX_UPON_OUTCOME LIKE 'Spayed%' or OUTS.SEX_UPON_OUTCOME LIKE 'Neutered%')
ORDER BY INS.ANIMAL_ID;

  • 우유와 요거트 장바구니
    https://school.programmers.co.kr/learn/courses/30/lessons/62284 

    SELECT CART_ID
FROM CART_PRODUCTS
WHERE NAME IN ('Yogurt','Milk')
GROUP BY CART_ID
HAVING COUNT(DISTINCT NAME) >= 2
ORDER BY CART_ID ASC;

  • 식품분류별 가장 비싼 식품의 정보
    https://school.programmers.co.kr/learn/courses/30/lessons/131116

    SELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE (CATEGORY, PRICE) IN (
                        SELECT CATEGORY, MAX(PRICE) 
                        FROM FOOD_PRODUCT
                        GROUP BY CATEGORY
                        HAVING CATEGORY IN ('국', '과자', '김치', '식용유')
                        )
ORDER BY MAX_PRICE DESC;

  • 입양 시각 구하기(2)
    https://school.programmers.co.kr/learn/courses/30/lessons/59413

    SET @hour := -1;

SELECT (@hour := @hour + 1) as HOUR,
       (SELECT COUNT(*) FROM ANIMAL_OUTS AS OUTS 
        WHERE HOUR(DATETIME) = @hour) as COUNT
FROM ANIMAL_OUTS AS OUTS
WHERE @hour < 23

  • 자동차 대여 기록 별 대여 금액 구하기
    https://school.programmers.co.kr/learn/courses/30/lessons/151141

    SELECT  RES.HISTORY_ID, 
       ROUND(IF(RES.DISCOUNT_RATE IS NULL, RES.DAILY_FEE * RES.DATE_DIFF, 
                RES.DAILY_FEE * RES.DATE_DIFF * (100-RES.DISCOUNT_RATE)*0.01),0) AS FEE
FROM  
(
       SELECT  CAR.*
               , HISTORY.HISTORY_ID
               , DATEDIFF(HISTORY.END_DATE, HISTORY.START_DATE) + 1 AS DATE_DIFF
               , DISCOUNT.DISCOUNT_RATE
         FROM  CAR_RENTAL_COMPANY_CAR CAR
         LEFT
         JOIN  CAR_RENTAL_COMPANY_RENTAL_HISTORY HISTORY
           ON  CAR.CAR_ID = HISTORY.CAR_ID
         LEFT
         JOIN  CAR_RENTAL_COMPANY_DISCOUNT_PLAN DISCOUNT
           ON  CASE WHEN DATEDIFF(HISTORY.END_DATE, HISTORY.START_DATE) BETWEEN 7 AND 29 
                           THEN DISCOUNT.PLAN_ID = 10
                    WHEN DATEDIFF(HISTORY.END_DATE, HISTORY.START_DATE) BETWEEN 30 AND 89 
                           THEN DISCOUNT.PLAN_ID = 11
                    WHEN DATEDIFF(HISTORY.END_DATE, HISTORY.START_DATE) >= 90 
                           THEN DISCOUNT.PLAN_ID = 12 END
        WHERE  CAR.CAR_TYPE = '트럭'
) RES 
ORDER BY FEE DESC, HISTORY_ID DESC;

  • 5월 식품들의 총 매출 조회
    https://school.programmers.co.kr/learn/courses/30/lessons/131117

    // my version
SELECT PRD.PRODUCT_ID, PRD.PRODUCT_NAME, (PRD.PRICE * ORD.AMOUNT) AS TOTAL_SALES
FROM FOOD_PRODUCT AS PRD
JOIN FOOD_ORDER as ORD ON PRD.PRODUCT_ID = ORD.PRODUCT_ID
WHERE DATE_FORMAT(ORD.PRODUCE_DATE, '%Y-%m') = '2022-05'
ORDER BY TOTAL_SALES DESC, PRD.PRODUCT_ID ASC;

// answer
SELECT PRD.PRODUCT_ID, PRD.PRODUCT_NAME, SUM(PRD.PRICE * ORD.AMOUNT) AS TOTAL_SALES
FROM FOOD_PRODUCT AS PRD
JOIN FOOD_ORDER as ORD ON PRD.PRODUCT_ID = ORD.PRODUCT_ID
WHERE DATE_FORMAT(ORD.PRODUCE_DATE, '%Y-%m') = '2022-05'
GROUP BY PRD.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, PRD.PRODUCT_ID ASC;

  • 주문량이 많은 아이스크림 조회
    https://school.programmers.co.kr/learn/courses/30/lessons/133027

    SELECT *
FROM FIRST_HALF;

    SELECT *
FROM FIRST_HALF
JOIN JULY ON FIRST_HALF.FLAVOR=JULY.FLAVOR;

// 정렬이 되어 있지 않다는 것 확인.
// Flavor, SHIPMENT_ID 2개의 KEY가 존재하지만
// 우리는 Flavor에 관심이 있기 때문에 Flavor로 key 선택 및 테이블 join.

    SELECT *
FROM FIRST_HALF
LEFT JOIN JULY ON FIRST_HALF.FLAVOR=JULY.FLAVOR
GROUP BY FIRST_HALF.FLAVOR;

// 같은 맛의 아이스크림이라도 다른 출하 번호를 갖게 되는 경우  有
// => 같은 맛이더라도 다른 record들이 존재할 수 있음.
// + 주문량이 많은 상위 3개의 맛 조회해야 함.
// => Flavor로 Group지어서 같은 맛은 함께 계산되도록.

    SELECT *
FROM FIRST_HALF
LEFT JOIN JULY ON FIRST_HALF.FLAVOR=JULY.FLAVOR
GROUP BY FIRST_HALF.FLAVOR
ORDER BY (FIRST_HALF.TOTAL_ORDER + JULY.TOTAL_ORDER) DESC
LIMIT 3;

// 7월 아이스크림 총 주문량과 상반기의 아이스크림 총 주문량을 더한 값이 
// 큰 순서대로 상위 3개의 맛을 조회
// 위 두 문장을 토대로 주문량 합을 큰 순으로 정렬
    // 주문량이 많은 .....
// https://school.programmers.co.kr/learn/courses/30/lessons/133027

SELECT FIRST_HALF.FLAVOR
// FLAVOR만 반
FROM FIRST_HALF
LEFT JOIN JULY ON FIRST_HALF.FLAVOR=JULY.FLAVOR
GROUP BY FIRST_HALF.FLAVOR
// 같은 맛이더라도 여러 개의 레코드들이 있기 때문에 맛 별 주문의 합을 구하기 위해
// GROUP BY FLAVOR로 동일한 FLAVOR를 갖는 레코드끼리 묶는다.
ORDER BY (SUM(FIRST_HALF.TOTAL_ORDER) + SUM(JULY.TOTAL_ORDER)) DESC
// FIRST_HALF 테이블과 JULY 테이블에서 모든 그룹의 TOTAL_ORDER 값을 합산한 후 
// 그 합계를 기준으로 정렬하는 것
LIMIT 3;
// 상위 3개 레코드로 제한

/*
이 쿼리는 FIRST_HALF와 JULY 테이블의 조인 결과를 FLAVOR를 기준으로 그룹화하고, 
각 그룹에서 TOTAL_ORDER의 합계를 계산한 후, 
이 합계를 기준으로 내림차순으로 정렬한 다음 상위 3개 그룹만 선택하는 쿼리입니다. 
결과는 가장 많은 주문을 받은 상위 3개 FLAVOR에 해당하는 레코드가 반환됨.
*/

     // 굳이 안써도 되는 HAVING 사용하는 방법 

SELECT FIRST_HALF.FLAVOR
FROM FIRST_HALF
LEFT JOIN JULY ON FIRST_HALF.FLAVOR=JULY.FLAVOR
GROUP BY FIRST_HALF.FLAVOR
HAVING SUM(FIRST_HALF.TOTAL_ORDER) + SUM(JULY.TOTAL_ORDER)
ORDER BY SUM(FIRST_HALF.TOTAL_ORDER) + SUM(JULY.TOTAL_ORDER) DESC
LIMIT 3;


향후 프로젝트

  • DB, 소공 끝나고 프로젝트 1회 (DB 설계) - 2일
  • 백엔드 코딩 배우고 프로젝트 - 3일
  • 프론트엔드 끝나고 프로젝트 - 2일
  • DevOps 끝나고 프로젝트 - 2일
  • 최종 프로젝트


해야할 일

  • 웹 서버 해결
    • 포트포워딩
    • 외부 IP
    • DB 서버 포트포워딩
  • 리눅스 복습
    • 모두의 리눅스 1, 2, 3, 4, 5, 6, 7, 9, 10, 11
  • DB 복습
    • 이것이 MariaDB다 6장 select 연습해보기 7.3 조인

https://sql-factory.tistory.com/2281
https://velog.io/@spy03128/백준-프로그래머스-Github-자동으로-커밋하기

profile
Tessssssssy
Web Developer
이전 포스트

ep5. Database 3

다음 포스트

ep6. Database 4

0개의 댓글