오늘의 TIL

1. 계층형 질의문으로 서열을 주고 데이터 출력하기 1 (start with~, connect by~)
2. RPAD를 사용해서 게층형 질의문 시각화하기
3. 계층형 질의문을 서열을 주고 데이터 출력하기 2 (connect by절에 조건주기)
4. 계층형 질의문으로 서열을 주고 데이터 출력하기 3 (order siblings by)
5. 계층형 질문으로 서열을 주고 데이터 출력하기 4(sys_connect_by_path)
6. 일반 테이블 생성하기(CREATE TABLE)
7. 데이터 유형 알아보기
8. 컬럼 추가/변경 명령어(add, drop column, modify)

점심시간 문제

merge into emp17 e
  using telecom_table t
  on (e.telecom = t.telecom)
  when matched then 
  update set e.t_price = t.t_price;
--  when not matched then 

---

계층형 질의문으로 서열을 주고 데이터 출력하기 1 (start with~, connect by~)

계층형 질의문이란, 데이터에서 서열을 발견해서 출력하는 쿼리문이다!!

예제1. 사원 테이블의 서열을 계층형 질의문으로 출력하시오 !

select level, empno, ename, mgr
  from emp
  start with ename ='KING' //  이부분이 가장 먼저 출력되는 서열 1위를 결정!
  connect by prior empno = mgr; // 서열 연결고리

• level은 start with~, connect by~ 를 사용하면 쓸 수 있는 컬럼이다!
  
  empno가 먼저고, 그 empno 순서대로 mgr을 가지고있는 사람이 다음레벨로 나온다..! empno 7839를 mgr로 가지고있는 사람은 레벨2로 출력되고, 7566을 mgr로 가지고있는 사람은 레벨 3으로 나온다.
  킹을 기준으로 2위-3위-4위 까지 출력했다가 다시 2위로 돌아가서 그밑직원(ford) 뻡고 다시 ㄱ스미스 뽑는다.다시 킹으로 올라와서 블레이크-앨런 으로 올라감!!
• empno는 pk(부모키), mgr(fk) 이다. connect by prior + pk + fk 순서!
• start with~ 는 어떤사람을 기준으로 출력할건지 설정
• start with mgr is null 이라고 쓰면 mgr이 널인게 사장이라 KING(사장) 부터 나옵니다!!

예제2. employees 테이블에서 level, employee_id, first_name, manager_id 를 출력하는데 서열1위를 manage_id가 null인 사원으로 설정

select level, employee_id, first_name, manager_id
 from employees
 start with manager_id is null
 connect by prior employee_id = manager_id; 

예제 3. 아래의 rpad 함수를 이용한 SQL을 수행하시오!

select rpad(ename, 10, '*') // ename을 10자리만드는데 나머지는 * 로 채우기
  from emp;

RPAD를 사용해서 게층형 질의문 시각화하기!

select rpad(' ', level*2)|| ename as employee, level, ename, sal, job
  from emp
  start with ename ='KING' 
  connect by prior empno = mgr;

문제 501. employees 테이블에서 first_name, level, salary, job_id를 출력하는데 first_name앞에 level의 2배로 공백을 넣어 출력

select rpad(' ', level*2)|| first_name as employee,first_name, level, salary, job_id
  from employees
  start with manager_id is null
  connect by prior employee_id = manager_id; 

문제 502. 월급이 2400 이상인 사원들의 이름, 월급을 출력하는데 계층형 질의문을 이용해서 이름앞에 level의 2배로 공백을 줘서 출력하시오

select rpad(' ', level*2)|| ename as employee, level, ename, sal, job
  from emp
  where sal >= 2400 // where절 사용 가능
  start with ename ='KING' 
  connect by prior empno = mgr;

문제 503. 위 SQL의 실행순서를 확인하기 위해서, 실행 계획을 확인하시오!

explain plan for
select rpad(' ', level*2)|| ename as employee, level, ename, sal, job
  from emp
  where sal >= 2400 // where절 사용 가능
  start with ename ='KING' 
  connect by prior empno = mgr;

select * from table(dbms_xplan.display);

• 2번이 계층형질의문(서열을 만들기), where 절로 필터링!
• 실행순서 : from -> start with - connect by -> where

