[오라클] 4월 17일 예제

ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제
dno, job, max(sal), sum(sal), avg(sal), count(*) 를 뽑아내는데 부서별, 그리고 전체 통계를 내라. cube, rollup 써서

 

 

ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제
dno, job, max(sal), sum(sal), avg(sal), count(*) 를 뽑아내는데 부서별, 그리고 전체 통계를 내라 큐브써서.;

1-3 크루핑 셋츠
그룹화로 지정된 컬럼들의 각각의 그룹화를 진행한 결과
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제
그루핑 셋츠를 써서
dno, job, max(sal), sum(sal), avg(sal), count(*) 의 각 dno별, job별 구해라

 

 

ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제
list agg 써서
dno마다 그 안에 어떤 사람들이 있는지 sal로 정렬해서 내라

ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제

job에 따른 max(sal)을 피봇해라
행에는 잡 쫙 나열
아래는 연봉맥스 나열

 

job에 따른 max(sal)을 피봇해라 dno에 따라서도
위에는 잡 쫙 나열
왼쪽에는 부서마다, 최대연봉을

 

ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제
피봇으로 왼쪽에는 에스이어 그리고 위에는 전공에 따른

ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제
위에있는걸 반대로

 

 

ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제
위에는 잡
왼쪽에는 디넘버
각 맥스 살을 구해라

ㅁㅁㅁㅁ문제
그걸 언피봇해라

ㅁㅁㅁㅁㅁ문제
거기에 dno붙여라

 

 

 

 

 

ㅁㅁㅁㅁ ㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제
각 전공에 따른 학년별 최대 평점응 만들어라

ㅁㅁㅁㅁ ㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제

이걸 unpivot으로 바꿔라

 

 

pivot으로 과목명을 열로 만들고 과목명에 해당하는 기말고사 성적의 평균값

 

 

 

 

학생별 기말고사 성적의 평균이 55점 이상인 학생번호, 학생이름, 기말고사 평균 조회(네츄럴조인);

 

 

 

최대급여가 4000만원 이상되는 부서의 번호, 부서이름, 급여 조회

 

 

ㅁㅁㅁㅁ ㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제

과목넘버, 과목이름, 그리고 기말과목의 평균을 갖고오는데 53 이상 조인 온으로, 조인 유징으로

 

 

 

ㅁㅁㅁㅁ ㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제
학점이 3점인 과목의 교수번호, 교수이름, 과목번호, 과목이름, 학점 조회(join-using 사용)

 

 

 

ㅁㅁㅁㅁ ㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제

dno이 10이면서 job이 분석인 사원이나 dno이 20이면서 job이개발인 사원 조회;

 

 

 

 

 

ㅁㅁㅁㅁ ㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제
문제 : 다중컬럼 in절(cno, pno)을 이용해서 기말고사 성적의 평균이 48점 이상인
과목번호 과목명 교수번호 교수이름 기말고사 성적 평균 조회;

 

 

ㅁㅁㅁㅁ ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제

문제 사원의 정보를 다중 컬럼 in을 이용해서 조회
(dno, mgr) 부서번호는 01, 02 사수번호 0001인 사원번호, 사원이름, 사수번호, 부서번호 조회;

 

 

 

가상테이블

 

ㅁㅁㅁ 문제 dno가 10 이고 잡이 개발인놈을 eno, ename, dno dname job

 

 

 

 

화학과 학생명단(스튜던트 테이블의 컬럼 전체),
기말고사 성적 중 과목명에 화학이 포함되는 성적 정보를 가상 테이블
(score의 cno, sno, result, course의 cname)로 만들고
학생별 화학이 포함된 과목의 기말고사 성적의 평균을 구해라(학생번호, 학생이름, 평균기말고사 점수);

 

 

 

 

 

 

===============================================================================roll up, cube 끝

그룹화 관련 함수
    101.롤업
    롤업사용 x
처음에는 그룹화 컬럼 모두에 대한 크룹화 진행
    다음부터는 그룹화 컬럼에서 마지막에 있는 컬럼을 하나씩 제거하고 그룹화 진행
    마지막에는 모든 그룹화 컬럼에 대한 그룹화가 진행되지 않은 전체 데이터에 대한 결과


select dno, job, max(sal), sum(sal), avg(sal), count(*)
from EMP
group by dno, JOB;

롤업을 사용하게 되면 부서별 업무별 한번 출력하고

    ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제
    dno, job, max(sal), sum(sal), avg(sal), count(*) 를 뽑아내는데 부서별, 그리고 전체 통계를 내라. cube, rollup 써서

