Oracle 12일차

어제 문제

-- 1. student, exam_01 테이블을 조인하여 학번, 이름, 학년, 시험성적을 갖는
-- student_2 테이블 생성 후 해당 테이블을 사용하여 각 학년별 최고 시험 성적자만 남기고 나머지 학생정보는 모두 삭제
select * from student;
select * from exam_01;
drop table student_2;
create table student_2
as
select s.studno, s.name, s.grade, e.total
from student s, exam_01 e
where s.studno = e.studno;
select * from student_2;

-- 학년별 최고 성적자의 점수 구하는 쿼리
select grade, max(total)
from student_2
group by grade;

-- 위의 쿼리를 이용하여 학년별 최고 성적자 구하는 쿼리
select *
from student_2
where (grade, total) in (select grade, max(total)
from student_2
group by grade);

-- 위의 쿼리를 이용하여 테이블에서 나머지 학생정보 삭제
delete student_2
where studno not in (select studno
from student_2
where (grade, total) in (select grade, max(total)
from student_2
group by grade));
select * from student_2;

-- 강사님 방법 : 그냥 total만 맞춰줘도 행 한줄이 다 사라지는거구나.
delete from student_2 s1
where TOTAL != (select max(total)
from student_2
where grade = s1.grade);

-- 2. 코딩테스트 문제
-- 계좌 정보가 0보다 큰 경우는 입금 정보이고 0보다 작은 경우는 신용카드 사용내역이다.
-- 매월 신용카드 사용 횟수가 3회 미만이거나 총 사용금액이 100 이하인 경우는 패널티를 매달 5 부여(-5)
-- 총 12개월 마다의 패널티가 계산된 계좌 총 합을 구하여라(….)

-- [ 데이터 만들기 ]
create table transactions (
amount number not null,
date1 date not null);
insert into transactions values ('1000', '2020-01-06');
insert into transactions values ('-10', '2020-01-14');
insert into transactions values ('-75', '2020-01-20');
insert into transactions values ('-5', '2020-01-25');
insert into transactions values ('-4', '2020-01-29');
insert into transactions values ('2000', '2020-03-10');
insert into transactions values ('-75', '2020-03-12');
insert into transactions values ('-20', '2020-03-15');
insert into transactions values ('40', '2020-03-15');
insert into transactions values ('-50', '2020-03-17');
insert into transactions values ('200', '2020-10-10');
insert into transactions values ('-200', '2020-10-10'); -- 2,746 이게 나와야 됨
commit;
select * from transactions;

-- 일단 그냥 sum(amount) 때리면 합은 나오고
select sum(amount)
from transactions;
-- 이제 생각할 게 한달에 3번이상 카드를 쓰고 & -100 이상이 되어야 -5를 안하고. 그렇지 않으면 매달 -5씩
-- 그러면 amount가 -인 애들만 따로 빼버리자.
-- 사용한 경우만 보기
select *
from transactions
where amount<0; 
-- 이제 월별로 그룹핑해서 count(월별)이 3이하면 -5처리. 그리고 sum(amount)가 100이하면 -5처리. 근데 둘은 중복되면 안됨.
 -- 어제 했던 거에서 from절 수정해서 짧게 바꿨음 
-- 문제에 '12개월마다'라는 언급이 있어서 group by할 때 YYYYMM으로 묶는 식으로 바꿔야 됨. 
select to_char(date1, 'YYYYMM') as 월,
        count(to_char(date1, 'YYYYMM')) as 사용횟수,
        sum(amount) as 사용금액 
from transactions 
where amount<0 
group by to_char(date1, 'YYYYMM'); 
-- 애초에 비어있는 달이 많으니깐 매달 -5씩 해놓은 채로 조건 2개 모두 충족한 달이 보이면 +5해주는 방식으로 가자 
select 월 
from (select to_char(date1, 'YYYYMM') as 월, 
                count(to_char(date1, 'MM')) as 사용횟수,
                sum(amount) as 사용금액 
         from transactions 
        where amount<0 
        group by to_char(date1, 'YYYYMM'))
 where 사용횟수>=3 and 사용금액<-100; 
