쿼리들 잡동산이...
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)