SELECT
회원의 ID(MEMBER_ID), 이름(MEMBER_NAME), 전화번호(TLNO), 성별(GENDER), 생년월일(DATE_OF_BIRTH)가 담긴 테이블을 받으면 다중 조건을 만족하는지 확인하고, 만족하는 값을 정렬하여 출력하는 문제다.
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 GENDER = "W"
AND TLNO IS NOT NULL
ORDER BY MEMBER_ID생년월일 칼럼은 DATETIME 자료형이므로 시간 정보를 같이 포함한다. 예시에서는 (연도 4자리)-(월)-(일)만 요구하기에 해당 형태로 바꿔주는 과정이 필요했다. DATE_FORMAT() 함수를 사용하여 DATETIME 자료형의 값을 지정한 형식대로 출력할 수 있다. 형식의 아래의 표를 참고하자.
| 구분기호 | 역할 | 구분기호 | 역할 |
|---|---|---|---|
| %Y | 4자리 년도 | %W | 영문 요일 |
| %y | 2자리 년도 | %a | 영문 요일(간략) |
| %M | 영문 월 | %I | 시간(12시간) |
| %m | 숫자 월(2자리) | %H | 시간(24시간) |
| %c | 숫자 월 | %i | 분 |
| %b | 영문 월(간략) | %S | 초 |
| %d | 일자(2자리) | %T | hh:mm:SS |
| %e | 일자 | %r | hh:mm:ss AM/PM |
DATE_FORMAT(DATE_OF_BIRTH,"%Y-%m-%d")의 형식으로 출력하면 1992-03-16이 나오는 것이다.
또한, IS NOT과 !=도 구분할 필요가 있다. IS NOT 연산자는 특정 값이나 NULL이 아닌 값을 검색할 때 사용한다. !=는 두 값이 같지 않을 때 주로 사용한다. 위의 문제처럼 NULL 값이 아님을 확인 할 때는 IS NOT을 쓰는 것이 더 적절하다.
식당 ID, 식당 이름, 음식 종류, 조회수, 즐겨찾기수, 주차장 유무, 주소, 전화번호가 포함된 테이블과 리뷰 ID, 식당 ID, 회원 ID, 점수, 리뷰 텍스트, 리뷰 작성일이 포함된 테이블 2개를 받아 이를 병합해야 한다.
SELECT REST_INFO.REST_ID, REST_INFO.REST_NAME, REST_INFO.FOOD_TYPE, REST_INFO.FAVORITES, REST_INFO.ADDRESS, REST_REVIEW.SCORE
FROM REST_INFO
INNER JOIN (SELECT REST_ID, ROUND(AVG(REVIEW_SCORE),2) AS SCORE
FROM REST_REVIEW GROUP BY REST_ID) REST_REVIEW
ON REST_INFO.REST_ID = REST_REVIEW.REST_ID
WHERE ADDRESS LIKE "서울%"
ORDER BY SCORE DESC, FAVORITES DESC양쪽 테이블 모두에 REST_ID가 존재해야 하므로, 내부 조인을 사용하여 병합한다. 병합할 테이블에는 REST_ID와 같은 식당에 대해 작성한 모든 리뷰의 평점이 있어야 한다. GROUP BY를 사용해 식당별로 리뷰를 그룹화하고, ROUND(AVG(),N)을 사용해 리뷰 점수의 평균에서 소수점 아래 N+1번째 자리에서 반올림한 값을 구한다. 이때, every derived table must have its own alias라는 오류가 계속 발생했는데 위처럼 서브쿼리를 사용할 경우 alias, 즉 이름을 끝에 붙여야 한다. 그 뒤로 서울에 있는 식당만을 선별하기 위해 와일드카드를 사용한다.
| 와일드카드 | 의미 |
|---|---|
| % | 해당 위치에 있는 0개 이상의 문자 |
| _ | 해당 위치에 있는 문자 하나 |
"서울%"이라고 쓰면 서울로 시작하는 값들을 찾는 것이다.
온라인 상품 판매 ID, 회원 ID, 상품 ID, 판매량, 판매일이 포함된 테이블과 오프라인 상품 판매 ID, 상품 ID, 판매량, 판매일이 포함된 테이블을 받아 합쳐야 한다. 이때, OFFLINE_SALE 테이블에는 USER_ID가 포함되어 있지 않다는 것을 주의해야 한다.
SELECT SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT FROM (
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 ALL
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
)SALE_DATA ORDER BY SALES_DATE, PRODUCT_ID, USER_IDUNION ALL 연산자를 사용하여 두 테이블을 합쳐 출력할 수 있다. 위의 문제처럼 합쳐진 쿼리에 대해 every derived table must have its own alias라는 오류가 발생했는데 뒤에 alias를 붙여 해결했다.
온라인 상품 판매 ID, 회원 ID, 상품 ID, 판매량, 판매일을 포함한 테이블에서 USER_ID와 PRODUCT_ID가 중복되는 항목을 출력하는 문제이다.
SELECT USER_ID, PRODUCT_ID FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(*) > 1
ORDER BY USER_ID, PRODUCT_ID DESCGROUP BY와 HAVING을 사용하여 중복되는 항목들을 찾을 수 있다. 회원 ID, 상품 ID 별로 GROUP BY를 한 다음, 각각에 대해 개수가 1보다 큰, 즉 2번 이상의 구매 이력이 있을 경우 재구매한 것으로 판단하고 테이블을 조회한다.
References
RyanGomdoriPooh) MySQL
좋은 블로그) [mysql] DATE_FORMAT - 날짜 형식 설정
Wakestand Island) MySQL Every derived table must have its own alias 에러 해결방법
