본문 바로가기

Database/Oracle

쿼리들 잡동산이...


쿼리들 잡동산이...

1. INSERT INTO
DEPT
       SELECT * FROM SCOTT.DEPT ;

2. UPDATE EMP
        SET SAL = SAL * 1.1
        WHERE DEPTNO = 20 ;

3. DELETE FROM EMP
        WHERE SAL < (SELECT AVG(SAL) FROM EMP) ;

4. SELECT empno, ename
         FROM  emp
         WHERE  empno IN (7900, 7934) ;

5. SELECT empno, ename
         FROM  emp
         WHERE  empno NOT IN (7900, 7934);

6. SELECT empno, ename
          FROM  emp
          WHERE  sal BETWEEN  3000 AND 5000 ;

7. SELECT empno, ename
        FROM  emp
        WHERE  UPPER(ename) like '%K%';


8. SELECT empno, ename
         FROM  emp
         WHERE  deptno = 30
         ORDER BY 2

9. SELECT a.dname, b.cnt
         FROM
dept a, (SELECT deptno, COUNT(empno) cnt FROM emp GROUP BY deptno) b
         WHERE a.deptno = b.deptno AND b.cnt > 3     


10. SELECT DISTINCT(a.deptno), b.deptno
         FROM emp a, dept b
         WHERE  
a.deptno = b.deptno

11. SELECT DISTINCT(a.deptno), b.deptno
          FROM emp a, dept b
          WHERE  
a.deptno(+) = b.deptno

12. SELECT DISTINCT(a.deptno), b.deptno
         FROM emp a, dept b
         WHERE  a.deptno(+) = b.deptno
              AND
a.ename(+) LIKE '%'

    DEPTNO     DEPTNO
---------- ----------
        10         10
        20         20
        30         30

                    40


13. 그룹합수
SELECT COUNT(deptno) FROM DEPT ;
SELECT
MAX(sal)  salary FROM  emp ;
SELECT
MIN(sal) salary FROM  emp ;
SELECT
ROUND(AVG(sal),1)  salary FROM  emp WHERE deptno = 30
SELECT
SUM(sal) salary FROM  emp WHERE deptno = 30;
SELECT
ROUND(STDDEV(sal),3) salary FROM  emp WHERE deptno = 30 ;

14. SELECT ename,job
         FROM emp
         WHERE job
= (SELECT job
                             FROM emp
                             WHERE empno = 7369)
;

15. SELECT empno,ename,sal,deptno  
        FROM emp
        WHERE sal
IN (SELECT MAX(sal)
                              FROM emp
                              GROUP BY deptno)
;

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


17. SELECT ename, sal
        FROM emp
        WHERE deptno != 20
             AND sal >
ALL(SELECT sal FROM emp WHERE job='SALESMAN');
 

- EXISTS 연산자를 사용하면 서브쿼리의 데이터가 존재하는가의 여부를 먼저 따져 존재하는 값들만을 결과로 반환해 줍니다.
- SUBQUERY에서
적어도 1개의 행을 RETURN하면 논리식은 참이고 그렇지 않으면 거짓 입니다.

18. SELECT empno, ename, sal
        FROM emp e
        WHERE
EXISTS (SELECT empno FROM emp WHERE e.empno = mgr)

 
19. SELECT empno, sal, deptno
        FROM emp
        WHERE
(sal, deptno) IN ( SELECT sal, deptno
                                            FROM emp
                                            WHERE deptno = 30
                                                 AND comm is NOT NULL )
;

20. SELECT empno, sal, deptno
        FROM emp
        WHERE
sal IN ( SELECT sal
                              FROM emp
                              WHERE deptno = 30
                                   AND comm is NOT NULL )

            AND deptno  IN ( SELECT deptno
                                    FROM emp
                                    WHERE deptno = 30
                                         AND comm is NOT NULL );


21. SELECT b.empno,b.ename,b.job,b.sal, b.deptno
        FROM
(SELECT empno
                  FROM emp  
                  WHERE sal >(SELECT AVG(sal) FROM emp WHERE deptno = 20))
a, emp b
        WHERE a.empno = b.empno
             AND b.mgr is NOT NULL
            AND b.deptno != 20


22. SELECT empno, ename, sal
       FROM emp e
       WHERE sal > (SELECT AVG(sal) sal FROM emp WHERE e.empno = mgr)