Alice's Story

** Group Fucntion
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY [ROLLUP|CUBE] group_by_expression]
[HAVING group_condition]


* COUNT : 행의 갯수 출력

* MAX : NULL을 제외한 모든행에서 최대값 출력 - 속도 저하

* MIN : NULL을 제외한 모든 행에서 최소값 출력 - 속도 저하

* SUM : NULL을 제외한 모든 행의 합계

* AVG : NULL을 제외한 모든 행의 평균값

* STDDEV : NULL을 제외한 모든 행의 표준편차

* VARIANCE : NULL을 제외한 모든 행의 분산 값

* GROUPING : 해당 칼럼이 그룹에 사용되었는지 여부를 1 또는 0으로 반환

* GROUPING SET : 한번의 질의로 여러개의 그룹화 가능

* ROLLUP : GROUP BY 절의 그룹 조건에 따라 전체 행을 그룹화하고 각 그룹에 대해 부분합을 구하는 연산자
ROLLUP 연산자의 그룹핑 조합 : n+1

* CUBE: ROLLUP에 의한 그룹 결과와 GROUP BY 절에 기술된 조건에 따라 그룹 조합을 만드는 연산자
CUBE 연산자의 그룹핑 조합 : 2n

** Join
* 드라이빙 테이블(Driving Table) : 조인 시 선행 테이블, Main table

*카티션 곱(cartesian product, cross join)
두개 이상의 테이블에 대해 연결 가능한 행을 모두 결합하는 조인방법.
WHERE 절에서 조인 조건절을 생략하거나 조인 조건을 잘못 설정하여 양쪽 테이블을 연결하는 조건을 만족하는 행이 하나도 없는 경우에 발생한다.

* EQUI JOIN
SQL 문에서 가장 많이 사용되는 조인으로 조인 대상 테이블에서 공통칼럼을 '=' 비교를 통해 같은 값을 갖는 행을 연결하여 결과를 생성하는 조인 방법임

* NON-EQUI JOIN
조인 조건에서 '<', BETWEEN a AND b 와 같이 '=' 조건이 아닌 다른 종류의 연산자를 사용하는 조인 방법

* OUTER JOIN
NULL에 어떤 연산을 적용시켜도 결과는 null 이 된다.
따라서 조인조건 중 하나의 값이 NULL일 경우 값이 NULL이 되어 출력되지 않는다.
하지만 결과가 NULL 일지라도 출력해야 할 경우가 발생하는데 이때 사용되는 것이 OUTER JOIN 이다.
(+) 기호를 사용한 OUTER JOIN NULL이 출력되는 칼럼에 (+) 기호를 추가하면 된다.
예를 들어 학생 테이블과 교수 테이블을 조인할 때 지도 교수가 배정되지 않은 학생의 명단(즉, 지도교수 값이 NULL인 명단)도 출력해야 할 경우 교수 테이블에 (+) 기호를 추가한다.
하지만 (+) 기호를 양쪽 테이블에 동시에 사용할 수 없다.

OUTER JOIN 연산자 (+)는 NULL이 존재하는 칼럼 쪽에 표시한다.
예) 지도교수가 없는 학생을 출력 하려 할 때는 지도 교수값이 NULL 임
OUTER JOIN 에서는 IN 연산자를 사용할 수 없다.
다른 조건과 OR 연산자로 결합 할 수 없다.

*SELF JOIN
하나의 테이블에서 두개의 칼럼을 연결하여 EQUI JOIN 을 하는 조인
한 개 이상의 테이블을 특정 컬럼 값이 같은 것을 연계하여 데이터를 조회

** Sub Query
* 단일행 서브쿼리 (스칼라 서브쿼리)
'=' ,'<', '>'  연산자를 사용한 단일행 서브쿼리

* 다중행 서브 쿼리
서브쿼리에서 반환되는 결과 행이 하나 이상일 때 사용하는 서브쿼리.
다중 행 비교 연산자를 사용 가능.

