Oracle 11일차

어제 문제

--1. professor_1을 professor와 동일하게 만들고
--홈페이지가 없는 직원들은 email_id를 사용하여 다음과 같이 변경
--양선희의 경우 => http://www.itwill.com/lamb1
create table professor_1 as select * from professor;
select * from professor_1;
update professor_1
set hpage = 'http://www.itwill.com/'||substr(email, 1, instr(email,'@')-1)
where hpage is null;
rollback;
commit;

--2. student2 테이블을 student과 동일하게 만들고 생성 후
--모든 학생의 주민번호를 아래와 같은 형태로 수정(그에 필요한 적절한 조치까지)
--7510231901813 => 751023-1901813
create table student2 as select * from student;
select * from student;
desc student2;
select * from student2;
alter table student2 modify jumin char(14); -- 데이터크기 바꿀 때 타입은 유지하자
update student2
set jumin = substr(jumin,1,6)||'-'||substr(jumin,7,7);

--3. movie 테이블을 사용하여
--요일별로 영화 이용비율이 가장 높은 성별을 확인, 요일은 월화수목금토일 순서대로 정렬
select * from movie;
-- 요일 확인
select to_char(to_date(년||to_char(월,'00')||to_char(일,'00'), 'YYYYMMDD'),'day')
from movie;
-- 요일별 성별 이용비율
select 성별,to_char(to_date(년||to_char(월,'00')||to_char(일,'00'), 'YYYYMMDD'),'day') as 요일, sum(이용비율) as 성별이용비율
from movie
group by 성별, to_char(to_date(년||to_char(월,'00')||to_char(일,'00'), 'YYYYMMDD'),'day');
-- 위에서 구한 것 중에서 max인 이용비율
select 요일, max(성별이용비율)
from (select 성별,to_char(to_date(년||to_char(월,'00')||to_char(일,'00'), 'YYYYMMDD'),'day') as 요일, sum(이용비율) as 성별이용비율
from movie
group by 성별, to_char(to_date(년||to_char(월,'00')||to_char(일,'00'), 'YYYYMMDD'),'day'))
group by 요일;
-- 위에서 구한 max인 이용비율, 요일이랑 같은 이용비율, 요일 출력
select i1.요일, i1.성별
from (select 성별,to_char(to_date(년||to_char(월,'00')||to_char(일,'00'), 'YYYYMMDD'),'day') as 요일, sum(이용비율) as 성별이용비율
from movie
group by 성별, to_char(to_date(년||to_char(월,'00')||to_char(일,'00'), 'YYYYMMDD'),'day')) i1,
(select 요일, max(성별이용비율) as 최대비율
from (select 성별,to_char(to_date(년||to_char(월,'00')||to_char(일,'00'), 'YYYYMMDD'),'day') as 요일, sum(이용비율) as 성별이용비율
from movie
group by 성별, to_char(to_date(년||to_char(월,'00')||to_char(일,'00'), 'YYYYMMDD'),'day'))
group by 요일) i2
where i1.성별이용비율 = i2.최대비율
and i1.요일=i2.요일
order by decode(i1.요일, '월요일',1, '화요일',2, '수요일',3, '목요일',4, '금요일',5, '토요일',6, '일요일',7);

-- 강사님 방법 : 굳이 to_char써서 월,일 형태를 00 두자리로 안맞춰도 된다~
select to_char(to_date(년||'/'||월||'/'||일, 'YYYY/MM/DD'),'day') as 요일,
성별, sum(이용비율) as 이용비율
from movie
group by to_char(to_date(년||'/'||월||'/'||일, 'YYYY/MM/DD'),'day'), 성별;

수업 내용

— [DML]

— 1. insert
— 2. update

update 테이블명
set 수정할 컬럼 = 수정할 값 — 서브쿼리 사용 가능
where 수정할 조건; — 서브쿼리 사용 가능
— 주의사항 : 수정할 값은 반드시 각 행별로 하나의 값으로 정의되어야 함

— 다중 컬럼 수정)
update 테이블명
set 수정할 컬럼1=수정할 값1, 수정할 컬럼2 = 수정할 값2,…
where 수정할 조건;

update 테이블명
set (수정할 컬럼1, 수정할 컬럼 2) = (select 수정할 값1, 수정할 값2
from 테이블명
…)
where 수정할 조건;

