이때 전화번호가 NULL인 경우는 출력대상에서 제외
-> TLNO IS NOT NULL
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
동일한 회원이 동일한 상품을 재구매한 데이터를 구하여
-> GROUP BY
HAVING 활용하기
SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(*) >= 2
ORDER BY USER_ID, PRODUCT_ID DESC
1) 2022년 3월의 데이터 추출.
2) OFFLINE_SALE 테이블의 판매 데이터의 USER_ID 값은 NULL 로 표시해주세요.
-> NULL as user_id
select date_format(sales_date, '%Y-%m-%d') as sales_date, product_id, user_id, sales_amount
from online_sale
where sales_date >= '2022-03-01' and sales_date < '2022-04-01'
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 sales_date >= '2022-03-01' and sales_date < '2022-04-01'
order by sales_date, product_id, user_id
이때 리뷰 평균점수는 소수점 세 번째 자리에서 반올림 해주시고
-> "Round 함수" 적용
ROUND(AVG(A.REVIEW_SCORE),2)