오라클 명령어-(3)

• SELECT sum(sal) FROM emp; (데이터의 합계를 구하는 sum)
• SELECT sum(DISTINCT sal), sum(ALL sal), sum(sal) FROM emp; (중복을 제외한 값의 합계,모든 데이터의 합계, 모든 든 데이터의 합계)
• SELECT count(*) FROM emp; (emp테이블의 데이터의 갯수)
• SELECT count(*) FROM emp WHERE deptno = 30; (부서번호가 30번인 직원 수)
• SELECT count(DISTINCT sal), count(ALL sal),count(sal) FROM emp; (중복제거한 데이터의 수, 모든 데이터의 수, 모든데이터의 수)
• SELECT count(comm) FROM emp; (NULL은 count에 포함되지 않는다)
• SELECT count(comm) FROM emp WHERE comm IS NOT NULL;  (결과는 위와 동일)
• SELECT max(sal) FROM emp WHERE deptno = 10; (sal이 가장 높은값)
• SELECT min(sal) FROM emp WHERE deptno = 10; (sal이 가장 낮은 값)
• SELECT min(hiredate) FROM emp WHERE deptno = 20; (부서가 20인 사원중에 가장 장기근무자를 출력)
• SELECT avg(sal) FROM emp; (sal의 평균 출력)
• SELECT round(avg(sal),2) FROM emp; ( 평균을 소수점 3째 자리에서 반올림)
• SELECT avg(sal), deptno FROM emp GROUP BY deptno; (부서별로 평균을 구하는 group by)
• SELECT deptno, job, avg(sal) FROM emp 
  GROUP BY deptno, job 
  ORDER BY deptno, job;  (부서번호와 직책별 평균 급여를  부서번호와 직책별로 정렬)
• SELECT ename, deptno, avg(sal) FROM emp
  GROUP BY deptno; (그룹화된 열 외에 일반 열을 select절에 명시할 수 없다.)
• SELECT deptno, job, avg(sal) FROM emp
  GROUP BY deptno, job
  HAVING avg(sal)>=2000
  ORDER BY deptno, avg(sal); (각 부서의 직책별 평균 급여를 구하되, 평균 급여가 2000 이상인 그룹만 출력(having절을 사용할 때 where절로 인해 데이터가 잘못 누락되는 일이 없게 해야함, 애초에 그룹 대상에서 제외할 대상 데이터가 있을 때 사용))
• SELECT deptno, job, count(*), max(sal), sum(sal),min(sal), round(avg(sal),2) FROM emp
  GROUP BY ROLLUP (deptno, job); (1차 기준 분류에 대한 합계를 구할 수 있다.)
• SELECT deptno, job, count(*), max(sal), sum(sal),min(sal), round(avg(sal),2) FROM emp
  GROUP BY deptno,ROLLUP(job); (그룹화 데이터의 일부만 ROLLUP 또는 CUBE를 할 수 있다.)
• SELECT deptno, job, count(*), max(sal), sum(sal),min(sal), round(avg(sal),2) FROM emp
  GROUP BY CUBE(deptno, job)
  ORDER BY deptno, job; (1,2 차 기준 분류에 대한 합계를 구할 수 있다.)
• SELECT deptno, job, count(*), max(sal), sum(sal),min(sal), round(avg(sal),2) FROM emp
  GROUP BY GROUPING SETS(deptno, job)
  ORDER BY deptno, job; (같은 수준의 그룹화 열이 여러개일 때 각각 열별 그룹화를 통해 결과 출력)
• SELECT deptno, job, count(*), max(sal), sum(sal), min(sal),round(avg(sal),2),
  GROUPING(deptno), GROUPING(job)
  FROM emp
  GROUP BY CUBE(deptno,job)
  ORDER BY deptno, job; (grouping 여부 --> 0: grouping, 1:grouping x)
• SELECT deptno, job, count(*), max(sal), sum(sal), min(sal),round(avg(sal),2),
  GROUPING(deptno), GROUPING(job),
  GROUPING_ID(deptno,job)
  FROM emp
  GROUP BY CUBE(deptno,job)
  ORDER BY deptno, job;

