[6주차_SQL] SQL 풀이 모음

icegosimperson commented 1 month ago

SQL 유형: JOIN
코멘트 템플릿 하단 텍스트 복사해서 사용 해 주세요💌

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

### 설명

### 코드

### 아이디어

KodaHye commented 1 month ago

문제: 있었는데요 없었습니다

설명

동물의 보호 관련 데이터가 있는 ANIMAL_INS테이블과 입양 데이터에 대한 ANIMAL_OUTS 테이블이 있을 때, 입양일이 보호 시작일보다 빠른 데이터 조회하기

코드

SELECT A.ANIMAL_ID, A.NAME
FROM ANIMAL_INS A LEFT JOIN ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE A.DATETIME > B.DATETIME
ORDER BY A.DATETIME

아이디어

ID로 left join 해주기
Where 절을 통해 입양일이 보호 시작일보다 빠른 데이터 뽑아내기

문제: 오랜 기간 보호한 동물(1)

설명

보호소에 맡겨진 정보(ANIMAL_INS)에 대한 테이블과 입양된 정보(ANIMAL_OUTS)에 대한 테이블이 주어졌을 때, 오랜 기간 보호된 동물 Top3 뽑기

코드

SELECT A.NAME, A.DATETIME
FROM ANIMAL_INS A
WHERE A.ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_OUTS)
ORDER BY A.DATETIME 
LIMIT 3

아이디어

입양된 동물을 제외해주기 위해 WHERE 절에서 NOT IN 사용하였고, 조건대로 출력해주기 위해 ORDER BY와 LIMIT 사용
ANIMAL_INS와 ANIMAL_OUTS를 LEFT JOIN해준 뒤, ANIMAL_OUTS.DATE이 NULL 값인 데이터를 가져오고 조건대로 출력해줘도 됨

icegosimperson commented 1 month ago

문제: 조건에 맞는 도서와 저자 리스트 출력하기

설명

경제 카테고리에 속하는 도서들의 도서 ID(BOOK_ID), 저자명(AUTHOR_NAME), 출판일(PUBLISHED_DATE) 리스트를 출력 정렬 : 출판일 기준 오름차순

코드