-- 예제) emp_2를 emp와 동일하게 생성 후 전체 직원의 급여를 각 부서의 평균급여로 수정
create table emp_2 as select * from emp;
select * from emp_2;
update emp_2 e1
set sal = (select avg(sal)
from emp_2 e2
where e1.deptno = e2.DEPTNO
group by deptno);

-- 예제) emp_3 테이블 생성 후 평균급여보다 낮은 급여를 받는 직원의 급여를 평균급여로 수정
create table emp_3 as select * from emp;
update emp_3
set sal = (select avg(sal)
from emp_3)
where sal < (select avg(sal) from emp_3);
select * from emp_3;

-- 예제) emp_4 생성 후 allen의 급여를 원래 급여의 10% 인상된 값으로 (정수 반올림),
-- comm은 기존값에 500을 더한 값으로 동시 수정
create table emp_4 as select * from emp;
select * from emp_4;
update emp_4
set SAL = round(sal*1.1), comm = comm+500
where ename = 'ALLEN';
commit;

-- 예제) emp_4 테이블에서 MANAGER 직급의 직원들의 급여와 comm을 전체직원 중 최대급여와 최대comm으로 동시수정
select * from emp_4;
update emp_4
set (sal, comm) = (select max(sal), max(comm)
from emp_4)
where job = 'MANAGER';

— 3. DELETE

— 행 단위 삭제 언어
— COMMIT, ROLLBACK으로 확정 지어줘야 함.
delete [from] 테이블명
where 삭제할 조건; — where절 생략 시 전체 행 삭제, 서브쿼리 사용 가능

-- 예제) 전체 행 삭제
select * from emp_4;
delete emp_4;
rollback;

-- 예제) 급여가 3000 미만인 직원 정보 삭제
delete emp_4
where sal<3000;
select * from emp_4;
commit;

-- 예제) emp_3에서 급여가 전체 평균 급여보다 낮은 급여를 받는 직원 삭제
select * from emp_3;
delete emp_3
where sal < (select avg(sal)
from emp_3);
commit;

-- 예제) student_3 테이블 생성 후 학년별로 평균키보다 작은키를 갖는 친구 삭제
create table student_3
as
select * from student;
select * from student_3;
delete student_3 s1
where height < (select avg(height)
from student_3 s2
where s2.GRADE = s1.grade);
commit;

— 중요 : drop / truncate / delete의 차이
— drop과 truncate는 즉시 반영. delete는 commit필요.
— truncate는 복구 불가능, drop은 recyclebin으로 복원가능, delete는 undo에 기록 rollback으로 원복가능.
— 즉, truncate만이 저장공간 즉시 회수, 나머지는 저장공간 즉시 반환X
— drop은 구조삭제, truncate와 delete는 데이터만 삭제.
— drop은 빠른 속도로 처리가능(테이블 명만 바꿈), truncate는 기록은 안해서 빠름,
— delete는 데이터 클수록 매우 느림.

— 4. MERGE

— 병합문
— 특정 테이블의 데이터와 같은 데이터로 만드는 작업
— update, insert 등의 작업이 동시에 실행되지만 굳이 merge를 쓸 이유가 없다.
— 성능이 좋지도 않고, 가독성이 좋지도 않고, 안전하지도 않아서.
merge into 수정할 테이블명 old
using 참조테이블명 new
on (연결조건) — 괄호 필수
when matched then
update
set col1 = new.col1
delete (삭제조건) — 괄호 생략가능
when not matched then
insert values(new.col1, new.col2, …);

-- 예제) 아래 테이블 생성 후 menu_old를 menu_new에 맞게 변경(merge)
create table menu_old(
no number,
name varchar2(10),
price number);

create table menu_new(
no number,
name varchar2(10),
price number);

insert into menu_old values(1,'아메리카노',1000);
insert into menu_old values(2,'라떼',2000);

insert into menu_new values(1,'아메리카노',1500);
insert into menu_new values(2,'라떼',3000);
insert into menu_new values(3,'모카',4000);
commit;

select * from menu_old;
select * from menu_new;