grouping ID의경우 그룹화 결과가 0,0 이면 0

0,1 이면 1

1,0이면 2

1,1이면 3으로 표현된다 (2진수를 10진수 형태로 표현))

• SELECT * FROM emp,dept; -- 두 테이블이 합쳐져서 출력(필요없는 정보까지 조합해서 가져옴)
• SELECT * FROM emp,dept
  WHERE emp.deptno = dept.deptno
  ORDER BY empno; (컬럼명을 비교하는 조건식으로 조인 -> 테이블명.컬럼명,  emp테이블과 dept테이블의 겹치는 컬럼이 deptno이므로 두개가 같을때만 출력한다)
• SELECT * FROM emp E,dept D ( 테이블의 별칭을 지정할 때는 주로 한칸 띄우고 별칭을 지정(emp는 E로 dept는 D로)
  WHERE E.deptno = D.deptno
  ORDER BY empno; (emp테이블과 dept테이블의 겹치는 컬럼이 deptno이므로 두개가 같을 때만 출력한다)
• SELECT E.empno,E.ename,E.deptno,D.dname,D.loc FROM emp E,dept D
  WHERE E.deptno = D.deptno
  ORDER BY D.deptno, E.empno; (컬럼이 양쪽에 들어있을경우 어느 테이블에서 컬럼을 가져올지 헷갈릴 수 있기 때문에 E.deptno 같이 컬럼의 테이블을 지정해주면 된다)
• SELECT E.empno, E.ename, E.sal, D.deptno, D.dname, D.loc FROM emp E, dept D
  WHERE E.deptno = D.deptno AND sal >= 3000
  ORDER BY D.deptno, E.empno; (where절에 추가 조건 주기)
• SELECT * FROM emp E, salgrade S
  WHERE E.sal BETWEEN S.losal AND S.hisal; (emp테이블에 있는 사원의 급여를 salgrade테이블에 있는 급여범위 에 따라 등급 부여)
• SELECT E1.empno, E1.ename, E1.mgr, E2.empno as mgr_empno, E2.ename as mgr_ename from emp E1, emp E2
  WHERE E1.mgr = E2.empno; (직장상사의 이름을 연결짓기 위해 같은 테이블 끼리 조인 하는 셀프조인)
• SELECT E1.empno, E1.ename, E1.mgr,
  E2.empno as mgr_empno, E2.ename as mgr_ename
  FROM emp E1, emp E2
  WHERE E1.mgr(+) = E2.empno
  ORDER BY E1.empno; (left join은 (+)가 오른쪽에 right join은 (+)가 왼쪽에 존재한다.)

표준 문법 join

• SELECT E.empno, E.ename, E.job, E.hiredate, E.sal, E.comm,
  deptno, D.dname, D.loc FROM emp E NATURAL JOIN dept D
  ORDER BY deptno, E.empno; (NATURAL JOIN)
• SELECT E.empno, E.ename, E.job, E.hiredate, E.sal, E.comm,
  deptno, D.dname, D.loc 
  FROM emp E JOIN dept D USING(deptno)
  ORDER BY deptno, E.empno; (JOIN ~USING 결과는 위와 동일)
• SELECT E.empno, E.ename, E.job, E.hiredate, E.sal, E.comm,
  E.deptno, D.dname, D.loc 
  FROM emp E JOIN dept D ON(E.deptno = D.deptno)
  ORDER BY deptno, E.empno; (JOIN ~ ON)
• SELECT E1.empno, E1.ename, E1.mgr,
  E2.empno as mgr_empno, E2.ename as mgr_ename
  FROM emp E1 LEFT OUTER JOIN emp E2 ON (E1.mgr = E2.empno)
  ORDER BY E1.empno; (LEFT OUTER JOIN)
• SELECT E1.empno, E1.ename, E1.mgr,
  E2.empno as mgr_empno, E2.ename as mgr_ename
  FROM emp E1 RIGHT OUTER JOIN emp E2 ON (E1.mgr = E2.empno)
  ORDER BY E1.empno; (RIGHT OUTER JOIN)
• SELECT E1.empno, E1.ename, E1.mgr,
  E2.empno as mgr_empno, E2.ename as mgr_ename
  FROM emp E1 FULL OUTER JOIN emp E2 ON (E1.mgr = E2.empno)
  ORDER BY E1.empno; (FULL JOIN 왼쪽 오른쪽 컬럼의 모든 NULL을 출력)

--(생략)
FROM T1, T2, T3
WHERE T1, T2, T3
WHERE T1.COL = T2.COL AND T2.COL = T3.COL

 

• SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'JONES'); (JONES보다 높은 급여를 받는 사원의 정보를 출력)
• SELECT * FROM emp WHERE hiredate < (SELECT HIREDATE
                                      FROM emp
                                      WHERE ename = 'BLAKE'); (BLAKE보다 빠르게 입사한 사원의 정보를 출력)
