프로그래머스 SQL Lv.3

💡 새로 알게된 것

• concat - 문자열 합치기 select 'HelloWorldTwok' as 원본,
  left("HelloWorldTwok", 5) as "왼쪽에서 5개 출력",
  mid("HelloWorldTwok", 1, 10) as "1부터10개 출력",
  right("HelloWorldTwok", 4) as "오른쪽에서 4개 출력";
  
• MONTH - 월 꺼내기

1. 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기 ⁉️

Link

-- 코드를 입력하세요
SELECT concat('/home','/grep','/src/',b.BOARD_ID,'/',f.FILE_ID,f.FILE_NAME,f.FILE_EXT) as FILE_PATH
from USED_GOODS_BOARD b join USED_GOODS_FILE f on b.BOARD_ID = f.BOARD_ID
where b.views = ( select max(b.views) from USED_GOODS_BOARD b)
order by f.FILE_ID DESC

• 조회수가 가장 높은 게시물은 하나만 존재하는데 왜 두개 출력?
  → 게시물에 파일이 두개
  → where 절에는 논리(logic)가 들어가야 하기 때문에 select 서브쿼리를 써야한다.

  ![스크린샷 2023-03-23 오후 7.53.10.png](https://s3-us-west-2.amazonaws.com/secure.notion-static.com/edbc8d90-b036-461b-bc48-5af11d7705fe/%E1%84%89%E1%85%B3%E1%84%8F%E1%85%B3%E1%84%85%E1%85%B5%E1%86%AB%E1%84%89%E1%85%A3%E1%86%BA_2023-03-23_%E1%84%8B%E1%85%A9%E1%84%92%E1%85%AE_7.53.10.png)

• concat 문자열 합치기

2. 조건에 맞는 사용자 정보 조회하기 (String, Date) 🔺

Link

-- 코드를 입력하세요
SELECT u.user_id, u.nickname,
        concat(u.CITY,' ',u.STREET_ADDRESS1,' ',u.STREET_ADDRESS2) as '전체주소',
        concat(LEFT(u.TLNO,3),'-',MID(u.TLNO,4,4),'-',RIGHT(u.TLNO,4)) as '전화번호'
from USED_GOODS_BOARD b join USED_GOODS_USER u on b.WRITER_ID = u.USER_ID
group by u.USER_ID
having count(*) >= 3
ORDER BY u.USER_ID DESC;

3. 조건에 맞는 사용자와 총 거래금액 조회하기 (Group by)

Link

-- 코드를 입력하세요
SELECT u.USER_ID, u.NICKNAME, sum(b.price) as TOTAL_SALES
from USED_GOODS_BOARD b JOIN USED_GOODS_USER u on b.WRITER_ID = u.USER_ID
where b.status = 'DONE'
group by u.user_id
having TOTAL_SALES >= 700000
order by TOTAL_SALES

4. 대여 기록이 존재하는 자동차 리스트 구하기 (String, Date)

Link

-- 코드를 입력하세요
SELECT distinct(h.CAR_ID)
from CAR_RENTAL_COMPANY_CAR c join CAR_RENTAL_COMPANY_RENTAL_HISTORY h on c.CAR_ID = h.CAR_ID
where c.car_type = '세단' and date_format(h.start_date,'%Y-%m') = '2022-10'
order by c.car_id desc

5. 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분 (Group by) ⭐

Link

-- 코드를 입력하세요
SELECT CAR_ID,
    IF(SUM(IF(START_DATE <= '2022-10-16' AND END_DATE >= '2022-10-16', 1, 0)) > 0, '대여중', '대여 가능') as AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC

6. 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기 (Group by)

Link

-- 코드를 입력하세요
SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE (MONTH(START_DATE) BETWEEN 8 AND 10) 
AND CAR_ID IN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE MONTH(START_DATE) BETWEEN 8 AND 10
    GROUP BY CAR_ID
    HAVING COUNT(*)>=5
)
GROUP BY MONTH(START_DATE), CAR_ID
ORDER BY MONTH(START_DATE), CAR_ID DESC

7. 카테고리 별 도서 판매량 집계하기 (Group by)

Link

-- 코드를 입력하세요
SELECT b.category, sum(s.sales) as TOTAL_SALES from BOOK b join BOOK_SALES s on b.BOOK_ID = s.BOOK_ID
where date_format(s.sales_date,'%Y-%m') = '2022-01'
group by b.category
order by b.category

8. 즐겨찾기가 가장 많은 식당 정보 출력하기 (Group by)

Link

-- 코드를 입력하세요
SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES from REST_INFO
where FOOD_TYPE in (
    select FOOD_TYPE from REST_INFO
    group by FOOD_TYPE
    having FAVORITES = max(FAVORITES))
order by FOOD_TYPE

9. 조건별로 분류하여 주문상태 출력하기 (String, Date)

Link

-- 코드를 입력하세요
SELECT ORDER_ID,PRODUCT_ID,date_format(OUT_DATE,'%Y-%m-%d') as OUT_DATE,
    (case when date_format(OUT_DATE,'%Y-%m-%d') <= '2022-05-01' then '출고완료'
          when out_date is null then '출고미정'
          else '출고대기' end) as 출고여부
from FOOD_ORDER
order by ORDER_ID

10. 헤비 유저가 소유한 장소 (2021 Dev-Matching)

Link

-- 코드를 입력하세요 Join 쓰고
SELECT A.ID, A.NAME, A.HOST_ID
FROM PLACES A
    JOIN (SELECT HOST_ID FROM PLACES GROUP BY HOST_ID HAVING COUNT(HOST_ID) > 1) B
    ON A.HOST_ID = B.HOST_ID
ORDER BY A.ID

-- 코드를 입력하세요 Join 안쓰고
SELECT *
FROM PLACES 
WHERE HOST_ID IN (SELECT HOST_ID
                 FROM PLACES
                 GROUP BY HOST_ID
                 HAVING COUNT(*) > 1)

11. 오랜 기간 보호한 동물(2) (String, Date)

Link

-- 코드를 입력하세요
SELECT i.ANIMAL_ID, i.NAME from ANIMAL_INS i join ANIMAL_OUTS o on i.ANIMAL_ID = o.ANIMAL_ID
order by datediff(i.DATETIME,o.DATETIME)
limit 2

12. 오랜 기간 보호한 동물(1) (Join)

Link

-- 코드를 입력하세요
SELECT a.name, a.datetime from animal_ins a left join animal_outs a2 on a.ANIMAL_ID = a2.ANIMAL_ID
where a2.datetime is null
order by a.datetime
limit 3

13. 있었는데요 없었습니다 (Join)

Link

-- 코드를 입력하세요
select i.animal_id, i.name from animal_ins i inner join animal_outs o on i.animal_id = o.animal_id
where i.datetime >= o.datetime
order by i.datetime

14. 없어진 기록 찾기 (Join)

Link

-- 코드를 입력하세요
select O.ANIMAL_ID, O.NAME from ANIMAL_OUTS O LEFT JOIN ANIMAL_INS I ON O.ANIMAL_ID = I.ANIMAL_ID
WHERE I.ANIMAL_ID IS NULL
ORDER BY O.ANIMAL_ID