IN : 메인쿼리의 비교 조건이 서브쿼리의 결과중에서 하나라도 일치하면 참. '=' 비교만 가능
ANY, SOME : 메인쿼리의 비교 조건이 서브쿼리의 결과중에서 하나 이상 일치하면 참. '=' 과 '<' , '>' 범위 비교도 가능함
> any (작은값), < any(큰값)
ALL : 메인쿼리의 비교 조건이 서브쿼리의 결과 중에서 모든값이 일치하면 참.
> all(큰값), < all(작은값)
EXISTS : 메인 쿼리의 비교조건이 서브 쿼리의 결과중에서 만족하는 값이 하나라도 존재하면 참.

* 다중 칼럼 서브쿼리
서브쿼리에서 여러 개의 칼럼 값을 검색하여 메인쿼리의 조건절과 비교하는 서브 쿼리이다.
PAIRWISE 방식과 UNPAIRWISE 방식이 있다.

* 상호 연관 서브쿼리
메인쿼리와 서브쿼리간에 검색 결과를 교환하는 서브쿼리이다

*** 서브쿼리 사용시 오류사항
단일행 서브쿼리오류 ? 서브쿼리 리턴값이 1개가 아닌경우
메인쿼리와 서브쿼리 칼럼의 수가 일치하지 않을 경우
서브쿼리내에 ORDER BY 절을 사용할 경우
서브쿼리 검색 결과가 NULL일 경우- NULL과 연산결과는 모두 NULL 됨

*문제 : 이광훈 학생의 학과의 평균몸무게보다 작은 학생들의 학생이름과 학생의 몸무게, 각 학생들의 학과 이름과 지도교수 이름을 출력하시요.
select s.name, s.weight, d.dname, p.name
from student s, department d, professor p
where s.deptno=d.deptno
and s.profno= p.profno(+)
and s.weight< (select avg(weight) from student where deptno=(select deptno from student where name='이광훈'))

** DML
* INSERT INTO table [(column[ ,column,…]) ] VALUES (value [, value,…]);

* UPDATE table SET column = value [, column=value,…] [WHERE condition];

* DELETE [FREOM ] table [WHERE condition];
DELETE FROM table WHERE (column1, column2,..) = (SELECT s_column1,s_column2.. FROM table2 [WHERE condition2])

* MERGE INTO [table] [alias]
USING [table | view| subquery ] alias
ON [ join condition]
WHEN MATCHED THEN
UPDATE SET …..
WHEN NOT MATCHED THEN
INSERT INTO ….
VALUES …;

* COMMIT
트랜잭션 내의 모든 SQL 명령문에 의해 변경된 작업 내용을 디스크에 영구적으로 저장하고 트랜잭션을 종료

* ROLLBACK
트랜잭션 내의 모은 SQL 명령문에 의해 변경된 작업 내용을 전부 취소하고 트랜잭션을 종료

* 시퀀스
CREATE SEQUENCE sequence
[INCREMENT BY n] <- 시퀀스 번호의 증가 값으로 기본값은 1
[START WITH n] <- 시퀀스 시작번호로 기본값은 1
[MAXVALUE n | NOMAXVALUE] <- 생성 가능한 시퀀스 최대값
[MINVALUE n | NOMINVALUE] <-CYCLE일 경우 새로 시작되는 값
[CYCLE | NOCYCLE] <- 시퀀스 번호를 순환 사용할 것인지 지정
[CACHE n | NOCACHE] <- 시퀀스 생성속도를 개선하기 위해 캐싱여부 지정

* CURRVAR과 NEXTVAL
시퀀스에서 생성된 현재 번호를 확인하거나 다음 번호를 생성하는 함수
CREATE SEQUENCE s_seq
INCREMENT BY 1
START WITH 1
MAXVALUE 100;

SELECT s_seq.NEXTVAL FROM dual;
SELECT s_seq.CURRVAL FROM dual;
INSERT INTO student(studno, name, deptno) VALUES (s_seq.NEXTVAL, '홍길동',101);
DROP SEQUENCE s_seq;