• SELECT E.empno, E.ename, E.job, E.sal, D.deptno, D.dname, D.loc
  FROM emp E, dept D
  WHERE E.deptno = D.deptno 
  AND E.deptno = 20
  AND E.sal> (SELECT avg(sal) 
              FROM emp); (부서번호가 20번이면서 전체 급여 평균보다 높은 급여를 받는 사원 정보 출력)
• SELECT * FROM emp WHERE sal IN (SELECT max(sal)
                                  FROM emp
                                  GROUP BY deptno); (각 부서별 최고 급여와 동일한 급여를 받는 사원의 정보 출력)
• SELECT * FROM emp WHERE sal = ANY(SELECT max(sal)
                                    FROM emp
                                    GROUP BY deptno); (서브쿼리가 하나라도 참이면 참)
• SELECT * FROM emp WHERE sal = SOME(SELECT max(sal)
                                    FROM emp
                                    GROUP BY deptno); (any와 결과값 동일)
• SELECT * FROM emp WHERE sal < ANY(SELECT sal
                                  FROM emp
                                  WHERE deptno = 30)
  ORDER BY sal, empno; (부서 번호가 30인 사원들의 최소급여보다 더 적은 급여를 받는 직원 정보 출력 )
• SELECT * FROM emp WHERE sal < ALL(SELECT sal
                                  FROM emp
                                  WHERE deptno = 30)
  ORDER BY sal, empno; (ALL- 모든 서브쿼리의 결과값보다 작은 급여를 받는 직원정보 출력)
• SELECT * FROM emp
  WHERE EXISTS (SELECT dname
              FROM dept
              WHERE deptno = 10); ( 서브쿼리에 결과값이 존재하면 true , 그렇지 않으면 false)
• SELECT * FROM emp
  WHERE (deptno,sal) IN (SELECT deptno, max(sal)
                          FROM emp
                          GROUP BY deptno); (비교할 컬럼이 여러개인 다중컬럼 서브쿼리)
• SELECT E1.empno, E1.ename, E1.deptno, D.dname, D.loc
  FROM (SELECT * FROM emp WHERE deptno = 10) E1, 
      (SELECT * FROM dept) D
  WHERE E1.deptno = D.deptno; (inlineview 예시)
• WITH
  E1 as (SELECT * FROM emp WHERE deptno = 10),
  D as (SELECT * FROM dept)
  SELECT E1.empno, E1.ename, E1.deptno, D.dname, D.loc
  FROm E1,D
  WHERE E1.deptno = D.deptno; (가독성 때문에 with문 사용 결과는 위와 동일)
• SELECT empno , ename, job, sal, (SELECT grade 
                                  FROM salgrade
                                  WHERE E.sal between losal AND hisal) as salgrade,
                                  deptno,
                                  (SELECT dname 
                                  FROM dept
                                  WHERE E.deptno = dept.deptno) as dname
  FROM emp E; (select절에서 사용하는 서브쿼리)