프로그래머스 SQL 고득점 QIT: SELECT

3월에 태어난 여성 회원 목록 출력하기

 

💡SQL에서 Date_of_birth가 '1992-03-01'이런식으로 주어져있을 때 생일이 3월인 사람을 조회하려면?

 

이 경우, `MONTH()`라는 함수를 사용하여 생일이 3월인 사람을 조회할 수 있습니다. 예를 들어, 데이터가 `people`이라는 테이블에 있고 그 테이블에 `Date_of_birth`라는 열이 있다면, 다음과 같이 쿼리를 작성할 수 있습니다:

SELECT *
FROM people
WHERE MONTH(Date_of_birth) = 3;

위의 쿼리는 `Date_of_birth`의 월이 3인 모든 행을 `people` 테이블에서 반환합니다. 이때, `MONTH()` 함수는 날짜에서 월을 추출하는 역할을 합니다. 

참고로, SQL에서는 월, 일, 년을 나타내는 함수들이 있습니다:

- `MONTH()` : 날짜에서 월을 추출
- `DAY()` : 날짜에서 일을 추출
- `YEAR()` : 날짜에서 년을 추출

 

오라클의 경우 다음과 같이 작성

SELECT *
FROM people
WHERE TO_CHAR(Date_of_birth, 'MM') = '03';

💡1992-03-16 00:00:00 이 포맷을 1993-03-16 이런 형태로 바꾸려면?

MySQL에서 날짜의 형식을 바꾸는 데는 `DATE_FORMAT()` 함수를 사용할 수 있습니다. 이 함수는 첫 번째 인수로 날짜를 받고, 두 번째 인수로 반환될 문자열의 형식을 지정합니다.

예를 들어, '1992-03-16 00:00:00'을 '1992-03-16' 형식으로 바꾸려면 다음과 같이 작성하면 됩니다:

SELECT DATE_FORMAT('1992-03-16 00:00:00', '%Y-%m-%d');

위의 쿼리에서, '%Y-%m-%d'는 형식 문자열이며, 각 부분은 다음과 같이 해석됩니다:

- %Y: 네 자리의 년도
- %m: 두 자리의 월
- %d: 두 자리의 일

따라서, 위의 쿼리는 '1992-03-16 00:00:00'를 '1992-03-16' 형태로 출력합니다.

이를 당신의 질문에 적용하면, people 테이블에서 Date_of_birth를 변경하는 방법은 다음과 같습니다:

SELECT DATE_FORMAT(Date_of_birth, '%Y-%m-%d') as Formatted_DOB, Other_Columns
FROM people
WHERE MONTH(Date_of_birth) = 3
AND phone_number IS NOT NULL;

여기서 `Other_Columns`는 조회하고자 하는 다른 열을 나타냅니다. 이 부분은 실제 쿼리에서 적절한 열 이름으로 대체하면 됩니다.

 

💾정답 코드

-- 코드를 입력하세요
SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_FORMAT(DATE_OF_BIRTH,'%Y-%m-%d') AS DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE MONTH(DATE_OF_BIRTH)=3 AND TLNO IS NOT NULL AND GENDER='W'
ORDER BY MEMBER_ID;

---

가격이 제일 비싼 식품의 정보 출력하기

🤢처음 작성한 코드

-- 코드를 입력하세요
SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE
FROM FOOD_PRODUCT
WHERE PRICE=MAX(PRICE)

`MAX()` 함수는 집계 함수로서, 하나의 결과 값을 반환하기 위해 여러 행의 값을 그룹화하는데 사용됩니다. 당신이 작성한 쿼리는 `WHERE` 절에서 `MAX(PRICE)`를 사용하고 있기 때문에 오류를 발생시킬 것입니다. 

가장 높은 가격을 가진 제품을 찾으려면, 하위 쿼리(subquery)를 사용하여 `MAX(PRICE)`를 먼저 찾아낸 후 그 값을 이용해 원하는 결과를 얻어낼 수 있습니다.

다음은 그 예입니다:

SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE
FROM FOOD_PRODUCT
WHERE PRICE = (SELECT MAX(PRICE) FROM FOOD_PRODUCT);

위 쿼리는 `FOOD_PRODUCT` 테이블에서 가격이 가장 높은 제품들의 `PRODUCT_ID`, `PRODUCT_NAME`, `PRODUCT_CD`, `CATEGORY`, `PRICE`를 반환합니다. `(SELECT MAX(PRICE) FROM FOOD_PRODUCT)`는 `FOOD_PRODUCT` 테이블의 `PRICE` 중 최대값을 반환하는 하위 쿼리입니다.

 

해결 방법: 하위 쿼리로 작성하기

 

💾정답 코드

-- 코드를 입력하세요
SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE
FROM FOOD_PRODUCT
WHERE PRICE=(SELECT MAX(PRICE) FROM FOOD_PRODUCT);

---

재구매가 일어난 상품과 회원 리스트

💾정답 코드

SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(*) > 1
ORDER BY USER_ID ASC, PRODUCT_ID DESC;

먼저, 동일한 회원이 동일한 상품을 재구매한 경우라면, 테이블에 해당 회원과 상품에 대한 두 개 이상의 엔트리가 있어야 합니다. 이를 확인하기 위해 `GROUP BY` 절과 `HAVING` 절을 사용하면 됩니다.

또한, 이 쿼리에서는 `USER_ID`로 오름차순 정렬하고, 그 후에 `PRODUCT_ID`로 내림차순 정렬하도록 요구하고 있습니다. 이는 `ORDER BY` 절에서 여러 열을 사용하여 이루어집니다.

위 쿼리는 다음을 수행합니다:

1. `ONLINE_SALE` 테이블에서 `USER_ID`와 `PRODUCT_ID`를 선택합니다.
2. 결과를 `USER_ID`와 `PRODUCT_ID`로 그룹화합니다.
3. 각 그룹에 대해 그룹의 크기 (즉, 같은 `USER_ID`와 `PRODUCT_ID`를 가진 행의 수)를 계산하고, 그 크기가 1보다 큰 그룹만 선택합니다. 이 부분이 재구매를 확인하는 부분입니다.
4. 결과를 `USER_ID`로 오름차순 정렬하고, 그 후 `PRODUCT_ID`로 내림차순 정렬합니다.

---

조건에 부합하는 중고거래 댓글 조회하기

🤢처음 작성한 코드

SELECT A.TITLE, A.BOARD_ID, B.REPLY_ID, B.WRITER_ID, B.CONTENTS, DATE_FORMAT(B.CREATED_DATE, '%Y-%m-%d') AS CREATED_DATE
FROM USED_GOODS_BOARD A, USED_GOODS_REPLY B
WHERE B.CREATED_DATE BETWEEN '2022-10-01' AND '2022-10-31'
ORDER BY B.CREATED_DATE, A.TITLE;

🥨해결책: JOIN 사용하기

제공된 쿼리는 USED_GOODS_BOARD 테이블과 USED_GOODS_REPLY 테이블을 조인하지 않고, 그대로 사용합니다. 이렇게 되면 두 테이블의 모든 행이 Cartesian Product를 형성하여 조합됩니다. 따라서 게시글과 댓글 사이의 실제 관계가 반영되지 않습니다.

게시글과 댓글이 ID를 통해 연결되어 있다고 가정하면, 테이블 간의 조인 조건을 추가해야 합니다. 게시글 ID가 두 테이블 간의 연결고리라고 가정하고 조인 조건을 추가해봅시다.

SELECT 
    A.TITLE, 
    A.BOARD_ID, 
    B.REPLY_ID, 
    B.WRITER_ID, 
    B.CONTENTS, 
    DATE_FORMAT(B.CREATED_DATE, '%Y-%m-%d') AS CREATED_DATE
FROM 
    USED_GOODS_BOARD A
JOIN 
    USED_GOODS_REPLY B ON A.BOARD_ID = B.BOARD_ID
WHERE 
    B.CREATED_DATE >= '2022-10-01' AND B.CREATED_DATE < '2022-11-01'
