[MySQL] SQL 문제풀이 (SELECT)

앞선 두개의 포스팅에 실린 sql 문제풀이를 따라가다 보면

요만큼의 진척도가 있었다.
여기서 풀지 않은 문제들을 풀려고한다.

일단 셀렉트 문제를 풀었다

(셀렉트 문제지만 join과 group by가 다 나오는.. ㅎ)

---

1) 12세 이하 여자 환자 목록 출력하기
- IFNULL (원하는 속성,"NULL일 경우 대체될 값")

SELECT PT_NAME, PT_NO, GEND_CD, AGE, IFNULL(TLNO, 'NONE') AS TLNO
FROM PATIENT
WHERE GEND_CD='W' AND AGE<=12
ORDER BY AGE DESC, PT_NAME;

---

2) 평균 일일 대여 요금 구하기

- ROUND(값, 표시되기 원하는 소숫점 자릿수) : ROUND(값,1) 을 하면 반올림하여 소숫점 1째짜리까지 보여줌

- TRUNC( 값, 표시되기 원하는 소숫점 자릿수) : 반내림? 버림?을 해서 보여줌

- AVG(속성) : 해당 속성의 평균 값을 보여줌

SELECT ROUND(AVG(DAILY_FEE),0) AS AVERAGE_FEE
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE='SUV';

---

3) 조건에 맞는 도서 리스트 출력하기

SELECT BOOK_ID, LEFT(PUBLISHED_DATE, 10) AS PUBLISHED_DATE
FROM BOOK
WHERE PUBLISHED_DATE LIKE '2021%' AND CATEGORY='인문';

위와 같이 해도 되고 아래와 같이 해도 된다,

위와 같은 경우에는 DATE를 왼쪽부터 10개를 잘라서 년월일을 추출해 낸 것이고

날짜를 비교할 때 2021이 포함된건지 판단한 것이다

 

아래와 같은 경우에는, DATE의 형식을 바꿔서 추출해 낸 것이고

DATE의 year만 추출해내 2021인지 비교한 것이다.

SELECT BOOK_ID, DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK
WHERE YEAR(PUBLISHED_DATE)='2021' AND CATEGORY='인문';

---

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

DATE_FORMAT을 연습하며 신기한 사실을 알아냈다

• DATE_FORMAT(col, 원하는 표기 형태)를 쓸 때,
• '%Y' 를 하면 년도 4자리 (1999)
• '%y'를 하면 년도 2자리 (99)
• '%M'을 하면 월을 영문으로 (March)
• '%m'은 월을 숫자로 (03)
• '%D'를 하면 st, nd, rd, th 가 붙는다 (16th)
• '%d'를 하면 그냥 숫자만 표기된다 (16)

SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d') AS DATE_OF_BIRTH
FROM MEMBER_PROFILE 
WHERE GENDER='W' AND MONTH(DATE_OF_BIRTH)=03 AND TLNO IS NOT NULL
ORDER BY MEMBER_ID;

위 코드와 아래 코드 모두 정답이다.

SELECT MEMBER_ID, MEMBER_NAME, GENDER, LEFT(DATE_OF_BIRTH,10) AS DATE_OF_BIRTH
FROM MEMBER_PROFILE 
WHERE GENDER='W' AND MONTH(DATE_OF_BIRTH)=03 AND TLNO IS NOT NULL
ORDER BY MEMBER_ID;

---

5) 과일로 만든 아이스크림 고르기

SELECT F.FLAVOR AS FLAVOR
FROM FIRST_HALF AS F LEFT JOIN ICECREAM_INFO AS I
ON F.FLAVOR = I.FLAVOR
WHERE INGREDIENT_TYPE='fruit_based' AND F.TOTAL_ORDER>3000
ORDER BY F.TOTAL_ORDER DESC;

---

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

이건 문제가 불친절하게 되어있었는데,

WHERE 절에서 댓글이 2022-10에 작성된게 아니라,

게시글이 2022-10에 작성된 댓글을 조회해야한다.. ㅎ

SELECT B.TITLE, B.BOARD_ID, R.REPLY_ID, R.WRITER_ID, R.CONTENTS, LEFT(R.CREATED_DATE,10) AS CREATED_DATE
FROM USED_GOODS_BOARD AS B RIGHT JOIN USED_GOODS_REPLY AS R
ON B.BOARD_ID = R.BOARD_ID
WHERE LEFT(B.CREATED_DATE,7)='2022-10'
ORDER BY R.CREATED_DATE, B.TITLE;

---

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

예제만 보고 앞부분이 서울특별시로 시작하면 되겠지~ 했는데

주소가 서울특별시, 서울시 등등 다양한게 있어서 앞부분에 서울이 들어가는지 확인하면 된다.

 