계층형 질의문을 서열을 주고 데이터 출력하기 2 (siblings)

계층형 질의문 결과에서 검색조건을 주는 방법을 배우기!

예제1. 앞의 예제에서 출력하는 사원테이블 전체의 서열을 출력하기

select rpad(' ', level*2)|| ename as employee, level, sal
  from emp
  start with ename ='KING' 
  connect by prior empno = mgr;

문제 504. 위 결과에서 이름이 BLAKE인 사원을 제외하고 출력하기

select rpad(' ', level*2)|| ename as employee, level, sal
  from emp
  where ename != 'BLAKE'
  start with ename ='KING' 
  connect by prior empno = mgr;

BLAKE는 빠졌지만, 직원들은 남아있다.

문제 505. 이번에는 BLAKE 뿐만 아니라, BLAKE의 팀원들도 전부 안나오게 하시오!

select rpad(' ', level*2)|| ename as employee, level, sal
  from emp
  start with ename ='KING' 
  connect by prior empno = mgr and ename !='BLAKE';

• ENAME이 BLAKE인 사원의 팀원들까지 모두 안나오게 하려면, connect by 절에 조건을 적는다
• 이것은 계층형 질의문에서 알아야 하는 것
• where절에 아래처럼 하면 왜 안되는것?
  

문제 505. employees 테이블에서 위와 같은 결과를 출력하는데, 아담과 아담의 팀원들을 전부 출력되지 않도록 작성

select rpad(' ', level*2)|| first_name as employee,first_name, level, salary, job_id
  from employees
  start with manager_id is null
  connect by prior employee_id = manager_id and first_name!= 'Adam';

계층형 질의문으로 서열을 주고 데이터 출력하기 3 (order siblings by)

계층형 질의문에서 서열의 틀을 깨트리지 않으면서 데이터를 정령하려면 order by 할 때 특별한 키워드를 하나 기술해야 합니다!

예제1. 다음과 같이 사원테이블의 전체의 서열을 출력하기

select rpad(' ', level*2)|| ename as employee, level, sal
 from emp
 start with ename ='KING' 
 connect by prior empno = mgr ;

예제2. 위 결과를 다시 출력하는데 월급이 높은 사원부터 출력하시오!

• 위처럼 order by 를 쓰는순간, 누가 누구의 팀원인지 또는 누가 누구의 팀장인지에 대한 서열 순서가 없어진다. -> order by는 맨 마지막에 수행되기 때문!

예제3. 누가 누구의 팀원이지에 대한 서열구조는 그대로 유지하면서 월급이 높은 사원순으로 출력되게 하시오

select rpad(' ', level*2)|| ename as employee, level, sal
  from emp
  start with ename ='KING' 
  connect by prior empno = mgr
  order siblings by sal desc;

• siblings 를 order와 by 사이에 넣어준다.

문제 507. 위 결과를 employees 테이블로 출력

select rpad(' ', level*2)|| first_name as employee,first_name, level, salary, job_id
  from employees
  start with manager_id is null
  connect by prior employee_id = manager_id 
  order siblings by salary desc;

계층형 질문으로 서열을 주고 데이터 출력하기 4(sys_connect_by_path)

계층형 질의문에 짝꿍 함수가 sys_connect_by_path가 있습니다. 이 함수는 가로로 데이터를 출력합니다!

예제1. 다음과 같이 sys_connect_by_path 함수를 이용한 SQL을 작성

select sys_connect_by_path(ename, ',')
  from emp
  start with ename ='KING'
  connect by prior empno = mgr;

• 콤마 부분을 / 로 설정하면

문제 508. 위 결과에서 아담스만 출력

select ename, sys_connect_by_path(ename, '/')
  from emp
  where ename = 'ADAMS'
  start with ename ='KING'
  connect by prior empno = mgr;

문제 509. 위 결과에서 맨처음에 이름 앞에 / 빼보기

select ename, substr(sys_connect_by_path(ename, '/'),2, 10000)
  from emp
  where ename = 'ADAMS'
  start with ename ='KING'
  connect by prior empno = mgr;