-- 이제 마지막 단계인듯? 
select sum(합)-(12-count(월))*5
from (select sum(amount) as 합 
         from transactions) t1, 
       (select 월
         from (select to_char(date1, 'YYYYMM') as 월,
                         count(to_char(date1, 'MM')) as 사용횟수,
                         sum(amount) as 사용금액
                  from transactions
                where amount<0
                group by to_char(date1, 'YYYYMM'))
        where 사용횟수>=3 and 사용금액<-100) t2;

-- 강사님 방법
-- 일자별 신용카드 사용횟수, 사용금액 출력
select t., 
        case when amount<0 then 1 end as 사용횟수, 
        case when amount<0 then abs(amount) end as 사용금액
 from transactions t;
 -- 월별 신용카드 사용횟수, 사용금액 출력 
select to_char(date1, 'YYYYMM') as YYYYMM,
        sum(amount) as 계좌총액,
        sum(case when amount<0 then 1 end) as 사용횟수,
        sum(case when amount<0 then abs(amount) end) as 사용금액,
        case when sum(case when amount<0 then 1 end)>=3
                 and sum(case when amount<0 then abs(amount) end)>100
                then 1
         end as nopenalty
 from transactions t
group by to_char(date1, 'YYYYMM');
 -- 연도별(12개월마다) 계좌총액과 패널티 계산 
select substr(YYYYMM, 1, 4) as 연도,
        sum(계좌총액) as "패널티 반영 전 총액",
        sum(nopenalty) as 노페널티구간,
        sum(계좌총액)-(12-sum(nopenalty))5 as 정답
from (select to_char(date1, 'YYYYMM') as YYYYMM,
                sum(amount) as 계좌총액,
                sum(case when amount<0 then 1 end) as 사용횟수,
                sum(case when amount<0 then abs(amount) end) as 사용금액,
                case when sum(case when amount<0 then 1 end)>=3
                        and sum(case when amount<0 then abs(amount) end)>100
                       then 1
                 end as nopenalty
          from transactions t
        group by to_char(date1, 'YYYYMM'))
group by substr(YYYYMM, 1, 4);

수업 내용

— [제약조건]

— 1. 정의

— : 테이블에 저장되는 데이터의 입력/수정/삭제를 제한하기 위해 테이블의 각 컬럼마다 정의하는 객체

— 2. 종류

— 1) primary key
— 2) unique
— 3) not null
— 4) check
— 5) foreign key

— 3. 생성

— 1) 테이블 생성 시 정의 가능

create table table1
(
col1 number [default 값] [constraint 제약조건명] primary key,
col2 date not null
);

— 2) 이미 생성된 테이블에 제약조건 추가

alter table 테이블명 add [constraint 제약조건명] 제약조건종류(컬럼1, 컬럼2,…);

— 예) pk 추가
alter table 테이블명 add [constraint 제약조건명] primary key(컬럼1, 컬럼2,…);
— 예) unique 추가. unique는 보통 단독 컬럼으로 지정
alter table 테이블명 add [constraint 제약조건명] unique(컬럼);
— 예) check 추가
alter table 테이블명 add [constraint 제약조건명] check(sal>0);
— 예) fk 추가. foreign key 사용
alter table 테이블명 add [constraint 제약조건명] foreign key(자식컬럼) references 부모테이블(부모컬럼);
— 예) nn 설정
alter table 테이블명 modify 컬럼 not null;

-- [연습문제]
-- emp와 dept와 동일한 테이블 emp_cons1, dept_cons1을 생성 후 각각 필요한 제약조건 생성
CREATE TABLE EMP_cons1
(
EMPNO NUMBER(4), -- pk
ENAME VARCHAR2(10), -- nn
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) -- fk 걸 때는 상대쪽에 pk가 있어야 걸 수 있음
);
CREATE TABLE DEPT_cons1
(
DEPTNO NUMBER(2), -- pk
DNAME VARCHAR2(14), -- uk
LOC VARCHAR2(13)
);
alter table emp_cons1 add constraint emp_cons1_empno_pk primary key(empno);
alter table emp_cons1 modify ename not null;
alter table dept_cons1 add constraint dept_cons1_deptno_pk primary key(deptno);
alter table emp_cons1 add constraint emp_cons1_deptno_fk
foreign key(deptno) references dept_cons1(deptno);
alter table dept_cons1 add constraint dept_cons1_dname_uk unique(dname);

