[오라클]4월 14일 수업예제

--1-6. 변환 함수
--숫자를 문자로 변환 TO_CHAR
SELECT TO_CHAR(100000000, '999,999,999') --9자리까지 숫자를 표기하되 3자리마다 ,를 표출
FROM DUAL;

SELECT TO_CHAR(1000000, '099,999,999') --9자리까지 숫자를 표기하되 3자리마다 ,를 표출하고
FROM DUAL; --앞 자리에 0을 붙여서 출력

SELECT TO_CHAR(10000000000, '999,999,999,999,999')
FROM DUAL;

--문자를 숫자로 변환 TO_NUMBER
--형식지정자의 자리수만 잘 지정해서 사용하거나 형식지정자를 지정하지 않고 사용한다.
SELECT TO_NUMBER('-123.456', '99.999') --문자열 자리수보다 형식지정자는 자리수를 같거나 더 많게 지정해줘야한다.
FROM DUAL;

SELECT TO_NUMBER('123', '999.99')
FROM DUAL;

SELECT TO_NUMBER('1234')
FROM DUAL;

--날짜를 문자로 변환하는 TO_CHAR
--TO_CHAR의 날짜 형식 지정
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD')
     , TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS')
     , TO_CHAR(SYSDATE, 'YYYY/MM/DD AM HH:MI:SS')
     , TO_CHAR(SYSDATE, 'YYYYMMDD DAY')
     , TO_CHAR(SYSDATE, '"오늘은 "YYYY"년 "MM"월 "DD"일 " DAY"입니다."') --형식지정자 안에서 문자열을 추가할 때 ""사용
     , TO_CHAR(SYSDATE, 'YYYY"년 " MONTH DD"일"')
FROM DUAL;

--문자를 날짜로 변환하는 TO_DATE
--날짜 출력 형식인 NLS_DATE_FORMAT 기준으로 출력
SELECT TO_DATE('20211201135123', 'YYYY/MM/DD HH24:MI:SS')
     , TO_DATE('202304141059', 'YYYYMMDD HH24MI')
     , TO_DATE('20230411', 'YYYY-MM-DD')
FROM DUAL;

--TO_DATE 함수로 입사일이 19920201보다 빠른 사원목록 조회
SELECT *
FROM EMP
WHERE HDATE < TO_DATE('19920201', 'YYYYMMDD');

--송강교수의 임용일자를 xxxx년 xx월 xx일 xx요일입니다. 조회(TO_CHAR)
SELECT PNO
     , PNAME
     , TO_CHAR(HIREDATE, 'YYYY"년 "MM"월 "DD"일 "DAY"입니다."') AS HIREDATE
FROM PROFESSOR
WHERE PNAME = '송강';

--1-7. Null값 처리를 해주는 NVL
SELECT ENO
     , ENAME
     , NVL(COMM, -1) AS COMM
FROM EMP;

SELECT CNO
     , CNAME
     , NVL(PNO, 0)
FROM COURSE;

--위 쿼리에서 PROFESSOR와 아우터 조인해서 PNAME이 NULL인 값들은 '교수 배정안됨'이라고 조회
SELECT C.CNO
     , C.CNAME
     , NVL(C.PNO, 0)
     , NVL(P.PNAME, '교수 배정안됨')
FROM COURSE C
         LEFT JOIN PROFESSOR P
                   ON C.PNO = P.PNO;


--업무가 개발인 사람들은 1.5배, 경영인 사람들은 1.3배, 지원인 사람들은 1.1배, 분석인 사람은 그대로 두고 나머지는 0.95배 해준다.

--1-8. 조건 처리해주는 DECODE
SELECT ENO
     , ENAME
     , JOB
     , SAL
     , DECODE(JOB,
              '개발', SAL * 1.5, --업무가 개발인 사람들
              '경영', SAL * 1.3, --업무가 개발이 아닌 사람들중 업무가 경영인 사람들
              '지원', SAL * 1.1, --업무가 개발, 경영이 아닌 사람들중 업무가 ERP인 사람들
              '분석', SAL, --업무가 개발, 경영, ERP가 아닌 사람들중 업무가 분석인 사람들
              SAL * 0.95 --업무가 개발, 경영, ERP, 분석이 아닌 사람들
    ) AS CHANGE_SAL
FROM EMP;

--1-9. 조건 처리해주는 CASE~WHEN
SELECT ENO
     , ENAME
     , JOB
     , SAL
     , CASE JOB
           WHEN '개발' THEN SAL * 1.5 --업무가 개발인 사람들
           WHEN '경영' THEN SAL * 1.3 --업무가 개발이 아닌 사람들중 업무가 경영인 사람들
           WHEN '지원' THEN SAL * 1.1 --업무가 개발, 경영이 아닌 사람들중 업무가 ERP인 사람들
           WHEN '분석' THEN SAL --업무가 개발, 경영, ERP가 아닌 사람들중 업무가 분석인 사람들
           ELSE SAL * 0.95 --업무가 개발, 경영, ERP, 분석이 아닌 사람들
    END AS CHANGE_SAL --END로 CASE문의 종결을 알림, 별칭을 붙여줌