merge into menu_old mo
using menu_new mn
on (mo.no = mn.no)
when matched then
update
set mo.name=mn.name, mo.price=mn.price
when not matched then
insert values(mn.no, mn.name, mn.price);
commit;

— [제약조건]

— 데이터 무결성을 위해 만드는 객체(올바른 데이터가 입력/수정되도록 관리하기 위한 객체)
— 독립적으로 생성 불가(테이블의 각 컬럼별로 정의)

create table 테이블명(
컬럼1 데이터타입 [default값] [제약조건],
컬럼1 데이터타입 [default값] [제약조건]
…);

— 1. PRIMARY KEY(PK, 기본키)

— 테이블에서의 고유 식별자를 지정하는 제약조건.
— 예) 학생테이블의 학번, 사원테이블의 사원, 게시글테이블의 게시글번호 등.
— 중복불가, null 허용 X => unique + not null.
— 한 테이블에 기본키는 반드시 한 개만 허용.
— pk를 구성하는 컬럼은 여러 개일 수 있음.
— pk를 구성하는 컬럼이 너무 많아지면 인조식별자를 만들기도 함.

— 2. UNIQUE(고유키)

— 중복을 허용하지 않는 제약조건. 예) 전화번호, 주민번호
— null을 허용. null은 값이 없다라고 봐서 허용

— 3. NOT NULL

— null을 허용하지 않는 특징(제약조건이라고 하지않은 이유는 기본값이 nullable이고 그걸 수정하는 느낌이기 때문)
— 그래서 다른 제약조건과는 다르게 컬럼의 속성을 변경하여 not null 설정 가능

— 4. CHECK

— 컬럼값의 범위를 정하고 싶을 때 사용

— 5. FOREIGN KEY(FK, 외래키)

— 참조테이블의 참조키의 값을 참조하는 제약조건
— 부모-자식 관계 성립. 자식쪽이 FK 가짐. 부모쪽엔 reference key 가짐.
— FK 걸려면 부모쪽에 PK나 unique가 생성되어 있어야 함.
— 자식쪽에 외래키 생성, 이에 대응하는 부모쪽에 참조키 생성
— 자식쪽 제약 : insert, update 제한
— 부모쪽 제약 : update, delete 제한

— [제약조건 생성]

— 1. 테이블 생성 시 정의

— 문법 1
create table 테이블명(
컬럼1 데이터타입 [default] [제약조건],
컬럼2 데이터타입 [default] [제약조건]
…
);
— 문법 2 (삽입하다가 제약조건 때문에 오류 발생 시 확인이 쉬워짐)
create table 테이블명(
컬럼1 데이터타입 [default] [constraint 제약조건명 제약조건],
컬럼2 데이터타입 [default] [constraint 제약조건명 제약조건]
…
);
— 문법 3 (primary key 여러 컬럼으로 설정할 때 사용함)
create table 테이블명(
컬럼1 데이터타입 [default],
컬럼2 데이터타입 [default],
[constraint 제약조건명] 제약조건(컬럼1, 컬럼2, …),
[constraint 제약조건명] 제약조건(컬럼1, 컬럼2, …)
…
);

-- 예제) emp_test100과 dept_test100 테이블 생성
create table dept_test100(
deptno number primary key,
dname varchar2(10) unique,
loc varchar2(10) not null
);

create table emp_test100(
empno number primary key,
ename varchar2(10) not null,
jumin char(13) unique,
hiredate date default sysdate not null,
sal number check(sal>0),
deptno number references dept_test100(deptno));

-- 예제) emp_test100과 dept_test100 테이블에 각 3건씩 데이터 입력
insert into dept_test100 values(10,'인사팀','서울');
insert into dept_test100 values(20,'총무팀','서울');
insert into dept_test100 values(30,'IT팀','과천');
commit;
insert into emp_test100 values(1000,'홍길동','1111111111111',sysdate,5000,10);
insert into emp_test100 values(1000,'홍길동','1111111111111',sysdate,5000,10);
-- 제약조건 이름이 없는 경우 어떤 문제로 데이터 삽입 오류가 발생했는지 파악하기 어려움

-- 예제) emp_test100 테이블 재생성(제약조건마다 이름 갖도록 설정)
drop table emp_test100 purge;

