Oracle 9일차

어제 문제

--1. 아래 테이블 생성 및 card_history.txt 파일을 적재한 후
-- 하루 의료비 지출을 월 평균 의료비 지출보다 많이 한 날(NUM)을 출력
create table card_history(
num number,
식료품 varchar2(10),
의복 varchar2(10),
외식비 varchar2(10),
책값 varchar2(10),
온라인소액결제 varchar2(10),
의료비 varchar2(10));
select * from card_history;

select avg(to_number(substr(substr(의료비,2,length(의료비)-2),1,instr(의료비,',')-2)||substr(substr(의료비,2,length(의료비)-2),instr(의료비,',')))) as 한달의료비평균
from card_history;

select num,
to_number(substr(substr(의료비,2,length(의료비)-2),1,instr(의료비,',')-2)||substr(substr(의료비,2,length(의료비)-2),instr(의료비,','))) as 의료비
from card_history
where to_number(substr(substr(의료비,2,length(의료비)-2),1,instr(의료비,',')-2)||substr(substr(의료비,2,length(의료비)-2),instr(의료비,','))) > (select avg(to_number(substr(substr(의료비,2,length(의료비)-2),1,instr(의료비,',')-2)||substr(substr(의료비,2,length(의료비)-2),instr(의료비,',')))) as 한달의료비평균
from card_history);

-- 강사님 방법
select avg(to_number(translate(의료비,'!",','!'))) as 의료비평균1,
avg(to_number(replace(replace(의료비,'"'),','))) as 의료비평균2
from card_history;

select *
from card_history
where to_number(translate(의료비,'!",','!')) > (select avg(to_number(translate(의료비,'!",','!')))
from card_history);

--2. customer.txt 파일과 blacklist.txt 파일을 적재 후 블랙리스트가 아닌 직원 정보 출력
create table customer(
mno varchar2(10),
mname varchar2(10),
jdate varchar2(15));

create table blacklist(
mno varchar2(10),
mname varchar2(10),
jdate varchar2(15));

select * from customer;
select * from blacklist;

select *
from customer
where mno not in (select mno from blacklist);

-- 강사님의 sol2) 차집합
select *
from customer
minus
select *
from blacklist;

-- !=조건으로 조인 시 각 행마다 일치하지 않는 모든 행을 연결하므로 잘못된 풀이다.

--3. movie 테이블 데이터를 사용하여 아래 테이블 생성 후 연령대별 영화이용률이 높은 성별을 출력
create table movie2
as
select 연령대, 성별, sum(이용비율) as 이용비율
from movie
group by 연령대, 성별
order by 1,2;
drop table movie2;
select * from movie2;
--1) 다중컬럼
select 연령대, 성별
from movie2
where (연령대, 이용비율) in (select 연령대, max(이용비율)
from movie2
group by 연령대);
--2) 상호연관
select 연령대, 성별
from movie2 m1
where 이용비율 = (select max(이용비율)
from movie2 m2
where m1.연령대=m2.연령대);

--4. 다음 테이블 생성 후 지역시도별 영화 이용비율의 평균 수치보다 낮은 이용비율을 갖는 지역시군구를 출력
create table movie3
as
select 지역시도, 지역시군구, sum(이용비율) as 이용비율
from movie
group by 지역시도, 지역시군구
order by 1,2;
select * from movie3;

-- 서브 쿼리 결과 - 지역시도별 평균이용비율
select 지역시도, avg(이용비율)
from movie3
group by 지역시도;

select *
from movie3 m1
where 이용비율 < (select avg(이용비율)
from movie3 m2
where m2.지역시도 = m1.지역시도);

--5. sales3.csv 파일을 적재 후 code별로 날짜가 작은 순서대로 qty의 누적합 출력
create table sales(
sdate varchar2(10),
code varchar2(10),
qty number);
drop table sales;
--<출력예>
--코드 날짜 누적판매량
--c1 2018-01-01 40
--c1 2018-01-02 84
--c1 2018-01-03 129
--c1 2018-01-04 174
--c1 2018-01-05 239
--c1 2018-01-06 295
--c1 2018-01-07 300
--c2 2018-01-01 34
--c2 2018-01-02 57
--c2 2018-01-03 143
--c2 2018-01-04 167
--….
select code, sdate, qty
from sales
order by code, sdate;

