Oracle #3

Group By

• 계산함수, 집계함수 사용
• group by절 : 그룹화 절
• having 절 : group by에 대한 조건절
  🚨 WHERE, HAVING, GROUP BY에는 별칭을 사용 불가!

select 
from 
where 
group by 
having 
order by 

예시

급여가 2000 이상인 사원들의 부서별 평균 급여의 반올림 값을 평균 급여 오름차순 정렬

SELECT deptno, ROUND( AVG(sal) ) FROM emp
WHERE sal >= 2000 GROUP BY deptno
ORDER BY ROUND( AVG(sal) ) ASC ;

DEPTNO     ROUND(AVG(SAL))
---------- ---------------
        30            2850
        20            2988
        10            3725

JOIN

조인을 쓰면 두 개의 테이블을 엮어서 원하는 데이터를 추출할 수 있다.

• INNER JOIN(내부 조인) : 두 테이블을 조인할 때, 두 테이블에 모두 지정한 열의 데이터가 있어야 한다.
• OUTER JOIN(외부 조인) : 두 테이블을 조인할 때, 1개의 테이블에만 데이터가 있어도 결과가 나온다.
• CROSS JOIN(상호 조인) : 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인하는 기능이다.
• SELF JOIN(자체 조인) : 자신이 자신과 조인한다는 의미로, 1개의 테이블을 사용한다.

SELECT 칼럼명1,칼럼명2..
FROM 테이블명1, 테이블명2..
WHERE JOIN 조건 AND 다른 조건

INNER JOIN(내부 조인)

두 테이블을 연결할 때 가장 많이 사용하는 조인

SELECT <열 목록>
FROM <첫 번째 테이블>
    INNER JOIN <두 번째 테이블>
    ON <조인 조건>
[WHERE 검색 조건]

OUTER JOIN(외부 조인)

내부 조인은 두 테이블에 모두 데이터가 있어야만 결과가 나오지만, 외부 조인은 한쪽에만 데이터가 있어도 결과가 나온다.

SELECT <열 목록>
FROM <첫 번째 테이블(LEFT 테이블)>
    <LEFT | RIGHT | FULL> OUTER JOIN <두 번째 테이블(RIGHT 테이블)>
     ON <조인 조건>
[WHERE 검색 조건]

SELF JOIN(자체 조인)

자체 조인은 자기 자신과 조인하므로 1개의 테이블을 사용한다.

SELECT <열 목록>
FROM <테이블> 별칭A
    INNER JOIN <테이블> 별칭B
[WHERE 검색 조건]

---

서브쿼리

하나의 SQL 문장절에 포함된 또다른 SELECT 문장, 따라서 두 번 질의를 해야 얻을수 있는 결과를 한 번의 질의로 해결이 가능하게 하는 쿼리

• 단일행 서브쿼리 : 서브쿼리의 실행결과가 하나의 칼럼과 하나의 행만을 리턴해주는 쿼리
  (하나의 데이터만 리턴해주는 쿼리)
• 복수행 서브쿼리 : 서브쿼리의 실행결과가 하나의 칼럼과 여러 개의 행을 리턴해주는 쿼리
  (여러 개의 데이터만 리턴해주는 쿼리)

  단일행 서브쿼리 예시
  
  'ALLEN' 의 급여와 동일하거나 더 많이 받는 사원의 이름과 급여를 출력

  SELECT ename, sal FROM emp
  WHERE sal >= (SELECT sal FROM emp WHERE ename='ALLEN');

  직속 상관이 'KING' 인 사원의 이름과 급여를 출력

  SELECT ename,sal FROM emp
  WHERE mgr = (SELECT empno FROM emp WHERE ename='KING');

---

다중행 서브쿼리 예시

30번 부서의 사원 중에서 급여를 가장 많이 받는 사원보다 더 많은 급여를 받는 사원의 이름과 급여를 출력

SELECT ename,sal FROM emp
WHERE sal > ALL(SELECT sal FROM emp WHERE deptno=30);

