[Oracle] days10

오늘 쪽지시험 

1. 오라클 각 DataType 에 대해 상세히 설명하세요
고정길이 char , nchar 
가변길이 varchar2, nvarchar2
long 2gb

char 고정길이 문자자료형 문자1바이트 한글 3바이트
nchar 고정길이 유니코드의 모든 문자를 2바이트로 처리
varchar 가변길이 최대 4000바이트 
nvarchar2 유니코드 +가변길이_+문자열 n은 모든 문자 똑같은 바이트로 저장 
number(p,s) p는 정확한 전체자릿수 s는 소숫점자리수 
이진데이터 raw ,long raw 
long-2gb 
날짜- date, timestamp
2.  emp 테이블에서 [년도별] [월별] 입사사원수 출력.( PIVOT() 함수 사용 )
select *
from (select extract(year from hiredate)year, extract(month from hiredate)month from emp) 
pivot ( count(month) for month in(1,2,3,4,5,6,7,8,9,10));
    [실행결과]
    1982	1	0	0	0	0	0	0	0	0	0	0	0
    1980	0	0	0	0	0	0	0	0	0	0	0	1
    1981	0	2	0	1	1	1	0	0	2	0	1	2
    
2-2.   emp 테이블에서 각 JOB별 입사년도별 1월~ 12월 입사인원수 출력.  ( PIVOT() 함수 사용 ) 


  select * 
  from (select job,extract(year from hiredate)year, extract(month from hiredate)hire_month from emp) 
  pivot( count(*) for hire_month in (1,2,3,4,5,6,7,8,9,10,11,12));
  

    [실행결과]
    ANALYST		1981	0	0	0	0	0	0	0	0	0	0	0	1
    CLERK		1980	0	0	0	0	0	0	0	0	0	0	0	1
    CLERK		1981	0	0	0	0	0	0	0	0	0	0	0	1
    CLERK		1982	1	0	0	0	0	0	0	0	0	0	0	0
    MANAGER		1981	0	0	0	1	1	1	0	0	0	0	0	0
    PRESIDENT	1981	0	0	0	0	0	0	0	0	0	0	1	0
    SALESMAN	1981	0	2	0	0	0	0	0	0       
    
3. emp테이블에서 입사일자가 오래된 순으로 3명 출력 ( TOP 3 )
    [실행결과]
    1	7369	SMITH	CLERK	    7902	80/12/17	800		    20
    2	7499	ALLEN	SALESMAN	7698	81/02/20	1600	300	30
    3	7521	WARD	SALESMAN	7698	81/02/22	1250	500	30    
   select* 
from(select emp.*, rank() over(order by hiredate asc)rank from emp)
where rank<=3;

1.top-n: rowmum 의사칼럼
select *
from(select rownum seq, emp.* 
from emp 
order by hiredate asc)
where seq<=2;

2.rank()
select*
from(select *, rank() over (order by hiredate asc) seq 
from emp )
where seq<=2;
4. SMS 인증번호  임의의  6자리 숫자 출력 ( dbms_random  패키지 사용 )
select 
   substr(ltrim( ltrim(dbms_random.value, '0.'),'0'),0,6)
   ,trunc(dbms_random.value(100000,1000000))
   from dual;
 
--오답
select floor(dbms_random.value(0,10))
,floor(dbms_random.value(0,10))
,floor(dbms_random.value(0,10))
,floor(dbms_random.value(0,10))
,floor(dbms_random.value(0,10))
,floor(dbms_random.value(0,10))
from dual;

4-2. 임의의 대소문자 5글자 출력( dbms_random  패키지 사용 )
select dbms_random.string('a',5)
from dual;

5. 게시글을 저장하는 테이블 생성
create table tbl_test(
--기본키 앞에 constraint로 기본키제약조건 이름 붙이기 
    seq number not null constraints PK_tbl_test_seq primary key
    ,writer varchar2(20) not null
    ,passwd varchar2(20) not null
    ,title varchar(100) not null
    ,content varchar2(300)
    ,regdate date default sysdate
);

   ㄱ.   테이블명 : tbl_test
   ㄴ.   컬럼
         글번호    seq            자료형  크기    널허용여부    고유키
         작성자    writer     
         비밀번호 passwd      
         글제목    title       
         글내용    content
         작성일    regdate   
    ㄷ.  글번호, 작성자, 비밀번호, 글 제목은 필수 입력 사항으로 지정
    ㄹ.  글번호가  기본키( PK )로 지정
    ㅁ.  작성일은 현재 시스템의 날짜로 자동 설정
    
