SQL - 함수

변환 함수

함수	설명	사용 예
TO_CHAR( 숫자 또는 날짜, format)	숫자 또는 날짜를 문자 로 변환해 주는 함수	쿼리문	SELECT TO_CHAR(123456789, ‘999,999,999’) FROM DUAL;
		결과	TO_CHAR(123456789, ‘999,999,999’) ------------------------------------------- 123,456,789
TO_NUMBER(ex pr, format)	문자나 다른 유형의 숫 자를 NUMBER 형으로 변환	쿼리문	SELECT TO_NUMBER(‘123456’) FROM DUAL;
		결과	TO_NUMBER(‘123456’) -------------------------- 123456
TO_DATE(char, format), TO_TIMESTAMP (char, format)	문자를 날짜형으로 변 환	쿼리문	SELECT TO_DATE(‘20140101’, ‘YYYY-MM-DD’) FROM DUAL;
		결과	TO_DATE(‘20140101’, ‘YYYY-MM-DD’) ------------------------------------------ 2014-01-01

 

 

NULL 관련 함수

함수	설명	사용 예
NVL(expr1, expr2)	expr1이 null이면expr2를 반환	쿼리문	SELECT NVL(manager_id, employee_id) FROM employees WHERE manager_id IS NULL;
		결과	NVL(manager_id, employee_id) -------------------------------------- 100
NVL2(expr1, expr2, expr3)	expr1이 null이 아니면 expr2를 null이면 expr3을 반환	쿼리문	SELECT employee_id, NVL2(commission_pct, salary+(salary*commission_pct), salary) AS salary2 FROM employees;
		결과
COALESCE(exp r1, expr2, ...)	NULL이 아닌 첫 번째 표현식을 반환	쿼리문	SELECT employee_id, salary, commission_pct, COALESCE(salary * commission_pct, salary) AS salary2 FROM employees;
		결과	급여*커미션 값이 NULL이면 salary를 표시하고, NULL이 아니면 급 여* 커미션 값을 반환
LNNVL(조건식)	조건식의 결과가 FALSE 또는UNKNOWN이면 TRUE, TRUE이면 FALSE를 반 환	쿼리문	SELECT COUNT(*) FROM employees WHERE LNNVL(commission_pct >= 0.2);
		결과

NULLIF(expr1, expr2)	expr1과 expr2를 비교 해 같으면 NULL, 같지 않으면 expr1을 반환	쿼리문	SELECT employee_id, TO_CHAR(start_date, ‘YYYY’) start_year, TO_CHAR(end_date, ‘YYYY’) end_year, NULLIF(TO_CHAR(end_date, ‘YYYY’), TO_CHAR(start_date, ‘YYYY’)) nullif_year FROM job_history;
		결과	job_history 테이블에서 start_date와 end_date의 연도만 추출하 여 두 년도가 같으면 NULL, 같지 않으면 종료년도를 출력한다.

 

 

SELECT emplyee_id,
    TO_CHAR(star_date, 'YYYY') start_year,
    TO_CHAR(end_date, 'YYYY') end_date,
    NULLIF(TO_CHAR(end_date,'YYYY'), TO_CHAR(start_date, 'YYYY')) 업무종료일
    FROM job_history;

 

기본 집계 함수

함수	설명	사용 예
COUNT(expr)	쿼리 결과 건수를 반환. NULL이 아닌 건에 대 해서만 집계한다.	쿼리문	SELECT COUNT(*) FROM employees;
		결과	COUNT(*) ------------- 107
SUM(expr)	expr의 전체 합계를 반 환하는 함수. 숫자만 가 능.	쿼리문	SELECT SUM(quantity_sold) FROM sales;
		결과	SUM(quantity_sold) ---------------------- 918843
AVG(expr)	expr 전체의 평균값을 반환	쿼리문	SELECT AVG(salary) FROM employees;
		결과	AVG(salary) --------------- 6461.831775
MIN(expr), MAX(expr)	MIN – 최소값을 반환MAX – 최대값을 반환	쿼리문	SELECT MIN(salary), MAX(salary) FROM employees;
		결과	MIN(salary), MAX(salary) ----------------------------- 2100 24000

함수	설명	사용 예
VARIANCE(expr), STDDEV(expr)	VARIANCE – 분산을 구 한다. STDDEV – 표준편차를 구한다.	쿼리문	SELECT VARIANCE(salary), STDDEV(salary) FROM employees;
		결과	VARIANCE(salary) STDDEV(salary)-------------- 15284813.7 3909.57973

 

 

 