select dno, job, max(sal), sum(sal), avg(sal), count(*)
from EMP
group by rollup (dno, JOB)
order by dno, job;

select dno, job, max(sal), sum(sal), avg(sal), count(*)
from EMP
group by rollup (JOB), dno
order by dno, job;
;

select dno, job, max(sal), sum(sal), avg(sal), count(*)
from EMP
group by cube (JOB), dno
order by dno, job;
;

--cube와 롤업은 지정방식이 동일하지만 동작방식이 다르다

--그룹화되는 칼럼들의 모든 조합과 그룹화를 진행하여 결과를 출력
;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제
 dno, job, max(sal), sum(sal), avg(sal), count(*) 를 뽑아내는데 부서별, 그리고 전체 통계를 내라 큐브써서.;

select dno, job, max(sal), sum(sal), avg(sal), count(*)
from EMP
group by cube (JOB, dno)
order by dno, job;
;
===============================================================================cube 끝

1-3 크루핑 셋츠
그룹화로 지정된 컬럼들의 각각의 그룹화를 진행한 결과
    ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제
그루핑 셋츠를 써서
    dno, job, max(sal), sum(sal), avg(sal), count(*) 의 각 dno별, job별 구해라

select dno, job, max(sal), sum(sal), avg(sal), count(*)
from EMP
group by grouping sets ( JOB, dno )
order by dno, job;
;
각각 그룹화를 가져오는것과 마찬가지이다


    그룹화 함수
    grouping
    그룹화 여부를 확인


===============================================================================roll up 시작

    그루핑 아이디는 걍 하지 말자 ㅇㅇㅇㅇㅇㅇㅇㅇㅇㅇㅇㅇㅇㅇㅇㅇㅇㅇㅇㅇㅇㅇㅇㅇㅇㅇ
select dno,
       job,
       max(sal),
       sum(sal),
       avg(sal),
       count(*),
       grouping(dno),
       grouping(job)
from EMP
group by rollup ( JOB, dno )
order by dno, job;
;

select dno,
       job,
       max(sal),
       sum(sal),
       avg(sal),
       count(*),
       grouping(dno),
       grouping(job),
       grouping_id(dno, job)
from EMP
group by rollup ( JOB, dno )
order by dno, job;
;

grouping id

    grouping의 결과를 ㅣㅇ진수로 인식하여 십진수로 변환한 값을 표시

select dno,
       job,
       max(sal),
       sum(sal),
       avg(sal),
       count(*),
       grouping(dno),
       grouping(job),
       grouping_id(dno, job)
from EMP
group by rollup ( JOB, dno )
order by dno, job;
;
===============================================================================roll up 끝




===============================================================================listagg 연습 시작
listagg
listagg사용 x;

select dno, ename
from emp
group by dno, ENAME;
;;
use listagg
     그룹화던 컬렘에 속하는 데이터들을 사용할 수 있다.
안에 뭐가 있는지 들여다볼수 있ㄸ음



    ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제
list agg 써서
     dno마다 그 안에 어떤 사람들이 있는지 sal로 정렬해서 내라

select dno,
       listagg(ename, ', ')
               within group (order by sal desc)
from emp
group by dno;

select dno, ename
from emp
group by dno, ENAME;
===============================================================================listagg 연습 끝




===============================================================================피봇 연습 시작
===============================================================================피봇 연습 시작
===============================================================================피봇 연습 시작

    ---1-6 pivot
--pivot을 사용 안했을떄

select job, max(sal)
from emp
group by job;


--pivot을 사용하게 될 경우
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제

job에 따른 max(sal)을 피봇해라
    행에는 잡 쫙 나열
    아래는 연봉맥스 나열

select *
from (select job, sal
      from emp)
    pivot (max(sal)
    for job in ('경영' as oper, '지원' as help, '개발' as dev, '회계' as account,'분석' as analys)
    );

-- 피벗의 메인 셀렉트절은 사용할 컬럼만 가져올 수 있도록 테이블을 재구성 하던가 사용할 컬럼만 가지고 있는테이블을 create table로 새로 맏늘어서 사용한다.


--pivot을 사용하게 될 경우
--무조건 서브쿼리를 하나 만들어야된다 일단
job에 따른 max(sal)을 피봇해라 dno에 따라서도
    위에는 잡 쫙 나열
    왼쪽에는 부서마다, 최대연봉을

select *
from (select job, dno, sal
      from emp)
    pivot (max(sal)
    for job in ('경영' , '지원' as help, '개발' as dev, '회계' as account,'분석' as analys)
    )
order by dno
;