5-2. 조회수    read   컬럼을 추가 ( 기본값 0 으로  설정 ) 
테이블생성 create 
    수정  alter
    삭제  drop

alter table tbl_test
add( read number(20) default 0);

desc tbl_test;

5-3. 글내용    content 컬럼의 자료형을 clob 로 수정 
alter table tbl_member
    modify (content  clob);
    
5-4. 테이블 구조 확인 
desc tbl_test;

5-5. 글제목     title 을   subject로 수정
select title subject from tbl_test; --별칭써도됨 
alter table tbl_test rename column title to subject;

5-6.  tbl_test  -> tbl_board 테이블명 변경 
rename tbl_test to tbl_board;

5-7. CRUD  ( insert, select, update, delete ) 
   ㄱ. 임의의 게시글 5개를 추가 insert
    
    insert into tbl_board(seq,writer, passwd ,content) values (1,'홍길동','abs','a');
     insert into tbl_board(seq,writer, passwd ,content) values (2,'홍길동','abs','a');
      insert into tbl_board(seq,writer, passwd ,content) values (3,'홍길동','abs','a');
 insert into tbl_board(seq,writer, passwd ,content) values (4,'홍길동','abs','a');
  insert into tbl_board(seq,writer, passwd ,content) values (5,'홍길동','abs','a');
  commit; 꼭해야함 
  
   ㄴ. 게시글 조회 select
   select *
   from tbl_board;
   ㄷ. 3번 게시글의 글 제목, 내용 수정 update
   select seq, subject, content
   from tbl_board
   where seq=3;
   
   update tbl_board
   set subject='[e]' || subject, content='[e]' || nvl(content, '내용 무')
   where seq=3;
   
   ㄹ. 4번 게시글삭제 
   delete from tbl_board
   where seq=4; 
   
5-8. tbl_board 테이블 삭제  

drop table tbl_board purge; ---완전삭제 

6-1. 오늘의 날짜와 요일 출력 
 [실행결과]
 
 select sysdate, to_char(sysdate, 'd'),to_char(sysdate, 'dy')  ,to_char(sysdate, 'day') 
 from dual; 
오늘날짜  숫자요일  한자리요일       요일
-------- ---        ------   ------------
22/04/15  6             금      금요일      

6-2. 이번 달의 마지막 날과 날짜만 출력 
select sysdate, last_day(sysdate), to_char(last_day(sysdate),'dd')
from dual;

 [실행결과]
오늘날짜  이번달마지막날짜                  마지막날짜(일)
-------- -------- -- ---------------------------------
22/04/15 22/04/30 30                                30

6-3.

select sysdate
    ,to_char(sysdate,'w')
    ,to_char(sysdate,'iw')
    ,to_char(sysdate,'ww')
from dual;
 [실행결과]
오늘날짜    월의주차 년의주차 년의 주차
--------    -       --      --
22/04/15    3       15      15


iw와 ww 의 차이점 
 "WW" 포맷은 무조건 1일에서 7일까지가 1주차로 시작을 하며, "IW" 포맷은 실제 달력에 맞게 주차가 계산된다

테이블 완전삭제 

drop table 테이블명 purge;

IW와 WW 의 다른점 

iw: 월요일~일요일 기준 

ww: 1일 ~7일 기준 

select to_char(to_date('2022.4.2'),'iw'),to_char (to_date('2022.4.2'),'ww') 
from dual;

 

서브쿼리를 이용하여 테이블 생성 

1. 이미 존재하는 테이블 있고
2. SELECT ~ 서브쿼리를 이용해서 
3. 새로운 테이블 생성 +  데이터 추가( INSERT )
4. 【형식】
CREATE TABLE 테이블명 [컬럼명 (,컬럼명),...]
AS subquery;
    • 다른 테이블에 존재하는 특정 컬럼과 행을 이용한 테이블을 생성하고 싶을 때 사용
    • Subquery의 결과값으로 table이 생성됨
    • 컬럼명을 명시할 경우 subquery의 컬럼수와 테이블의 컬럼수를 같게해야 한다.
    • 컬럼을 명시하지 않을 경우, 컬럼명은 subquery의 컬럼명과 같게 된다.
    • subquery를 이용해 테이블을 생성할 때 CREATE TABLE 테이블명 뒤에 컬럼명을 명시해 주는 것이 좋다.

 