SELECT A.BOOK_ID, B.AUTHOR_NAME, DATE_FORMAT(A.PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK A JOIN AUTHOR B ON A.AUTHOR_ID = B.AUTHOR_ID
WHERE CATEGORY = '경제'
ORDER BY A.PUBLISHED_DATE ASC;

아이디어

JOIN 사용한 이유
BOOK 테이블 : AUTHOR_NAME 없음, AUTHOR : BOOK_ID 없음 -> AUTHOR_ID 필드로 연결

baexxbin commented 1 month ago

문제: 570. Managers with at Least 5 Direct Reports

설명

최소 5명의 직속 부하 직원이 있는 관리자를 찾기

코드

SELECT e.name
FROM Employee e
JOIN Employee m ON e.id = m.managerId
GROUP BY m.managerId
HAVING COUNT(m.managerId) >=5;

아이디어

self Join 사용
- 컬럼간의 관계를 이용해 데이터 추출
Employee e: 매니저 나타냄
Employee m: 매니저가 관리하는 직원 나타냄
- JOIN Employee m ON e.id = m.managerId
- 매니저id와 직원의 매니저id가 같을 경우 조인
- 매니저의 ID와 일치하는 직원들 찾기
- GROUP BY로 매니저별 관리하는 직원의 수 찾기

yeahdy commented 1 month ago

문제: 5월 식품들의 총매출 조회하기

설명

생산일자가 2022년 5월인 식품들의 식품 ID, 식품 이름, 총매출을 조회
총매출 내림차순,식품 ID 오름차순

코드

SELECT
    fp.PRODUCT_ID
    ,fp.PRODUCT_NAME
    ,SUM(fp.PRICE*fo.AMOUNT) AS TOTAL_SALES
FROM FOOD_ORDER fo
INNER JOIN FOOD_PRODUCT fp
ON fo.PRODUCT_ID = fp.PRODUCT_ID
WHERE fo.PRODUCE_DATE LIKE '2022-05%'
GROUP BY fp.PRODUCT_ID,fp.PRODUCT_NAME
ORDER BY TOTAL_SALES DESC ,fp.PRODUCT_ID;

아이디어

총매출을 구하기 위해 FOOD_ORDER테이블에서 수량, FOOD_PRODUCT 테이블에서 가격이 필요해서 PRODUCT_ID로 JOIN
년-월-일 별로 PRODUCT_ID가 있기 때문에 GROUP BY 로 집계

Jewan1120 commented 1 month ago

문제: 1280. Students and Examinations

설명

각 시험의 과목 별로 학생이 몇 번 시험을 치르러 왔는지 카운트 하는 문제

코드

SELECT
    A.student_id
    , A.student_name
    , B.subject_name
    , COUNT(C.student_id) attended_exams
FROM
    Students A
    CROSS JOIN Subjects B
    LEFT JOIN Examinations C
        ON A.student_id = C.student_id
        AND B.subject_name = C.subject_name
GROUP BY
    A.student_id
    , A.student_name
    , B.subject_name
ORDER BY
    A.student_id
    , A.student_name
    , B.subject_name

아이디어

전부 JOIN 한 후 COUNT하는 문제
처음엔 INNER JOIN에 조건을 1=1을 넣어서 곱집합 테이블을 만들었는데 CROSS JOIN이라는 간단한 구문이 있음
COUNT를 할 때 주 테이블의 PK값을 넣었었지만 이번 문제는 NULL 값을 제외해야 하므로 다른 칼럼을 기준으로 카운트
```
COUNT(컬럼명)을 사용하면 NULL 값은 제외하고 COUNT 합니다. 
COUNT(*)를 사용하면 NULL도 포함하여 전부 COUNT 합니다.
```

yeongleej commented 1 month ago

문제: 대장균들의 자식의 수 구하기

설명

대장균들의 자식 수 구하기

코드

SELECT P.ID, COUNT(C.ID) AS CHILD_COUNT
FROM ECOLI_DATA P
LEFT JOIN ECOLI_DATA C
ON P.ID = C.PARENT_ID
GROUP BY(P.ID)
ORDER BY P.ID;

아이디어

모든 부모가 나와야 하니깐 외부조인(left join)으로 진행
자식이 null인 것은 카운트하면 안되므로 count(c.id)로 자식의 수 카운트

yeongleej commented 1 month ago

문제: 주문량이 많은 아이스크림들 조회하기

설명

7월 아이스크림 주문량과 상반기 주문량의 합이 가장 많은 상위 3개의 아이스크림 구하기

코드

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

아이디어

아이스크림 종류별로 7월의 주문량, 상반기의 주문량을 합해서 총 주문량을 구함
order by를 통해 총 주문량이 많은 순으로 정렬 (내림차순)
limit를 활용해 상위 3개만 출력

baexxbin commented 1 month ago

문제: 1907. Count Salary Categories

설명

각 급여 범주에 대한 은행 계좌 수를 계산
결과표에는 세 가지 범주가 모두 포함
카테고리에 계정이 없으면 0 반환

+-------------+------+ | Column Name | Type | +-------------+------+ | account_id | int | | income | int | +-------------+------+

코드

  SELECT 'High Salary' AS category,
      SUM(income > 50000) AS accounts_count
  FROM Accounts
  UNION
  SELECT 'Average Salary' AS category,
      SUM(income BETWEEN 20000 AND 50000) AS accounts_count
  FROM Accounts
  UNION
  SELECT 'Low Salary' AS category,
      SUM(income < 20000) AS accounts_count
  FROM Accounts

아이디어

우선, 이번주 주제가 Join이였으나 해당 문제는 UNION으로 풀었음...!

JOIN을 사용해야하는 이유
- 위 문제에 카테고리에 계정이 없으면 0 반환이란 조건 존재
- 0을 포함하려면, 모든 카테고리를 포함한 테이블이 존재해야하고, 이를 위해 CTE같은 걸로 모든 카테고리를 포함한 테이블을 만들어 이를 합쳐줘야함.
  
  그냥 CASE WHEN 조건으로 카테고리 나누고, COUNT로 세면 계정이 없는 카테고리는 빠짐
UNION을 사용한 이유
- 각 카테고리를 개별적으로 처리하고, 그 결과를 UNION으로 합쳐줌. (위 문제처럼 간단한 문제에선 JOIN보다 직관적이고 간단)
- 카테고리 별로 독립된 쿼리를 작성 후 합쳐주기

JOIN은 수평적 결합, UNION은 수직적 결합

SUM과 COUNT
- SUM
  - 숫자 값만을 대상으로 컬럼 갯수 더함
  - 논리조건 사용 시 참, 거짓(0,1)로 처리할 수 있어 특정 조건을 만족하는 값의 개수 구할 수 있음
- COUNT
  - NULL이 아닌 컬럼의 개수를 셈
  - 조건에 해당하지 않는 부분은 모두 NULL로 처리해서 세지 않음
  - 따라서 조건에 해당하는 값만 세기 위해 CASE WHEN을 사용
    
    위 문제에서 COUNT보다 SUM으로 보다 간결하게 쿼리를 작성할 수 있어서 SUM 사용

icegosimperson commented 1 month ago

문제: 있었는데요 없었습니다

설명

동물의 보호 관련 데이터가 있는 ANIMAL_INS테이블과 입양 데이터에 대한 ANIMAL_OUTS 테이블이 있을 때, 입양일이 보호 시작일보다 빠른 데이터 조회

코드

SELECT A.ANIMAL_ID, A.NAME
FROM ANIMAL_INS A LEFT JOIN ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE A.DATETIME > B.DATETIME
ORDER BY A.DATETIME;

아이디어

LEFT JOIN하는 이유 : 문제 조건에 관리자 실수로 입양일이 잘못되었다는 조건 -> 보호 시작일(A Table) 기준으로 LEFT JOIN
Where 절을 통해 입양일이 보호 시작일보다 빠른 날만 조회 (>)

KodaHye commented 1 month ago

문제: 보호소에서 중성화한 동물

설명

ANIMAL_INS, ANIMAL_OUTS 테이블이 있을 때, 보호소에서 중성화된 동물들의 데이터 출력하

코드

SELECT A.ANIMAL_ID, A.ANIMAL_TYPE, A.NAME
FROM ANIMAL_INS A 
    LEFT JOIN ANIMAL_OUTS B
    ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE 
    A.SEX_UPON_INTAKE LIKE 'Intact%'
    AND 
    B.SEX_UPON_OUTCOME NOT LIKE 'Intact%'
ORDER BY A.ANIMAL_ID ASC

아이디어

중성화 되지 않은 동물들은 Intact로 시작, 중성화된 동물들은 Intact가 아닌 다른 단어로 시작하므로 LIKE와 NOT LIKE 사용

yeahdy commented 1 month ago

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

설명

조회수가 가장 높은 중고거래 게시물에 대한 첨부파일 경로를 조회
첨부파일 경로는 FILE ID를 기준으로 내림차순
/home/grep/src/게시글 ID/파일 ID + 파일 이름 + 파일 확장자

코드

SELECT 
    CONCAT('/home/grep/src/',ugf.BOARD_ID,'/',ugf.FILE_ID,ugf.FILE_NAME,ugf.FILE_EXT) FILE_PATH
FROM USED_GOODS_BOARD ugb
INNER JOIN USED_GOODS_FILE ugf
ON ugb.BOARD_ID = ugf.BOARD_ID
WHERE ugb.VIEWS = (SELECT MAX(VIEWS) FROM USED_GOODS_BOARD)
ORDER BY ugf.FILE_ID DESC;

아이디어

문자열 합치기 CONCAT(n1, n2, n...) 인수는 2개 이상 254개 이하로 사용 가능

MAX 또는 MIN 값을 가진 칼럼을 조회할 때, 단순히 SELECT 문에 MAX,MIN 함수와 칼럼을 조회하면 잘못된 값이 나오기 때문에 주의하기
조회수가 제일 높은 게시글ID 찾기
- 조회수가 제일 높은 게시글은 B0008
- 아래 쿼리로 조회할 경우 테이블의 첫번째 ROW 데이터가 나옴
```
SELECT MAX(VIEWS),BOARD_ID
FROM USED_GOODS_BOARD;
```

Jewan1120 commented 1 month ago

문제: 1934. Confirmation Rate

설명

Signups 테이블에 저장된 유저별로 Confirmations테이블에서 action칼럼의 confirmed비율을 출력

코드

SELECT
    A.user_id
    , ROUND(AVG(IF(B.action = 'confirmed', 1, 0)), 2) confirmation_rate
FROM
    Signups A
    LEFT JOIN Confirmations B
        ON A.user_id = B.user_id
GROUP BY
    A.user_id

아이디어

소수점 2자리까지 표현해야 하므로 ROUND를 사용해서 반올림
집계함수 AVG를 사용해서 confirmed비율을 계산
조건문 IF를 사용해서 confirmed의 값을 1로, 그 외의 값은 0으로 처리

GreatAlgorithm-Study / AlgorithmStudy