[SQL 쿼리/테이블 기본 정리]

서브쿼리

1) 결과가 하나의 행 : =, >, < 등과 함께 사용

2) 결과가 다중 행 : In, All, Any/Some, Exist 등의 연산자와 함께 사용

NVL(e.comm, 0)

Join 사용법 예시

FROM emp e JOIN dept d ON e.deptno = d.deptno

서브쿼리 예시

WHERE deptno IN (SELECT deptno FROM dept WHERE dname = 'RESEARCH');

- IN은 여러개의 결과를 반환할 때

- = 는 하나의 결과만 반환할 때

WHERE sal > (SELECT AVG(sal) FROM emp);

DISTINCT

SELECT DISTINCT e.ename, e.sal...

ALL

SELECT ename, sal

FROM emp

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

// 30번 부서에 속하는 모든 사람들의 SAL 집합

MAX(위와 동일한 예시)

SELECT ename, sal

FROM emp

WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno=30);

NOT IN ( <> ALL과 동일 )

ANY/SOME

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

==

WHERE sal > (SELECT MIN(sal) FROM emp WHERE deptno=30)

EXISTS

SELECT dname

FROM dept d

WHERE EXISTS (SELECT * FROM emp WHERE deptno=d.deptno AND job='CLERK')

 

---

TABLE

CREATE TABLE

CREATE TABLE emp02 AS SELECT * FROM EMP;

ALTER TABLE 

ALTER TABLE emp01

MODIFY (age CHAR(2));

COLUMN

ALTER TABLE emp01

RENAME COLUMN ename TO emp_name;

 

ALTER TABLE emp01

DROP COLUMN email;

 

RENAME emp01 TO emp_01;

DROP TABLE dept;

 

TRUNCATE

TRUNCATE TABLE dept;

INSERT

INSERT INTO dept

VALUES(60, '환경과','서울')

UPDATE

UPDATE dept

     SET dname='Production', loc='서울'

      WHERE deptno=10;

DELETE

DELETE FROM dept

WHERE deptno=30;