— 4. 삭제

alter table emp_cons1 drop constraint 제약조건명 [cascade];
— cascade는 pk만 지워서 연결된 fk도 삭제시킬 때 사용

-- 예제) board에 있는 제약조건이름 확인 후 pk 제약조건 삭제
-- system 계정에서 수행
select constraint_name
from dba_constraints
where table_name = 'BOARD'
and constraint_type = 'P';
-- scott계정으로 바꾸기
alter table board drop constraint BOARD_NO_PK;

-- 예제) member3에 있는 제약조건 이름 확인 후 pk 제약조건 삭제
-- system 계정에서 수행
select constraint_name
from dba_constraints
where table_name = 'MEMBER3'
and constraint_type = 'P';
-- scott계정으로 바꾸기
alter table member3 drop constraint MEMBER3_USERID_PK cascade;
-- board 테이블에 있던 fk도 삭제됨

— 5. 조회 **중요

— 1) 제약조건 조회

— 권한 있는 계정에서 작동
select CONSTRAINT_NAME, — 제약조건명
CONSTRAINT_TYPE, — 제약조건 종류(P:primary key, U:unique, C:Check/NotNull, R:foreign key)
TABLE_NAME, — 테이블명
SEARCH_CONDITION, — notnull 여부 확인
R_CONSTRAINT_NAME — reference key 제약조건명
from dba_constraints
where table_name in (‘EMP’, ‘DEPT’);

select * from user_constraints;

— 2) 제약조건 구성 컬럼 조회

— 권한 있는 계정에서 작동
select TABLE_NAME, — 테이블명
CONSTRAINT_NAME, — 제약조건명
COLUMN_NAME, — 컬럼명
POSITION — 컬럼순서(제약조건 구성 컬럼이 여러 개일 경우 중요)
from dba_cons_columns
where table_name in (‘EMP’,’DEPT’);

select * from user_cons_columns;

— [외래키 제약]

insert into dept_cons1
select * from dept;
insert into emp_cons1
select * from emp;
commit;

— 자식 테이블 제약

insert into emp_cons1(empno, ename, deptno) values(9999,’홍길동’,50); — error(insert 제한)
update emp_cons1
set deptno = 50
where deptno = 10; — error(update 제한)
delete emp_cons1; — 정상(delete는 제한없음)

— 부모 테이블 제약

insert into dept_cons1 values(50, ‘IT부’, ‘서울’); — 정상(insert 제한없음)
update dept_cons1
set deptno=50
where deptno=10; — error(update 제한)
— fk는 부모자식 양쪽 다 제한때문에 수정이 안돼서 제약조건을 삭제하든 해야되는데 귀찮아지니깐
— 현업에서는 윗단(JAVA)에서 처리해버리는 경우가 많다.
delete dept_cons1
where deptno=10; — error(delete 제한)

— [foreign key 옵션]

— foreign key 생성 시 옵션 정의 가능

— 1) on delete cascade — 부모쪽 데이터 삭제 시 자식쪽도 같이 삭제
— 2) on delete set null — 부모쪽 데이터를 삭제하고 자식쪽 데이터는 null로 수정