집합 연산자

집합 연산자의 개념
 여러개의SELECT문을연결해또하나의쿼리를만드는역할을하는것  수학의 집합 개념에서 합집합, 교집합, 차집합 등의 개념과 유사.

 

 

 UNION

합집합을 의미하며 두 개의 데이터의 집합을 모두 포함한 결과를 반환한다.  두개의데이터집합에서중복되는결과는한번만조회된다.
 <예>

한국과 일본의 수출품을 모두 조회

SELECT goods
FROM exp_goods_asia

WHERE country = ‘한국’ UNION
SELECT goods

FROM exp_goods_asia WHERE country = ‘일본’ ;

 

 

 

 

 

 

 

 

 

 

INTERSECT

데이터 집합에서 공통된 항목만 추출해 낸다. 교집합을 의미한다.

SELECT goods
FROM exp_goods_asia

WHERE country = ‘한국’ INTERSECT
SELECT goods

FROM exp_goods_asia WHERE country = ‘일본’ ;

 

 

MINUS

한 데이터의 집합을 기준으로 다른 데이터의 집합과 공통된 항목을 제외한 결과만추출한다. 차집합을의미한다.

SELECT goods
FROM exp_goods_asia

WHERE country = ‘한국’ MINUS
SELECT goods

FROM exp_goods_asia WHERE country = ‘일본’ ;

 

 

 

 

조인의 개념

 두 개 이상의 테이블이나 데이터베이스를 연결하여 데이터를 검색하는 방법이다.

 검색하고싶은컬럼이다른테이블에있을경우주로사용하며여러개의 테이블을 하나의 테이블인 것처럼 활용하는 방법

 보통 Primary Key 또는 Foreign Key로 두 테이블을 연결한다. 테이블을 연결하려면 적어도 하나의 컬럼은 서로 공유되고 있어야 한다.

 

 Join의 종류

 내부 조인(Inner Join) – 동등 조인(Equi Join), 안티 조인(Anti Join), 셀프

조인(Self Join), 세미 조인(Semi Join), 카타시안 조인(Carasian Product)

 외부 조인(Outer Join)

 ANSIJoin–ANSISQL을사용하는조인문법.위의내부조인,외부조인을모두 포함

 

Join의 형태

 INNER JOIN – 양쪽 테이블에서 최소한 하나라도 일치하면 모든 행을 가져온다.

 LEFT JOIN – 왼쪽 테이블에서는 모든 행을 가져오고 오른쪽 테이블에서는 일치하는 행만 가져온다.

 RIGHT JOIN – 오른쪽 테이블에서는 모든 행을 가져오고 왼쪽 테이블에서는 일치하는 행만 가져온다.

 FULL JOIN – 양쪽 테이블의 모든 행을 가져온다.

 

 

 

 

동등 조인(Equi Join)

 가장 기본적인 조인 방법이며, WHERE 절에서 등호(‘=‘) 연산자를 사용하여

2개 이상의 테이블이나 뷰를 연결하는 조인
 WHERE 절 조건에 만족하는 데이터를 추출하는 조인.
 조인조건은컬럼단위로기술하고,두컬럼값이같은행을추출함.

 <예> 사원 테이블(customers)과 부서 테이블(departments)에서 부서 번호(department_id)가 일치하는 행을 양쪽 테이블에서 추출한다.

SELECT a.employee_id, a.emp_name, a.department_id, b.department_name

FROM employees a, departments b

WHERE a.department_id = b.department_id;

 

 

 

 

 

 

 

 

 

 

 

 

세미 조인(Semi Join)

 서브 쿼리를 사용해 서브 쿼리에 존재하는 데이터만 메인 쿼리에서

추출하는 조인 방법으로 IN과 EXISTS를 사용한다.
 서브 쿼리에 존재하는 메인 쿼리 데이터가 여러 건 존재하더라도 최종 반환되는

메인 쿼리 데이터에는 중복되는 건이 없다. 

 

 

 EXISTS 사용

<예> 사원 테이블의 부서 번호(department_id)와 부서테이블의 부서 번호가 같고 급여가 3000 이상인 사원테이블의 모든 컬럼을 가져와서 결과에 존재하는 행들에 대해 부서 테이블에서 부서번호와 부서명을 출력한다.

 

SELECT department_id, department_name FROM departments a

WHERE EXISTS( SELECT * FROM employees b
WHERE a.department_id = b.department_id

AND b.salary > 3000) ORDER BY a.department_name;