-- 오 이거..?
select code, sum(qty)
from sales s2
where sdate <= '2018-01-02'
group by code
order by code;

-- 아예 조인이나 써볼까? - 이게 정답이었네. 좀 잘 생각해서 조인부터 해봤으면 풀었을듯 ㄲㅂ
select s1.code as 코드, s1.sdate as 날짜, sum(s2.qty) as 누적판매량
from sales s1, sales s2
where s1.sdate>=s2.sdate
and s1.code = s2.code
group by s1.code, s1.sdate
order by s1.code;

-- 강사님 방법
select s1.code, s1.sdate, sum(s2.qty)
from sales s1, sales s2
where s1.code=s2.CODE
and s1.sdate>=s2.SDATE
group by s1.code, s1.SDATE
order by 1,2;

수업 내용

— [서브쿼리]

— 1. 단일행 서브쿼리

select *
from EMP
where sal > (select avg(SAL)
from EMP);

— 2. 다중행 서브쿼리

select *
from customer
where mno not in (select mno
from blacklist);

— 3. 다중컬럼 서브쿼리

select *
from emp
where (deptno, sal) in (select deptno, max(sal)
from EMP
group by deptno);

— 4. 상호연관 서브쿼리

select *
from EMP e1
where sal > (select avg(sal)
from EMP e2
where e2.deptno = e1.deptno);

— 5. 인라인 뷰

— from절에 쓰는 서브쿼리
— 하나의 테이블처럼 사용

-- 예제) emp에서 그룹별 최소급여자 출력
select *
from emp e, (select deptno, min(sal) as min_sal
from emp
group by deptno) i
where e.deptno = i.deptno
and e.sal = i.min_sal;

-- 예제) professor 테이블에서 학과별 평균급여보다 낮은 급여를 받는 교수의
-- 이름, 학과명, 급여를 학과별 평균급여와 출력
select * from professor;
select * from department;

select deptno,avg(pay)
from professor
group by deptno;

select p.name, d.dname, p.pay, i.avg_pay
from professor p, department D, (select deptno,avg(pay) as avg_pay
from professor
group by deptno) i
where p.deptno = d.deptno
and p.deptno = i.deptno
and p.pay < i.avg_pay;

— 어제 문제에서 movie를 가공해서 movie2, movie3를 만들었음
— 연령대별 이용비율이 높은 성별 확인
— ->연령대별, 성별, 이용비율을 출력한 뒤에야 확인가능
— 한 번에 다루기엔 큰 데이터를 잘라서 쿼리짜는데에 사용

— 6. 스칼라 서브쿼리 – 스칼라는 ‘하나’같은 느낌

— select절에 사용하는 서브쿼리
— 마치 하나의 컬럼처럼 특정 쿼리 결과를 출력하기 위해 사용
— 서브쿼리 결과가 각 행마다 하나의 값이 매칭되도록 출력 필요
— outer join을 명시하지 않아도 데이터 생략 발생하지 X
— 그치만 스칼라 서브쿼리보다 join을 쓰는 편이 성능이 좋다
— 그래서 스칼라 서브쿼리 사용금지인 회사도 있다.

-- 예제) 각 직원의 이름, 급여, 부서번호, 전체평균급여 출력
select ename, sal, deptno,
(select round(avg(sal)) from emp) as avg_sal
from emp;

-- 예제) 각 직원의 이름, 급여, 부서번호, 각 직원의 소속부서의 평균급여 출력
select ename, sal, deptno,
(select round(avg(sal))
from emp e2
where e2.deptno = e1.deptno) as avg_sal
from emp e1;

-- 예제) student, professor 테이블을 사용하여 각 학생의
-- 이름, 학년, 지도교수이름 출력(단, 스칼라 서브쿼리로)
select name, grade, (select name
from professor p
where p.PROFNO = s.PROFNO) as 지도교수이름
from student s;

-- 예제) student, exam_01, hakjum 테이블을 사용하여 각 학생의
-- 이름, 시험성적, 학점 출력(단, 스칼라 서브쿼리 2개 사용)
select * from hakjum;
select (select name
from student s
where s.STUDNO=e.STUDNO) as 이름,
total as 시험성적,
(select grade
from hakjum H
where e.total between h.min_point and h.max_point) as 학점
from exam_01 e;