ORDER BY 
    B.CREATED_DATE ASC, 
    A.TITLE ASC;

위의 쿼리는 USED_GOODS_BOARD 테이블과 USED_GOODS_REPLY 테이블을 BOARD_ID를 기준으로 조인합니다. 조건절에서는 2022년 10월에 작성된 댓글을 필터링하고, 결과는 댓글의 작성일로 먼저 정렬한 다음, 게시글 제목으로 정렬합니다.

참고로, 이 쿼리는 BOARD_ID라는 필드가 두 테이블에 공통으로 존재하고, 이 필드를 통해 두 테이블이 연결되어 있다고 가정하고 작성한 것입니다. 실제 필드 이름이나 관계는 데이터베이스 스키마에 따라 달라질 수 있습니다.

 

💾정답 코드

-- 코드를 입력하세요
SELECT A.TITLE, A.BOARD_ID, B.REPLY_ID, B.WRITER_ID, B.CONTENTS, DATE_FORMAT(B.CREATED_DATE, '%Y-%m-%d') AS CREATED_DATE
FROM USED_GOODS_BOARD A JOIN USED_GOODS_REPLY B ON A.BOARD_ID=B.BOARD_ID
WHERE A.CREATED_DATE>='2022-10-01'AND A.CREATED_DATE <'2022-11-01'
ORDER BY B.CREATED_DATE ASC, A.TITLE ASC;

---

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

🤢처음 작성한 코드

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

🥨해결책: GROUP BY 사용하기

작성하신 쿼리에서 몇 가지 사항이 누락되었습니다.

1. **GROUP BY 절의 누락**: `AVG`와 같은 집계 함수를 사용할 때는 `GROUP BY` 절을 사용하여 집계 대상을 지정해야 합니다. 여기서는 REST_ID별로 평균 점수를 계산하려는 것 같으므로 `GROUP BY A.REST_ID, A.REST_NAME, A.FOOD_TYPE, A.FAVORITES, A.ADDRESS`를 추가해야 합니다.

2. **반올림 자릿수 불일치**: 문제에서는 리뷰 평균점수를 소수점 세 번째 자리에서 반올림하라고 요구했으나, 쿼리에서는 둘째 자리에서 반올림하고 있습니다.

위 두 사항을 고려하여 쿼리를 수정하면 다음과 같습니다.

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

이 쿼리를 사용하면 문제에서 요구하는 대로 각 식당의 리뷰 평균점수를 계산하고, 결과를 평균점수와 즐겨찾기 수를 기준으로 내림차순 정렬할 수 있습니다.

 

💾정답 코드

-- 코드를 입력하세요
SELECT A.REST_ID, A.REST_NAME, A.FOOD_TYPE, A.FAVORITES, A.ADDRESS, ROUND(AVG(B.REVIEW_SCORE),2) AS SCORE
FROM REST_INFO A JOIN REST_REVIEW B ON A.REST_ID=B.REST_ID
WHERE A.ADDRESS LIKE ('서울%')
GROUP BY REST_ID
ORDER BY SCORE DESC, A.FAVORITES DESC;

---

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

🥨해결책: UNION ALL 사용하기

NULL값으로 채우려면 NULL AS 컬럼명

 

💾정답 코드

-- 코드를 입력하세요
SELECT DATE_FORMAT(A.SALES_DATE,'%Y-%m-%d') AS SALES_DATE, A.PRODUCT_ID, A.USER_ID, A.SALES_AMOUNT
FROM ONLINE_SALE A
WHERE A.SALES_DATE>='2022-03-01' AND A.SALES_DATE<'2022-04-01'

UNION ALL

SELECT DATE_FORMAT(B.SALES_DATE,'%Y-%m-%d') AS SALES_DATE, B.PRODUCT_ID, NULL AS USER_ID, B.SALES_AMOUNT
FROM OFFLINE_SALE B
WHERE B.SALES_DATE>='2022-03-01' AND B.SALES_DATE<'2022-04-01'

ORDER BY SALES_DATE, PRODUCT_ID, USER_ID;