07월 31일 수 | SQL활용 10 - SQL DB변환과 NCS 시험범위

 

SQL DB변환과 NCS 시험범위

우편번호 테이블로 실습

우편번호 테이블을 가져와서 DB화 시키기.

외부자료를 데이터베이스에 저장하는 법.

변환작업하기를 실습할 것이다.

그 전까지 실습할 때 insert를 이용하여 데이터를 삽입하였지만 그런식으로 데이터를 매번 저장하는 것은

수기로 일일이 작성하는 것과 다를바가 없으므로 프로그래밍을 이용할 이유가 없다.

 

< DB다루는 방법 >

공공데이터 data.go.kr/

 

 

 

 

우편번호 CSV 파일 변환하기

 

 

1. SQL 파일생성

-- post.csv 파일을 가져와서 zipcode 테이블에 저장하기.

 

 

2. 우편번호 테이블 생성

CSV파일을 참고하며 진행해야한다.

* 실습용 구버전임

 

1) 테이블 작성

--CSV 변환 create table zipcode( zipcode char(7)    not null  -- 우편번호(A열) number보다는 char형으로 관리하는게 낫다. ,sido varchar(30)  not null  -- 시,도(B열)  ,gugun varchar(50)           -- 구,군(C열)  ,dong varchar(50)             -- 동(D열) ,li   varchar(50)                -- 리(E열) ,bunji varchar(255)            -- 번지(F열)   ,etc varchar(255)              -- 기타(G열) );

계산이 필요없는 숫자코드들은 char형으로 관리하는 것이 더 좋다.

 

 

2) 데이터 임포트

이렇게 테이블 -> 오른쪽 마우스 -> 데이터 임포트

 

그런데 테이블 drop 해주었음

임포트 하면서 테이블 만들 것이다.

 

2)-1 임포트하기

 

2)-2 테이블 이름 지정

 

 

2)-3 가져올 열 선택

 

 

2)-4 처음 만들었던 테이블처럼 세부설정하기

 

 

 

2)-5 완료, 커밋하고 확인

rownum 설정하고 조회해보았다.

 

 

 

참고: https://offbyone.tistory.com/162

sqldeveloper를 사용하여 export/import 하기

 

 

 

 

 

문제 1) 시도의 갯수를 구하시오

select count(sido) as 시도갯수 from zipcode;

 

 

문제 2) 서울지역의 구의 갯수를 구하시오

 

 

문제 3) 각 시도별, 구군별 동의 갯수를 구하시오

select sido, count(dong) as 동의갯수 from zipcode group by sido; select gugun, count(dong) as 동의갯수 from zipcode group by gugun;

 

 

 

zipcode.ctl 파일만들기

컨트롤하는 파일이다.

이 방법을 쓰기 전에

그전의 테이블 drop 시키고 다시

create table zipcode( zipcode char(7)    not null  -- 우편번호(A열) number보다는 char형으로 관리하는게 낫다. ,sido varchar(30)  not null  -- 시,도(B열)  ,gugun varchar(50)           -- 구,군(C열)  ,dong varchar(50)            -- 동(D열) ,li   varchar(50)            -- 리(E열) ,bunji varchar(255)          -- 번지(F열)   ,etc varchar(255)            -- 기타(G열) );

테이블 생성 후 실행해야한다!!! (안그럼 SQL*Loader-601: For INSERT option, table must be empty.  Error on table ZIPCODE 이런 오류가 생겼었다.)

 

 

1) 파일 작성 후 메모장으로 확장자 ctl로 저장

load data infile 'post.csv' insert into table zipcode fields terminated by ',' trailing nullcols (zipcode,sido,gugun,dong,li,bunji,etc)

 

 

2) CMD로 디렉토리 확인 후 열기

 

 

 

 

도로명주소 DB 저장하기

우편번호 테이블을 가져와서 DB화 시키기.

외부자료를 

 

 

문제 1) 도로명 주소를 DB에 저장하시오.

create table doro ( r_id      char(12)             -- 1. 도로명코드 ,r_kname varchar(80)          -- 2. 도로명 ,r_ename varchar(80)          -- 3. 도로명로마자 ,r_code  char(2)              -- 4.읍면동 일련번호 ,ksido   varchar(40)          -- 5. 시도명 ,esido   varchar(40)          -- 6. 시도명 로마자 ,kgungu  varchar(40)          -- 7. 시군구명 ,egungu  varchar(40)          -- 8. 시군구명 로마자 ,kdong   varchar(40)          -- 9. 읍면동명 ,edong   varchar(40)          -- 10. 읍면동명 로마자 ,sr_id   char(1)              -- 11. 읍면동 구분 ,sr_code char(3)              -- 12. 읍면동 코드 ,r_use   char(1)              -- 13. 사용여부 ,constraint doro_id_pk primary key (r_id, r_code) );

행정안전부에서 제공하는 이 기준표를 보고 테이블을 작성한다.

 

 

테이블을 작성하고 DORO 테이블에서 우클릭해서 데이터를 임포트한다.

 

 

 

 

데이터 임포트 헤더 체크해제 하고 다음

 

 

이미 생성된 테이블이라서 알아서 매치가 되어있음.

 

 

이 메시지가 뜨면 성공한 것임.

테스트 조회를 해보면

 

 

358154행의 정보가 조회되었다.

새삼 우리나라 작은 나라...

 

 

 

문제 2) 관심있는 주제를 선정하여 DB에 저장하시오. (data.go.kr 참조)

 

나는 서울시 내의 제과점 정보를 가져와서 DB에 저장하였다. 

DB csv 파일의 모든 컬럼을 가져온 것은 아니고 일부만 가져왔다.

출처: http://data.seoul.go.kr/dataList/datasetView.do?infId=OA-15224&srvType=F&serviceKind=1¤tPageNo=1

 

 

1) 테이블 생성

create table Sbakery ( bak_no char(10)         -- 1.번호 ,bak_name varchar(255)   -- 2.사업장명 ,bak_add varchar(255)    -- 3.소재지전체주소 ,radd varchar(255)       -- 4.도로명전체주소 ,opendate date          -- 5.개업일자 ,openstate varchar(10)  -- 6.영업상태명 ,bak_postno char(8)     -- 7.소재지우편번호 ,multi char(4)          -- 8.다중이용업소여부 ,bak_size float         -- 9.시설총규모 ,area varchar(255)       --10.영업장주변구분명 ,bak_class varchar(40)  --11.위생업종명 ,bak_emp int            --12.총종업원수 ,bak_phone varchar(20)  --13.전화번호 ,locateX float          --14.위치정보(X) ,locateY float          --15.위치정보(Y) ,onsale varchar(20)     --16.상세영업상태명 );

 

우선 위 컬럼만 남기고 나머지 컬럼은 삭제하고 csv파일을 다시 저장하였다.

 

 

2) 데이터 조회

 

DB에 잘 저장되었는지 확인하기 위해 데이터 조회를 해보았다.

 

select bak_name, opendate, bak_add from sbakery where radd like '%성동구%' order by opendate;

 

사업장명, 개업일자, 소재지전체주소를 조회하는 쿼리문이다.

 

 

 

 

 

시험문제, 범위

 

20190619_Oracle 설치 (테이블스페이스 생성, 사용자계정 생성, 권한부여)

20190722_SQL 활용연습문제

20190723_group

20190724_casewhen

20190727_조인