[23주차_SQL] SQL 풀이 모음

[jewan100]

고다혜:
FrontEnd 개발자 찾기

배수빈:
물고기 종류 별 대어 찾기

백제완:
조건에 맞는 개발자 찾기

손지민:
오프라인/온라인 판매 데이터 통합하기

이지영:
그룹별 조건에 맞는 식당 목록 출력하기

## 문제: [문제명](링크)


### 설명


### 코드


### 아이디어

[KodaHye]

문제: FrontEnd 개발자 찾기

설명

• SKILLCODES, DEVELOPER 테이블이 있을 때, FrontEnd 개발자 출력하기

코드

SELECT DISTINCT(ID), EMAIL, FIRST_NAME, LAST_NAME
FROM DEVELOPERS a, (SELECT * FROM SKILLCODES WHERE CATEGORY = "Front End") b
WHERE a.SKILL_CODE & b.CODE > 0
ORDER BY ID

아이디어

• 개발자 테이블과, 프론트엔드 기술 테이블을 크로스 조인
• 개발자 기술 코드와 프론트엔드 기술 코드를 & 연산한 후 결과 값이 0보다 크다면 프론트엔드 개발자

[yeongleej]

문제: 그룹별 조건에 맞는 식당 목록 출력하기

설명

• MEMBER_PROFILE : 사람 정보 테이블
• REST_REVIEW : 리뷰 정보 테이블
• 가장 많이 리뷰를 작성한 사람의 정보 출력

코드

WITH BEST_REVIEWER AS (
    SELECT MEMBER_ID
    FROM REST_REVIEW
    GROUP BY MEMBER_ID
    ORDER BY COUNT(MEMBER_ID) DESC
    LIMIT 1
)

SELECT M.MEMBER_NAME, R.REVIEW_TEXT
    , DATE_FORMAT(REVIEW_DATE, "%Y-%m-%d") AS REVIEW_DATE
FROM MEMBER_PROFILE M
JOIN REST_REVIEW R
ON M.MEMBER_ID = R.MEMBER_ID
WHERE M.MEMBER_ID = (SELECT * FROM BEST_REVIEWER)
ORDER BY R.REVIEW_DATE, R.REVIEW_TEXT

아이디어

• 가장 많이 리뷰한 사람의 MEMBER_ID를 출력하기 -> 가상테이블 이용 ( or 서브쿼리 이용)
• 사람 테이블과 리뷰 테이블을 조인해서 가장 많이 리뷰한 사람의 정보만 출력하기
  • 한 사람이기 때문에 = 연산 사용
  • 만약 여러 사람인 경우, IN연산자 사용하기

[baexxbin]

문제: 물고기 종류 별 대어 찾기

설명

• 물고기 종류 별로 가장 큰 물고기의 ID, 물고기 이름, 길이를 출력하는 SQL 문을 작성
• FISH_INFO : 잡은 물고기 정보
• FISH_NAME_INFO: 잡은 물고기 이름 정보

코드

-- WHERE절 사용 코드
SELECT I.ID, N.FISH_NAME, I.LENGTH
FROM FISH_INFO I 
JOIN FISH_NAME_INFO N ON I.FISH_TYPE = N.FISH_TYPE
WHERE (I.FISH_TYPE, I.LENGTH) IN (
    SELECT FISH_TYPE, MAX(LENGTH)
    FROM FISH_INFO
    GROUP BY FISH_TYPE
) AND I.FISH_TYPE = N.FISH_TYPE
ORDER BY I.ID;

-- DENSE_RANK()와 OVER 이용
WITH MAX_RANK AS (
    SELECT ID, FISH_TYPE, LENGTH, 
    DENSE_RANK() OVER(PARTITION BY FISH_TYPE ORDER BY LENGTH DESC) AS RANK_L
    FROM FISH_INFO
)
SELECT ID, FISH_NAME, LENGTH
FROM MAX_RANK
LEFT JOIN FISH_NAME_INFO USING(FISH_TYPE)
WHERE RANK_L=1
ORDER BY ID;

아이디어

• GROUP BY로 그룹을 지은 후, 그룹내 최대값을 뽑기 위해선 따로 처리 필요
• WHERE절 이용
  • (I.FISH_TYPE, I.LENGTH)에 대한 부분을 서브쿼리로 가져옴
• DENSE_RANK()와 OVER 이용

  다른 사람 코드인데 좋아보여서 가져왔습니다.

  • DENSE_RANK : 같은 값 동일 순서 부여
  • OVER함수를 통해 그룹을 나눈 후(윈도우 적용 범위 지정), 1등에 대한 값을 가져옴
  • USING()
    • ON대신 조인을 간결하게 함
    • ON은 조인을 진행한 기준 컬럼이 테이블 별로 2개가 유지되나 USING은 하나로 합쳐짐

[jmxx219]

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

설명

• ONLINE_SALE 테이블과 OFFLINE_SALE 테이블에서 2022년 3월의 오프라인/온라인 상품 판매 데이터의 판매 날짜, 상품ID, 유저ID, 판매량을 출력
• ONLINE_SALE(SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT)
• OFFLINE_SALE(SALES_DATE, PRODUCT_ID, SALES_AMOUNT)

코드

SELECT DATE_Format(SALES_DATE, '%Y-%m-%d') as SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE
WHERE YEAR(SALES_DATE) = 2022 and MONTH(SALES_DATE) = 3

UNION

SELECT DATE_Format(SALES_DATE, '%Y-%m-%d') as SALES_DATE, PRODUCT_ID, NULL as USER_ID, SALES_AMOUNT
FROM OFFLINE_SALE
WHERE YEAR(SALES_DATE) = 2022 and MONTH(SALES_DATE) = 3
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID

아이디어

• 단순 UNION 문제
• OFFLINE_SALE 테이블의 경우, USER_ID가 없기 때문에 NULL로 출력한다.

[jewan100]

문제: 조건에 맞는 개발자 찾기

설명

• 스킬의 코드는 2진수로 표현했을 때 각 bit로 구분될 수 있도록 2의 제곱수로 구성되어 있음
• DEVELOPERS 테이블에서 Python이나 C# 스킬을 가진 개발자의 정보를 조회
• 조건에 맞는 개발자의 ID, 이메일, 이름, 성을 조회하는 SQL 문을 작성
• ID를 기준으로 오름차순 정렬

코드

SELECT
    ID,
    EMAIL,
    FIRST_NAME,
    LAST_NAME
FROM
    DEVELOPERS
WHERE
    (SKILL_CODE & (SELECT CODE FROM SKILLCODES WHERE NAME = 'Python'))
    OR (SKILL_CODE & (SELECT CODE FROM SKILLCODES WHERE NAME = 'C#'))
ORDER BY
    ID;

아이디어

• 비트연산 사용
  • 내가 원하는 스킬의 비트와 &연산 했을 때 0이 아니면 값이 해당 스킬을 가지고 있다고 판단