๋ค์์ ์ข
ํฉ๋ณ์์ ์ํ ์์ฌ ์ ๋ณด๋ฅผ ๋ด์DOCTOR
ํ
์ด๋ธ์
๋๋ค. DOCTOR
ํ
์ด๋ธ์ ๋ค์๊ณผ ๊ฐ์ผ๋ฉฐ DR_NAME
, DR_ID
, LCNS_NO
, HIRE_YMD
, MCDP_CD
, TLNO
๋ ๊ฐ๊ฐ ์์ฌ์ด๋ฆ, ์์ฌID, ๋ฉดํ๋ฒํธ, ๊ณ ์ฉ์ผ์, ์ง๋ฃ๊ณผ์ฝ๋, ์ ํ๋ฒํธ๋ฅผ ๋ํ๋
๋๋ค.
Column | name | Type | Nullable |
---|---|---|---|
DR_NAME | VARCHAR(20) | FALSE | |
DR_ID | VARCHAR(10) | FALSE | |
LCNS_NO | VARCHAR(30) | FALSE | |
HIRE_YMD | DATE | FALSE | |
MCDP_CD | VARCHAR(6) | TRUE | |
TLNO | VARCHAR(50) | TRUE |
DOCTOR ํ ์ด๋ธ์์ ์ง๋ฃ๊ณผ๊ฐ ํ๋ถ์ธ๊ณผ(CS)์ด๊ฑฐ๋ ์ผ๋ฐ์ธ๊ณผ(GS)์ธ ์์ฌ์ ์ด๋ฆ, ์์ฌID, ์ง๋ฃ๊ณผ, ๊ณ ์ฉ์ผ์๋ฅผ ์กฐํํ๋ SQL๋ฌธ์ ์์ฑํด์ฃผ์ธ์. ์ด๋ ๊ฒฐ๊ณผ๋ ๊ณ ์ฉ์ผ์๋ฅผ ๊ธฐ์ค์ผ๋ก ๋ด๋ฆผ์ฐจ์ ์ ๋ ฌํ๊ณ , ๊ณ ์ฉ์ผ์๊ฐ ๊ฐ๋ค๋ฉด ์ด๋ฆ์ ๊ธฐ์ค์ผ๋ก ์ค๋ฆ์ฐจ์ ์ ๋ ฌํด์ฃผ์ธ์.
DOCTOR
ํ
์ด๋ธ์ด ๋ค์๊ณผ ๊ฐ์ ๋
DR_NAME | DR_ID | LCNS_NO | HIRE_YMD | MCDP_CD | TLNO |
---|---|---|---|---|---|
๋ฃจํผ | DR20090029 | LC00010001 | 2009-03-01 | CS | 01085482011 |
ํจํฐ | DR20090001 | LC00010901 | 2009-07-01 | CS | 01085220122 |
๋ฝ๋ก๋ก | DR20170123 | LC00091201 | 2017-03-01 | GS | 01034969210 |
ํฐ๊ฑฐ | DR20100011 | LC00011201 | 2010-03-01 | NP | 01034229818 |
ํ๋ฐ | DR20090231 | LC00011302 | 2015-11-01 | OS | 01049840278 |
ํฐ๋ชฌ | DR20090112 | LC00011162 | 2010-03-01 | FM | 01094622190 |
๋๋ชจ | DR20200012 | LC00911162 | 2020-03-01 | CS | 01089483921 |
์ค๋ก๋ผ | DR20100031 | LC00010327 | 2010-11-01 | OS | 01098428957 |
์์ค๋ฏผ | DR20100032 | LC00010192 | 2010-03-01 | GS | 01023981922 |
๋ฒจ | DR20100039 | LC00010562 | 2010-07-01 | GS | 01058390758 |
SQL์ ์คํํ๋ฉด ๋ค์๊ณผ ๊ฐ์ด ์ถ๋ ฅ๋์ด์ผ ํฉ๋๋ค.
DR_NAME | DR_ID | MCDP_CD | HIRE_YMD |
---|---|---|---|
๋๋ชจ | DR20200012 | CS | 2020-03-01 |
๋ฝ๋ก๋ก | DR20170123 | GS | 2017-03-01 |
๋ฒจ | DR20100039 | GS | 2010-07-01 |
์์ค๋ฏผ | DR20100032 | GS | 2010-03-01 |
ํจํฐ | DR20090001 | CS | 2009-07-01 |
๋ฃจํผ | DR20090029 | CS | 2009-03-01 |
๋ ์ง ํฌ๋งท์ ์์์ ๋์ผํ๊ฒ ๋์์ผํฉ๋๋ค.
SELECT DR_NAME, DR_ID, MCDP_CD, DATE_FORMAT(HIRE_YMD,'%Y-%m-%d')
FROM DOCTOR
WHERE MCDP_CD IN ('CS', 'GS')
ORDER BY HIRE_YMD DESC, DR_NAME ASC;