sql중급 / sequene

098 절대로 중복되지 않는 번호 만들기(SEQUENE)

특정 테이블에 번호를 반드시 중복되지 않게 순서대로 입력하는 경우에 시퀀스를 사용하면 안전하게 입력할 수 있음.

insert into emp(empno, ename, sal)

  values(1,'scott',3000);

    :

    :                이런식으로 사람이 일일이 수작업으로 입력할 경우 실수할 수 있음. 

                     ex.)오타발생, 일일이 데이터를 조회해서 이전 번호를 찾아야 함(다음번호 부여를 위해서)

--(시퀀스 사용)사원번호에 번호를 입력할 때 데이터가 중복되지 않고 순서대로 입력되게 하시오
insert into emp(empno, ename, sal)
 values(seq1.nextval, 'allen',3000);

ㅇ시퀀스 만드는 법

create sequence seq1    -- (시퀀스이름)
start with 1            -- (시작숫자)
increment by 1          -- (증가치)
maxvalue 100;           -- (최대값)


-- 시퀀스 사용하기
select seq1.nextval
 from dual;

ㅇ시퀀스 삭제하기

drop sequence seq1;

ㅇ시퀀스 리스트 확인하기

select sequence_name
 from user_sequences;

문제536. 주식매매에 대한 데이터를 입력하는 테이블을 다음과 같이 생성하시오

create table stock
( stock_id    number(10),
  stock_type   varchar2(20),
  stock_time   date);

문제537. 위의 stock테이블에 stock_id에 중복되지 않은 순차적인 번호를 입력하기 위해 시퀀스를 생성하는데 시작숫자는 1번, max 100000번까지, 증가치는 1, 시퀀스명 seq2로 하시오

create sequence seq2
start with 1
increment by 1
maxvalue 100000;

문제538. stock테이블에 seq2 시퀀스를 이용해서 다음과 같이 데이터를 입력하시오

insert into stock values(seq2.nextval, '매도', sysdate);
insert into stock values(seq2.nextval, '매수', sysdate);

문제539. dept테이블에 deptno에 데이터를 입력하는 시퀀스를 생성하는데 현재 dept테이블에 40번까지 데이터가 있으므로 50번부터 입력되게 하고, 10씩 증가, 200번까지 번호가 부여되게 하시오

create sequence seq3
start with 50
increment by 10
maxvalue 200;

문제540. 위의 시퀀스를 사용해서 dept 테이블에 아래의 데이터를 입력하시오

부서번호 50 / 부서명 AS team / 부서위치 SEOUL

부서번호 60 / 부서명 AS2 team / 부서위치 BUSAN

insert into dept(deptno, dname, loc)
  values( seq3.nextval, 'AS team', 'SEOUL');
  
insert into dept(deptno, dname, loc)
  values( seq3.nextval, 'AS2 team', 'BUSAN');

ㅇ시퀀스 사용시 따로 테이블을 지정하지 않기 때문에 서로 다른 테이블에서 사용하면 자동넘버링이 됨. 따라서 하나의 시퀀스는 하나의 테이블에서 쓰도록 유의해야함.

 

문제541. 카페 데이터게시판에서 '네이버 영화평점'에 대한 csv를 내려받아 테이블로 구성하시오(테이블명 : movie_review)

문제542. movie_review테이블의 컬럼명을 다음과 같이 변경하시오

열1 > m_name / 열2 > m_score / 열3 > m_review

alter table movie_review
 rename column 열1 to m_name;
 
 alter table movie_review
 rename column 열2 to m_score;
 
 alter table movie_review
 rename column 열3 to m_review;

문제543. movie_review 테이블의 있는 영화가 몇개인지 출력하시오

 select count(distinct m_name)
  from movie_review;

ex)1746건의 영화에 대한 10000개의 리뷰를 분석해본 결과, ~ 라고 분석보고서 작성

문제544. 영화 평점이 10점인 영화명, 10점의 갯수를 출력하시오 

select m_name, count(*)
 from movie_review
 where m_score = 10
 group by m_name
 order by 2 desc;

문제545. 위의 결과를 다시 출력하는데 10점의 갯수가 많은 순으로 1위부터 10위까지 출력하시오

create or replace view m_view
as
select m_name, cnt, rank() over (order by cnt desc) 순위
from ( select m_name, count(*) as cnt
          from movie_review
          where m_score = 10
          group by m_name );
          
          
select *
 from m_view
 where 순위 <= 10;

문제546. 긍정단어 한글테이블과 부정단어 한글테이블을 생성하시오

긍정단어 테이블 : positive_kor

부정단어 테이블 : negative_kor

문제547. 영화 모가디슈의 영화평을 출력하시오 

select m_review
 from movie_review
 where m_name = '모가디슈';

문제548. 영화 모가디슈의 영화평을 어절별로 잘라서 출력하시오

select *
 from ( select regexp_substr(m_review,'[^ ]+',1,n) as word
          from movie_review, ( select level as n
                                 from dual
                                 connect by level <= 500)
 where m_name = '모가디슈')
 where word is not null;

문제549. 위의 결과를 view로 만드세요.(뷰이름 : moga)

create view moga
as
select *
 from ( select regexp_substr(m_review,'[^ ]+',1,n) as word
          from movie_review, ( select level as n
                                 from dual
                                 connect by level <= 500)
 where m_name = '모가디슈')
 where word is not null;

문제550. 영화 모가디슈 영화평에는 긍정단어가 더 많은가 부정단어가 더 많은가

-- 긍정단어 : 572건
select count(*)
 from moga m
 where exists (select 'x'
                from positive_kor p
                where p.열1 = m.word );
                
-- 부정단어 : 384건
select count(*)
 from moga m
 where exists (select 'x'
                from negative_kor n
                where n.열1 = m.word ) ;