어제 문제
-- 1. emp2, dept2를 사용하여 각 직원과 같은 취미를 가진 동료직원(본인제외)의 수를
-- 각 직원의 사번, 이름, 취미, 부서명과 함께 출력
-- 단, 취미가 같은 동료 직원이 없는 경우도 출력
select * from emp2;
select * from dept2;
select e1.empno, e1.name, e1.hobby, d.dname, count(e2.empno)
from emp2 e1, emp2 e2, dept2 D
where e1.hobby=e2.hobby(+)
and e1.empno!=e2.empno(+)
and e1.deptno=d.dcode
group by e1.empno, e1.name, e1.hobby, d.dname;-- 2. professor, department 테이블을 사용하여 각 교수의 소속학과별로
-- 소속교수의 pay의 평균을 구하되 아래와 같이 소속학과명, 소속학과번호, 소속학과위치 출력
-- deptno dname build avg_pay
-- 101 컴퓨터공학과 정보관 400
-- 102 멀티미디어~ 멀티미디어~ 363
-- 103 소프트웨어~ 소프트웨어관 383
select * from professor;
select * from department;
select d.deptno, d.dname, d.build, round(avg(p.pay)) as avg_pay
from professor p, department D
where p.deptno=d.deptno
group by d.deptno, d.dname, d.build
order by d.deptno;-- 3. student, exam_01, hakjum 테이블을 사용하여
-- 아래와 같이 각 학점별 학생수, 성적평균, 최고점수, 최저점수 출력
--학점 학생수 평균 최저 최고
--A 4 93.75 91 97
--B 12 85 81 89
--C 3 78 77 79
--D 1 62 62 62
select * from student;
select * from exam_01;
select * from hakjum;
select substr(h.grade,1,1) as 학점,
count(s.studno) as 학생수,
avg(e.total) as 평균,
min(e.total) as 최저,
max(e.total) as 최고
from student s, exam_01 e, hakjum h
where s.studno = e.studno
and e.total between h.min_point and h.max_point
group by substr(h.grade,1,1)
order by substr(h.grade,1,1);--4. 아래와 같이 테이블 생성 후(scott 소유)
--std_jumsu_class.csv 파일 데이터를 DB화 한 뒤,
--각 학생의 학생번호, 전과목 평균점수, 같은 클래스 내 각 학생보다 전과목 평균점수가 높은 친구수를 함께 출력
create table std(
학생번호 number,
국어 number,
수학 number,
영어 number,
과학 number,
클래스 varchar2(10));
select * from std;
select s1.학생번호, (s1.국어 + s1.수학 + s1.영어 + s1.과학)/4 as 전과목평균,
count(s2.학생번호) as 평균높은친구수
from std s1, std s2
where s1.클래스 = s2.클래스(+)
and (s1.국어+s1.수학+s1.영어+s1.과학)/4 < (s2.국어(+)+s2.수학(+)+s2.영어(+)+s2.과학(+))/4
and s1.학생번호!=s2.학생번호(+)
group by s1.학생번호, (s1.국어 + s1.수학 + s1.영어 + s1.과학)/4
order by s1.학생번호;--5. student, department, exam_01 테이블을 사용하여
--아래와 같은 정보 출력(각 평균값의 반올림)
-- 남 여
--기계공학과 88 81
--멀티미디어공학과 79 87
--문헌정보학과 87 79
--소프트웨어공학과 84
--전자공학과 80 95
--컴퓨터공학과 93 92
select * from student;
select * from department;
select * from exam_01;
-- 어디다 (+)써야할지 진짜 모르겠다->이거 아예 틀림. 그냥 처음에 한 방법 남겼음.
-- 소프트웨어공학과 행이 출력이 안됐는데, 이런식으로 하면 남자여자가 모두 있어야지 행이 출력됨.
select d.dname, round(avg(e1.total)) as 남, round(avg(e2.total)) as 여
from student s1, student s2, department d, exam_01 E1, exam_01 e2
where s1.deptno1=d.deptno(+)
and s2.deptno1(+)=d.deptno
and substr(s1.jumin,7,1)='1'
and substr(s2.jumin(+),7,1)='2'
and s1.studno=e1.studno(+)
and s2.studno=e2.studno(+)
group by d.dname
order by d.dname;
-- 애초에 방향이 잘못됐음. 결과 화면만 보고 빈칸이 있으니 outer join일거라 생각했지만 그게 아니었고.
-- 그럼 판단을 어떻게 해야되냐면, 주어진 테이블을 보고, 어떤 식으로 진행해야할지 생각해보고,
-- null을 avg하면 null 나오는데 이게 join과정에서 누락된게 아니잖아.
-- 그러면 join과정에서 누락되는 게 없다는 게 보이니 outer join은 아니게 된다.
-- 즉, inner join만으로 해결 가능하다.
-- 그후 작성했을 때 group by 표현식이 아니라는 말에 substr()을 group by에 추가하지말아야했고,
-- 오히려 e.total을 감싸던 집계함수들을 case밖으로 빼서 해결해야함.
select d.dname,
round(avg(case substr(s.jumin,7,1) when '1' then e.total end)) as 남,
round(avg(case substr(s.jumin,7,1) when '2' then e.total end)) as 여
from student s, department d, exam_01 e
where s.deptno1 = d.deptno
and s.studno=e.studno
group by d.dname
order by d.dname;수업 내용
— [서브쿼리]
— 1. 정의 : 쿼리(메인쿼리) 안에 있는 쿼리(서브쿼리)
— 2. 종류
— 1) 위치에 따라(실무적 의미)
— select col1, (select … from …) — 스칼라 서브쿼리(하나의 컬럼처럼 출력을 원할 때)
— from tab1, (select … from …) — 인라인뷰(하나의 테이블처럼 사용하고 싶을 때)
— where col1=(select … from …) — (일반)서브쿼리(상수를 대체하는 표현을 하고 싶을 때)
— 2) 문법에 따라(개념적 의미)
— – 단일행 서브쿼리
— – 다중행 서브쿼리
— – 다중컬럼 서브쿼리
— – (상호)연관 서브쿼리
— [단일행 서브쿼리]
— 서브쿼리 결과가 단 하나의 행을 리턴하는 경우(하나의 컬럼만 출력)
— =,>,< 등의 비교연산 시 반드시 단일행 서브쿼리여야 함
-- 예제) emp에서 전 직원의 전체 평균급여보다 높은 급여를 받는 직원의 이름, 급여 출력
-- step1) 평균급여 확인
select avg(sal)
from EMP;
-- step2) 대상 출력
select *
from emp
where sal>2073.21;
-- step3) 서브쿼리로 변경=상수의 변수화
select *
from EMP
where sal>(select avg(sal) from emp); -- 드래그로 영역 선택해서 ctrl+l 하면 그 부분만 실행-- 예제) emp에서 가장 늦게 입사한 사람의 이름, 입사일, 급여 출력
select ename, hiredate, sal
from emp
where hiredate = (select max(hiredate) from emp);
-- 대소비교, 동등비교는 단일행 서브쿼리만 가능하다.
-- 행이 여러 개가 나오면 비교불가해서.— [다중행 서브쿼리]
— 서브쿼리 결과가 둘 이상의 행을 리턴하는 경우
— =,>,< 등의 비교연산 불가->대체 연산자로 변경하거나 단일행 서브쿼리로 변경
-- 예제) emp에서 이름이 J로 시작하는 직원과 같은 부서의 직원을 모두 출력(J 시작 직원 포함)
-- step1)
select deptno
from emp
where substr(ename,1,1)='J';
-- step2) =연산자 못 써서 대체연산자 in을 사용하는 방법
-- = 사용 시 에러명 : 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.
select *
from EMP
where deptno in (select deptno
from emp
where substr(ename,1,1)='J');-- 예제) emp에서 이름이 J로 시작하는 직원의 급여보다 더 많이 받는 직원 정보 출력
-- sol1)단일행 서브쿼리 형태로 변경하는 방법. 이 방법이 가독성 더 좋아서 추천
select *
from EMP
where sal > (select max(sal)
from emp
where substr(ename,1,1)='J');
-- sol2)대소비교 다중행 서브쿼리 연산자(any, all)로 변경
select *
from EMP
where sal > all(select sal
from emp
where substr(ename,1,1)='J');— any, all 연산자 : 대소비교와 여러 상수를 동시 비교하도록 하는 연산자
— ‘> any(100, 200)’ -> 100보다 크든 200보다 크든 -> ‘> 100’
— ‘< any(100, 200)’ -> 100보다 작든 200보다 작든 -> ‘< 200’ — ‘> all(100, 200)’ -> 100보다도 크고 200보다도 크다 -> ‘> 200’
— ‘< all(100, 200)’ -> 100보다도 작고 200보다도 작다 -> ‘< 100’
— [연습문제]
-- 1. student 테이블에서 여학생 중 키가 가장 큰 학생의 이름, 학년, 키 출력
select name, grade, height
from student
Where height = (select max(height) from student where substr(jumin,7,1)='2')
and substr(jumin,7,1)='2';-- 2. emp2 테이블에서 1005번 부서원들의 취미와 같은 취미를 같는 직원의 이름, 취미, 부서번호 출력
select name, hobby, deptno
from emp2
where hobby in (select hobby
from emp2
where deptno=1005);— [다중 컬럼 서브쿼리]
— 비교할 컬럼이 2개 이상
— 서브쿼리 출력 결과가 2개 이상 컬럼
-- 예제)emp에서 전체 직원 중 최대 급여를 받는 직원의 이름, 부서번호, 급여 출력(단일행 서브쿼리)
select ename, deptno, sal
from emp
where sal = (select max(sal)
from EMP);-- 예제)emp에서 각 부서별 최대 급여를 받는 직원의 이름, 부서번호, 급여 출력(다중컬럼 서브쿼리)
-- 아래에 나오는 잘못된 커리를 검증하기 위한 데이터 수정.
update EMP
set sal=2850
where empno=7782;
-- 아래는 옳은 쿼리. 세트를 괄호로 묶어서 처리해야된다.
select ename, deptno, sal
from emp
where (deptno, sal) in (select deptno, max(sal)
from EMP
group by deptno);
-- 아래는 잘못된 쿼리. deptno=10인데 sal=2850인 BLAKE가 나온다.
-- 그러니 deptno와 sal 묶어서 처리해야된다.
select ename, deptno, sal
from emp
where sal in (select max(sal)
from EMP
group by deptno);-- 예제) student 테이블에서 학년별로 가장 키가 큰 학생의 이름, 학년, 키 출력
select name, grade, height
from student
where (grade, height) in (select grade, max(height)
from student
group by grade);-- 예제) student, exam_01 테이블을 사용하여 각 학년별로 최고성적자의 이름, 학년, 성적 출력
select s.name, s.grade, e.total
from student s, exam_01 e
where s.studno=e.studno
and (s.grade, e.total) in (select s.grade, max(e.total)
from student s, exam_01 E
where s.studno=e.studno
group by s.grade);— [상호연관 서브쿼리]
— 다중컬럼 서브쿼리에서 대소비교가 안되는 점 해결 가능
— 동시에 두 컬럼의 값을 비교하지 않는다.
— 먼저 확인하거나 고정되어야 할 정보(메인쿼리와 서브쿼리간의 연결 조건)를 서브쿼리에 전달
-- 예제) emp에서 부서별 평균급여보다 높은 급여를 받는 직원의 이름, 부서번호, 급여 출력
select ename, deptno, sal
from EMP e1
where sal > (select avg(sal)
from EMP e2
where e1.deptno=e2.DEPTNO);— [연습문제]
-- 1. emp2에서 각 emp_type별 생년월일이 가장 빠른 직원의 이름, emp_type, 생년월일 출력
-- 1) 다중컬럼
select * from emp2;
select name, emp_type, to_char(birthday,'RRMMDD') as 생년월일
from emp2
where (emp_type,birthday) in (select emp_type, min(birthday)
from emp2
group by emp_type);
-- 2) 상호연관->emp_type별로 한 이유는 emp_type에 따라 min(birthday)가 달라져서
-- 그리고 group by는 없어도 되니깐 꼭 지우자.
select name, emp_type, to_char(birthday,'RRMMDD') as 생년월일
from emp2 e1
where birthday = (select min(birthday)
from emp2 e2
where e1.emp_type = e2.emp_type);-- 2. professor, department테이블을 사용하여 각 position별로 평균급여보다 낮은 급여를 받는 사람의
-- 이름, 직급, 급여, 소속부서명 출력
select * from professor;
select * from department;
select p1.name, p1.position, p1.pay, d.dname
from professor p1, department D
where p1.deptno = d.deptno
and p1.pay < (select avg(p2.pay)
from professor p2
where p2.position = p1.position);오늘 문제
--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);--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);--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 * from sales;
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 code, sdate, sum(qty) as 누적판매량 -- fix
from sales s1 -- fix
where (s1.sdate,qty) in (select s2.sdate, sum(s2.qty)
from sales s2 -- fix
where s2.sdate<=s1.sdate -- fix
group by s2.SDATE)
group by code, SDATE -- fix
order by code; -- fix