create table tbl_emp10 (no, name, ibsadate, pay)
as(
    select empno,ename, hiredate,sal+nvl(comm,0)pay
    from emp
    where deptno=10
);
desc tbl_emp10;

 

원래테이블은 그대로 두고 복사해서 사용

create table tbl_empcopy
as(
 select * from emp 
);

 

제약조건은 복사되지 않는다.   not null 제약조건은 예외 

 

제약조건 확인 

select * 
from user_constraints
where table_name=upper('emp');

-- PK           P
-- NN           C
-- FK           R

select * 
from user_constraints
where table_name=upper('tbl_empcopy');

테이블 삭제

drop table tbl_emp10 purge;
drop table tbl_empcopy purge;

 

테이블은 기존 테이블 서브쿼리를 사용해서 생성+데이터x 테이블의 구조

create table tbl_emp_copy
as(
select *
from emp
);

delete from tbl_emp_copy; --where 조건을 삭제하면 모든 데이터 삭제=구조만 복사됨 


--2번째방법 

create table tbl_emp_copy
as(
    select *
    from emp
    where 1=0
);


select *
from tbl_emp_copy;

 

 where 1 = 1은 테이블의 모든 행을 반환하고 WHERE 1 = 0 은 none을 반환

 

tbl_member 테이블 있는지 확인 

1.tbl_member테이블 있는지 확인
 select *
 from user_tables
  where regexp_like(table_name,'member','i');
 where table_name like '%member%';

 

 

테이블생성 

3.테이블 생성
주민등록번호를 주면 나이, 생일, 성별 추출 가능 = 추출속성 
,rrn -주민등록번호 (나이,생일 ->주민등록번호로 알아내기) 
create table tbl_member(
    id varchar2(10) not null constraints pktblmember_id primary key --회원id ,고유키pk
    ,name varchar2(20) not null--회원이름 
    , age number(3)
    ,birth date 
    ,regdate date default sysdate -- 회원가입일
    ,point number default 100  
);select *
from user_constraints
where table_name='tbl_member';

제약조건 설정하지 않으면 sys로 자동 설정됨 

--오류 보고 
--ORA-01830: date format picture ends before converting entire input string
insert into tbl_member(id,name,age,birth, regdate, point) 
values('admin','관리자',32,'03/04/1991',sysdate, 100);

select *
from tbl_member;


--ORA-00001: unique constraint (SCOTT.PKTBLMEMBER_ID) violated
--고유키 제약조건에 위배된다. 
--pk=uk+not null 기본키는 고유키+ null이 아님 
insert into tbl_member(id,name,age,birth, regdate, point) 
values('admin','홍길동',22,'2001.01.01',sysdate, 100);

--칼럼의 순서대로 입력할꺼면, 칼럼명 생략해도됨 
insert into tbl_member values('hong','홍길동',22,'2001.01.01',sysdate, 100);

insert into tbl_member values('park','박지순',25,'1998.05.09',sysdate, 100);

--SQL 오류: ORA-00947: not enough values 
--defualt값을 안줄때는 칼럼명을 써주자 
insert into tbl_member (id,name,age,birth)values('park','박지순',25,'1998.05.09');

insert into tbl_member (id,name,age,birth)values('kenik','이진수',25,'1998.12.15');

--값의 순서가 안맞다면 그거에 맞게 컬럼명 순서를 넣어주기 
insert into tbl_member (id,birth,name,age)values('kenia','1998.12.15','이진수',25);

--null값을 대신 넣어도 된다.(null 허용하는 칼럼은) 
insert into tbl_member (id,birth,name,age)values('keke',null,'이진수',25);

select *
from tbl_member;

서브쿼리를 이용한 insert 

emp 테이블의 10번 부서원들을 select해서 tbl_emp10테이블에 추가 
insert into tbl_emp10(
    select *
    from emp
    where deptno=10
);

 

multi table insert

 

unconditional insert all
conditional insert all 
conditional first insert
pivoting insert 

 

 

 

Conditional insert all

Conditional insert all 문은 조건없이 무조건 삽입되는 unconditional insert all 문과 달리

 특정 조건들을 기술하여 그 조건에 맞는 행들을 원하는 테이블에 나누어 삽입한다.
서브쿼리로부터 한번에 하나의 행을 반환받아 when ... then 절에서 조건을 체크한 후 조건에 맞는 절에 기술된 테이블에 insert 절을 수행한다.

【형식】
INSERT ALL
WHEN 조건절1 THEN
  INTO [테이블1] VALUES (컬럼1,컬럼2,...)
