Thinking in Data/[DL] DataSQL
[SQL Quiz] #A1008. 문자열내 문자/숫자 분리하여 정렬하기
띤킹인
2023. 4. 23. 23:48
Level=2
Q) 실수로 국가명,국가코드(숫자),국가코드(3자리),국가코드(2자리)를 하나의 문자열로 만들어졌다. 아래 문자열 내 국가코드(숫자) 들에 대하여 내림차순으로 숫자 정렬하여 출력하는 SQL을 작성하시오
/* Oracle */
WITH W_NAT AS
(
SELECT '가나288GHA_GH' AS NAT_STR FROM DUAL UNION ALL
SELECT '가봉266GAB_GA' AS NAT_STR FROM DUAL UNION ALL
SELECT '가이아나328GUY_GY' AS NAT_STR FROM DUAL UNION ALL
SELECT '감비아270GMB_GM' AS NAT_STR FROM DUAL UNION ALL
SELECT '건지 섬831GGY_GG' AS NAT_STR FROM DUAL UNION ALL
SELECT '과들루프312GLP_GP' AS NAT_STR FROM DUAL UNION ALL
SELECT '과테말라320GTM_GT' AS NAT_STR FROM DUAL UNION ALL
SELECT '괌316GUM_GU' AS NAT_STR FROM DUAL UNION ALL
SELECT '그레나다308GRD_GD' AS NAT_STR FROM DUAL UNION ALL
SELECT '조지아268GEO_GE' AS NAT_STR FROM DUAL UNION ALL
SELECT '그리스300GRC_GR' AS NAT_STR FROM DUAL UNION ALL
SELECT '그린란드304GRL_GL' AS NAT_STR FROM DUAL UNION ALL
SELECT '기니324GIN_GN' AS NAT_STR FROM DUAL UNION ALL
SELECT '기니비사우624GNB_GW' AS NAT_STR FROM DUAL UNION ALL
SELECT '나미비아516NAM_NA' AS NAT_STR FROM DUAL UNION ALL
SELECT '나우루520NRU_NR' AS NAT_STR FROM DUAL UNION ALL
SELECT '나이지리아566NGA_NG' AS NAT_STR FROM DUAL UNION ALL
SELECT '남극10ATA_AQ' AS NAT_STR FROM DUAL UNION ALL
SELECT '남아프리카 공화국710ZAF_ZA' AS NAT_STR FROM DUAL UNION ALL
SELECT '네덜란드528NLD_NL' AS NAT_STR FROM DUAL UNION ALL
SELECT '네덜란드령 안틸레스530ANT_AN' AS NAT_STR FROM DUAL UNION ALL
SELECT '네팔524NPL_NP' AS NAT_STR FROM DUAL UNION ALL
SELECT '노르웨이578NOR_NO' AS NAT_STR FROM DUAL UNION ALL
SELECT '노퍽 섬574NFK_NF' AS NAT_STR FROM DUAL UNION ALL
SELECT '누벨칼레도니540NCL_NC' AS NAT_STR FROM DUAL UNION ALL
SELECT '뉴질랜드554NZL_NZ' AS NAT_STR FROM DUAL UNION ALL
SELECT '니우에570NIU_NU' AS NAT_STR FROM DUAL UNION ALL
SELECT '니제르562NER_NE' AS NAT_STR FROM DUAL UNION ALL
SELECT '니카라과558NIC_NI' AS NAT_STR FROM DUAL UNION ALL
SELECT '대한민국410KOR_KR' AS NAT_STR FROM DUAL UNION ALL
SELECT '덴마크208DNK_DK' AS NAT_STR FROM DUAL UNION ALL
SELECT '도미니카212DMA_DM' AS NAT_STR FROM DUAL UNION ALL
SELECT '도미니카 공화국214DOM_DO' AS NAT_STR FROM DUAL UNION ALL
SELECT '독일276DEU_DE' AS NAT_STR FROM DUAL UNION ALL
SELECT '동티모르626TLS_TL' AS NAT_STR FROM DUAL UNION ALL
SELECT '라오스418LAO_LA' AS NAT_STR FROM DUAL UNION ALL
SELECT '라이베리아430LBR_LR' AS NAT_STR FROM DUAL UNION ALL
SELECT '라트비아428LVA_LV' AS NAT_STR FROM DUAL UNION ALL
SELECT '러시아643RUS_RU' AS NAT_STR FROM DUAL UNION ALL
SELECT '레바논422LBN_LB' AS NAT_STR FROM DUAL UNION ALL
SELECT '레소토426LSO_LS' AS NAT_STR FROM DUAL UNION ALL
SELECT '레위니옹638REU_RE' AS NAT_STR FROM DUAL UNION ALL
SELECT '루마니아642ROU_RO' AS NAT_STR FROM DUAL UNION ALL
SELECT '룩셈부르크442LUX_LU' AS NAT_STR FROM DUAL UNION ALL
SELECT '르완다646RWA_RW' AS NAT_STR FROM DUAL UNION ALL
SELECT '리비아434LBY_LY' AS NAT_STR FROM DUAL UNION ALL
SELECT '리투아니아440LTU_LT' AS NAT_STR FROM DUAL UNION ALL
SELECT '리히텐슈타인438LIE_LI' AS NAT_STR FROM DUAL UNION ALL
SELECT '마다가스카르450MDG_MG' AS NAT_STR FROM DUAL UNION ALL
SELECT '마르티니크474MTQ_MQ' AS NAT_STR FROM DUAL UNION ALL
SELECT '마셜 제도584MHL_MH' AS NAT_STR FROM DUAL UNION ALL
SELECT '마요트175MYT_YT' AS NAT_STR FROM DUAL UNION ALL
SELECT '마카오446MAC_MO' AS NAT_STR FROM DUAL UNION ALL
SELECT '마케도니아 공화국807MKD_MK' AS NAT_STR FROM DUAL UNION ALL
SELECT '말라위454MWI_MW' AS NAT_STR FROM DUAL UNION ALL
SELECT '말레이시아458MYS_MY' AS NAT_STR FROM DUAL UNION ALL
SELECT '말리466MLI_ML' AS NAT_STR FROM DUAL UNION ALL
SELECT '맨 섬833IMN_IM' AS NAT_STR FROM DUAL UNION ALL
SELECT '멕시코484MEX_MX' AS NAT_STR FROM DUAL UNION ALL
SELECT '모나코492MCO_MC' AS NAT_STR FROM DUAL UNION ALL
SELECT '모로코504MAR_MA' AS NAT_STR FROM DUAL UNION ALL
SELECT '모리셔스480MUS_MU' AS NAT_STR FROM DUAL UNION ALL
SELECT '모리타니478MRT_MR' AS NAT_STR FROM DUAL UNION ALL
SELECT '모잠비크508MOZ_MZ' AS NAT_STR FROM DUAL UNION ALL
SELECT '몬테네그로499MNE_ME' AS NAT_STR FROM DUAL UNION ALL
SELECT '몬트세랫500MSR_MS' AS NAT_STR FROM DUAL UNION ALL
SELECT '몰도바498MDA_MD' AS NAT_STR FROM DUAL UNION ALL
SELECT '몰디브462MDV_MV' AS NAT_STR FROM DUAL UNION ALL
SELECT '몰타470MLT_MT' AS NAT_STR FROM DUAL UNION ALL
SELECT '몽골496MNG_MN' AS NAT_STR FROM DUAL UNION ALL
SELECT '미국840USA_US' AS NAT_STR FROM DUAL UNION ALL
SELECT '미국령 군소 제도581UMI_UM' AS NAT_STR FROM DUAL UNION ALL
SELECT '미국령 버진아일랜드850VIR_VI' AS NAT_STR FROM DUAL UNION ALL
SELECT '미얀마104MMR_MM' AS NAT_STR FROM DUAL UNION ALL
SELECT '미크로네시아 연방583FSM_FM' AS NAT_STR FROM DUAL UNION ALL
SELECT '바누아투548VUT_VU' AS NAT_STR FROM DUAL UNION ALL
SELECT '바레인48BHR_BH' AS NAT_STR FROM DUAL UNION ALL
SELECT '바베이도스52BRB_BB' AS NAT_STR FROM DUAL UNION ALL
SELECT '바티칸 시국336VAT_VA' AS NAT_STR FROM DUAL UNION ALL
SELECT '바하마44BHS_BS' AS NAT_STR FROM DUAL UNION ALL
SELECT '방글라데시50BGD_BD' AS NAT_STR FROM DUAL UNION ALL
SELECT '버뮤다60BMU_BM' AS NAT_STR FROM DUAL UNION ALL
SELECT '베냉204BEN_BJ' AS NAT_STR FROM DUAL UNION ALL
SELECT '베네수엘라862VEN_VE' AS NAT_STR FROM DUAL UNION ALL
SELECT '베트남704VNM_VN' AS NAT_STR FROM DUAL UNION ALL
SELECT '벨기에56BEL_BE' AS NAT_STR FROM DUAL UNION ALL
SELECT '벨라루스112BLR_BY' AS NAT_STR FROM DUAL UNION ALL
SELECT '벨리즈84BLZ_BZ' AS NAT_STR FROM DUAL UNION ALL
SELECT '보스니아 헤르체고비나70BIH_BA' AS NAT_STR FROM DUAL UNION ALL
SELECT '보츠와나72BWA_BW' AS NAT_STR FROM DUAL UNION ALL
SELECT '볼리비아68BOL_BO' AS NAT_STR FROM DUAL UNION ALL
SELECT '부룬디108BDI_BI' AS NAT_STR FROM DUAL UNION ALL
SELECT '부르키나파소854BFA_BF' AS NAT_STR FROM DUAL UNION ALL
SELECT '부베 섬74BVT_BV' AS NAT_STR FROM DUAL UNION ALL
SELECT '부탄64BTN_BT' AS NAT_STR FROM DUAL UNION ALL
SELECT '북마리아나 제도580MNP_MP' AS NAT_STR FROM DUAL UNION ALL
SELECT '불가리아100BGR_BG' AS NAT_STR FROM DUAL UNION ALL
SELECT '브라질76BRA_BR' AS NAT_STR FROM DUAL UNION ALL
SELECT '브루나이96BRN_BN' AS NAT_STR FROM DUAL UNION ALL
SELECT '사모아882WSM_WS' AS NAT_STR FROM DUAL UNION ALL
SELECT '사우디아라비아682SAU_SA' AS NAT_STR FROM DUAL UNION ALL
SELECT '사우스조지아 사우스샌드위치 제도239SGS_GS' AS NAT_STR FROM DUAL UNION ALL
SELECT '산마리노674SMR_SM' AS NAT_STR FROM DUAL UNION ALL
SELECT '상투메 프린시페678STP_ST' AS NAT_STR FROM DUAL UNION ALL
SELECT '생피에르 미클롱666SPM_PM' AS NAT_STR FROM DUAL UNION ALL
SELECT '서사하라732ESH_EH' AS NAT_STR FROM DUAL UNION ALL
SELECT '세네갈686SEN_SN' AS NAT_STR FROM DUAL UNION ALL
SELECT '세르비아688SRB_RS' AS NAT_STR FROM DUAL UNION ALL
SELECT '세이셸690SYC_SC' AS NAT_STR FROM DUAL UNION ALL
SELECT '세인트루시아662LCA_LC' AS NAT_STR FROM DUAL UNION ALL
SELECT '세인트빈센트 그레나딘670VCT_VC' AS NAT_STR FROM DUAL UNION ALL
SELECT '세인트키츠 네비스659KNA_KN' AS NAT_STR FROM DUAL UNION ALL
SELECT '세인트헬레나654SHN_SH' AS NAT_STR FROM DUAL UNION ALL
SELECT '소말리아706SOM_SO' AS NAT_STR FROM DUAL UNION ALL
SELECT '솔로몬 제도90SLB_SB' AS NAT_STR FROM DUAL UNION ALL
SELECT '수단736SDN_SD' AS NAT_STR FROM DUAL UNION ALL
SELECT '수리남740SUR_SR' AS NAT_STR FROM DUAL UNION ALL
SELECT '스리랑카144LKA_LK' AS NAT_STR FROM DUAL UNION ALL
SELECT '스발바르 얀마옌744SJM_SJ' AS NAT_STR FROM DUAL UNION ALL
SELECT '스와질란드748SWZ_SZ' AS NAT_STR FROM DUAL UNION ALL
SELECT '스웨덴752SWE_SE' AS NAT_STR FROM DUAL UNION ALL
SELECT '스위스756CHE_CH' AS NAT_STR FROM DUAL UNION ALL
SELECT '스페인724ESP_ES' AS NAT_STR FROM DUAL UNION ALL
SELECT '슬로바키아703SVK_SK' AS NAT_STR FROM DUAL UNION ALL
SELECT '슬로베니아705SVN_SI' AS NAT_STR FROM DUAL UNION ALL
SELECT '시리아760SYR_SY' AS NAT_STR FROM DUAL UNION ALL
SELECT '시에라리온694SLE_SL' AS NAT_STR FROM DUAL UNION ALL
SELECT '싱가포르702SGP_SG' AS NAT_STR FROM DUAL UNION ALL
SELECT '아랍에미리트784ARE_AE' AS NAT_STR FROM DUAL UNION ALL
SELECT '아루바533ABW_AW' AS NAT_STR FROM DUAL UNION ALL
SELECT '아르메니아51ARM_AM' AS NAT_STR FROM DUAL UNION ALL
SELECT '아르헨티나32ARG_AR' AS NAT_STR FROM DUAL UNION ALL
SELECT '아메리칸사모아16ASM_AS' AS NAT_STR FROM DUAL UNION ALL
SELECT '아이슬란드352ISL_IS' AS NAT_STR FROM DUAL UNION ALL
SELECT '아이티332HTI_HT' AS NAT_STR FROM DUAL UNION ALL
SELECT '아일랜드372IRL_IE' AS NAT_STR FROM DUAL UNION ALL
SELECT '아제르바이잔31AZE_AZ' AS NAT_STR FROM DUAL UNION ALL
SELECT '아프가니스탄4AFG_AF' AS NAT_STR FROM DUAL UNION ALL
SELECT '안도라20AND_AD' AS NAT_STR FROM DUAL UNION ALL
SELECT '알바니아8ALB_AL' AS NAT_STR FROM DUAL UNION ALL
SELECT '알제리12DZA_DZ' AS NAT_STR FROM DUAL UNION ALL
SELECT '앙골라24AGO_AO' AS NAT_STR FROM DUAL UNION ALL
SELECT '앤티가 바부다28ATG_AG' AS NAT_STR FROM DUAL UNION ALL
SELECT '앵귈라660AIA_AI' AS NAT_STR FROM DUAL UNION ALL
SELECT '에리트레아232ERI_ER' AS NAT_STR FROM DUAL UNION ALL
SELECT '에스토니아233EST_EE' AS NAT_STR FROM DUAL UNION ALL
SELECT '에콰도르218ECU_EC' AS NAT_STR FROM DUAL UNION ALL
SELECT '에티오피아231ETH_ET' AS NAT_STR FROM DUAL UNION ALL
SELECT '엘살바도르222SLV_SV' AS NAT_STR FROM DUAL UNION ALL
SELECT '영국826GBR_GB' AS NAT_STR FROM DUAL UNION ALL
SELECT '영국령 버진아일랜드92VGB_VG' AS NAT_STR FROM DUAL UNION ALL
SELECT '영국령 인도양 지역86IOT_IO' AS NAT_STR FROM DUAL UNION ALL
SELECT '예멘887YEM_YE' AS NAT_STR FROM DUAL UNION ALL
SELECT '오만512OMN_OM' AS NAT_STR FROM DUAL UNION ALL
SELECT '오스트레일리아36AUS_AU' AS NAT_STR FROM DUAL UNION ALL
SELECT '오스트리아40AUT_AT' AS NAT_STR FROM DUAL UNION ALL
SELECT '온두라스340HND_HN' AS NAT_STR FROM DUAL UNION ALL
SELECT '올란드 제도248ALA_AX' AS NAT_STR FROM DUAL UNION ALL
SELECT '요르단400JOR_JO' AS NAT_STR FROM DUAL UNION ALL
SELECT '우간다800UGA_UG' AS NAT_STR FROM DUAL UNION ALL
SELECT '우루과이858URY_UY' AS NAT_STR FROM DUAL UNION ALL
SELECT '우즈베키스탄860UZB_UZ' AS NAT_STR FROM DUAL UNION ALL
SELECT '우크라이나804UKR_UA' AS NAT_STR FROM DUAL UNION ALL
SELECT '왈리스 퓌튀나876WLF_WF' AS NAT_STR FROM DUAL UNION ALL
SELECT '이라크368IRQ_IQ' AS NAT_STR FROM DUAL UNION ALL
SELECT '이란364IRN_IR' AS NAT_STR FROM DUAL UNION ALL
SELECT '이스라엘376ISR_IL' AS NAT_STR FROM DUAL UNION ALL
SELECT '이집트818EGY_EG' AS NAT_STR FROM DUAL UNION ALL
SELECT '이탈리아380ITA_IT' AS NAT_STR FROM DUAL UNION ALL
SELECT '인도네시아360IDN_ID' AS NAT_STR FROM DUAL UNION ALL
SELECT '인도356IND_IN' AS NAT_STR FROM DUAL UNION ALL
SELECT '일본392JPN_JP' AS NAT_STR FROM DUAL UNION ALL
SELECT '자메이카388JAM_JM' AS NAT_STR FROM DUAL UNION ALL
SELECT '잠비아894ZMB_ZM' AS NAT_STR FROM DUAL UNION ALL
SELECT '저지 섬832JEY_JE' AS NAT_STR FROM DUAL UNION ALL
SELECT '적도 기니226GNQ_GQ' AS NAT_STR FROM DUAL UNION ALL
SELECT '조선민주주의인민공화국408PRK_KP' AS NAT_STR FROM DUAL UNION ALL
SELECT '중앙아프리카 공화국140CAF_CF' AS NAT_STR FROM DUAL UNION ALL
SELECT '중화민국158TWN_TW' AS NAT_STR FROM DUAL UNION ALL
SELECT '중화인민공화국156CHN_CN' AS NAT_STR FROM DUAL UNION ALL
SELECT '지부티262DJI_DJ' AS NAT_STR FROM DUAL UNION ALL
SELECT '지브롤터292GIB_GI' AS NAT_STR FROM DUAL UNION ALL
SELECT '짐바브웨716ZWE_ZW' AS NAT_STR FROM DUAL UNION ALL
SELECT '차드148TCD_TD' AS NAT_STR FROM DUAL UNION ALL
SELECT '체코203CZE_CZ' AS NAT_STR FROM DUAL UNION ALL
SELECT '칠레152CHL_CL' AS NAT_STR FROM DUAL UNION ALL
SELECT '카메룬120CMR_CM' AS NAT_STR FROM DUAL UNION ALL
SELECT '카보베르데132CPV_CV' AS NAT_STR FROM DUAL UNION ALL
SELECT '카자흐스탄398KAZ_KZ' AS NAT_STR FROM DUAL UNION ALL
SELECT '카타르634QAT_QA' AS NAT_STR FROM DUAL UNION ALL
SELECT '캄보디아116KHM_KH' AS NAT_STR FROM DUAL UNION ALL
SELECT '캐나다124CAN_CA' AS NAT_STR FROM DUAL UNION ALL
SELECT '케냐404KEN_KE' AS NAT_STR FROM DUAL UNION ALL
SELECT '케이맨 제도136CYM_KY' AS NAT_STR FROM DUAL UNION ALL
SELECT '코모로174COM_KM' AS NAT_STR FROM DUAL UNION ALL
SELECT '코스타리카188CRI_CR' AS NAT_STR FROM DUAL UNION ALL
SELECT '코코스 제도166CCK_CC' AS NAT_STR FROM DUAL UNION ALL
SELECT '코트디부아르384CIV_CI' AS NAT_STR FROM DUAL UNION ALL
SELECT '콜롬비아170COL_CO' AS NAT_STR FROM DUAL UNION ALL
SELECT '콩고 공화국178COG_CG' AS NAT_STR FROM DUAL UNION ALL
SELECT '콩고 민주 공화국180COD_CD' AS NAT_STR FROM DUAL UNION ALL
SELECT '쿠바192CUB_CU' AS NAT_STR FROM DUAL UNION ALL
SELECT '쿠웨이트414KWT_KW' AS NAT_STR FROM DUAL UNION ALL
SELECT '쿡 제도184COK_CK' AS NAT_STR FROM DUAL UNION ALL
SELECT '크로아티아191HRV_HR' AS NAT_STR FROM DUAL UNION ALL
SELECT '크리스마스 섬162CXR_CX' AS NAT_STR FROM DUAL UNION ALL
SELECT '키르기스스탄417KGZ_KG' AS NAT_STR FROM DUAL UNION ALL
SELECT '키리바시296KIR_KI' AS NAT_STR FROM DUAL UNION ALL
SELECT '키프로스196CYP_CY' AS NAT_STR FROM DUAL UNION ALL
SELECT '타이764THA_TH' AS NAT_STR FROM DUAL UNION ALL
SELECT '타지키스탄762TJK_TJ' AS NAT_STR FROM DUAL UNION ALL
SELECT '탄자니아834TZA_TZ' AS NAT_STR FROM DUAL UNION ALL
SELECT '터크스 케이커스 제도796TCA_TC' AS NAT_STR FROM DUAL UNION ALL
SELECT '터키792TUR_TR' AS NAT_STR FROM DUAL UNION ALL
SELECT '토고768TGO_TG' AS NAT_STR FROM DUAL UNION ALL
SELECT '토켈라우772TKL_TK' AS NAT_STR FROM DUAL UNION ALL
SELECT '통가776TON_TO' AS NAT_STR FROM DUAL UNION ALL
SELECT '투르크메니스탄795TKM_TM' AS NAT_STR FROM DUAL UNION ALL
SELECT '투발루798TUV_TV' AS NAT_STR FROM DUAL UNION ALL
SELECT '튀니지788TUN_TN' AS NAT_STR FROM DUAL UNION ALL
SELECT '트리니다드 토바고780TTO_TT' AS NAT_STR FROM DUAL UNION ALL
SELECT '파나마591PAN_PA' AS NAT_STR FROM DUAL UNION ALL
SELECT '파라과이600PRY_PY' AS NAT_STR FROM DUAL UNION ALL
SELECT '파키스탄586PAK_PK' AS NAT_STR FROM DUAL UNION ALL
SELECT '파푸아 뉴기니598PNG_PG' AS NAT_STR FROM DUAL UNION ALL
SELECT '팔라우585PLW_PW' AS NAT_STR FROM DUAL UNION ALL
SELECT '팔레스타인275PSE_PS' AS NAT_STR FROM DUAL UNION ALL
SELECT '페로 제도234FRO_FO' AS NAT_STR FROM DUAL UNION ALL
SELECT '페루604PER_PE' AS NAT_STR FROM DUAL UNION ALL
SELECT '포르투갈620PRT_PT' AS NAT_STR FROM DUAL UNION ALL
SELECT '포클랜드 제도238FLK_FK' AS NAT_STR FROM DUAL UNION ALL
SELECT '폴란드616POL_PL' AS NAT_STR FROM DUAL UNION ALL
SELECT '푸에르토리코630PRI_PR' AS NAT_STR FROM DUAL UNION ALL
SELECT '프랑스250FRA_FR' AS NAT_STR FROM DUAL UNION ALL
SELECT '프랑스령 기아나254GUF_GF' AS NAT_STR FROM DUAL UNION ALL
SELECT '프랑스령 남부와 남극 지역260ATF_TF' AS NAT_STR FROM DUAL UNION ALL
SELECT '프랑스령 폴리네시아258PYF_PF' AS NAT_STR FROM DUAL UNION ALL
SELECT '피지242FJI_FJ' AS NAT_STR FROM DUAL UNION ALL
SELECT '핀란드246FIN_FI' AS NAT_STR FROM DUAL UNION ALL
SELECT '필리핀608PHL_PH' AS NAT_STR FROM DUAL UNION ALL
SELECT '핏케언 제도612PCN_PN' AS NAT_STR FROM DUAL UNION ALL
SELECT '허드 맥도널드 제도334HMD_HM' AS NAT_STR FROM DUAL UNION ALL
SELECT '헝가리348HUN_HU' AS NAT_STR FROM DUAL UNION ALL
SELECT '홍콩344HKG_HK' AS NAT_STR FROM DUAL
)
SELECT NAT_STR
FROM W_NAT