select ename, deptno, sal*12 ansal from emp where ename=(uppper(scott)) order by sal;
deptno=10 or sal>=2000 or deptno < 30 or
alter session set nls_date_format='DD-MON-YY';
alter session set nls_date_format='DD-MON-RR';
--날짜표시변경
RR형식
2006
50-99 이전세기
00-49 현재세기
1998
50-99 현재세기
00-49 다음세기
YY형식
현재세기
ex1:
select empno, ename, job, sal from emp where sal>=3000;
ex2:
select empno, ename, job, sal, deptno from emp where job='MANAGER';
ex3:
select empno, ename, job, sal, hiredate, deptno from emp where hiredate>'01-jan-82';
ex4:
select ename, job, sal, deptno from emp where sal>1250 and sal<=1500;
ex5:
select empno, ename, job, sal, hiredate from emp where empno=7902 or empno=7788 or empno=7566;
select empno, ename, job, sal, hiredate from emp where empno IN (7902, 7788, 7566);
ex6:
select empno, ename, job, sal, hiredate, deptno from emp where hiredate like '82%';
select empno, ename, job, sal, hiredate, deptno from emp where TO_CHAR(hiredate,'rr')='82';
ex7:
select empno, ename, job, sal, comm, deptno from emp where comm is Null;
ex8:
select empno, ename, job, sal, hiredate, deptno from emp where sal>=1100 and job='MANAGER';
ex9:
select empno, ename, job, sal, hiredate, deptno from emp where sal>=1100 or job='MANAGER';
ex10:
select empno, ename, job, sal, deptno from emp where NOT job IN('MANAGER','CLERK','ANALYST');
ex11:
select empno, ename, job, sal from emp where sal>=1500 or job='MANAGER';
ex12:
select empno, ename, job, sal from emp where job='PRESIDENT' or (sal>=1500 and job='SALESMAN');
select empno, ename, job, sal from emp where ename like 'S%'; 처음에 S가 나옴
ename like '%S'; 끝에 S가 나옴
ename like '%S%'; S가 나옴
ename like '_S%'; 두번째에 S가 나옴
select empno, ename, job, sal, hiredate, deptno from emp where substr(hiredate,1,2)=82;
select empno, ename, job, sal, hiredate, deptno from emp where hiredate between '82-01-01' and '82-12-31';
A 가 B보다 작아야함
날짜 형식 RR-MM-DD
ex13:
select hiredate, empno, ename, job, sal, deptno from emp order by hiredate;
ex14:
select hiredate, empno, ename, job, sal, deptno from emp order by hiredate DESC;
ex15:
select deptno, sal, empno, ename, job from emp order by sal DESC;
ex16:
select deptno, job, sal, empno, hiredate from emp order by deptno, job, sal;
연습문제
1. WHERE 절에 HIREDATE 의 비교를 '23-JAN-82' 가 아닌 '23-jan-82'로 기술하면 결과는?
SQL> SELECT * FROM emp WHERE hiredate = '23-JAN-82';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL> SELECT * FROM emp WHERE hiredate = '23-jan-82';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82 1300 10
차이없음
2.EMP 테이블에서 급여가 3000 이상인 사원의 정보를 사원번호, 이름, 담당업무, 급여를 출력하는 SELECT 문장을 작성하시오.
select empno, ename, job, sal from emp where sal>= 3000;
SQL> select empno, ename, job, sal from emp where sal>= 3000;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7788 SCOTT ANALYST 3000
7839 KING PRESIDENT 5000
7902 FORD ANALYST 3000
3.EMP 테이블에서 사원번호가 7788인 사원의 이름과 부서번호를 출력하는 SELECT 문장을 작성하시오.
SQL> select ename, deptno from emp where empno=7788;
ENAME DEPTNO
---------- ----------
SCOTT 20
4.EMP 테이블에서 입사일이 February 20, 1981 과 May 1, 1981 사이에 입사한 사원의 이름 업무 입사일을 출력하는 SELECT문장을 작성하시오, 단 입사일 순으로 출력하시오.
SQL> select ename, job, hiredate from emp where hiredate between '20-Feb-81' and '01-May-81' order by hiredate;
ENAME JOB HIREDATE
---------- --------- ---------
ALLEN SALESMAN 20-FEB-81
WARD SALESMAN 22-FEB-81
JONES MANAGER 02-APR-81
BLAKE MANAGER 01-MAY-81
5.EMP 테이블에서 부서번호가 10, 20 인 사원의 모든 정보를 출력하는 SELECT 문장을 작성하시오. 단 이름순으로 정렬하여라.
SQL> select * from emp where deptno IN(10,20) order by ename;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7369 SMITH CLERK 7902 17-DEC-80 800 20
8 개의 행이 선택되었습니다.
6.EMP 테이블에서 급여가 1500 이상이고 부서번호가 10, 30 인 사원의 이름과 급여를 출력하는 SELECT 문장을 작성하여라. 단 HEADING을 Emplyee과 Monthly Salary 로 출력하여라.
SQL> select ename as Employee, sal as "Monthly Salary" from emp where sal >= 1500 and deptno IN(10,30);
EMPLOYEE Monthly Salary
---------- --------------
ALLEN 1600
BLAKE 2850
CLARK 2450
KING 5000
TURNER 1500
7.EMP 테이블에서 1982년에 입사한 사원의 모든 정보를 출력하는 SELECT 문을 작성하여라.
SQL> select * from emp where hiredate like '%82';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82 1300 10
8.EMP 테이블에서 COMM 에 NULL 이 아닌 사원의 모든 정보를 출력하는 SELECT 문을 작성하여라.
select * from emp where comm IS NOT NULL;
SQL> select * from emp where comm IS NOT NULL;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
9.EMP 테이블에서 보너스가 급여보다 10% 많은 모든 종업원에 대하여 이름, 급여, 보너스를 출력하는 SELECT문을 작성하여라.
SQL> select ename, sal, comm from emp where comm<sal*1.1;
ENAME SAL COMM
---------- ---------- ----------
MARTIN 1250 1400
10.EMP 테이블에서 업무가 CLERK 이거나 Analyst 이고 급여가 1000, 3000, 5000 이 아닌 모든 사원의 정보를 출력하는 SELECT문을 작성하여라.
SQL> select * from emp where job IN('CLERK','ANALYST') and sal NOT IN (1000,3000,5000);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
11.EMP 테이블에서 이름에 L 이 두 자가 있고 부서가 30 이거나 또는 관리자가 7782 인 모든 사원 정보를 출력하는 SELECT 문을 작성하여라.
SQL> select * from emp where (ename like '%L%L%' and deptno=30) or mgr=7782;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