-- 예제) dept_cons1 데이터 삭제 시도
-- step1) 제약조건 조회
select *
from user_constraints
where table_name in ('DEPT_CONS1','EMP_CONS1')
order by table_name;
-- step2) dept_cons1 데이터 삭제 시도
delete from dept_cons1
where deptno=10; -- error(자식 레코드가 발견되었습니다)
-- step3) foreign key 재생성(on delete cascade 옵션)
alter table emp_cons1 drop constraint emp_cons1_deptno_fk;
alter table emp_cons1 add constraint emp_cons1_deptno_fk
foreign key(deptno) references dept_cons1(deptno) on delete cascade;
-- step4) dept_cons1 데이터 삭제 시도
delete from dept_cons1
where deptno=10; -- 정상 수행
select * from dept_cons1; -- 10번 부서 삭제
select * from emp_cons1; -- 10번 부서원 함께 삭제
commit;
-- step5) foreign key 재생성(on delete set null 옵션)
alter table emp_cons1 drop constraint emp_cons1_deptno_fk;
alter table emp_cons1 add constraint emp_cons1_deptno_fk
foreign key(deptno) references dept_cons1(deptno) on delete set null;
-- step6) dept_cons1 데이터 삭제 시도
delete from dept_cons1
where deptno = 20; -- 정상 수행
select * from dept_cons1; -- 20번 부서 삭제
select * from emp_cons1; -- 20번 부서원의 deptno가 null로 바뀜
commit;

— [기타 오브젝트]

— 1. 뷰(VIEW)

— 테이블처럼 조회 가능한 걕체
— 물리적으로 실제 저장된 공간이 있지는 X
— create view 권한을 가진 사람만이 생성 가능
— view는 쿼리를 실행하는 순간순간마다 view를 만드는 느낌이라 변경사항이 바로바로 적용이 된다.

— 1) view 생성 및 수정

create [or replace] view 뷰이름 — 이미 존재하면 뷰 바꿈
as
select …;

— 2) 삭제

drop view 뷰이름; — recyclebin 에 들어가지 않고 즉시 삭제

— 3) 조회

select *
from dba_views
where owner = ‘SCOTT’;

— system 계정에서
grant dba to scott; — scott에게 dba 권한 줌

— scott에서 재접속 후
— 예제)
create table student_exam01
as
select s.STUDNO, s.name, s.grade, e.total
from student s, exam_01 E
where s.STUDNO = e.STUDNO;

create view view_student_exam01
as
select s.STUDNO, s.name, s.grade, e.total
from student s, exam_01 E
where s.STUDNO = e.STUDNO;

select * from student_exam01;
select * from view_student_exam01;

insert into student(studno, name, id, jumin) values(9999, ‘홍길동’,’abcd’,’1111111111111′);
insert into exam_01 values(9999, 100);
commit;
— 이러고 다시 select 해보면 table에는 새로 입력한 데이터가 없지만 view에는 있다.

— dictionary view 종류

— 1) static dictionary view : 객체 현황 조회
— dba_% : 전체 객체
— all_% : 접속한 계정 소유 + 권한을 가진 객체
— user_% : 접속한 계정 소유의 객체

— 예)
select * from dba_tables;
select * from dba_tab_columns;
select * from dba_constraints;
select * from dba_cons_columns;
select * from dba_view;

— 2) dynamic performance view : 성능과 관련된 실시간 정보 조회(세션, lock 현황, 메모리 사용률)
— v$%
select * from v$parameter;
select * from v$session; — 현재 있는 세션
select * from v$sql; — 현재 날라오는 쿼리 정보

— 2. 시퀀스(sequence)

— 주문번호, 게시글번호, 송장번호처럼 자동 증가하는 숫자를 부여하는 객체
— 채번 프로세스 중 하나
— 시퀀스는 한번 지나간 거는 다시 못 돌아온다. 즉, rollback을 하고 다시 insert하면 지나간 번호는 지나치는 것.
— 11g new feature : deferred segment creation
— 새(비어있는) 테이블을 메모리에 할당하는 걸 저장공간 절약을 위해 미룬다는 의미.
— 그 과정에서 시작번호 한 번 건너뛰게 됨.
— 다시말해 기존에 있던 테이블에 시퀀스를 쓰면 시작번호가 반영되지만
— 데이터가 없던 새 테이블에 시퀀스를 쓰면 시작번호가 1번 씹힌다.(생성하면서 1, 삽입하면서 2 이런 느낌인듯?)
— 단! 일반계정에만 해당하고 시스템계정에서는 deffer하지 않는다.
— 동시 트랜잭션을 위해서 cache가 존재함. 캐시 관련 오류 발생 시 캐시에 들어가있던 번호는 증발.