시험에서는 sys_connect_by_path를 고르는 문제가 주로 나온다. 암기하고있기 !!

1. order siblings by
2. sys_connect_by_path

SQLD 기출문제에서 한번 언급된 계층형 질의문에 대한 함수(기출문제 33회 출제)
CONNECT_BY_ISLEAF함수 : 말단 사원인지 아닌지를 출력하는 함수 !

select rpad(' ', level*2) || ename, sal, connect_by_isleaf as isleaf
  from emp
  start with ename ='KING'
  connect by prior empno = mgr;

• 말단사원이면 1로 출력되고, 아니면 0으로 출력되는것을 확인할 수 있다!
• 이건 그냥 알고만있기.

일반 테이블 생성하기(CREATE TABLE)

테이블 종류 2가지
1. 영구히 data를 저장하는 테이블
2. 임시로 data를 저장하는 테이블

문법 !

create table 테이블명
(컬럼명  데이터 유형,
 컬럼명  데이터 유형);

※ 테이블명과 컬럼명을 지을 때 주의할 사항!!!
1. 테이블명과 컬럼명은 반드시 문자로 시작해야합니다.
2. 테이블명의 길이는 30자를 넘을 수 없습니다.
3. 테이블명 이름에 특수문자는 $ , _ , # 만 포함할 수 있습니다!

create table emp907
( empno   number(10),     ---> 숫자를 10자리 허용하겠다.
  ename   varchar2(20),   ---> 영문자 20자를 허용하겠다. 한글은 10자를 허용.!!!
  sal     number(10) ); 

※ 각 나라의 언어에 맞춰서 지원하는 문자형 데이터 유형
1. nvarchar2(길이) -> 오라클은 항상 2를 붙이는데, 별뜻은 없고 그냥 특허? 같은 문제로 ....웅앵
2. nchar(길이)

데이터 유형의 3가지

1. 문자형 : char, varchar2, long, clob, blob
2. 숫자형 : number
3. 날짜형 : date

• char(10), varchar2(10)의 차이!!

          char(10)                              varchar(10)
   M I R A C L E x x x x                  M I R A C L E x x x x
          고정형                                  가변형
    // 빈 4자리는 공백이 된다.              // 빈 4자리는 회수가 된다. 

varchar(10)의 장점은, 공백을 없애버리니까 공간낭비가 없다.
그렇다고 varchar(10)이 다 좋은것은 아니다. 예를들어서 miracle에서 오타로 miracl 이라고 e를 빼먹어서 업데이트를 하고싶으면 char(10)는 뒤에 추가가되는데 varchar(10)는 못한다. 그래서 다른곳에 이사를 가서 다시 miracle로 만들어주어야 한다.
※ 이렇게 이사를 가는 (이전하는) 현상은, row migration 이다! -> 이 현상이 많으면 성능이 느려진다.
※ 그렇다면 처음부터 테이블을 설계할 때, 업데이트가 많이 일어날 것 같은 컬럼은 char(10)로 하는것이 좋고 그렇지 않다면 varchar(10)로 잡는것이 좋다.

• row migration 현상이일어남에도 불구하고 char이 아닌 varchar2로 많이 생성하는 이유는 뭘까???

          char(10)                              varchar(10)
   1 0 x x x x x x x x x                  1 0 x x x x x x x x x
          고정형                          가변형(남은빈자리 회수)
            ↓                                       ↓
          char(10)                                char(2)    

테스트 스크립트

create table emp600
( ename  varchar2(10),
  sal    number(10),
  deptno varchar2(10));

create table dept600
( deptno  char(10),
loc       varchar2(10));

• 서브쿼리로 insert !

insert into emp600 (ename, sal,deptno)
  select ename, sal, to_char(deptno)
  from emp;

insert into dept600 (loc, deptno)
  select eloc, to_char(deptno)
  from dept;

문제 510. dept600 table에서 deptno가 10번인 deptno, loc를 출력하세요!

select loc, deptno
from dept600
where deptno = '10';
    char(10) = char(2) -----  내부적으로 공백을 채워서 char(10) = char(10) 로 실행된거임 그래서 결과가 잘 출력된것임

문제 511. emp600 table에서 deptno가 10번인 사원들의 ename, sal 출력