--학과별 학년별 평점의 최대값 => 학과가 컬럼으로 변경되는 쿼리
--데이터들을 열로 바꿔서
--통계함수를 썼는데 group by가 없는 이유는
--데이터들이 컬럼이 되면서 컬럼은 중복으로 존재할 수 없기 때문에
--자동으로 그룹화된다.


ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제
피봇으로 왼쪽에는 에스이어 그리고 위에는 전공에 따른

select*
from (select major, syear, avr from STUDENT)
    pivot (max(avr)
    for major in (
        '화학' as 화학,
        '물리' as 물리,
        '생물' as 생물


        )

    )
;
--학과별 학년별 평점의 최대값 => 학과가 컬럼으로 변경되는 쿼리
--데이터들을 열로 바꿔서
--통계함수를 썼는데 group by가 없는 이유는
--데이터들이 컬럼이 되면서 컬럼은 중복으로 존재할 수 없기 때문에
--자동으로 그룹화된다.
--그 반대로
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제
위에있는걸 반대로
select*
from (select major, syear, avr from STUDENT)
    pivot (max(avr)
    for syear in (
        1 as "1",
        2 as "2",
        3 as "3",
        4 as "4"


        )

    )
;

--unpivot
--unpivot 사용x
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제
위에는 잡
    왼쪽에는 디넘버
    각 맥스 살을 구해라

select *
from (select dno
           , max(decode(job, '경영', sal)) as "경영"
           , max(decode(job, '지원', sal)) as "지원"
           , max(decode(job, '회계', sal)) as "회계"
           , max(decode(job, '개발', sal)) as "개발"
           , max(decode(job, '분석', sal)) as "분석"
      from emp
      group by dno);

--unpivot을 사용할경우

ㅁㅁㅁㅁ문제
그걸 언피봇해라

select *
from (select max(decode(job, '경영', sal)) as "경영"
           , max(decode(job, '지원', sal)) as "지원"
           , max(decode(job, '회계', sal)) as "회계"
           , max(decode(job, '개발', sal)) as "개발"
           , max(decode(job, '분석', sal)) as "분석"
      from emp)
    unpivot (

    sal for job in (경영,지원,회계,개발,분석)
    );
;


ㅁㅁㅁㅁㅁ문제
거기에 dno붙여라

select *
from (select dno
           , max(decode(job, '경영', sal)) as "경영"
           , max(decode(job, '지원', sal)) as "지원"
           , max(decode(job, '회계', sal)) as "회계"
           , max(decode(job, '개발', sal)) as "개발"
           , max(decode(job, '분석', sal)) as "분석"
      from emp
      group by dno)
    unpivot (

    sal for job in (경영,지원,회계,개발,분석)
    );



--1 추가적인 조인 방식
--1 1-1natural join
--natural방식으로 sno sname avg(result)를 더해라
select sno, sname, avg(result)
from score sc
         natural join student st
group by sname, sno


---unpivot
--pivot된 테이블 구조를 만들어 준다.
    ㅁㅁㅁㅁ ㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제
각 전공에 따른 학년별 최대 평점응 만들어라
select *
from (select SYEAR,
             Max(decode(major, '물리', avr)) 물리,
             Max(decode(major, '생물', avr)) 생물,
             Max(decode(major, '화학', avr)) 화학
      from STUDENT
      group by SYEAR);

ㅁㅁㅁㅁ ㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제

이걸 unpivot으로 바꿔라

select *
from (select SYEAR,
             Max(decode(major, '물리', avr)) 물리,
             Max(decode(major, '생물', avr)) 생물,
             Max(decode(major, '화학', avr)) 화학
      from STUDENT
      group by SYEAR)
    unpivot (
    avr for major in (물리, 생물, 화학)

    );


pivot으로 과목명을 열로 만들고 과목명에 해당하는 기말고사 성적의 평균값

select *
from (select course.cname, score.result
      from course course,
           score score
      where course.cno = score.cno)
    pivot (

    avg(result)
    for cname in ('유기화학' as 유기화학, '무기화학' as 무기화학, '정역학' as 정역학)


    );



select *
from (select cname, result
      from score
               natural join course)
    pivot (avg(result)
    for cname in ('정역학' as 정역학, '일반화학' as 일반화학, '양자물리학' as 양자물리학)

    )

--- 여기다가 아래 언피봇 함수도 넣을 수 있음

;

select cname, avg(result)
from score
         natural join course
group by cname;


select *
from (select avg(decode(cname, '정역학', result))   as 정역학,
             avg(decode(cname, '일반화학', result))  as 일반화학,
             avg(decode(cname, '양자물리학', result)) as 양자물리학
      from SCORE
               natural join COURSE)
