SQL Table, View

DataBase object 5가지

1. Table

2. View

3. index

4. Sequence

5. Synonym

 

데이터 타입 종류 3가지

1. 문자형 : varchar2, char, long, clob

2. 숫자형 : number

3. 날짜형 : date

 

• Long과 clob을 사용해야하는 경우

  : 긴 텍스트 문장을 db에 저장할 때 사용해야한다.

  텍스트 문장을 데이터 분석가들이 분석을 한다.

 

   Long은 최대 2기가까지 문자열을 입력할 수 있다.

   Clob은 최대 4기가까지 문자열을 입력할 수 있다.

   Blob은 이미지나 동영상을 저장할 때 사용하는 데이터 타입

 

 

Ex) create table emp700

    (ename varchar2(20),

     age number(10),

     self_intro long);

 

   insert into emp700

   values('김인호',27,'어렸을때 부터 우리집은 가난했었고 어머님은 짜장면이 싫다고 하셨습니다.');

 

* 테이블 리스트 확인 방법

-> select * from user_tables;

 

* 테이블 만든 날짜 확인하기

-> select object_name, object_type, created

    from user_objects

    where object_name = 'EMP801';

    => 테이블 emp801의 생성 관련 정보를 알 수가 있다.

 

• Regexp_count

   : regular expression (정규표현식)

 

   

    ^ - not

   [^ ]+ 공백이 아닌 단어단위

   Regexp_substr('문자열','[^ ]+', 1, 1) -> 문자열에서 공백이 아닌 철자 여러 개 즉 단어를 찾는데

                                                     처음부터 읽어서 처음 만나는 단어 잘라내는 것

---

• View

    복잡한 sql을 단순하게 만들기 위한 db object

    보안상 이유로 공개하면 안되는 데이터를 감출때 사용하는 DB object

 

   

    Creat view 이름

    As

    Select 문

 

   -> 뷰의 데이터 변경 시, 테이블 데이터도 변경이 된다.

 

  

내가 소유하고 있는 view를 확인하는 방법

-> select view_name text

    from user_views;

 

•  View의 종류 2가지

   - 단순 view 

     : Table 개수 1개, Group 함수 포함 x, group by 포함 하지 않는다, DML 여부 가능

 

   - 복합 view

     : 테이블 개수 2개이상, group 함수 포함, group by 포함, DML문 불가능할 수도 있음

 

     ex) 단순 view 생성 예제

          create view emp905

          as

          select empno, ename, sal from emp

 

     ex) 복합 view 생성 예제

          create view emp906

          as

          select job, sum(sal) 토탈

          from emp

          group by job

 

 

   

• View 삭제하기

      Drop view 이름

 

---

문제 334. 영화 겨울왕국 대본을 winter라는 테이블로 생성하세요

-> Create table winter_kingdom

    (win_text varchar2(4000));

Create table winter_kingdom
(win_text varchar2(4000));

 

문제 335. 영화 겨울왕국에 elsa가 많이 나오는지, anna가 많이 나오는지 확인

Select regexp_count(lower(win_text),'elsa') as cnt

From winter_kingdom;

Select regexp_count(lower(win_text),'elsa') as cntFrom winter_kingdom;

 

• Regexp_count

   : regular expression (정규표현식)

     

     - Anna 나오는 횟수

       Select sum(regexp_count(lower(win_text),'anna')) as cnt

       From winter_kingdom;

 

 

문제 336. 겨울왕국의 대본을 단어별로 나누세요

-> select regexp_substr(lower(win_text),'[^ ]+',1,1) word

    from winter_kingdom;

 

문제 337. 겨울왕국 대본에는 긍정 단어가 더 많은가 부정단어가 더 많은가

Create table positive

(p_text varchar2(2000));

 

Create table negative

(n_text varchar2(2000));

 

- 긍정 단어가 몇개인지?

select count(*) from(

Select regexp_substr(lower(win_text),'[^ ]+',1,num) word

From winter_kingdom, (select level as num from dual connect by level<=15))

