Day48 - 데이터베이스-오라클(2). 22.11.3.목

< 문제 >

-- 직책이 CLERK 인 이름은?
SELECT ENAME FROM EMP
WHERE JOB ='CLERK';

-- 직책이CLERK이고 고용일이 1990년 이전인 사람은?
SELECT ENAME FROM EMP
WHERE JOB ='CLERK' AND HIREDATE <= '1990/01/01';

< 연산자 종류와 활용 방법 알아보기 > p101~131

-- 👌비교연산자 p102
-- 급여 2500이상, 직업이 ANALYST   P102
SELECT * FROM EMP
WHERE SAL >= 2500 AND JOB = 'ANALYST';

-- 👌등가 비교 연산자 p104
-- 급여가 3000이 아닌 사원 데이터  
SELECT* FROM EMP
WHERE SAL <> 3000 AND JOB <>'SALESMAN';

-- 👌논리 부정 연산자 p105
-- 급여가 3000이 같지 않다.  
SELECT* FROM EMP
WHERE NOT SAL = 3000;

-- 👌IN 연산자 p106
-- JOB이 메니저, 세일즈맨, 클럭이거나 한다.   
SELECT * FROM EMP
WHERE JOB ='MANAGER' OR JOB = 'SALESMAN' OR JOB ='CLERK';

SELECT* FROM EMP
WHERE JOB IN('MANGER','SALESMAN','CLERK');

-- IN 연산자를 사용하여 부서번호 10,20번인 사원들만 뽑아주세요.  P108
SELECT*FROM EMP
WHERE DEPTNO IN(10,20);

-- 👌BETWEEN A AND B 연산자 p109
-- 급여가 2000이상이고 3000 이하인 레코드   
SELECT*FROM EMP
WHERE SAL >= 2000 AND SAL <= 3000;

-- SAL열이 2000~3000사이의 데이터만 출력하시오.     
SELECT*FROM EMP
WHERE SAL BETWEEN 2000 AND 3000;

-- NOT를 붙이려면 NOT=는 <>랑 같다. P110
SELECT*FROM EMP
WHERE SAL NOT BETWEEN 2000 AND 3000;

-- 👌LIKE 연산자와 와일드 카드
SELECT* FROM EMP
WHERE ENAME LIKE 'S%';

SELECT * FROM EMP
WHERE ENAME LIKE '_M%';

-- 사원이름에 AM이 있는 사람 출력해 달라  P112
SELECT * FROM EMP
WHERE ENAME LIKE '%AM%';

-- 👌IS NULL 연산자 P116
SELECT * FROM EMP
WHERE COMM IS NULL;

SELECT * FROM EMP
WHERE COMM IS NOT NULL;

-- 👌집합 연산자 UNION P119
SELECT EMPNO, ENAME , SAL , DEPTNO
FROM EMP
WHERE DEPTNO = 10
UNION
SELECT EMPNO, ENAME , SAL , DEPTNO
FROM EMP
WHERE DEPTNO = 20;

-- 👌UNION, UNION ALL P121
SELECT EMPNO, ENAME , SAL , DEPTNO
FROM EMP
WHERE DEPTNO = 10
UNION
SELECT EMPNO, ENAME , SAL , DEPTNO
FROM EMP
WHERE DEPTNO = 10;

SELECT EMPNO, ENAME , SAL , DEPTNO
FROM EMP
WHERE DEPTNO = 10
UNION ALL
SELECT EMPNO, ENAME , SAL , DEPTNO
FROM EMP
WHERE DEPTNO = 10;

-- 👌MINUS P122
SELECT EMPNO, ENAME , SAL , DEPTNO
FROM EMP
WHERE DEPTNO = 10 OR DEPTNO =20
MINUS
SELECT EMPNO, ENAME , SAL , DEPTNO
FROM EMP
WHERE DEPTNO = 10;

-- 👌INTERSECT P123
SELECT EMPNO, ENAME , SAL , DEPTNO
FROM EMP
WHERE DEPTNO = 10 OR DEPTNO =20
INTERSECT
SELECT EMPNO, ENAME , SAL , DEPTNO
FROM EMP
WHERE DEPTNO = 10;

-- 👌오라클 함수 p131
SELECT ENAME, UPPER(ENAME), LOWER(ENAME), INITCAP(ENAME)
FROM EMP;

SELECT * FROM EMP
WHERE UPPER(ENAME) = 'SCOTT';

SELECT * FROM EMP
WHERE UPPER(ENAME) LIKE UPPER('%co%');