[2주차_SQL] SQL 풀이 모음

baexxbin commented 2 months ago

SQL 문제 링크 첨부하기

baexxbin commented 2 months ago

문제: 조건에 맞는 사용자 정보 조회하기

설명: 게시글 3건 이상 등록한 사용자, id기준 내림차순으로 출력

코드

SELECT 
  u.USER_ID, u.NICKNAME,
  CONCAT(u.CITY, ' ', u.STREET_ADDRESS1, ' ', u.STREET_ADDRESS2) AS 전체주소,
  CONCAT_WS('-', SUBSTR(u.TLNO, 1,3), SUBSTR(u.TLNO, 4,4), SUBSTR(u.TLNO,8,4)) AS 전화번호
FROM
  USED_GOODS_BOARD b
JOIN USED_GOODS_USER u ON b.WRITER_ID = u.USER_ID
GROUP BY b.WRITER_ID
HAVING COUNT(b.WRITER_ID) >= 3
ORDER BY u.USER_ID DESC;

문자열 합치기
- CONCAT : 컬럼 문자열 합치기 / 구분자 필요시 ' ' 공백 추가하기
- CONCAT_WS : 구분자를 기준으로 문자열 합치기 / (구분자, 합칠 문자열)순으로 적기
문자열 자르기
- SUBSTR : (대상 문자열, 시작인덱스(1부터 시작), 갯수)
GROUP BY, HAVING을 통해 3건이상 등록한 사용자 뽑기

icegosimperson commented 2 months ago

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

설명 진료과가 CS 이거나 GS인 의사 이름, 의사 ID, 진료과, 고용일자 조회 고용일자를 기준으로 내림차순 정렬
코드

SELECT DR_NAME, DR_ID, MCDP_CD, DATE_FORMAT(HIRE_YMD, '%Y-%m-%d') AS HIRE_DATE
FROM DOCTOR
WHERE MCDP_CD = 'CS' OR MCDP_CD = 'GS'
ORDER BY HIRE_YMD DESC;

DATE TIME DATETIME 타입 : YYYY-MM-DD hh:mm:ss의 형식을 가짐 SQL에서 yyyy-mm-dd 출력 시 DATE_FORMAT(날짜행, '%Y-%m-%d') AS 날짜행으로 적기

Jewan1120 commented 2 months ago

문제: 업그레이드 할 수 없는 아이템 구하기

설명

더 이상 업그레이드할 수 없는 아이템의 아이템 ID(ITEM_ID), 아이템 명(ITEM_NAME), 아이템의 희귀도(RARITY)를 출력(아이템 ID를 기준으로 내림차순 정렬)

코드

SELECT
    A.ITEM_ID,
    ITEM_NAME,
    RARITY
FROM
    ITEM_INFO A
    LEFT JOIN ITEM_TREE B
        ON A.ITEM_ID = B.PARENT_ITEM_ID
WHERE
    PARENT_ITEM_ID IS NULL
ORDER BY
    A.ITEM_ID DESC

아이디어

더 이상 업그레이드할 수 없는 아이템은 PARENT_ITEM_ID가 없는 레코드를 의미
결합을 할 때 LEFT JOIN을 이용해서 PARENT_ITEM_ID가 NULL인 것을 출력

yeahdy commented 2 months ago

문제: 서울에 위치한 식당 목록 출력하기

설명 서울에 위치한 식당들의 식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수를 조회 리뷰 평균점수는 소수점 세 번째 자리에서 반올림 평균점수를 기준으로 내림차순 정렬 평균점수가 같다면 즐겨찾기수를 기준으로 내림차순 정렬

코드

SELECT
ri.REST_ID, ri.REST_NAME, ri.FOOD_TYPE, ri.FAVORITES, ri.ADDRESS
,ROUND(AVG(rr.REVIEW_SCORE),2) AS SCORE
FROM REST_INFO ri
INNER JOIN REST_REVIEW rr
ON ri.REST_ID = rr.REST_ID
WHERE ri.ADDRESS LIKE '서울%'
GROUP BY ri.REST_ID, ri.REST_NAME, ri.FOOD_TYPE, ri.FAVORITES, ri.ADDRESS
ORDER BY SCORE DESC, ri.FAVORITES DESC;

주소(ri.ADDRESS)를 LIKE 로 검색 시 테스트케이스를 여러 시나리오로 생각하기 (ex. 서울에 위치 하지 않지만 서울이라는 키워드가 들어간 건물이름)

