처음에는 다음과 같이, SQL 문을 작성하였다.
SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_OF_BIRTH
from MEMBER_PROFILE
where DATE_OF_BIRTH like '%-03-%' // 생일이 3월인
and TLNO is not null // 전화번호가 NULL인 경우를 제외
and GENDER = "W" // 여성 회원
ORDER BY MEMBER_ID; // 회원 ID를 기준으로 오름차순 정렬
하지만, 통과가 되지 않아서 무엇이 문제지?라고 생각했는데 DATE_OF_BIRTH
이 아래와 같이 출력되었다.
DATE_FORMAT
를 통해서 출력되는 DATE의 포멧을 수정해줘야 한다.
✔️ like 사용
SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d') AS DATE_OF_BIRTH
from MEMBER_PROFILE
where DATE_OF_BIRTH like '%-03-%'
and TLNO is not null
and GENDER = 'W'
ORDER BY MEMBER_ID;
✔️ MONTH() 사용
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 FACTORY_ID, FACTORY_NAME, ADDRESS from FOOD_FACTORY
where ADDRESS like '강원도%'
order by FACTORY_ID;
SELECT DR_NAME, DR_ID, MCDP_CD, DATE_FORMAT(HIRE_YMD, '%Y-%m-%d') AS HIRE_YMD
from DOCTOR
where MCDP_CD = 'CS' or MCDP_CD = 'GS'
order by HIRE_YMD DESC, DR_NAME;
SELECT BOOK_ID, DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
from BOOK
where YEAR(PUBLISHED_DATE) = 2021 and CATEGORY='인문'
order by PUBLISHED_DATE;
해당 문제들을 풀면서 필요한 지식은 DATE 함수와 ORDER BY이다.
DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d')
https://myhappyman.tistory.com/73
https://www.w3schools.com/sql/func_mysql_date_format.asp
SQL 날짜 함수
https://velog.io/@seyeop03/SQL%EC%9D%98-%ED%95%A8%EC%88%98%EC%A0%95%EB%A6%AC