select sal, ename
from emp600
where deptno = '10';
     varchar2(2) = varchar2(2) ---- 결과 잘 나옴

문제 512. dept600 테이블과 emp600 테이블을 서로 조인해서 ename, sal, loc출력

select e.ename, e.sal, d.loc
 from emp600 e, dept600 d
 where e.deptno = d.deptno ;
       varchar2(2) != char(10) ---> 이부분이 같지 않아서 안나온다!!!!

• emp600에서 deptno 번호 10번은 잘 조회되고, dept600에서 deptno 번호 10번이 잘 조회되지만, 조인을 했을때는 결과가 나오지 않는다. 왜냐면, varchar2(2)가 char(10)을 맞춰주기 위해서 공백 8을 채우지 않는다. 양쪽다 varchar2던지, char이면 괜찮은데 둘이 맞지 않으면 안된다.
  위와같은 현상이 일어나지 않게 하려고 일부러 char 을 잘 쓰지않고 varchar2로 설계한다.
• 그렇다면 row migration 현상은?!
  -> 데이터가 조인되어서 안보이는 문제가 더 심각하지 이런 아주 작은 성능상의 이슈보다는 데이터를 출력하는게 더 중요하다!!!!!

데이터 유형 (책에있음)

• char : 고정길이 문자 데이터 유형, 최대 길이는 2000
• varchar : 가변길이 문자 데이터 유형, 최대길이는 4000
• long : 가변길이 문자 데이터 유형, 2gb의 문자 데이터 허용
• clob : 문자 데이터 유형이며 최대 4gb의 바이너리 데이터 허용
• blob : 바이너리 데이터 유형이며 최대 4gb의 바이너리 데이터 허용
• number : 숫자데이터 유혀으 십진 숫자의 자리수는 최대 38자리까지 허용, 소숫점 이하 자리 -84 ~ 127까지 허용
• date : 날짜 데이터 유형, 기원전 4712년 1월 1일부터 기원후 9999년 12월 31일까지의 날짜를 허용
  괄호열고 써주는건 char(), varchar2() , number밖에 없음!
  데이터 마이그레이션 할 때 중요하게 생각해야하는 것이 long, clob 임. (데이터 이행을 위해 알아두어야 함)
  char, varchar2는 데이터 모델링할 때 알아두어야하는것이 좋다.

책 참고해서 풀기!
문제 513. 스티브 잡스 연설문을 db에 저장하기 위해, 아래와 같이 테이블을 생성하시오

create table steve_jobs
 ( s_text long);

문제 514. steve_jobs테이블에 스티브 잡스 연설문을 입력하시오
연설문 다운

새로고침

• 안들어가서 drop했음! clob으로 다시생성 -> 근데 이것도 안됨

create table steve_jobs
(s_text clob);
----------------------------------
create table steve_jobs
(s_text varchar(4000) );   ---> 이거로 넣었음

※ 큰 텍스트 데이터를 입력할 때는 long, clob, varchar2(4000) 중 하나를 선택해서 오류가 없는것으로 데이터를 넣으면 된다.

문제 515. 우리반 데이터를 입력하기 위한 테이블을 emp17_my로 생성하기

create table emp17_my
( EMPNO  number(10),
  ENAME  varchar2(10),
  GENDER varchar2(5),
  BIRTH  date,
  AGE  number(10),
  TELECOM varchar2(10),
  EMAIL   varchar2(100),
  MAJOR   varchar(50),
  ADDRESS  varchar(200) );

• 보통 아래처럼 테이블정의서를 받게되는데 보고 그대로 만들어주면 된다.
  

문제 516. 위 emp17_my 테이블에 emp17 테이블의 데이터를 입력하시오(t_price빼고) -> sub query insert 사용

insert into emp17_my(empno, ename, gender, birth, age, telecom, email, major, address)
  select empno, ename, gender, birth, age, telecom, email, major, address
  from emp17;

  commit;

• 서브쿼리 사용하면 values 쓰지 않고 넣을 수 있다.

문제 517.
dept테이블의 data를 입력하기 위한 dept_my테이블을 생성하고, dept의 모든 데이터를 dept_my 테이블에 입력하시오

 describe dept; -> dept테이블 확인해서 컬럼명 보기