--일반 테이블을 가져와서
    unpivot (
    resavg for cname in (정역학, 일반화학, 양자물리학)

    );
;



SELECT *
FROM (SELECT course.cname, score.result
      FROM course,
           score
      WHERE course.cno = score.cno)
    PIVOT (
    AVG(result) FOR cname IN ('유기화학' AS 유기화학)
    );

select*
from (select major, syear, avr from STUDENT)
    pivot (max(avr)
    for major in (
        '화학' as 화학,
        '물리' as 물리,
        '생물' as 생물
        )
    )
;
===============================================================================피봇 연습 끝
===============================================================================피봇 연습 끝
===============================================================================피봇 연습 끝


학생별 기말고사 성적의 평균이 55점 이상인 학생번호, 학생이름, 기말고사 평균 조회(네츄럴조인);

select sno, sname, avg(result)
from student
         natural join SCORE
group by sno, sname
having avg(result) >= 55
;

select cname, avg(result)

from course,
     SCORE
where course.CNO = SCORE.CNO
group by cname
;

select cname, avg(result)

from course,
     SCORE
where course.CNO = SCORE.CNO
group by cname
;

=======================================================================natural join 연습

--natural join에서는 조인되는 컬럼에 테이블의 별칭을 달아서 사용할 수 없다.

최대급여가 4000만원 이상되는 부서의 번호, 부서이름, 급여 조회

select dname, dno, max(sal)
from EMP
         natural join DEPT
group by dname, dno
having max(sal) >= 4000;

--조인되는 컬럼에만 별칭 못붙이고
select dno, d.dname, max(e.sal)
from emp e
         natural join dept d
group by dno, d.dname
having max(e.sal) >= 4000;

select e.ename, e.sal, d.dname
from emp e
         natural join DEPT d;
=======================================================================natural join 연습 끝

=======================================================================join using 연습

--join using
--join-on
    ㅁㅁㅁㅁ ㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제

    과목넘버, 과목이름, 그리고 기말과목의 평균을 갖고오는데 53 이상 조인 온으로, 조인 유징으로

select sc.cno, c.cname, avg(sc.result)
from score sc
         join course c
              on sc.cno = c.cno
--조인온에서는 조건을 명시
group by sc.cno, c.cname
having avg(sc.RESULT) >= 53;

--joinusing
---------------------------------------------------------------------두가지방법 비교

과목넘버, 과목이름, 그리고 기말과목의 평균을 갖고오는데 53 이상 조인 유징으로
select cno, c.cname, avg(sc.result)
from score sc
         join course c
              using (cno)
     --조인온에서는 조건을 명시
group by cno, c.cname
having avg(sc.RESULT) >= 53;

--안되는 예

select sc.cno, c.cname, avg(sc.result)
from score sc
         join course c
              using (sc.cno)
     --조인온에서는 조건을 명시
group by sc.cno, c.cname
having avg(sc.RESULT) >= 53;
------------- 조인된 구에는 별명이 들어가면 안되서그럼
-------------------------------------------------------------------두가지방법 비교

ㅁㅁㅁㅁ ㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제
학점이 3점인 과목의 교수번호, 교수이름, 과목번호, 과목이름, 학점 조회(join-using 사용)

select PNO, pname, SECTION cno, cname, ST_NUM
from COURSE
         join PROFESSOR using (pno)
where ST_NUM >= 3;
------공통적인거 잇는것만 using뒤에 붙인다.
=======================================================================join using 연습 끝

=====================================================================================중요한 다중컬럼 in
 2. 다중컬럼  in 절

    ㅁㅁㅁㅁ ㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제

     dno이 10이면서 job이 분석인 사원이나 dno이 20이면서 job이개발인 사원 조회;

이게 잘된 예
select dno, eno, ename, job
from emp
where (dno, job) in (('10', '분석'), ('20', '개발'));
잘못 사용한 쿼리

select dno, eno, ename, job
from emp
where (dno) in ('10', '분석')
  and job in ('20', '개발');
---- 이렇게하면 아예 다갖고 오게됨

이거 세개가 다 잘못된 예이다.
select dno, eno, ename, job from emp where dno in ('10','20') and job in ('분석','개발');

select dno, eno, ename, job
from emp
where (dno = '10' and job = '분석')
   or (dno = '10' and job = '개발')
   or (dno = '20' and job = '분석')
   or (dno = '20' and job = '개발');