셀렉트 문제를 풀고 있지만.. ㅎㅎ 그룹바이가 들어간 문제를 풀게됐는데, 공부하면서 이런저런 걸 알게 됐다.

https://sxyzn.tistory.com/104 이 게시글의 2번 문제의 이야깃거리를 정리하자면

GROUP BY에 정의한 칼럼만 SELECT하거나,
정의하지 않은 컬럼을 SELECT절에서 사용하려면 반드시 집계함수 처리를 해야 한다.

위와 같다.

그렇다면 이 7번 문제를 풀면서 의아할 수도 있다.

 

여기서는 왜 그룹바이에 정의된 칼럼이 아닌 칼럼을, select 절에 사용해도 되는지

정리하여 설명하자면, 이 문제의 경우 그룹으로 묶었을 때 한 그룹 내 별점을 제외한 모든 값이 같다.

그룹바이를 할 때 집계함수를 사용한 값이 아니거나, 그룹바이에 정의된 칼럼이 아니라면

그룹 내 랜덤한 값 혹은 그룹 내 가장 위에 속한 값을 가져온다고 하는데,

만약 랜덤값을 가져온다 하더라고 그룹 내 별점을 제외한 모든 값이 같기 때문에 select 하는데 이상이 안생기고,

같은 그룹안에서도 달라질 수 있는 값인 별점은 집계함수 AVG를 사용하기 때문에 원하는 결과 값이 나오는 것이다 ㅎㅎ

SELECT I.REST_ID, I.REST_NAME, I.FOOD_TYPE, I.FAVORITES, I.ADDRESS, ROUND(AVG(R.REVIEW_SCORE),2) AS SCORE
FROM REST_INFO AS I JOIN REST_REVIEW AS R
ON I.REST_ID=R.REST_ID
GROUP BY I.REST_ID
HAVING LEFT(I.ADDRESS,2) = '서울'
ORDER BY SCORE DESC, I.FAVORITES DESC;

---

8) 흉부외과 또는 일반외과 의사 목록 출력하기

SELECT DR_NAME, DR_ID, MCDP_CD, LEFT(HIRE_YMD, 10) AS HIRE_YMD
FROM DOCTOR 
WHERE MCDP_CD ='CS' OR MCDP_CD = 'GS'
ORDER BY HIRE_YMD DESC, DR_NAME;

---

 

9) 강원도에 위치한 생산공장 목록 출력하기

SELECT FACTORY_ID, FACTORY_NAME, ADDRESS
FROM FOOD_FACTORY
WHERE ADDRESS LIKE '강원도%'
ORDER BY FACTORY_ID;

---

10) 재구매가 일어난 상품과 회원 리스트 구하기

우선 USER_ID와 PRODUCT_ID를 기준으로 그룹을 묶어준다.

그리고 해당 그룹에 2개 이상의 값이 존재한다면 재구매한 것이다.

즉, HAVING을 통해 재구매 여부를 알아내면 된다!

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

---

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

어렵지 않은 문제이다. 하지만 훼이크가 있다.

문제를 읽다보면 마치, 오프라인 테이블의 유저아이디를 'NULL' 이라는 문자열로 설정하라는 듯한 뉘앙스가 풍긴다.

하지만 'NULL'이라는 문자열이 아닌 NULL 로 설정해야 주어야 정답이다..

 

Q. 'NULL'이 아닌 NULL일 경우.. 실행결과에 유저아이디가 공백값이 나오는데, 그러면 예제랑 틀린거 아닌가? 

A. 예제가 틀리게 나온 것이다. 공백으로 나오는 것이 정답이다.

 

그리고 UNION ALL을 아주 오랜만에 써봤다! (처음 써보는 것 같기도 하다..)

UNION과 UNION ALL, 둘 다 테이블을 합치는 것이다.

그런데 이것들은 사용을 지양하는 편이라고 한다. 특히 UNION은 더욱.. (느리기 때문에)

처음부터 하나의 테이블에 저장하도록 합시다..

 

- UNION : 테이블을 하나로 합치며 중복을 제거한다. 중복을 제거하는 과정을 거쳐 속도가 더 느리다.

- UNION ALL : 테이블을 하나로 합치며 중복을 제거하지 않는다. 그래서 속도가 더 빠르다.

SELECT LEFT(SALES_DATE,10) AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE
WHERE SALES_DATE LIKE '2022-03%'
UNION ALL
SELECT LEFT(SALES_DATE,10) AS SALES_DATE, PRODUCT_ID, NULL, SALES_AMOUNT
FROM OFFLINE_SALE
WHERE SALES_DATE LIKE '2022-03%'
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID;

---

 

SELECT 문제풀이 끝!