— [SQL 종류]

— 1. DDL : Data Definition Language. AUTO COMMIT 자동확정

— 1) CREATE : 객체 생성
— 2) DROP : 객체 삭제(구조 삭제=>아예 없어짐)
— 3) ALTER : 객체 변경(컬럼추가/삭제,데이터타입변경,사이즈변경…)
— 4) TRUNCATE : 구조는 남기고 전체 데이터만 삭제

— 2. DML : Data Manipulation Language. ROLLBACK, COMMIT으로 명령어로 수행 확정

— 확정 안 하면 테이블 락 걸림.
— 1) INSERT : 한 row씩 삽입
— 2) DELETE : 한 row씩 삭제
— 3) UPDATE : 셀 단위로 데이터 변경
— 4) MERGE : 특정 테이블을 참고하여 다른 테이블 변경(병합)

— 3. TCL : Transaction Control Language.

— 1) COMMIT : 저장
— 2) ROLLBACK : 되돌리기
— 3) SAVEPOINT : 저장 지점

— 4. DCL : Data Control Language.

— 1) GRANT : 권한/롤 부여
— 2) REVOKE : 권한/롤 회수

— 5. DQL : Data Query Language.

— 1) SELECT

— [DDL]

— 1. CREATE

— 객체(테이블, 인덱스, 유저, 뷰 등) 생성 언어

— 1) 테이블 생성

— create table 테이블명(
— 컬럼1 datatype [constraint(제약조건)],
— 컬럼2 datatype [constraint(제약조건)],
— …);

-- 예제) 테스트용 테이블 생성
create table test_1(
no number,
name varchar2(10));

— * 데이터 타입 전달 시 주의사항
— – 숫자타입은 데이터 사이즈 생략 가능
— – 문자타입은 데이터 사이즈 필수
— – 날짜타입은 데이터 사이즈 전달 불가

— 2) 테이블 복제 : CTAS. 이미 있는 테이블의 구조나 데이터의 전체 또는 일부를 복제

— CTAS로 권한과 제약조건(primary key라 생긴 not null)은 복제되지 않지만,
— 일반 not null 속성은 복제됨.

-- 예제) emp 테이블과 구조가 데이터가 동일한 emp_backup 테이블 생성
create table emp_backup
as
select * from emp;
select * from emp_backup;

-- 예제) emp 테이블에서 10번 부서원의 사번, 이름, 입사일, 부서번호를 갖는 emp_backup2 테이블생성
create table emp_backup2
as
select empno, ename, hiredate, deptno
from EMP
where deptno=10;

-- 예제) 데이터 없이 구조만 복사
create table emp_backup
as
select *
from EMP
where 1=2;

desc emp_backup;
desc emp;

-- student_backup 테이블 생성 : pk가 가진 not null만 복제 안 됨.
create table student_backup
as
select *
from student;

desc student;
desc student_backup;

-- 예제) TAB 테이블을 사용하여 조회되는 모든 테이블에 대해(BIN으로 시작하는 테이블 제외)
-- 백업테이블 생성 스크립트 출력
select 'create table '||tname||'_backup as select * from '||tname||';'
from tab
where tname not like 'BIN%'
and tname not like '%BACKUP';

— [DROP]

— 객체 삭제. 삭제 후에는 조회되지 않음
— ROLLBACK 불가
— 삭제된 테이블을 RECYCLEBIN에 보관 & flashback 명령어로 테이블 복원가능
— purge 옵션을 사용하여 삭제한 테이블은 RECYCLEBIN에 보관 X
— 문법 :
drop table 테이블명 [purge];

-- 예제) emp_1, emp_2 생성 후 emp_1은 purge 없이, emp_2는 purge 옵션으로 테이블 drop
create table emp_1 as select * from emp;
create table emp_2 as select * from emp;

select * from emp_1;
select * from emp_2;

drop table emp_1;
drop table emp_2 purge;

-- recyclebin 조회
select * from user_recyclebin;

-- recyclebin 에서 테이블 복원하기
flashback table "BIN$VBOFfeFiQPamQo2aZD58uQ==$0" to before drop rename to emp_1;
select * from emp_1;

— [ALTER]

— 객체 변경
— 컬럼추가/삭제, 컬럼데이터타입 변경, 컬럼 사이즈 변경, 컬럼 이름 변경,
— 컬럼 default값 변경, 제약조건 추가/삭제