WHEN 조건절2 THEN
  INTO [테이블2] VALUES (컬럼1,컬럼2,...)
........
ELSE
  INTO [테이블3] VALUES (컬럼1,컬럼2,...)
Subquery;

• subquery로부터 한번에 하나씩 행을 리턴받아 WHEN...THEN절에서 체크한 후, 조건에 맞는 절에 기술된 테이블에 insert 절을 수행한다.
• VALUES 절에 지정한 DEFAULT 값을 사용할 수 있다. 만약 default값이 지정되어 있지 않다면, MULL 값이 삽입된다.

insert all 
when deptno=10 then
    into emp_10 values (emp,ename, job,mgr, hiredate ,sal, comm, deptno)
when deptno=20 then
    into emp_20 values (emp,ename, job,mgr, hiredate ,sal, comm, deptno)
when deptno=30 then 
    into emp_30 values (emp,ename, job,mgr, hiredate ,sal, comm, deptno)
else 
    into emp_40 values (emp,ename, job,mgr, hiredate ,sal, comm, deptno)
select * from emp;

select * from emp_10;

테이블의 모든 데이터 삭제 

where 조건문 없는 delete 
truncate 

delete from emp_10; --where 조건 문이 없으면 모든 데이터삭제 
commit;

truncate table emp_10; --모든 데이터를 삭제 delete의 where 조건이 없는 것과 같다. +자동커밋+롤백불가

conditional first insert

조건있는 
all과 first의 차이점: 
조건문1개에 맞으면 나머지는 체크안함 

【형식】
INSERT FIRST
WHEN 조건절1 THEN
  INTO [테이블1] VALUES (컬럼1,컬럼2,...)
WHEN 조건절2 THEN
  INTO [테이블2] VALUES (컬럼1,컬럼2,...)
........
ELSE
  INTO [테이블3] VALUES (컬럼1,컬럼2,...)
Sub-Query;

• conditional INSERT FIRST는 조건절을 기술하여 조건에 맞는 값들을 원하는 테이블에 삽입할 수 있다.
• 여러 개의 WHEN...THEN절을 사용하여 여러 조건 사용이 가능하다. 단, 첫 번째 WHEN 절에서 조건을 만족한다면, INTO 절을 수행한 후 다음의 WHEN 절들은 더 이상 수행하지 않는다.
• subquery로부터 한 번에 하나씩 행을 리턴 받아 when...then절에서 조건을 체크한 후 조건에 맞는 절에 기술된 테이블에 insert를 수행한다.
• 조건을 기술한 when 절들을 만족하는 행이 없을 경우 else절을 사용하여 into 절을 수행할 수 있다. else절이 없을 경우 리턴 딘 그행에 대해서는 아무런 작업도 발생하지 않는다.

 

select *
from emp
where job='clerk';

insert first
    when deptno=10 then
    into emp_10 values(emp,ename, job,mgr, hiredate ,sal, comm, deptno)
    when job='clerk' then
    into emp_20 values(emp,ename, job,mgr, hiredate ,sal, comm, deptno)
    else 
    into emp_40 values(emp,ename, job,mgr, hiredate ,sal, comm, deptno)
select * from emp;

drop table emp_10 purge
drop table emp_20 purge;
drop table emp_30 purge;
drop table emp_40 purge;

pivoting insert

형식】
INSERT ALL
WHEN 조건절1 THEN
  INTO [테이블1] VALUES (컬럼1,컬럼2,...)
  INTO [테이블1] VALUES (컬럼1,컬럼2,...)
  ..........
Sub-Query;

• 여러 개의 INTO 절을 사용할 수 있지만, INTO 절 뒤에 오는 테이블은 모두 동일하여야 한다.
• 주로 여러 곳의 시스템으로부터 데이터를 받아 작업하는 dataware house에 적합하다. 정규화 되지 않은 data source들이나 다른 format으로 저장된 data source들을 Oracle의 관계형 DB에서 사용하기에 적합한 형태로 변환한다.
• 정규화 되지 않은 데이터를 oracle이 제공하는 relational한 형태로 테이블을 변경하는 작업을 pivoting이라고 한다.

 

 

 

문제

    insa 테이블의 num,name 칼럼만 복사해서 tbl_score 테이블 생성 
    조건1) num<=1005 자료만 복사 

create table tbl_score as(
    select num,name
    from insa
    where num<=1005
);
select *
from tbl_score;

 

