GROUP BY 절

-> 어떤 칼럼 값을 기준으로 그룹으로 묶어서 그룹의 최대값, 최소값, 평균등을 보고자 하는 경우에 쓰인다

SELECT deptno, ROUND(AVG(sal),2) "부서별 평균 급여", COUNT(ename) FROM emp GROUP BY deptno;--

SELECT deptno, COUNT(*) "사원수" ,ROUND(AVG(sal),1) "평균급여" FROM emp GROUP BY deptno ORDER BY deptno;

SELECT deptno, COUNT(*) "사원수" ,ROUND(AVG(sal),1) "평균급여" FROM emp GROUP BY deptno HAVING AVG(sal) >= 2000 ORDER BY deptno;

에러 발생 경우

SELECT deptno, ename FROM emp GROUPT BY deptno -> 기준 이외에 다른 컬럼을 출력하려고 하면 에러 발생(ename 칼럼은 호출 할 수 없다)

-- ex) 인원수가 4명이상 근무하는 부서를 대상으로 인원수, 평균급여를 출력해보세요

SELECT deptno, COUNT(*), ROUND(AVG(sal)) FROM emp GROUP BY deptno HAVING COUNT(*) >= 4;

계층구조 쿼리란?

오라클 데이터베이스 scott 유저의 emp 테이블을 보면 empno와 mgr컬럼이 있으며, mgr 컬럼 데이터는 해당 사원의 관리자의 empno를 의미 한다.

예를 들어서 아래의 데이터를 보면

• - empno 7369사원의 관리자는 7902의 empno를 가진 사원이며
• - empno 7902사원의 관리자는 7566의 empno를 가진 사원이다.

이런 상위 계층과 하위계층의 관계를 오라클에서는 START WITH와 CONNECT BY를 이용해서 쉽게 조회 할 수 있다.

계층구조 쿼리 Synctax

START WITH

• - 계층 질의의 루트(부모행)로 사용될 행을 지정 한다.
• - 서브쿼리를 사용할 수도 있다.

CONNECT BY

• - 이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정 할 수 있다.
• - PRIOR 연산자와 함께 사용하여 계층구조로 표현할 수 있다.
• - CONNECT BY PRIOR 자식컬럼 = 부모컬럼 : 부모에서 자식으로 트리구성 (Top Down)
• - CONNECT BY PRIOR 부모컬럼 = 자식컬럼 : 자식에서 부모로 트리 구성 (Bottom Up)
• - CONNECT BY NOCYCLE PRIOR : NOCYCLE 파라미터를 이용하여 무한루프 방지
• - 서브쿼리를 사용할 수 없다.

LEVEL Pseudocolumn

• - LEVEL은 계층구조 쿼리에서 수행결과의 Depth를 표현하는 의사컬럼이다.

ORDER SIBLINGS BY

• - ORDER SIBLINGS BY절을 사용하면 계층구조 쿼리에서 편하게 정렬작업을 할 수 있다.

CONNECT BY의 실행순서는 다음과 같다.

• - 첫째 START WITH 절
• - 둘째 CONNECT BY 절
• - 세째 WHERE 절 순서로 풀리게 되어있다.

• -- 계층구조커리(Hierachycal Query)
• SELECT level, LPAD(' ',(level-1) * 2,' ')|| LPAD('└',level-1,' ') || ENAME "이름" FROM emp
• START WITH /*ename='KING'*/ mgr IS NULL
• CONNECT BY PRIOR empno = mgr ORDER SIBLINGS BY ename;-- king의 사번이 mgr번호와 같은 사원을 붙힌다
• 
  
• 
  
• 
• 
  

-- 숫자 함수

SELECT '2^3=' || power(2,3) AS "2^3" from dual;

SELECT ENAME, ROUND(SAL * 1.03,0) FROM emp;

-- 문자 함수

SELECT ENAME FROM emp WHERE LOWER(ENAME) = 'smith';