— 1) 생성

create sequence 시퀀스명
[start with n] — 시작값(default:1)
[increment by n] — 증가값(default:1), 음수가능
[maxvalue n] — 최대번호/재시작값(default:10^27 또는 -1)
[minvalue n] — 최소번호/재시작값(cycle시) (default:1 또는 -10^26)
[cycle | nocycle] — 재사용여부(default:nocycle)
[cache n]; — 캐시사이즈(default:20)
— 음수시퀀스와 양수시퀀스의 default값이 다르다.
— 양수 시퀀스에서는 maxvalue까지 도달, cycle시 minvalue부터 재시작(start with보다 작아야 됨)
— 음수 시퀀스에서는 minvalue까지 도달, cycle시 maxvalue부터 재시작(start with보다 커야 됨)

— 2) 삭제

drop sequence 시퀀스명;

— 3) 조회

select *
from dba_sequences;

-- 예제) 시퀀스를 통한 자동 번호 부여
-- 테이블 생성
create table jumun
(
jno number(4) primary key, -- 주문 번호
pno number(4) not null, -- 상품 번호(1~10)
gno number(4) not null, -- 고객 번호(100~200)
qty number not null, -- 수량
jdate date default sysdate -- 주문 날짜
);
-- 시퀀스 생성
create sequence seq_jumun
start with 1000
maxvalue 1010;
-- 시퀀스를 통한 주문번호 생성 및 데이터 입력
insert into jumun values(seq_jumun.nextval,1,100,5,sysdate);
insert into jumun values(seq_jumun.nextval,10,101,10,sysdate);
insert into jumun values(seq_jumun.nextval,5,102,1,sysdate);
select * from jumun;
rollback;

-- 예제) 시퀀스를 통한 자동 번호 부여(scott계정으로, cycle옵션) - cycle되면 1번부터 다시 시작
create table jumun2
(
jno number(4) primary key, -- 주문 번호
pno number(4) not null, -- 상품 번호(1~10)
gno number(4) not null, -- 고객 번호(100~200)
qty number not null, -- 수량
jdate date default sysdate -- 주문 날짜
);
create sequence seq_jumun2
start with 1000
maxvalue 1010
cycle;
insert into jumun2 values(seq_jumun2.nextval,2,100,5,sysdate);
select * from jumun2;

-- [실습 - 캐시 설정으로 인한 시퀀스 번호 손실]
-- 테이블 생성
create table jumun3 as select * from jumun2 where 1=2;
-- 시퀀스 생성
create sequence seq_jumun3
start with 1
maxvalue 1000
cache 100;
-- 시퀀스 조회
select seq_jumun3.currval from dual; -- 조회불가(아직 시작안되서)
select seq_jumun3.nextval from dual; -- 시작
select seq_jumun3.currval from dual; -- 조회가능
-- currval 은 해당세션에서 nextval을 안하면 조회되지 않는다. 시퀀스를 조회하고 싶다면 해당테이블 select로 확인하자.
-- 데이터 입력
insert into jumun3 values(seq_jumun3.nextval,1,1,1,sysdate);
select * from jumun3;
commit;
-- 시퀀스 현황 조회
select * from dba_sequences where sequence_owner = 'SCOTT';
-- db 비정상 종료
-- cmd창에서 'sqlplus / as sysdba' : sysdba라는 권한으로 접속하란 뜻
-- 후에 'shutdown abort' : db 강제종료
-- 'startup' : db 기동
-- 데이터 입력
insert into jumun3 values(seq_jumun3.nextval,1,1,1,sysdate);
select * from jumun3; -- 캐시에 이미 100번까지 들어있어서 세션강종 이후에는 101번이 들어가게됐다.
commit;