where word in (select lower(p_text) from positive);

select count(*) from(Select regexp_substr(lower(win_text),'[^ ]+',1,num) word
From winter_kingdom, (select level as num from dual connect by level<=15))
where word in (select lower(p_text) from positive);

 

- 어절 단위 생성

Select regexp_substr(lower(win_text),'[^ ]+',1,num) word

From winter_kingdom, (select level as num from dual connect by level<=15);

Select regexp_substr(lower(win_text),'[^ ]+',1,num) word
From winter_kingdom, (select level as num from dual connect by level<=15);

 

문제 340. 뷰를 사용해서 아래의 sql을 심플하게 하세요

create view win_view

as

select regexp_substr(lower(win_text),'[^ ]+',1,num) word from winter_kingdom,

(select level as num from dual connect by level<=14);

create view win_viewasselect regexp_substr(lower(win_text),'[^ ]+',1,num) word 
from winter_kingdom,(select level as num from dual connect by level<=14);

 

select word, count(*)

from win_view

where word is not null

group by word

order by count(*) desc;

select word, count(*)
from win_viewwhere word is not nullgroup by wordorder by count(*) desc;

 

문제 341. 만약 emp900의 scott의 월급을 0으로 변경하면, emp 테이블도 변경이 되는지 확인하세요

update emp900

set sal = 0

where ename = 'SCOTT';

 

->결과가 변경되는 것을 확인할 수가 있다.

   뷰 변경시,  테이블도 변경이 되는데 그 이유는 view는 테이블을 바라보는 object이기 때문이다.

 

 

문제 342. emp 906의 직업이 salesman의 토탈을 2000으로 수정하세요

update emp906

set 토탈 = 2000

where job = 'SALESMAN';

update emp906
set 토탈 = 2000where job = 'SALESMAN';

-> 변경되지 않는다. 수정 불가능!

 

문제 343. 이름, 월급, 부서위치를 출력하는 쿼리의 결과를 view로 생성하세요

create view emp301

as

select e.ename, e.sal, d.loc

from emp e, dept d

where e.deptno = d.deptno;

create view emp301
as
select e.ename, e.sal, d.loc
from emp e, dept d
where e.deptno = d.deptno;

 

문제 344. emp301의 결과에서 king의 부서위치를 washington으로 변경하세요

update emp301

set loc = 'WASHINGTON'

where ename = 'KING'

update emp301
set loc = 'WASHINGTON'
where ename = 'KING'

-> 수정되지 않는다.

 

문제 345. 남자와 여자가 각각 많이 걸리는 암을 출력하세요

국립암센터_24개암종 암발생률_2018년 12월.csv

0.00MB

select * from(

select distinct 암종, 성별, 환자수, rank() over(partition by 성별 order by 환자수 desc) 등급

from cancer

where 환자수 is not null and 성별 != '남녀전체' and 암종!='모든암')

where 등급 = 1 ;

select * from(select distinct 암종, 성별, 환자수, 
rank() over(partition by 성별 order by 환자수 desc) 등급
from cancerwhere 환자수 is not null and 성별 != '남녀전체' and 암종!='모든암')
where 등급 = 1 ;

 

문제 346. 케글의 타이타닉 데이터를 오라클에 테이블로 구성하세요

  create table train_titanic

  (passengerId number(5),

 survived number(5),

 pclass number(10),

 name varchar2(100),

 sex varchar2(20),

 age number(5,2),

 sibsp number(5),

 parch number(5),

 ticket varchar2(20),

 fare number(18,5),

 cabin varchar2(50),

 embarked varchar2(5));

 

 

문제 347. 남자 승객수와 여자 승객수가 각각 어떻게 되는지 출력하세요

select sex, count(*)

from train_titanic

group by sex;

 

문제 348. 타이타닉호가 운항중에 들린 항구명

select embarked

from train_titanic

group by embarked;

 

select distinct embarked

from train_titanic

;