부서번호가 30번인 사원들의 급여중 최저 급여보다 높은 급여를 받는 사원의 이름, 급여를 출력 (ANY 연산자 사용)

SELECT ename,sal FROM emp
WHERE sal > ANY(SELECT sal FROM emp WHERE deptno=30)

직책이 'SALESMAN' 인 사원의 최소 급여보다 많이 받는 사원들의 이름과 급여,
직책을 출력하되 'SALESMAN' 은 출력하지 않습니다. (ANY 연산자 사용)

SELECT ename, sal, job FROM emp
WHERE sal > ANY(SELECT sal FROM emp WHERE job='SALESMAN')
AND job != 'SALESMAN' ;

---

제약 조건

테이블의 해당 칼럼에 원하지 않는 데이터를 입력/수정/삭제 되는 것을 방지 하기 위해 테이블 생성 또는 변경 시 설정하는 조건
👉 저장된 데이터의 신뢰성을 높이기 위해.

종류
(1) NOT NULL : : NULL 로 입력이 되어서는 안되는 칼럼에 부여하는 조건으로 칼럼 레벨에서만 부여할수 있는 제약조건
(2) UNIQUE KEY : 저장된 값이 중복되지 않고 오직 유일하게 유지되어야 할 때 사용하는 제약조건 (NULL 은 허용)
(3) PRIMARY KEY : NOT NULL 조건과 UNIQUE KEY 를 합친 조건
(4) CHECK : 조건에 맞는 데이터만 입력되도록 조건을 부여하는 제약 조건
(5) FOREIGN KEY : 부모 테이블의 PRIMARY KEY 를 참조하는 칼럼에 붙이는 제약조건

제약 조건 조회

SELECT *
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = "테이블 이름";

제약 조건을 변경하는 명령문

• 추가

ALTER TABLE 테이블이름 
ADD CONSTRAINT 제약이름 제약조건;

• 수정

ALTER TABLE 테이블이름 
MODIFY 컬럼명 컬럼조건;

• 삭제

ALTER TABLE 테이블이름 
DROP CONTRAINT 제약이름;

• 이름 변경

ALTER TABLE 테이블이름
RENAME CONSTRAINT 기존 제약조건 이름 TO 변경할 제약조건 이름;

• 제약 비활성화

ALTER TABLE 테이블이름
DISABLE 제약조건;

• 제약 활성화

ALTER TABLE 테이블이름
ENABLE 제약조건;

제약 조건 예시

create table emp2 -- 테이블 생성 시 제약조건
(empno number(4) constraint emp2_empno_pk primary key,
ename varchar2(12) constraint emp2_ename_nn not null,
deptnno number(2) constraint emp2_deptno_fk references dept2(deptno));

alter table emp2 -- 테이블 생성 후 제약조건 추가
add constraint emp2_empno_pk primary key(empno);

alter table emp2 -- 테이블 생성 후 제약조건 추가
modify ename constraint emp2_ename_nn not null;

alter table emp2 -- 테이블 생성 후 제약조건 추가
add constraint emp2_deptno_fk foreign key(deptno) references dept(deptno);

---

시퀀스(Sequence)

연속적인 숫자 값을 자동으로 증감 시켜서 값을 리턴하는 객체

CREATE SEQUENCE 시퀀스명
 INCREMENT BY 한번에 증감할 양(DEFAULT : +1)
 START WITH 시작값 (DEFAULT: 0 )
 cache 여부

함수

(1) NEXTVAL : 다음 값을 얻어온다.

 SELECT MY_SEQ.NEXTVAL FROM DUAL;

(2) CURRVAL : 현재 값을 얻어온다.

 SELECT MY_SEQ.CURRVAL FROM DUAL;

(3) 삭제

 DROP SEQUENCE 시퀀스명;

(4) 조회

 SELECT SEQUENCE_NAME FROM USER_SEQUENCES;