쌍용교육센터 - 18일

2024.03.13

 

일반함수

NVL(value1, value2) : value1이 null이면 value2를 쓴다. 단, value1과 value2의 자료형이 일치해야한다.

SELECT ename,sal,comm,(sal+NVL(comm,0)*12) FROM emp;
SELECT ENAME,NVL(TO_CHAR(COMM),'NO COMMISSION') FROM EMP;

NVL2(VALUE1, VALUE2, VALUE3) : VALUE1이 NULL인지 확인하고, NULL이면 VALUE3을 반환한다. 만약 NULL이 아니라면 VALUE2를 반환한다. 자료형이 일치하지 않아도 된다.

SELECT NVL2(COMM,'COMMISION','NO COMMISION') FROM EMP;

NULLIF(VALUE1,VALUE2) : 두 개의 값이 일치하면 NULL을 반환하고, 두 개의 값이 일치하지 않으면 VALUE1을 반환한다.

SELECT NULLIF(LENGTH(ENAME),LENGTH(JOB)) "NULLIF" FROM EMP;

COALESCE(VALUE1, VALUE2, VALUE3 ....) : NULL값이 아닌 값을 반환한다.(자료형 일치)

SELECT COMM,SAL,COALESCE(COMM,SAL,0) FROM EMP;
SELECT COMM,MGR,SAL,COALESCE(COMM,MGR,SAL) FROM EMP;

DECODE : =비교만 가능(오라클 전용)

select ename,sal,job,
    decode(trunc(sal/1000),5,'A',
                           4,'A',
                           3,'B',
                           2,'C',
                           1,'D',
                             'F') "Grade"
from emp;

그룹함수 : 행 집합 연산을 수행하여 그룹별로 하나의 결과를 산출한다.

AVG() : NULL을 제외한 모든 값들의 평균을 반환, NULL값은 평균 계산에서 무시된다.

SELECT AVG(SAL) FROM EMP;

COUNT() : NULL을 제외한 값을 가진 모든 레코드의 수를 반환.

SELECT COUNT(COMM) FROM EMP;
SELECT COUNT(*) FROM EMP;

COUNT(*)형식을 사용하면 NULL도 계산에 포함한다.

MAX() : 레코드 내에 있는 여러 값 중 가장 큰 값을 반환한다.

SELECT MAX(SAL) FROM EMP;
SELECT MAX(ENAME) FROM EMP;

MIN() : 레코드 내에 있는 여러 값 중 가장 작은 값을 반환한다.

SELECT MIN(SAL) FROM EMP;
SELECT MIN(ENAME) FROM EMP;

SUM() : 레코드들이 포함하고 있는 모든 값을 더하여 반환한다.

SELECT SUM(SAL) FROM EMP;

GROUP BY 절

SELECT절에 집합함수 적용시 개별 컬럼을 지정할 수 없다. 개별 컬럼을 지정할 경우에는 반드시 GROUPB BY절에 지정된 컬럼만 가능히디.

분석함수

rank() : 순위를 표현할 때 사용하는 함수이다.

rank(조건값) within group (order by 조건값 컬럼명) : 특정 데이터의 순위 확인하기

SELECT RANK('SMITH') WITHIN GROUP(ORDER BY ENAME DESC) "RANK"
FROM EMP;

(주의) rank 뒤에 나오는 데이터와 order by 뒤에 나오는 데이터는 같은 컬럼이여야 함.

rank() over(order by 컬럼명) : 전체 순위 보기

select empno, ename, sal, rank() over (order by sal desc) from emp;

partition by : deptno가 같은 행을 묶어서 순위 매김

select empno, ename, sal, deptno, rank() over(partition by deptno order by sal desc) "rank"
from emp;

Join

둘 이상의 테이블을 연결하여 데이터를 검색하는 방법이다. 보통 둘 이상의 행들의 공통된 값 primary key 및 foreign key 값을 사용하여 조인한다. 두 개의 테이블을 select 문장 안에서 조인하려면 적어도 하나의 컬럼이 그 두 테이블 사이에서 공유 되어야 한다.

Cartesian Product(카티션 곱) : 검색하고자 했던 데이터뿐 아니라 조인에 사용된 테이블들의 모든 데이터가 반환되는 현상이다.

카티션 곱이 발생하는 경우

• 조인 조건을 정의하지 않았을 경우
• 조인 조건이 잘못된 경우
• 첫 번째 테이블의 모든 행들이 두 번째 테이블의 모든 행과 조인이 되는 경우

Select * from emp e,dept d;

[Oracle 전용] 동등 조인(Equi Join) : 조건절에서 (=) 조건에 의하여 조인이 이루어짐

select * from emp,dept
where emp.deptno = dept.deptno;

테이블에 알리아스 부여하기
select * from emp e, dept d
where e.deptno=d.deptno;

비동등 조인(Non Equi Join) : 테이블이 어떤 column도 Join할 테이블의 Column에 일치하지 않을 때 사용하고 조인 조건은 동등(=) 이외의 연산자를 갖는다. (between and, is null, in)

select e.ename,e.sal,s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal;

셀프 조인(Self Join) : 동일한 테이블 조인

SELECT E.ENAME 사원이름,M.ENAME 관리자이름
FROM EMP E, EMP M
WHERE E.MGR=M.EMPNO;

외부 조인(Outer Join) : Equi Join 문장들의 한 가지 제약점은 그것들이 조인을 생성하려 하는 두 개의 테이블의 두 개 컬럼에서 공통된 값이 없다면 테이블로부터 데이터를 반환하지 않는다는 것이다. 정상적으로 조인 조건을 만족하지 못 하는 행들을 보기 위해 Outer Join을 사용한다.

select distinct (e.deptno), d.deptno
from emp e, dept d
where e.deptno(+)=d.deptno;

(주의) 누락된 행을 표시하기 위해서 누락된 행이 있는 테이블의 반대 테이블의 조인 조건에 + 명시