평균 구하는 2가지 방법
- AVG() : null 을 제외하고 평균을 계산
```
SELECT AVG(컬럼) FROM 테이블;
```
- *`SUM() / COUNT()` : null도 포함하고 평균을 계산**
```
SELECT SUM(컬럼)/COUNT(*) FROM 테이블;
```

Jewan1120 commented 2 months ago

문제: 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기

설명

USED_GOODS_BOARD와 USED_GOODS_FILE 테이블에서 조회수가 가장 높은 중고거래 게시물에 대한 첨부파일 경로를 조회하는 SQL문을 작성
첨부파일 경로는 FILE ID를 기준으로 내림차순 정렬
기본적인 파일경로는 /home/grep/src/ 이며, 게시글 ID를 기준으로 디렉토리가 구분되고, 파일이름은 파일 ID, 파일 이름, 파일 확장자로 구성되도록 출력
코드

WITH CTE AS (
    SELECT
        BOARD_ID
    FROM
        USED_GOODS_BOARD
    ORDER BY
        VIEWS DESC
    LIMIT 1
)

SELECT
    CONCAT('/home/grep/src/', BOARD_ID, '/', FILE_ID, FILE_NAME, FILE_EXT) AS FILE_PATH
FROM 
    USED_GOODS_FILE
WHERE
    BOARD_ID = (
        SELECT
            BOARD_ID
        FROM
            CTE
    )
ORDER BY
    FILE_ID DESC

아이디어

CTE를 이용해서 조회수가 최대인 레코드만 남김
CONCAT을 이용해 문자열 결합

yeahdy commented 2 months ago

문제: 오프라인/온라인 판매 데이터 통합하기

설명 2022년 3월의 오프라인/온라인 상품 판매 데이터의 판매 날짜, 상품ID, 유저ID, 판매량을 출력 OFFLINE_SALE 테이블의 판매 데이터의 USER_ID 값은 NULL 로 표시 판매일 오름차순 정렬, 상품 ID 오름차순, 유저 ID 오름차순 정렬

코드

SELECT
DATE_FORMAT(SALES_DATE,'%Y-%m-%d') SALES_DATE,
PRODUCT_ID,
USER_ID, 
SALES_AMOUNT
FROM ONLINE_SALE
WHERE SALES_DATE LIKE '%2022-03%'
GROUP BY USER_ID, PRODUCT_ID, SALES_DATE
UNION ALL
SELECT
DATE_FORMAT(SALES_DATE,'%Y-%m-%d') SALES_DATE,
PRODUCT_ID,
NULL,
SALES_AMOUNT
FROM OFFLINE_SALE
WHERE SALES_DATE LIKE '%2022-03%'
GROUP BY PRODUCT_ID, SALES_DATE
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID;

서로 다른 테이블 병합하기
- UNION : 중복 제거
- UNION ALL: 중복 포함
- UNION 키워드를 사용 하기 위한 조건
- 각 쿼리의 SELECT 절 칼럼의 개수 및 데이터 타입 일치할것
- 대응하는 칼럼명이 같을 것 (별칭 사용가능)
- ORDER BY는 절의 마지막에 1번만 사용가능

baexxbin commented 2 months ago

문제: 헤비 유저가 소유한 장소

설명
- 공간을 둘 이상 등록한 사람(헤비유저)일때, 테이블의 정보 출력

코드

  SELECT ID, NAME, HOST_ID
  FROM PLACES
  WHERE HOST_ID IN (
    SELECT HOST_ID
    FROM PLACES
    GROUP BY HOST_ID
    HAVING COUNT(HOST_ID) >= 2
  )
  ORDER BY ID;

아이디어
- 헤비유저에 해당하는 모든 값을 출력해야함. (단순 GROUP BY로는 안됨)
- 서브쿼리 이용: 헤비 유저에 해당하는 조건을 WHERE안에 서브쿼리로 넣어줌

icegosimperson commented 2 months ago

문제: 자동차 평균 대여 기간 구하기

설명 평균 대여 기간이 7일 이상인 자동차들의 자동차 ID와 평균 대여 기간(AVERAGE_DURATION) 리스트를 출력
평균 대여 기간은 소수점 두번째 자리에서 반올림
결과는 평균 대여 기간을 기준으로 내림차순 정렬, 평균 대여 기간이 같으면 자동차 ID를 기준 내림차순 정렬