tbl_score테이블에 국어 수학 영어 총점 grade rank 칼럼추가
(k,e,m 기본값 0, grade 칼럼추가 ) 

alter table tbl_score
add  (
    kor number(3) defuault 0
    ,eng number(3) defuault 0
    ,mat number(3) defuault 0
    ,tot number(3)
    ,avg number(5,2)
    ,grade char(1 char)
    ,rank number
);

 1001_1005 num,name- kor,eng,mat 임의의 점수를 발생시켜서 수정 

 

    update tbl_score
    set kor=trunc(dbms_random(0,101))
    ,eng=trunc(dbms_random(0,101))
    ,mat=trunc(dbms_random(0,101));

   tbl_score 테이블에 tot, avg 계산해서 수정 (update)

  update tbl_score
    set tot=kor+eng+mat, 
    avg=(kor+eng+mat)/3;

 평균 - 90 이상 a, 80이상 b , 70이상 c, 60이상 d 

1.
update tbl_score 
set grade=(decode(trunc( avg/10),10,'a',9,'a',8,'b',7,'c',6,'d','f');


2.
UPDATE tbl_score
SET  grade = CASE
               WHEN avg BETWEEN 90 AND 100 THEN  'A'   -- avg >= 90
               WHEN avg BETWEEN 80 AND 89 THEN  'B'    -- avg >=80
               WHEN avg BETWEEN 70 AND 79 THEN  'C'    -- avg >=70
               WHEN avg BETWEEN 60 AND 69 THEN  'D'    -- avg >=60
               ELSE  'F'
             END;

 

 

1001번 학생의 국어 영어 점수를 1005번학생의 국어 영어 점수로 수정 

1.
update tbl_score 
set 
    kor=(select kor from tbl_score where num=1005)
    eng=(select eng from tbl_score where num=1005)
where num=1001;
commit;

2.
update tbl_score 
set 
    kor,eng=(select kor,eng from tbl_score where num=1005)
where num=1001;
commit;

rank 등수 처리하는 UPDATE문 

1.
UPDATE tbl_score  y
SET rank = (  SELECT COUNT(*)+1 FROM tbl_score WHERE tot > y.tot  );

2.
update tbl_score y
set rank-(select r
      from  (select num, tot, 
            rank() over (order by tot desc)r
            from tbl_score
)x
  WHERE  x.num = y.num   
             );

1번 설명 

한석봉의 tot보다 큰 tot이 몇개가 있는지,그리고 그것의  count를 해주고 +1 를 더해주면  그 결과가 rank와 같다. 

 

모든 학생의 국어 점수를 5점 증가..( UPDATE ) 

update tbl_score 
set kor= case 
			when kor>=95 then 100
            else 		then kor+5
            end;

 

만년달력 

SELECT   
           NVL( MIN( DECODE( TO_CHAR(dates, 'D'), 1,TO_CHAR(dates, 'DD') ) ), ' ')  일
         , NVL( MIN(  DECODE( TO_CHAR(dates, 'D'), 2,TO_CHAR(dates, 'DD') ) ), ' ') 월
         , NVL( MIN(  DECODE( TO_CHAR(dates, 'D'), 3,TO_CHAR(dates, 'DD') ) ), ' ') 화
         , NVL( MIN(  DECODE( TO_CHAR(dates, 'D'), 4,TO_CHAR(dates, 'DD') ) ), ' ') 수
         , NVL( MIN(  DECODE( TO_CHAR(dates, 'D'), 5,TO_CHAR(dates, 'DD') ) ), ' ') 목
         , NVL( MIN(  DECODE( TO_CHAR(dates, 'D'), 6,TO_CHAR(dates, 'DD') ) ), ' ') 금
         , NVL( MIN(  DECODE( TO_CHAR(dates, 'D'), 7,TO_CHAR(dates, 'DD') ) ), ' ') 토         
FROM (
        SELECT TO_DATE( :yyyymm, 'YYYYMM') + (LEVEL -1) dates
        FROM dual
        CONNECT BY LEVEL <= EXTRACT( DAY FROM LAST_DAY( TO_DATE( :yyyymm , 'YYYYMM') ) )
      ) t      
                                  --  일 
GROUP BY  DECODE( TO_CHAR(dates, 'D'), 1, TO_CHAR( dates, 'IW') +1,  TO_CHAR( dates, 'IW')   )  
ORDER BY  DECODE( TO_CHAR(dates, 'D'), 1, TO_CHAR( dates, 'IW') +1,  TO_CHAR( dates, 'IW')   );