create table emp_test100(
empno number constraint empt100_empno_pk primary key,
ename varchar2(10) constraint empt100_ename_nn not null,
jumin char(13) constraint empt100_jumin_uq unique,
hiredate date default sysdate constraint empt100_hiredate_nn not null,
sal number constraint empt100_sal_chk check(sal>0),
deptno number constraint empt100_deptno_fk references dept_test100(deptno));

insert into emp_test100 values(1000,'홍길동','1111111111111',sysdate,5000,10);
insert into emp_test100 values(1000,'홍길동','1111111111111',sysdate,5000,10);
-- 제약조건 이름이 있어서 어디서 어떤 문제인지 파악하기 쉬움(empt100_empno_pk)

-- 예제) col1~5를 갖는 test100 테이블 생성, col1, col2, col3에 pk 설정
create table test100(
col1 number,
col2 number,
col3 number,
col4 number,
col5 number,
constraint test100_col1_col2_col3_pk primary key(col1, col2, col3));

-- SQL/1. 수업교재/DDL_예제문제
-- 1. MEMBER3 테이블 제약조건 포함해서 생성
CREATE TABLE MEMBER3
(
USERID VARCHAR2(10) constraint member3_userid_pk primary key,
USERNAME VARCHAR2(10) constraint member3_username_nn not null,
PASSWD VARCHAR2(10) constraint member3_passwd_nn not null,
IDNUM VARCHAR2(13) constraint member3_idnum_uk unique,
PHONE VARCHAR2(13),
ADDRESS VARCHAR2(30),
REGDATE DATE,
INTEREST VARCHAR2(15)
);
desc member3;

-- 2. BOARD 테이블 레이아웃 참조하여 테이블과 무결성 제약조건 생성
create table board
(
NO number(4) constraint board_no_pk primary key,
subject varchar2(50) constraint board_subject_nn not null,
content varchar2(100),
rdate date,
userid varchar2(10) constraint board_userid_fk references member3(userid)
);
desc board;

-- 3. member3와 board를 참조하여 무결성 제약조건을 위반하지 않는 데이터를 3건씩 입력
insert into member3 values(1000,'kim','sh','kimsh','010-1234-5678','서울 구로구',sysdate,'음악');
insert into member3 values(1001,'song','ji','songji','010-2222-2222','서울 구로구',sysdate,'옷');
insert into member3 values(1002,'ryu','jh','ryujh','010-3333-3333','경기 부천시',sysdate,'공부');
select * from member3;

insert into board values(2000,'title0','neyong0',sysdate,1000);
insert into board values(2001,'title1','neyong1',sysdate,1001);
insert into board values(2002,'title2','neyong2',sysdate,1002);
select * from board;

-- 4. 3번에서 입력한 데이터를 재입력하는 경우에 발생하는 오류를 확인하여라
'무결성 제약 조건(SCOTT.BOARD_NO_PK)에 위배됩니다.';

-- 5. 4번의 데이터를 재입력할 수 있도록 해당 무결성 제약조건을 비활성화하여라

오늘 문제

-- 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;

-- 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처리. 근데 둘은 중복되면 안됨.
select to_char(date1, 'MM') as 월,
        count(to_char(date1, 'MM')) as 사용횟수,
        sum(amount) as 사용금액
from (select * from transactions where amount<0)
group by to_char(date1, 'MM');
-- 애초에 비어있는 달이 많으니깐 매달 -5씩 해놓은 채로 조건 2개 모두 충족한 달이 보이면 +5해주는 방식으로 가자
select 월
from (select to_char(date1, 'MM') as 월,
                count(to_char(date1, 'MM')) as 사용횟수,
                sum(amount) as 사용금액
         from (select *
                  from transactions
                 where amount<0)
        group by to_char(date1, 'MM'))
where 사용횟수>=3 and 사용금액<-100;
-- 이제 마지막 단계인듯?
select sum(합)-125+5count(월)
from (select sum(amount) as 합
         from transactions) t1,
       (select 월
         from (select to_char(date1, 'MM') as 월,
                         count(to_char(date1, 'MM')) as 사용횟수,
                         sum(amount) as 사용금액
                  from (select *
                           from transactions
                         where amount<0)
                group by to_char(date1, 'MM'))
         where 사용횟수>=3 and 사용금액<-100) t2;