코드

SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE, START_DATE)+1), 1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID 
HAVING AVERAGE_DURATION >=7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC;

아이디어 소수점 두번째 자리에서 반올림 -> ROUND(x, 1) DATEDIFF() 사용 AVG(END_DATE - START_DATE) x START DATE : 2024-09-19 ~ END DATE : 2024-09-19이여도 대여 기간 1일 이므로, 평균 대여 기간 계산 시 +1 ORDER BY 평균 대여 기간(AVERAGE_DURATION )과 자동차 ID(CAR_ID) 두개 사용

yeongleej commented 2 months ago

[SQL] 특정 물고기를 잡은 총 수 구하기

문제 설명
- 물고기 정보(FISH_INFO) 테이블과 물고기 이름 정보(FISH_NAME_INFO)에서
- 물고기 이름이 "BASS" 와 "SNAPPER"인 물고기의 개수 출력

코드

SELECT COUNT(*) AS FISH_COUNT
FROM FISH_INFO I
JOIN FISH_NAME_INFO F
ON I.FISH_TYPE = F.FISH_TYPE
WHERE FISH_NAME = "BASS" OR FISH_NAME = "SNAPPER"

아이디어
- JOIN을 활용: FISH_INFO와 FISH_NAME_INFO의 FISH_TYPE 컬럼을 기준으로 JOIN
- WHERE 절에 조건 적용하기: 물고기 이름 필터링

yeongleej commented 2 months ago

[SQL] 물고기 종류별 대어 찾기

문제 설명
- 물고기 정보(FISH_INFO) 테이블과 물고기 이름 정보(FISH_NAME_INFO)에서
- 물고기 종류(FISH_TYPE)별 가장 큰 길이를 가진 물고기 출력

코드

SELECT ID, FISH_NAME, LENGTH
FROM FISH_INFO I
JOIN FISH_NAME_INFO N
ON I.FISH_TYPE = N.FISH_TYPE
WHERE (I.FISH_TYPE, LENGTH) IN(
            SELECT FISH_TYPE, MAX(LENGTH)
            FROM FISH_INFO
            GROUP BY FISH_TYPE)
ORDER BY ID

-아이디어

JOIN을 활용: FISH_INFO와 FISH_NAME_INFO의 FISH_TYPE 컬럼을 기준으로 JOIN
서브쿼리를 이용, IN 연산자 이용
- 서브쿼리의 값인 (물고기 타입, 최대 길이)와 일치하는 물고기를 select 해야 함
- *서브쿼리로 구한 최대길이의 값과 같은 조건만 제시하면, 물고기 타입이 달라도 출력되기 때문에 (물고기 타입, 최대 길이)를 하나의 컬럼처럼 묶어서 사용

KodaHye commented 2 months ago

문제: ROOT 아이템 구하기

설명: PARENTS 아이템이 없는 ROOT 아이템 구하기
코드

SELECT A.ITEM_ID, B.ITEM_NAME
FROM ITEM_TREE A LEFT JOIN ITEM_INFO B
ON A.ITEM_ID = B.ITEM_ID
WHERE A.PARENT_ITEM_ID IS NULL

ITEM_INFO테이블과 ITEM_TREE 테이블을 join 한 뒤, ITEM.PARENT_ID가 null 인 값 찾기

KodaHye commented 2 months ago

문제: 조건에 맞는 사원 정보 조회하기

설명: HR_DEPARTMENT, HR_EMPLOYEES, HR_GRADE 테이블이 있을 때, 평가 점수가 가장 높은 사원의 정보 출력
코드

SELECT SUM(SCORE) AS SCORE, A.EMP_NO, A.EMP_NAME, A.POSITION, A.EMAIL
FROM HR_EMPLOYEES A 
    LEFT JOIN HR_DEPARTMENT B ON A.DEPT_ID = B.DEPT_ID
    LEFT JOIN HR_GRADE C ON A.EMP_NO = C.EMP_NO
GROUP BY A.EMP_NO
ORDER BY SCORE DESC
LIMIT 1

HR_EMPLOYEES에서 DEPT_ID로 HR_DEPARTMENT, HR_GRADE 테이블 조인
GROUP BY를 통해 사원의 평가 점수 합계를 구할 수 있도록 함
ORDER BY와 LIMIT 1을 통해 평가 점수가 가장 높은 사원의 정보 출력

GreatAlgorithm-Study / AlgorithmStudy