[Oracle] SQL그룹함수, TO_CHAR함수, 서브쿼리

[1] SQL그룹함수

--count,sum,avg,max,min

--1)count(*): null값 포함, 나머지는 null값 제외
select count(*),count(bonus),count(hpage) from professor;

--2)sum
select count(bonus),sum(bonus) from professor;

--3)avg
select count(bonus),sum(bonus),avg(bonus) from professor;

--4)max,min
select max(sal),min(sal) from emp;
select max(hiredate),min(hiredate) from emp;

--round: 소수점 지정
select round(avg(sal),1) from emp; --소수점 1자리
select round(avg(sal),2) from emp; --소수점 2자리
select round(avg(sal),0) from emp; --소수점 없애기(자동반올림)
select round(avg(sal),-1) from emp; --10단위
select round(avg(sal),-2) from emp; --100단위

--현재날짜를 콘솔에 출력
select sysdate from dual; --dual:콘솔창에 출력하겠다는 의미

--내일날짜
select sysdate+1 내일 from dual;

--일주일 뒤 날짜
select sysdate+7 "일주일 뒤" from dual;

 

[2] TO_CHAR함수(to_char)

--날짜 및 숫자를 문자로 변환하기위한 함수. 변환시 특정포맷 지정가능(컬럼명[포맷])
--현재년도

select TO_CHAR(sysdate,'year') "년 도" from dual; --영어로 출력됨. 대/소문자 상관없음
select TO_CHAR(sysdate,'yyyy') "년 도" from dual; --2022로 출력됨

--현재 월
select TO_CHAR(sysdate,'MONTH') from dual; --22/08/01인 날짜를 '8월'으로만 출력
select TO_CHAR(sysdate,'mm') from dual; 
select TO_CHAR(sysdate,'yyyy-mm-dd hh-mi-ss') from dual; --시는 hh,hh12,hh24로 표기할수있음

--오라클 클라우드 sysdate는 미국현지시간. 한국 현재시간으로 얻으려면 current_date 또는 current_timestamp
select TO_CHAR(current_date,'yyyy-mm-dd hh-mi-ss') from dual;
select TO_CHAR(current_timestamp,'yyyy-mm-dd hh-mi-ss') from dual;

--to_char는 숫자적용가능
select to_char(2345670,'999,999,999') from dual; --천단위 구분기호
select to_char(25.9,'99999.00') from dual; --9는 값이 없으면 공백
select to_char(25.9,'00999.00') from dual; --0은 값이 없어도 0으로 채워져서 나옴

--emp에서 사원번호,사원명,입사일자(년도만 표기) 출력
select empno 사원번호,ename 사원명,to_char(hiredate,'yyyy') 입사년도 from emp;
--emp에서 사원번호,사원명,급여(천단위 구분기호 넣어서) 출력
select empno,ename,to_char(sal,'$999,999') 급여 from emp;

[3] 서브쿼리

--쿼리문안에 또다른 쿼리가 담기는 것

--메인쿼리(서브쿼리)
--1.emp에서 SCOTT보다 급여를 많이 받는사람의 이름,급여 출력
select ename,sal
from emp
where sal>(select sal from emp where ename='SCOTT');
--서브쿼리가 먼저 수행되어 결과값을 메인쿼리에 전해주고 그 값을 받아서 메인쿼리 수행을 함!
--order by는 최종에 수행하는 것이므로 서브쿼리에 들어갈 수 없음

--2.emp에서 이름이 WARD인 사람의 MGR과 같은값을 가진사람의 전체목록을 출력
select * from emp where MGR=(select MGR from emp where ename='WARD');

--3.emp에서 이름이 S로 시작하는 사람과 같은 부서사람의 부서번호와 이름을 출력
select deptno,ename from emp where deptno in(select deptno from emp where ename like 'S%');

--2가지 조건의 서브쿼리
--emp에서 FORD사원보다 나중에 입사한 사람 중 SCOTT보다 급여가 작은 사람의 사원명,급여,입사일 출력
select ename,sal,hiredate from emp 
where hiredate>(select hiredate from emp where ename='FORD')
and sal<(select sal from emp where ename='SCOTT');