집합연산자

• UNION → 합집합(중복 제거)
• UNION ALL → 합집합(중복 가능)
• INTERSECT → 교집합
• MINUS → 차집합

--집합연산자
select name from student where deptno1=101;
select name from student where deptno2=201;
select name from student where deptno1=101 union select name from student where deptno2=201;
select name from student where deptno1=101 union all select name from student where deptno2=201;
select name from student where deptno1=101 intersect select name from student where deptno2=201;
select name from student where deptno1=101 minus select name from student where deptno2=201;

집계함수

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

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

--max, min
select max(sal),min(sal) from emp;
select max(hiredate) 최근입사자,min(hiredate) 가장오래된입사자 from emp;

포맷함수

--콘솔에 출력할 때 dual 사용
select to_char (sysdate,'yyyy-mm-dd') 현재날짜 from dual;
select to_char (sysdate+1,'yyyy-mm-dd') 내일날짜 from dual;
select to_char (sysdate,'yy-mm-dd') 현재날짜 from dual;
select to_char (sysdate,'yyyy-mm-dd hh24:mi') 현재날짜 from dual;

round

--round
select round(avg(sal),2) 급여평균 from emp; --소수점 2자리
select round(avg(sal),1) 급여평균 from emp; --소수점 1자리
select round(avg(sal),0) 급여평균 from emp; --소수점 x, 반올림
select round(avg(sal),-1) 급여평균 from emp; --10단위
select round(avg(sal),-2) 급여평균 from emp; --100단위

서브쿼리

쿼리 안의 쿼리

서브쿼리가 먼저 수행된 후 결과값을 메인쿼리에 전달

order by 사용불가