— 1) 컬럼 추가

— 맨 뒤에만 추가 가능(중간에는 불가능)
— alter table 테이블명 add (컬럼명1 데이터타입 [default] [제약조건],
— 컬럼명2 데이터타입 [default] [제약조건]);
— 한 컬럼만 추가 시 괄호는 생략가능. 하지만 2개 이상 컬럼을 동시에 추가할 경우는 괄호 필수

-- 예제) emp_1에 col1, col2, col3 컬럼 추가
desc emp_1;
alter table emp_1 add col1 number;
alter table emp_1 add (col2 varchar2(10),
col3 char(5));

-- 예제) emp_1에 col4 컬럼 추가 시 default value 선언
alter table emp_1 add col4 date default sysdate;
select * from emp_1;

오늘 문제

--1. professor, department, student 테이블을 사용하여
-- position별로 가장 적은 급여(pay)를 받는
-- 교수의 이름, position, pay, 학과명, 지도학생수 출력
--1-1) 다중컬럼
select * from professor;
select * from department;
select * from student;

select p.name, p.position, p.pay, d.dname, count(s.studno) as 지도학생수
from professor P, department d, student s
where p.deptno = d.deptno
and p.profno = s.profno(+)
and (p.position, p.pay) in (select position, min(pay)
from professor
group by position)
group by p.name, p.position, p.pay, d.dname;

--1-2) 인라인뷰 - 오 이거 복잡해서 못하겠다 ㅋㅋ
select p.name, p.position, p.pay, d.DNAME, count(s.STUDNO)
from professor p,
(select position, min(pay) as min_pay
from professor
group by position) i,
department d,
student s
where p.pay = i.min_pay
and p.position = i.position
and p.deptno = d.DEPTNO
and p.profno = s.PROFNO(+)
group by p.name, p.position, p.pay, d.DNAME;

--1-3) 상호연관
select p.name, p.position, p.pay, d.dname, count(s.studno) as 지도학생수
from professor P, department d, student s
where p.deptno = d.deptno
and p.profno = s.profno(+)
and p.pay = (select min(pay)
from professor p2
where p2.position = p.position)
group by p.name, p.position, p.pay, d.dname;

--2. subway.csv 파일을 subway 테이블을 만들어 적재한 뒤
-- 노선별 승차가 가장 많은 시간대 확인
-- (시간 506 => 5시~6시를 의미)
create table subway(
노선번호 varchar2(10),
시간 number,
승차 number,
하차 number);
select * from subway;
--2-1) 다중컬럼
select 노선번호,max(승차)
from subway
group by 노선번호;

select *
from subway
where (노선번호, 승차) in (select 노선번호,max(승차)
from subway
group by 노선번호);

--2-2) 인라인뷰
select *
from subway s,
(select 노선번호, max(승차) as max_승차
from subway
group by 노선번호) i
where s.노선번호=i.노선번호
and s.승차 = i.max_승차;

--2-3) 상호연관
select *
from subway s1
where 승차 = (select max(승차)
from subway s2
where s2.노선번호=s1.노선번호);

--3. 아래 테이블 생성 후 delivery.csv 파일을 적재한 뒤
-- 해당 테이블을 사용하여 각 시간대별 가장 인기 있는 음식 출력(자유)
create table delivery(
일자 varchar2(10),
시간대 varchar2(10),
업종 varchar2(50),
시도 varchar2(50),
시군구 varchar2(50),
읍면동 varchar2(50),
통화건수 number);
select * from delivery;

select 시간대, 업종, sum(통화건수) as 통화건수
from delivery
group by 시간대, 업종
order by 시간대;

select 시간대, max(d.통화건수)
from (select 시간대, 업종, sum(통화건수) as 통화건수
from delivery
group by 시간대, 업종
order by 시간대) D
group by 시간대;

select 시간대, 업종
from (select 시간대, 업종, sum(통화건수) as 통화건수
from delivery
group by 시간대, 업종
order by 시간대) d
where (시간대, 통화건수) in (select 시간대, max(d.통화건수)
from (select 시간대, 업종, sum(통화건수) as 통화건수
from delivery
group by 시간대, 업종
order by 시간대) D
group by 시간대);