FROM EMP;

--COMM이 NULL인 사람은 해당사항 없음, COMM이 0인 사람은 '보너스 없음', 나머지 사람들은 '보너스 : ' || COMM
--COMM_TXT, ENO, ENAME, COMM
--CASE ~ WHEN
SELECT ENO
     , ENAME
     , COMM
     , CASE NVL(COMM, -1)
           WHEN 0 THEN '보너스 없음'
           WHEN -1 THEN '해당사항 없음'
           ELSE '보너스 : ' || COMM
    END AS COMM_TXT
FROM EMP;

SELECT ENO
     , ENAME
     , COMM
     , CASE
           WHEN COMM = 0 THEN '보너스 없음'
           WHEN COMM IS NULL THEN '해당사항 없음'
           ELSE '보너스 : ' || COMM
    END AS COMM_TXT
FROM EMP;

--DECODE
SELECT ENO
     , ENAME
     , COMM
     , DECODE(NVL(COMM, -1),
              0, '보너스 없음',
              -1, '해당사항 없음',
              '보너스' || COMM) AS COMM_TXT
FROM EMP;

SELECT ENO
     , ENAME
     , COMM
     , DECODE(COMM,
              0, '보너스 없음',
              NULL, '해당사항 없음',
              '보너스' || COMM) AS COMM_TXT
FROM EMP;


-- 그룹함수
-- 맥스
-- 전체 학생 중 최대 평점
select max(avr)
from student;

--전공별 최대 평점
select max(avr), MAJOR
from STUDENT
group by major;


--전공에 따른 학년별

select max(student.avr), student.MAJOR, student.SYEAR, (select sname from student where AVR = max(student.avr))
from student student
group by major, SYEAR;


--- 화학과중 학년별로 최대평점 조사. 최대평점이 2.5 이사잉어야 한다.
select st.sno, st.sname, maxavr.avr, st.MAJOR, st.SYEAR
from student st
         join(select max(avr) as avr, MAJOR, SYEAR
              from student
              group by major, SYEAR
              having major = '화학'
                 and max(avr) > 2.5) maxavr
             on st.avr = maxavr.avr
                 and st.major = maxavr.MAJOR
                 and st.syear = maxavr.syear;
--기말고사 성적 중 과목별로 가장 높은 점수 조회
---------------------------------------------------------중요
--기말고사 성적 중 과목별로 가장 높은 점수 조회 그리고 그 학생의 이름을 구해라
select course.cname, course.cno, a.maxscore, b.SNAME
from course course
         join(select max(RESULT) as maxscore, cno
              from SCORE
              group by cno) a
             on a.cno = course.cno

         join (select sc.result, st.SNAME, sc.CNO
               from student st,
                    SCORE sc
               where st.SNO = sc.SNO) b
              on b.RESULT = a.maxscore and course.CNO = b.CNO;

---걍 score만 가져와서 할수도 있다.


select sc.result, st.SNAME
from student st,
     SCORE sc
where st.SNO = sc.SNO;



--최저값
--테이블 전체 데이터의 통계를 낼 때는 groupby를 사용할 필요가 없다.
select min(result)
from score;

--부서별 연봉 최저값

select DNO, DNAME
from DEPT;

group by DNAME;


    --이엠피를 여기랑 조인해서 찾아와야한다. 그냥 여기다가 ename을 넣으면 안된다.

select min(e.sal), e.dno, d.DNAME
from emp e,
     dept d
where d.DNO = e.DNO
group by e.DNO, d.DNAME
;


--부서별 연봉 최적밧 부서명 포함

select mnsl.dno, d.dname, mnsl.minsal
from dept d
         join(select dno, min(sal) as minsal from emp group by dno) mnsl
             on d.dno = mnsl.dno;


--부서별 연봉 최적밧 부서명, 사원이름 포함(잘못된 groupby)
--공통된 값이 없는 컬럼을 groupby하면 그룹화의 의미가 달라진다.


--부서별 최저값 부서명 포함


select e.ename, min(e.sal), e.DNO, g.DNAME
from emp e
         join (select DNO, DNAME from DEPT) g
              on g.DNO = e.DNO
group by e.ename, e.DNO, g.DNAME, e.DNO, e.ename;
;

select e.ename, e.sal, e.DNO
from emp e from ()k


--====================================================================
--다시해보잦ㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅈ=-==========================================================================
--====================================================================
--다시해보잦ㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅈ=-==========================================================================
--====================================================================
--다시해보잦ㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅈ=-==========================================================================

select a.dno, a.dname, a.minsal, e.ENAME
from (select min(e.sal) minsal, e.dno, d.DNAME
      from emp e,
           dept d
      where d.DNO = e.DNO
      group by e.DNO, d.DNAME) A


   , emp e