SELECT ENAME, SUBSTR(HIREDATE,1,2) FROM emp WHERE SUBSTR(HIREDATE,1,2) = '81';

SELECT SUBSTR('웰컴투오라클',3,4), SUBSTRB('오라클데이터베이스',10,9 ) FROM DUAL;

SELECT EMPNO, LPAD('.',DEPTNO/2,'.') || ENAME FROM emp ORDER BY DEPTNO;

1. NUMBER 

-> 숫자 데이터를 저장하기 위해서 제공되는 데이터형

형식 -> NUMBER(precision, scale)

precision은 소수점을 포함한 전체 자릿수

scale은 소수점 이하 자릿수

precision, scale 모두 생략 -> 입력한 데이터값만큼만 공간이 할당

반대로 모두 지정해주면 실수 형태의 값만 저장 된다

2. DATE

-> 세기, 년, 월, 일, 시, 분, 초의 날짜 및 시간 데이터를 저장하기 위한 데이터형

기본 형식은 YY/MM/DD

3. CHAR

-> 문자 데이터를 저장하기 위한 데이터형

고정 길이 문자 데이터를 저장한다

CHAR은 주어진 크기만큼 저장 공간이 할당되므로 편차가 심한 데이터를 입력하면 저장 공간이 낭비 된다.

4. VARCHAR2

-> 가변적인 길이의 문자열을 저장하기 위한 데이터형

주로 문자열을 저장하기 위해서는 VARCHAR2를 사용한다

SELECT * FROM tab; -> 사용자가 가지고 있는 테이블을 모두 출력 tab은 TABLE의 약자

DESC 테이블명 -> 테이블의 구조를 출력

-- WHERE 행 조건문, AND, IN

ex) 급여가 2000이상이고 부서번호가 20 인 사원 이름, 부서번호, 급여, 입사일 출력 결과

SELECT ENAME,DEPTNO,SAL,HIREDATE FROM emp WHERE SAL >= 2000 AND DEPTNO =20;

ex) 81년도에 입사하고 직무가 MANAGER가 아닌 사원 이름, 입사일, 직무 출력

SELECT ENAME, HIREDATE, JOB FROM emp WHERE HIREDATE >= '80/12/31'AND HIREDATE <= '82/01/01' AND JOB != 'MANAGER';

ex) 직무가 SALESMAN이 아니고 부서번호가 20이거나 급여가 1500 미안인 사원 이름, 직무, 부서번호, 급여 츨력

SELECT ENAME, JOB, DEPTNO, SAL FROM emp WHERE NOT JOB = 'SALESMAN' AND (DEPTNO = 20 OR NOT SAL < 1500);

ex) 부서 번호가 20이고 급여가 2000이상 3000이하, 직무가 MANAGER이 아닌 사원 이름, 부서번호, 급여, 직무 출력

-- BETWEEN 사용법 -> sal  BETWEEN A AND B sal이 A와 B 사이 인 경우 true

SELECT ENAME, DEPTNO, SAL, JOB FROM emp WHERE DEPTNO = 20 AND (SAL BETWEEN 2000 AND 3000) AND JOB != 'MANAGER';

IN 사용법 -> job IN (a,b) job이 괄호 안에 있는 조건에 하나라도 만족하면 true

SELECT ENAME, EMPNO, JOB FROM emp WHERE JOB IN('CLERK', 'ANALYST') AND EMPNO BETWEEN 7300 AND 7839;

-- LIKE 문자열 검색 할때 사용

-- 81% -> 81로 시작하는 모든 문자열

-- %81 -> 81로 끝나는 모든 문자열

-- %81% -> 81을 포함하는 모든 문자열

SELECT * FROM emp WHERE HIREDATE NOT LIKE '81%';

SELECT * FROM emp WHERE MGR IS NULL;

SELECT * FROM emp ORDER BY SAL, ENAME;

SELECT distinct sysdate FROM emp;

SELECT sysdate() from emp;-- 오라클에서는 파라미터를 받지 않는 함수는 괄호를 없앤다