[SQL]비교연산자,AND,OR,IN,BETWEEN,LIKE,합집합(UNION),차집합(MINUS),교집합(INTERSECT)

/*비교연산자*/
/* !=, <>, ^=, not */
SELECT * FROM emp where deptno !=10 and deptno !=30;
SELECT * FROM emp where deptno <>10 and deptno <>30;
SELECT * FROM emp where deptno ^=10 and deptno ^=30;
SELECT * FROM emp where not sal=3000;
/*OR, IN*/
SELECT * FROM emp where job='MANAGER' or job='SALESMAN' or job='CLERK';
/*IN*/
SELECT * FROM emp where job in ('MANAGER','SALESMAN','CLERK');
SELECT * FROM emp where job not in ('MANAGER','SALESMAN','CLERK');
SELECT * FROM emp where Deptno in (10 , 20);
/*BETWEEN*/
SELECT * FROM emp where sal>=2000 and asl<=3000;
SELECT * FROM emp where sal BETWEEN 2000 AND 3000 ;
SELECT * FROM emp where sal not BETWEEN 2000 AND 3000 ;
/*LIKE연산자*/
SELECT * FROM emp where ename like '%E';
SELECT ename FROM emp where ename like '_L%' ;
/*LIKE 응용*/
SELECT * FROM emp where some_column like 'A\_A%' ESCAPE '\' ;
/*5-26 NULL값만 출력*/
SELECT * FROM emp where comm is not null;
SELECT * FROM emp where not comm in not null;
SELECT * FROM emp where sal>NULL or comm is null ;
/*UNION 합집합*/
rem UNION MINUS INTERSECT
/*1번 쿼리*/
select empno,ename,sal,deptno from emp
MINUS
/*2번 쿼리*/
select empno,ename,sal,deptno from emp
where deptno=20;
/*5-33 정상동작*/
select empno,ename,sal,deptno from emp
where deptno =10
UNION
select empno,ename,sal,deptno from emp
where deptno=20;
/*5-34*/
select empno,ename,sal,deptno from emp
where deptno =10
UNION
select empno,ename,sal,deptno from emp
where deptno=10;
/*MINUS*/
select empno,ename,sal,deptno from emp
MINUS
select empno,ename,sal,deptno from emp
where deptno=10;
/*INTERSECT 교집합*/
select empno,ename,sal,deptno from emp
INTERSECT
select empno,ename,sal,deptno from emp
where deptno=10;
/*125p 1번문제*/
SELECT * FROM emp where ename like '%S';
/*125p 3번문제*/
SELECT empno,ename,sal,deptno FROM emp where deptno=20 or deptno=30
INTERSECT
SELECT empno,ename,sal,deptno FROM emp where sal>2000;
SELECT * FROM emp where deptno in(20,30) and sal>2000;