where e.dno = a.DNO
  and e.SAL = a.minsal
;


--부서별 연봉 최적밧 부서명 포함

select mnsl.dno, d.dname, mnsl.minsal
from dept d
         join(select dno, min(sal) as minsal from emp group by dno) mnsl
             on d.dno = mnsl.dno;

--==============================================너무어렵다 !!!!!!!!!!======================
--다시해보잦ㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅈ=-==========================================================================
--====================================================================
--다시해보잦ㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅉㅈ=-==========================================================================
--====================================================================
--다시해보잦ㅉㅉ


select sno, cno, max(result)
from score
group by sno, cno
order by sno, cno;
--학생별 최대점수 select
select sno, max(result)
from score
group by sno;
--학생별 최대점수의 과목번호
select mxrs.sno, sc.cno, mxrs.maxres
from (select sno, max(result) as maxres
      from score
      group by sno) mxrs
         join score sc on mxrs.sno = sc.sno and mxrs.maxres = sc.result;
--- 이것도 존나어렵다 ㅂㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄸㄷ


--select 절에 컬럼이 하나 추가되는 순간 groupby에도 추가해줘야 되기 대문에 그룹바이의 의미가 변질된다. 그래서 group by 로 이미 묶여있는 데이터는 서브
--쿼리로 빼서 별도의 테이블로 생각하고 사용한다.


--부서별 연봉 최적밧 부서명 포함
--학생별 최대점수의 과목번호
--------------------------제일 매운거 두개임


--각 부서별 보너스의 합계 comm에 null이 있는 부서가 존재하기 때문에 nvl로 null값을 처리한 comm값으로 합계를 내야 한다
select dno, sum(nvl(comm, 0))
from emp
group by dno;


--count
--각 학과별 학년별 학생수
select major, syear, count(*)
from STUDENT
group by major, syear;

---학과별 교수의 수

select section, count(*)
from professor
group by section

-- 1-5 avg 학과별 평점의 평균
--학과별 평점의 평균을 가져오는 쿼리 만들어진 가상 테이블의 전체 데이터를 사용해서 통계를 내는 거니까 별도의 group vy는 필요없다.

select MAJOR, avg(avr)
from student
group by major;

select max(avg(avr))
from student
group by major;

--having 절 : group by절에 명시된 칼럼들에 대한 조건 작성
--dno이 10, 20, 30인 부서의 평균 급여

select dno, avg(sal)
from emp
group by dno
having dno in ('10', '20', '30');

--group by 에 명시됐거나 통계함수가 아닌 칼럼은 having절에 사용불가

select dno, avg(sal)
from emp
group by dno
having comm > 600;
--그룸바이에 명시되지 않았으니까 안되는것같다.

--and/or 여러개의 조건을 붙일 수 있다.

select dno, avg(sal)
from emp
group by dno
having dno in ('10', '20', '30')
   and avg(sal) <= 3000;

--부서중 평균급여가 가장 높은 부서번호와 평균급여 (emp 테이블만 이용)

select avg(sal), dno
from emp
group by dno;



select avg(sal), DNO
from emp
group by DNO
having avg(sal) = (select max(avg(sal))
                   from emp
                   group by dno);
----- 위 아래가 같다. having에는 별칭을 못써서 그렇게된거.
SELECT MAX(aaa)
from (select avg(sal) as aaa, DNO
      from emp
      group by DNO);

select max(avg(sal))
from emp
group by dno;


--where절에서 그룹함수를 쓰는 방식은 그룹함수를 사용한 쿼리를 서브쿼리로 묶어서 테이블을 만든 다음에 사용할 수 있음
--매우 많은 다른 방법들이 있다.


SELECT *
FROM (SELECT dno, AVG(sal) AS avg_sal
      FROM emp
      GROUP BY dno) avgsal
WHERE avgsal.avg_sal = (SELECT MAX(avg_sal)
                        FROM (SELECT AVG(sal) AS avg_sal
                              FROM emp
                              GROUP BY dno));
;
SELECT dno, AVG(sal)
FROM emp
GROUP BY dno
HAVING AVG(sal) = (SELECT MAX(avg_sal)
                   FROM (SELECT AVG(sal) AS avg_sal
                         FROM emp
                         GROUP BY dno));


select dno, avg(sal)
from emp
group by dno
having avg(sal) = (select max(avg(sal))
                   from emp
                   group by dno);


--임용년도가 동일한 교수의 수를 조회

select professor.PNO, Count(*)
from PROFESSOR
group by professor.PNO;

SELECT EXTRACT(YEAR FROM HIREDATE), COUNT(*)
FROM PROFESSOR
GROUP BY EXTRACT(YEAR FROM HIREDATE);


select to_char(hiredate, 'yyyy'), count(*)
from professor
group by to_char(hiredate, 'yyyy');