093 일반 테이블 생성하기(CREATE TABLE)
ㅁSQL의 종류 5가지
1. Query : select문의 6가지절, 함수, 조인, 집합연산자, 서브쿼리
2. DML : insert, update, delete, merge
3. DDL : create, alter, drop, truncate, rename
4. DCL : grant, revoke
5. TCL : commit, rollback, savepoint
ㅁcreate문으로 만들 수 있는 데이터베이스 오브젝트 5개
1. table : 데이터베이스에 데이터를 저장하는 기본 저장소
2. view : 복잡한 쿼리를 간단하게 엑세스 하기 위한 db object
3. index : 대용량 데이터에서 특정 데이터의 검색을 빠르게 할 수 있음
4. sequence : 번호 생성기
5. synonym : 동의어(emp 테이블의 또 다른 이름을 부여할 수 있음)
--사원번호, 이름, 월급, 입사일을 저장하는 테이블을 다음과 같이 생성하시오
create table emp93
(empno number(10),
ename varchar2(10),
sal number(10,2),
hiredate date );- 테이블명과 컬럼명을 지정할때는 문자로 시작해야함(영어,한글 다 가능하나 숫자만 안됨)
- number(10) = 숫자형 데이터타입, 자릿수 10자리
- varchar2(10) = 문자형 데이터타입, 자릿수 10자리 ( 알파벳 10자 허용)
- number(10,2) = 숫자형 데이터타입, 자릿수 10자리 중 2자리는 소수점는 입력가능
- date = 날짜형 데이터타입, 9999/12/31일까지 입력가능
문제477. 위에서 만든 emp93테이블에 다음의 데이터를 입력하시오
empno 3829
ename JACK
sal 3000
hiredate 2021/04/29
insert into emp93(empno, ename, sal, hiredate)
values(3829, 'JACK',3000, to_date('2021/04/29','RRRR/MM/DD'));
ㅇchar와 varchar2의 차이점
| char | varchar2 | |
| 데이터유형 | 문자형 | 문자형 |
| 특징 | 고정형 | 가변형 |
| 허용길이 | 2000byte | 4000byte |
varchar2는 데이터를 입력하고 남은 공간을 회수해버리면서 저장공간 낭비가 생기지 않게함.
| create table test01 ( ename varchar2(10), job varchar2(1000) ); insert into test01 velues('scott','analyst'); |
위와같이 테이블을 생성하고 데이터를 입력하였을 때,
job의 경우 varchar2(1000)으로 1000개의 철자가 허용되게끔 길이를 지정하고 만들었지만 실제로 입력되는 철자는 analyst 7개의 철자가 입력되어 나머지 993개의 공간은 오라클이 회수해버림.
반면에 char(1000)로 지정했다면 위와 같이 데이터를 입력한 상황에서 공간 회수를 하지 못함. 저장공간 낭비가 생기게 됨. 따라서 공간을 회수하면 안되는 테이블의 경우에 char로 데이터유형을 생성하여 사용하게 됨.
ㅇ데이터 분석할 경우, char와 varchar가 섞여있는 테이블들의 데이터를 조인할 때 발생할 수 있는 문제점
drop table emp700;
drop table dept700;
create table emp700
( ename varchar2(10),
deptno varchar2(10) );
insert into emp700 values('scott','10');
insert into emp700 values('allen','20');
-- varchar2(10)으로 10자리를 확보했는데 입력할 때 2자리만 사용했으므로 나머지 8자리는 회수했을 것
create table dept700
( deptno char(10),
loc char(10) );
insert into dept700 values('10','dallas');
insert into dept700 values('20','chicago');
select *
from dept700
where loc = 'dallas';
-- 데이터는 'dallas ' 이렇게 10자리에 맞춰 입력되어 있을텐데,
-- 위와같이 select해도 결과가 나오는 이유는 오라클이 알아서 공백을 제거해서 보여주기때문.select e.ename, d.loc
from emp700 e, dept700 d
where e.deptno = d.deptno;
/* 다만, 조인문 결과가 출력되지 않음. 두 테이블의 데이터타입이 같았다면 결과가 출력될텐데
하나는 varchar2이고 하나는 char라서 조인된 결과가 출력되지 않음
> 따라서 현업에서는 보통 varchar2로 문자형을 구성하고 특별한 데이터만 char로 구성함 */
-- 데이터유형 확인하는 법
describe emp700;
describe dept700;
-- 데이터타입을 바꿀 수 없을 때, 조인하기 위해 데이터 가공하는 법
select e.ename, d.loc
from emp700 e, dept700 d
where e.deptno = substr(d.deptno,1,2);
/* e.deptno는 varchar2라서 공백이 8개 회수된 상태인데 d.deptno는 char(10)라서 공백이 8개 회수되지 않은 상태.
따라서 조인하면 결과가 나오지 않으므로 substr을 사용하여 2바이트로 만들어줌 */
ㅇlong과 clob은 아주 큰 텍스트 데이터를 입력할 때 사용하는 데이터유형
long : 최대 2GB까지 문자 데이터를 저장할 수 있음
clob : 최대 4GB까지 문자 데이터를 저장할 수 있음
--long데이터 유형을 사용해서 테이블 생성하기
create table resume
( ename varchar2(20),
self_intro long );
insert into resume
values('김인호','어렸을때부터 우리집은 가난했었고 어머니는 짜장면이 싫다고 하셨어요. 야히 야히야' );텍스트 데이터 분석 > 이 텍스트에는 긍정적 단어가 많은지 부정적 단어가 많은지 쿼리 하나로 확인가능(감정분석)
--예제. 스티븐 잡스 연설문에 가장 많이 나오는 영어 단어는 무엇인가?
create table speech
(speech_text varchar2(1000) );(테이블에 데이터 입력하는 방법 책 315페이지 참고)
ㅁ문장을 어절별로 나누는 방법
select regexp_substr('I never graduated from college','[^ ]+',1,2) as word
from dual;
/* 설명
regexp_substr함수는 substr함수를 업그레이드한 함수로 텍스트 데이터를 전처리할 때 아주 유용
위의 수식은 I never graduated from college에서 공백이 아닌(^) 여러개(+)인 것을 찾는데
처음부터 읽어서 두번째인 것을 출력하라는 뜻.
regexp_substr은 substr보다 좀 더 자세하게 텍스트 데이터를 잘라낼 수 있음.
select regexp_substr('I never graduated from college','[^ ]+',1,a) as word
from dual, (1부터 10까지 출력하는 쿼리문) 이 자리의 숫자가 바뀌어서 어절을 쪼갬
select level as a
from dual
connect by level <=10
where절 없이 dual과 인라인뷰가 조인했으므로 a에 1부터 10까지의 숫자가 계속 제공되면서 함수를 실행 */
select regexp_substr('I never graduated from college','[^ ]+',1,a) as word
from dual,( select level as a
from dual
connect by level <=10 );문제478. (점심시간 문제) 위의 sql의 결과에서 word에 null값은 제외하고 출력하시오
select *
from ( select regexp_substr('I never graduated from college','[^ ]+',1,a) as word
from dual,( select level as a
from dual
connect by level <=10 ) )
where word is not null;
문제479. 위의 코드에서 dual 대신에 speech를 from절로 가져오시오
select *
from (
select regexp_substr(speech_text,'[^ ]+',1,a) as word
from speech,( select level as a
from dual
connect by level <=10 )
)
where word is not null;문제480. 위의 어절별로 출력되는 어절들이 전부 소문자로만 출력되게 하시오
select *
from (
select lower(regexp_substr(speech_text , '[^ ]+', 1, a)) as word
from speech, ( select level as a
from dual
connect by level <= 10)
)
where word is not null;
--lower를 어디다 쓰든 상관은 없음. lower(speech_text)로 작성가능문제481. 영국 옥스포드에서 영어의 거의 모든 문장을 컴퓨터에 집어넣고 통계를 내보았더니 대략 100단어 정도가 영어문장의 거의 50%를 차지하는 것을 확인했다고 합니다. 내가 아는 단어들은 지금 대충 세어보아도 100개는 충분히 넘는데 왜 나는 영어문장을 50%도 이해 못하나 라는 질문을 하실 수 있는데 여기에 또 다른 비밀이 숨어있습니다. 그 100단어는 대부분 '전치사'입니다. - 책 에로우 잉글리쉬 중
이 말이 진짜로 맞는지 스티브 잡스 연설문으로 확인하세요.
어절, 어절별 건수를 출력하시오
select distinct word, count(*)
from (
select regexp_substr(lower(speech_text) , '[^ ]+', 1, a) as word
from speech, ( select level as a
from dual
connect by level <= 100)
)
where word is not null
group by word;문제482. 위의 결과를 다시 출력하는데 건수가 높은것부터 출력하시오
select distinct word, count(*)
from (
select regexp_substr(lower(speech_text) , '[^ ]+', 1, a) as word
from speech, ( select level as a
from dual
connect by level <= 100)
)
where word is not null
group by word
order by 2 desc;
데이터 분석의 텍스트 데이터 분석에서 감정분석이 있음. 어떤글이 긍정적인지 부정적인지 확인하는 데이터 분석법
문제483. 특정 텍스트에 긍정적인 단어가 많은지 부정적인 단어가 많은지를 분석하기 위해 아래의 테이블 2개를 생성하시오
create table positive
( p_text varchar2(2000) );
create table negative
(n_text varchar2(2000) );
(데이터 카페에서 다운로드)
문제484. speech를 어절별로 나눈 데이터를 view로 생성하시오.
create view v_speech
as
select *
from (
select regexp_substr(lower(speech_text) , '[^ ]+', 1, a) as word
from speech, ( select level as a
from dual
connect by level <= 100)
)
where word is not null;
문제485. v_speech view와 positive테이블을 이용해서 스티브 잡스 연설문에 긍정단어가 몇개 있는지 확인하시오
select *
from v_speech v
where exists (select 'x'
from positive p
where p.p_text = v.word);
--68건의 긍정단어가 검색됨문제486. 위의 sql을 이용해서 스티브 잡스가 가장 많이 사용한 긍정단어는 무엇인지 출력하시오
select word, count(*)
from v_speech v
where exists (select 'x'
from positive p
where p.p_text = v.word)
group by word
order by 2 desc fetch first 1 rows with ties;문제487. 스티브 잡스 연설문에서 가장 많이 나오는 부정단어가 무엇인지 출력하시오
select word, count(*)
from v_speech v
where exists (select 'x'
from negative n
where n.n_text = v.word)
group by word
order by 2 desc fetch first 1 rows with ties;
(데이터 전처리한 view
create or replace view v_speech
as
select *
from ( select lower( rtrim( trim('"' from regexp_substr(speech_text,'[^ ]+',1,n)), ',.:;?!' ) ) as word
from speech, (select level as n
from dual
connect by level <= 100) )
where word is not null;1. 어절에서 양쪽의 따옴표(")를 잘라냄
2. 그 다음에 오른쪽에 콤마(,),마침표(.),콜론(:),세미콜론(;),물음표(?),느낌표(!)를 제거)
문제488. 스티브잡스 연설문에는 긍정단어가 많은지 부정단어가 많은지 각각 건수를 알아내시오
-- 긍정단어 87건
select count(*)
from v_speech v
where exists (select 'x'
from positive n
where n.p_text = v.word);
-- 부정단어 50건
select count(*)
from v_speech v
where exists (select 'x'
from negative n
where n.n_text = v.word);
문제489. 우리반 테이블의 데이터에서 학생이름, 나이, 생일, 주소를 담기위한 테이블을 emp14_2라는 이름으로 생성하시오.
학생이름 name, 나이 age, 생일 birth, 주소 address
create table emp14_2
( name varchar2(20),
age number(2),
birth date,
address varchar2(50) );문제490. emp14테이블에서 학생이름, 나이, 생일, 주소를 emp14_2에 입력하시오
insert into emp14_2(name, age, birth, address)
select ename, age, birth, address
from emp14;
ㅁ컬럼을 삭제, 추가, 변경하는 명령어
1. 컬럼 삭제
| alter table 테이블명 drop column 컬럼명; |
2. 컬럼 추가
| alter table 테이블명 add 컬럼명 데이터타입; |
3. 컬럼 변경
| alter table 테이블명 modify 컬럼명 데이터타입; |
4. 컬럼 감추기
| alter table 테이블명 set unused column 컬럼명; |
감추는 것은 나중에 삭제하기 위해서 미리 감춰놓는 것.
나중에 감춘 컬럼들을 일괄적으로 삭제시킴.
| alter table 테이블명 drop unused columns; |
감춘컬럼 조회하기 > 감춘컬럼의 갯수만 확인할 수 있고 어떤 컬럼을 감췄는지 컬럼명 조회는 불가
| select * from user_unused_col_tabs; |
5. 컬럼 이름 변경하기
| alter table 테이블명 rename column 변경전컬럼명 to 변경후컬럼명; |
문제491. (오늘의 마지막문제) 대검찰청 범죄 발생지 현황.csv를 내려받아 테이블로 구성하고 사기가 가장 많이 일어나는 지역이 어디인지 출력하시오
1. 데이터 임포트로 csv 파일을 불러와 unpivot하여 view를 생성함
--view 생성
create view v_crime
as
select *
from crime_loc_type
unpivot ( 건수 for 지역 in (서울특별시_종로,서울특별시_중구, 서울특별시_용산, 서울특별시_성동, 서울특별시_광진, 서울특별시_동대문,서울특별시_중랑, 서울특별시_강북, 서울특별시_도봉, 서울특별시_노원, 서울특별시_은평, 서울특별시_서대문,서울특별시_마포, 서울특별시_양천, 서울특별시_강서, 서울특별시_구로, 서울특별시_금천, 서울특별시_영등포,서울특별시_동작,서울특별시_성북,서울특별시_관악, 서울특별시_서초, 서울특별시_강남, 서울특별시_송파, 서울특별시_강동,
부산광역시_중구, 부산광역시_서구, 부산광역시_동구, 부산광역시_영도, 부산광역시_부산진,부산광역시_동래, 부산광역시_남구, 부산광역시_북구, 부산광역시_해운대,부산광역시_사하, 부산광역시_금정, 부산광역시_강서, 부산광역시_연재, 부산광역시_수영, 부산광역시_사상, 부산광역시_기장,
대구광역시_중구, 대구광역시_동구, 대구광역시_서구, 대구광역시_남구, 대구광역시_북구, 대구광역시_수성, 대구광역시_달서, 대구광역시_달성,
인천광역시_중구, 인천광역시_동구, 인천광역시_서구, 인천광역시_남구, 인천광역시_연수, 인천광역시_남동, 인천광역시_부평, 인천광역시_계양, 인천광역시_강화, 인천광역시_웅진,
광주광역시_동구, 광주광역시_서구, 광주광역시_남구, 광주광역시_북구, 광주광역시_광산,
대전광역시_중구, 대전광역시_동구, 대전광역시_서구, 대전광역시_유성, 대전광역시_대덕,
울산광역시_중구, 울산광역시_동구, 울산광역시_남구, 울산광역시_북구, 울산광역시_울주,
부천, 수원,성남,전주,안양,청주,마산,창원,광명,포항,안산,진주, 고양,제주,목포,의정부,익산, 군산,구미,천안, 여수, 춘천,원주, 평택,경주, 김해,순천,군포, 남양주, 강릉,충주,안동,경산,아산,거제,김천,정읍, 용인, 시흥,파주, 양산,이천,구리,서산, 제천,논산, 기타도시, 도시이외) );2. 코드작성
select 지역, to_number(건수), rank() over (order by to_number(건수) desc) as 순위
from ( select *
from v_crime
where 범죄종류 like '%사기%' )
order by 순위 fetch first 1 rows only;
'Study > class note' 카테고리의 다른 글
| 문제7. SQL / 데이터 정제(문장부호 제거) (0) | 2021.11.24 |
|---|---|
| 문제6. SQL / 새로운 컬럼에 값을 갱신할 때 null값 없애기 (0) | 2021.11.23 |
| sql중급 / 계층형 질의문3,4 (0) | 2021.11.23 |
| 문제5. SQL / 서브쿼리 튜닝 및 조인 (0) | 2021.11.22 |
| sql중급 / 계층형 질의문1,2 (0) | 2021.11.22 |