[SQL 코딩 테스트 대비-MySQL] SELECT

[프로그래머스 문제 / MySQL 기준]

 

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

1) 날짜의 형식을 별도로 지정하고 싶을 때 DATE_FORMAT(컬럼명, "날짜 형식") 으로 지정한다.
2) 컬럼의 값 중 특정 문자 포함여부를 확인하고 싶을 때 LIKE "%문자열%" 로 확인한다.
3) 특정 컬럼을 출력하고자 하는데 NULL이 아닌 값을 출력할 때는 WHERE 컬럼명 IS NOT NULL를 사용한다. 

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

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

1) 컬럼 내의 값을 정렬하고자 할 때 정렬 방식은 컬럼명 뒤에 작성하며, 정렬되는 기준 순서대로 작성된다. 아래의 정렬은 고용일자를 내림차순으로 정렬한 후 만약 고용일자가 같다면 그중 의사 이름을 기준으로 오름차순 정렬하는 것이다. 

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

3. 평균 일일 대여 요금 구하기

1) 특정 컬럼을 기준으로 그룹바이하고 컬럼의 값별로 다른 컬럼의 평균값을 구할 때 AVG(컬럼명)로 사용한다. 
2) 값을 반올림하고자 할 때는 ROUND(컬럼명, 반올림자리수) 로 작성한다 -1인 경우 일의자리에서 반올림, 0인 경우 소수점 첫째자리에서 반올림, 1인 경우 소수점 둘째 자리에서 반올림한다.

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

4. 12세 이하인 여자 환자 목록 출력하기

1) 컬럼 값이 NULL인 경우 값을 치환하여 출력하고자 한다면 COALESCE(컬럼명, "대체값") AS 출력하고자 하는 컬럼명으로 작성한다.

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

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

1) 두 개의 테이블을 함께 조회하고자 할 때는 각 테이블에 별칭을 붙여서 컬럼명 앞에 별칭과 . 을 작성해 지정해준다. 주의할 점은 특정 게시글과 해당 게시글에 작성한 댓글을 매칭하여 걸러야 하기 때문에 게시글과 댓글의 BOARD_ID가 같은 경우라는 조건을 걸어야 한다는 점이다.

SELECT B.TITLE, R.BOARD_ID, R.REPLY_ID, R.WRITER_ID, R.CONTENTS, DATE_FORMAT(R.CREATED_DATE,"%Y-%m-%d") AS CREATED_DATE
FROM USED_GOODS_BOARD AS B, USED_GOODS_REPLY AS R
WHERE B.CREATED_DATE LIKE "%2022-10%"
AND B.BOARD_ID = R.BOARD_ID
ORDER BY R.CREATED_DATE ASC, B.TITLE ASC

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

1) 식당 별로 리뷰 점수를 평균낸 값에 대하여 소수점 반올림을 하고 싶다면 AVG로 리뷰 점수 컬럼명을 감싸고 그 밖에 소수점 반올림 처리를 해주면 된다.
2) 여기서 "식당별로" 라는 조건을 충족시키기 위해서는 REST_ID에 대해서 GROUP BY를 진행해줘야 한다. 그렇게 하지않는다면 가장 상단의 식당으로 모든 리뷰 점수가 평균처리 될 것이고 단 한개의 식당만 출력될 것이다.
3) 그리고 코드를 작성하기 전에 전체 데이터를 먼저 확인하는 습관이 필요하다. 주소가 서울특별시 또는 서울시로 작성되어 있기에 WHERE 구문에서 '서울특별시'가 아닌 '서울'로 필터링을 해야 하기 때문이다.
4) LIKE를 작성할 때 %서울% 이라고 쓴다면 서울이라는 글자가 명백히 데이터의 중간에 있다는 뜻이 되어버린다. 원하지 않는 결과를 출력할 수 있기 때문에 반드시 '서울%'로 필터링하자
5) SELECT 문으로 보여줄 컬럼의 순서가 정해졌다면 GROUP BY 나 ORDER BY 구문에서는 컬럼 순서인 숫자로 지정할 수 있다. SQL에서 인덱스는 1부터 시작한다.  

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 AS A, REST_REVIEW AS B
WHERE A.ADDRESS LIKE "서울%"
AND A.REST_ID = B.REST_ID
GROUP BY A.REST_ID
ORDER BY ROUND(AVG(B.REVIEW_SCORE),2) 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 AS A, REST_REVIEW AS B
WHERE A.ADDRESS LIKE "서울%"
AND A.REST_ID = B.REST_ID
GROUP BY 1
ORDER BY 6 DESC, 4 DESC

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

1) 동일한 유저와 동일한 상품을 필터링 해야 하므로 GROUP BY 뒤에 두 개의 컬럼명을 작성하고
2) 그룹핑 후  각 상품 아이디 행의 갯가 2개 이상이면 재구매로 판단 => 여기서 HAVING은 결과 값이 나오고 난 후에 필터링을 하는 것이라고 생각하면 이해하기 쉽다.

SELECT USER_ID, PRODUCT_ID FROM ONLINE_SALE
GROUP BY PRODUCT_ID, USER_ID
HAVING COUNT(PRODUCT_ID) >=2
ORDER BY USER_ID ASC, PRODUCT_ID DESC

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

1) 두 개의 테이블의 컬럼명이 동일해서.. 어떻게 조회해야 하는지 난감했다. 하지만 두 테이블을 합치자는 생각을 했지만 한 컬럼이 오프라인 판매 테이블에 없어서 고민... 하다 그 값은 NULL로 처리하면 된다길래 처리
2) 두 테이블을 합칠 때는 UNION ALL을 사용하면 된다..

SELECT DATE_FORMAT(SALES_DATE, "%Y-%m-%d") AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE
WHERE SALES_DATE LIKE "2022-03%"
UNION ALL
SELECT DATE_FORMAT(SALES_DATE, "%Y-%m-%d") 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