select dno, eno, ename, job
from emp
where (dno = '10' and (job = '분석' or job = '개발'))
   or (dno = '20' and (job = '분석' or job = '개발'));

select dno, eno, ename, job
from emp
where (dno, job) in (('10', '분석'), ('20', '개발'));

ㅁㅁㅁㅁ ㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제
문제 : 다중컬럼 in절(cno, pno)을 이용해서 기말고사 성적의 평균이 48점 이상인
과목번호 과목명 교수번호 교수이름 기말고사 성적 평균 조회;

이건 그냥 웨어로 한거
select c.cno, c.cname, p.pno, p.pname, avg(result)
from PROFESSOR p,
     score s,
     course c
where c.CNO = s.CNO
  and p.PNO = c.PNO
group by c.cno, c.cname, p.pno, p.pname
having avg(result) >= 48
;



이건 내가했는데 각각 넣어서 한거. 이것도 되는데?
select COURSE.cno, COURSE.cname, PROFESSOR.pno, PROFESSOR.pname, avg(result)
from PROFESSOR,
     course,
     score score
where (professor.pno) in (course.pno)
  and (score.CNO) in (course.CNO)
group by COURSE.cno, COURSE.cname, PROFESSOR.pno, PROFESSOR.pname
having avg(RESULT) >= 48;
;



이게 제대로된거 아닐까 한다.;

select COURSE.cno, COURSE.cname, PROFESSOR.pno, PROFESSOR.pname, avg(result)
from PROFESSOR,
     course,
     score score
WHERE (COURSE.pno, COURSE.cno) IN (SELECT P.pno, S.cno
                                   FROM PROFESSOR P,
                                        SCORE S,
                                        course c
                                   WHERE P.pno = c.PNO
                                     and s.CNO = c.CNO)
  AND COURSE.pno = PROFESSOR.pno
  AND COURSE.cno = SCORE.cno
group by COURSE.cno, COURSE.cname, PROFESSOR.pno, PROFESSOR.pname
having avg(RESULT) >= 48;
;



풀이;

select cno, c.cname, pno, p.pname, avg(sc.result)
from score sc
         natural join course c
         natural join professor p


where (cno, pno)
          in (select cno, pno
              from score scc
                       natural join course cc
                       natural join professor pp
              group by cno, pno
              having avg(scc.result) >= 48)
group by cno, c.cname, pno, p.pname;
똑같다.


ㅁㅁㅁㅁ ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ문제

문제 사원의 정보를 다중 컬럼 in을 이용해서 조회
    (dno, mgr) 부서번호는 01, 02 사수번호 0001인 사원번호, 사원이름, 사수번호, 부서번호 조회;

select eno, ename, mgr as 사수번호, dno
from EMP
where dno in (01, 02)
  and mgr in (0001);

=== 이렇게 두가지 방법으로 할 수 있음.
select eno, ename, mgr as 사수번호, dno
from EMP
where (dno,mgr) in (select dno, mgr from emp where dno in (01,02) and mgr in (0001));


=====================================================================================중요한 다중컬럼 in 끝

     3.
with 가상테이블 생성
;

ㅁㅁㅁ 문제 dno가 10 이고 잡이 개발인놈을
with 써서 구해라;
with dno10 as (select * from dept where dno = '10'),
     jobdev as (select * from emp where job = '개발')
select jobdev.ENO, jobdev.ENAME, jobdev.DNO, dno10.DNAME, jobdev.JOB
from jobdev,
     dno10
where jobdev.DNO = dno10.DNO;


화학과 학생명단(스튜던트 테이블의 컬럼 전체),
    기말고사 성적 중 과목명에 화학이 포함되는 성적 정보를 가상 테이블
    (score의 cno, sno, result, course의 cname)로 만들고
학생별 화학이 포함된 과목의 기말고사 성적의 평균을 구해라(학생번호, 학생이름, 평균기말고사 점수);

with majorchemi as (select * from student where major = '화학'),
     AAA as (select course.cno, score.SNO, score.RESULT, course.CNAME
             from course course,
                  score score
             where course.CNAME like '%화학%' and course.CNO = score.CNO)

select sno, majorchemi.sname, round(avg(AAA.result),2) as rr
from majorchemi
         natural join AAA
group by sno, majorchemi.sname;


with chmist as (select * from student where major = '화학'),
     chmisc as (select cno, c.cname, sc.sno, sc.result
                from score sc
                         natural join course C
                where c.cname like '%화학%')

select sno, chmist.SNAME, round(avg(chmisc.RESULT), 2)
from chmist
         natural join chmisc
group by sno, chmist.SNAME;