create table dept_my   -> dept_my테이블 생성
 ( DEPTNO  NUMBER(10),
   DNAME   VARCHAR2(10),
   LOC     VARCHAR2(20) );

insert into dept_my (DEPTNO, DNAME, LOC)
 select DEPTNO, DNAME, LOC
 from dept;

• VARCHAR2처럼 NUMBER도 가변형이다. 글자수 넉넉히 주자

문제 518. 아래의 데이터를 dept_my 데이터에 입력하세요
부서번호 : 50
부서명 : hr
부서위치 : Chaubunagungamaug

• 만약 내가 만든 길이보다 데이터가 길면 이렇게 에러가 난다.

dba가 반드시 알아야할 컬럼 추가/변경 명령어

1. 컬럼 추가 (add)

예제. emp 테이블에 email 라는 컬럼 추가

alter table emp
  add email varchar2(30);

2. 컬럼 삭제 (drop column)

예제. emp 테이블에 sal 컬럼을 삭제

alter table emp
  drop column sal;

★ 컬럼삭제는 한번 삭제되면 flashback이나 rollback이 안된다. 조심하기!!!! ★

3. 컬럼 변경 (modify)

예제. emp 테이블의 job의 컬럼의 길이를 varchar2(50)로 늘리시오

alter table emp 
  modify job varchar2(50);

• 늘리는것은 잘 되지만, 줄이는것은 데이터가 자리를 확보하고 있으면 줄일 수 없다.

문제 519. (dba에게 들어오는 요청) dept_my 테이블 loc 컬럼의 길이를 varchar2(50) 으로 늘리시오!
-> 얘도 테이블정의서처럼 온다.

문법

alter table dept_my // alter + table + 컬럼명
  modify loc varchar2(50); // modify + 컬럼명 + 데이터유형

• 주의사항 !!! 위 alter문을 이용한 변경작업은, dba가 낮에 업무시간(한참 바쁠 때) 수행하면 안된다. db가 한가한 밤이나 주말에 수행해야 한다. -> 낮에하면 db엄청 느려짐. -> 크론이라고 예약어쩌고 처럼 몇시에 되도록 설정해놓는 경우가 많음

문제 520. emp테이블에 enmae에 문자의 길이를 varchar2(60)으로 변경해보기

alter table emp
  modify ename varchar2(60);

• dba는 위와 같은 변경사항에 대한 이력은 별도의 테이블로 만들어서 관리하면 나중에 db쪽 감사가 있거나 이력을 확인하고 싶을 때 쉽게 확인이 가능하다. SI프로젝트시 주로 감사는 받는데, 테이블 정의서 (엑셀파일) 와 실제 DB가 일치하는지 확인을 한다.

                        일치하는지 확인 ----> PL/SQL, 파이썬
    엑셀 테이블 정의서 ------------------------------------------- 물리적 db 설계         

문제 521. emp table에 address라는 컬럼을 varchar2(30)으로 추가

alter table emp
  add address varchar2(30);

문제 522. 위에서 추가한 address 컬럼을 삭제

alter table emp
    drop column address;

문제 523. emp 테이블의 sal 컬럼을 삭제해보기 (sal은 데이터가 있었음)

alter table emp
    drop column sal;

• rollback; 이랑 flashback도 불가

 alter table emp enable row movement;
    flashback table emp to timestamp
   ( systimestamp - interval '5' minute); 

• 해결방법 : 백업 솔루션에서 받은 백업본으로 복구를 하면 됩니다. 백업 솔루션을 안쓰는 회사는, 수업 때 배운 백업과 복구 방법으로 복구하면 됨!

문제 524. (마지막 문제) 데이터 게시판의 388번의 지방간 데이터를 저장할 테이블을 생성하고, 데이터를 입력하시오. (테이블명: fatliver, 컬럼명 알아서)
자료 다운

create table fatliver
 ( "age" varchar2(10),
   "gender" varchar2(10),
   "drink" varchar2(20),
   "smoking" varchar2(10),
   "fatliver" varchar2(10));

market_2022 넣은 큰데이터 넣기 방법, 내벨로그 참고