— 3. 시노님
— 4. USER관리
— 5. 권한
— 6. 인덱스

— [기타 SQL 문법]
— 1. 윈도우 함수
— 2. 정규식 표현식
— 3. pivot/unpivot
— 4. 계층형질의
— 5. 그룹함수

오늘 문제

-- 1. 제약조건 조인문제
-- dba_constraints, dba_cons_columns 뷰를 사용하여 scott 소유의
-- 제약조건 이름, 소유자, 테이블명, 제약조건타입, 제약조건컬럼명, 참조테이블명, 참조컬럼명을 함꼐 출력
-- 단, 참조테이블이 없는 제약조건도 모두 출력
select * from dba_constraints where owner = 'SCOTT';
select * from dba_cons_columns where owner = 'SCOTT';

select d1.CONSTRAINT_NAME as 제약조건명,
       d1.OWNER as 소유자,
       d1.TABLE_NAME  as 테이블명,
       d1.CONSTRAINT_TYPE  as 제약조건타입,
       c1.COLUMN_NAME as 제약조건컬럼명,
       d2.CONSTRAINT_NAME  as 참조테이블명,
       c2.COLUMN_NAME as 참조컬럼명
from dba_constraints d1,
     dba_constraints d2,
     dba_cons_columns c1,
     dba_cons_columns c2
where d1.owner = 'SCOTT'
and d1.R_CONSTRAINT_NAME = d2.CONSTRAINT_NAME(+)
and d1.CONSTRAINT_NAME=c1.CONSTRAINT_NAME
and d2.CONSTRAINT_NAME=c2.CONSTRAINT_NAME(+);

-- 2. 아래를 수행한 뒤
-- empno 컬럼에 pk생성(만약 중복 데이터가 있다면 단 한 건을 남기고 나머지 모두 제외)
create table emp_test99 as select * from emp;
insert into emp_test99 select * from emp where ename = 'ALLEN';
insert into emp_test99 select * from emp where ename = 'ALLEN';
commit;
select rowid, rownum, e.* -- rownum은 출력때마다 달라지는 임의 값이다. 1부터 식별가능
from emp e; -- rowid는 고유값이라 식별 가능
select * from emp_test99;
desc emp_test99;
alter table emp_test99 add constraint emp_test99_empno_pk primary key(empno);
-- rowid 한개만 구하기
select rowid
from emp_test99
where ename='ALLEN'
and rownum=1;
-- 구한 rowid만 빼고 다 삭제
delete from emp_test99
where ename='ALLEN'
and rowid not in (select rowid
                    from emp_test99
                    where ename='ALLEN'
                    and rownum=1);
rollback;
commit;

-- 3. SQL_코테1_실습1_스크립트.sql, SQL_코테1_실습2_스크립트.sql을 실행한 뒤
select count() from employee; -- 11건 
select count() from sellings; -- 40건
select count() from places; -- 11건 
select count() from place_reviews; -- 350건
-- [문제 설명]
-- employee 테이블은 보험 회사 직원의 정보를 담고 있는 테이블입니다.
-- employee 테이블 구조는 다음과 같다.
--name type nullable 설명
--id number FALSE 직원아이디
--name varchar FALSE 이름
--salary number FALSE 월급
--branch_id number FALSE 근무 대리점 아이디

--sellings 테이블은 자동차 보험 판매 기록을 담고 있는 테이블입니다.
--테이블 구조는 다음과 같습니다.
--name type nullable 설명
--car_id number FALSE 보험 대상 자동차 ID
--employee_name number FALSE 보험 설계사 사원 ID
--created_at date FALSE 계약일
--price int number FALSE 보험료

--employee 테이블과 sellings 테이블을 이용하여 대리점별로 보험을 몇 건이나 성사했는지 조회하는 SQL문을 작성.
select * from employee;
select * from sellings;

select * from employee;
select * from sellings;

select e.branch_id as 대리점,
       count(s.car_id) as 성사건수
from employee e,
     sellings S
where e.id = s.employee_name
group by e.branch_id;