728x90
SQL> select ename, sal, sal*12.0*0.03 from emp;
ENAME SAL SAL*12.0*0.03
---------- ---------- -------------
SMITH 800 288
ALLEN 1600 576
WARD 1250 450
JONES 2975 1071
MARTIN 1250 450
BLAKE 2850 1026
CLARK 2450 882
SCOTT 3000 1080
KING 5000 1800
TURNER 1500 540
ADAMS 1100 396
JAMES 950 342
FORD 3000 1080
MILLER 1300 468
14 개의 행이 선택되었습니다.
SQL> select ename, sal 봉급, sal*12.0*0.03 세금 from emp;
ENAME 봉급 세금
---------- ---------- ----------
SMITH 800 288
ALLEN 1600 576
WARD 1250 450
JONES 2975 1071
MARTIN 1250 450
BLAKE 2850 1026
CLARK 2450 882
SCOTT 3000 1080
KING 5000 1800
TURNER 1500 540
ADAMS 1100 396
JAMES 950 342
FORD 3000 1080
MILLER 1300 468
14 개의 행이 선택되었습니다.
SQL> describe emp
이름 널? 유형
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> select job from emp;
JOB
---------
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK
14 개의 행이 선택되었습니다.
SQL> select distinct job from emp;
JOB
---------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
SQL> select distinct ename from emp;
ENAME
----------
ALLEN
JONES
FORD
CLARK
MILLER
SMITH
WARD
MARTIN
SCOTT
TURNER
ADAMS
BLAKE
KING
JAMES
14 개의 행이 선택되었습니다.
SQL> desc emp
이름 널? 유형
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> select deptno from emp;
DEPTNO
----------
20
30
30
20
30
30
10
20
10
30
20
30
20
10
14 개의 행이 선택되었습니다.
SQL> select empno from emp;
EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
14 개의 행이 선택되었습니다.
SQL> describe dept
이름 널? 유형
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> select deptno from dept
2 ;
DEPTNO
----------
10
20
30
40
SQL> select deptno from dept;
DEPTNO
----------
10
20
30
40
SQL> select loc from dept;
LOC
-------------
NEW YORK
DALLAS
CHICAGO
BOSTON
SQL> select dname from dept;
DNAME
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS
SQL> desc emp
이름 널? 유형
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> select ename, sal, comm from emp;
ENAME SAL COMM
---------- ---------- ----------
SMITH 800
ALLEN 1600 300
WARD 1250 500
JONES 2975
MARTIN 1250 1400
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500 0
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
14 개의 행이 선택되었습니다.
SQL> select deptno from dept where deptno>10;
DEPTNO
----------
20
30
40
SQL> select deptno from dept where deptno>=10;
DEPTNO
----------
10
20
30
40
SQL> select ename, job from emp where deptno=10;
ENAME JOB
---------- ---------
CLARK MANAGER
KING PRESIDENT
MILLER CLERK
SQL> desc emp
이름 널? 유형
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> desc job
이름 널? 유형
----------------------------------------- -------- ----------------------------
JOB_NAME VARCHAR2(100)
JOB_CLASS VARCHAR2(32)
JOB_STYLE VARCHAR2(11)
JOB_TEMPLATE VARCHAR2(100)
PROGRAM_ACTION VARCHAR2(4000)
ACTION_TYPE VARCHAR2(20)
SCHEDULE_NAME VARCHAR2(65)
REPEAT_INTERVAL VARCHAR2(4000)
SCHEDULE_LIMIT INTERVAL DAY(2) TO SECOND(6)
START_DATE TIMESTAMP(6) WITH TIME ZONE
END_DATE TIMESTAMP(6) WITH TIME ZONE
EVENT_CONDITION VARCHAR2(4000)
QUEUE_SPEC VARCHAR2(100)
NUMBER_OF_ARGS NUMBER
ARGUMENTS SYS.JOBARG_ARRAY
PRIORITY NUMBER
JOB_WEIGHT NUMBER
MAX_RUN_DURATION INTERVAL DAY(2) TO SECOND(6)
MAX_RUNS NUMBER
MAX_FAILURES NUMBER
LOGGING_LEVEL NUMBER
RESTARTABLE VARCHAR2(5)
STOP_ON_WINDOW_EXIT VARCHAR2(5)
RAISE_EVENTS NUMBER
COMMENTS VARCHAR2(240)
AUTO_DROP VARCHAR2(5)
ENABLED VARCHAR2(5)
FOLLOW_DEFAULT_TZ VARCHAR2(5)
PARALLEL_INSTANCES VARCHAR2(5)
AQ_JOB VARCHAR2(5)
INSTANCE_ID NUMBER
METHOD
------
FINAL CONSTRUCTOR FUNCTION JOB RETURNS SELF AS RESULT
인수명 유형 기본 내부/외부?
------------------------------ ----------------------- --------- --------
JOB_NAME VARCHAR2 IN
JOB_STYLE VARCHAR2 IN DEFAULT
JOB_TEMPLATE VARCHAR2 IN DEFAULT
PROGRAM_ACTION VARCHAR2 IN DEFAULT
ACTION_TYPE VARCHAR2 IN DEFAULT
SCHEDULE_NAME VARCHAR2 IN DEFAULT
REPEAT_INTERVAL VARCHAR2 IN DEFAULT
EVENT_CONDITION VARCHAR2 IN DEFAULT
QUEUE_SPEC VARCHAR2 IN DEFAULT
START_DATE TIMESTAMP WITH TIME ZONE IN DEFAULT
END_DATE TIMESTAMP WITH TIME ZONE IN DEFAULT
NUMBER_OF_ARGS UNDEFINED IN DEFAULT
ARGUMENTS JOBARG_ARRAY IN DEFAULT
JOB_CLASS VARCHAR2 IN DEFAULT
SCHEDULE_LIMIT INTERVAL DAY TO SECOND IN DEFAULT
PRIORITY UNDEFINED IN DEFAULT
JOB_WEIGHT UNDEFINED IN DEFAULT
MAX_RUN_DURATION INTERVAL DAY TO SECOND IN DEFAULT
MAX_RUNS UNDEFINED IN DEFAULT
MAX_FAILURES UNDEFINED IN DEFAULT
LOGGING_LEVEL UNDEFINED IN DEFAULT
RESTARTABLE BOOLEAN IN DEFAULT
STOP_ON_WINDOW_EXIT BOOLEAN IN DEFAULT
RAISE_EVENTS UNDEFINED IN DEFAULT
COMMENTS VARCHAR2 IN DEFAULT
AUTO_DROP BOOLEAN IN DEFAULT
ENABLED BOOLEAN IN DEFAULT
FOLLOW_DEFAULT_TZ BOOLEAN IN DEFAULT
PARALLEL_INSTANCES BOOLEAN IN DEFAULT
AQ_JOB BOOLEAN IN DEFAULT
INSTANCE_ID UNDEFINED IN DEFAULT
SQL> select ename
2 , job
3 from emp
4 where job = 'MANAGER';
ENAME JOB
---------- ---------
JONES MANAGER
BLAKE MANAGER
CLARK MANAGER
SQL> set pagesize 100
SQL> desc job
이름 널? 유형
----------------------------------------- -------- ----------------------------
JOB_NAME VARCHAR2(100)
JOB_CLASS VARCHAR2(32)
JOB_STYLE VARCHAR2(11)
JOB_TEMPLATE VARCHAR2(100)
PROGRAM_ACTION VARCHAR2(4000)
ACTION_TYPE VARCHAR2(20)
SCHEDULE_NAME VARCHAR2(65)
REPEAT_INTERVAL VARCHAR2(4000)
SCHEDULE_LIMIT INTERVAL DAY(2) TO SECOND(6)
START_DATE TIMESTAMP(6) WITH TIME ZONE
END_DATE TIMESTAMP(6) WITH TIME ZONE
EVENT_CONDITION VARCHAR2(4000)
QUEUE_SPEC VARCHAR2(100)
NUMBER_OF_ARGS NUMBER
ARGUMENTS SYS.JOBARG_ARRAY
PRIORITY NUMBER
JOB_WEIGHT NUMBER
MAX_RUN_DURATION INTERVAL DAY(2) TO SECOND(6)
MAX_RUNS NUMBER
MAX_FAILURES NUMBER
LOGGING_LEVEL NUMBER
RESTARTABLE VARCHAR2(5)
STOP_ON_WINDOW_EXIT VARCHAR2(5)
RAISE_EVENTS NUMBER
COMMENTS VARCHAR2(240)
AUTO_DROP VARCHAR2(5)
ENABLED VARCHAR2(5)
FOLLOW_DEFAULT_TZ VARCHAR2(5)
PARALLEL_INSTANCES VARCHAR2(5)
AQ_JOB VARCHAR2(5)
INSTANCE_ID NUMBER
METHOD
------
FINAL CONSTRUCTOR FUNCTION JOB RETURNS SELF AS RESULT
인수명 유형 기본 내부/외부?
------------------------------ ----------------------- --------- --------
JOB_NAME VARCHAR2 IN
JOB_STYLE VARCHAR2 IN DEFAULT
JOB_TEMPLATE VARCHAR2 IN DEFAULT
PROGRAM_ACTION VARCHAR2 IN DEFAULT
ACTION_TYPE VARCHAR2 IN DEFAULT
SCHEDULE_NAME VARCHAR2 IN DEFAULT
REPEAT_INTERVAL VARCHAR2 IN DEFAULT
EVENT_CONDITION VARCHAR2 IN DEFAULT
QUEUE_SPEC VARCHAR2 IN DEFAULT
START_DATE TIMESTAMP WITH TIME ZONE IN DEFAULT
END_DATE TIMESTAMP WITH TIME ZONE IN DEFAULT
NUMBER_OF_ARGS UNDEFINED IN DEFAULT
ARGUMENTS JOBARG_ARRAY IN DEFAULT
JOB_CLASS VARCHAR2 IN DEFAULT
SCHEDULE_LIMIT INTERVAL DAY TO SECOND IN DEFAULT
PRIORITY UNDEFINED IN DEFAULT
JOB_WEIGHT UNDEFINED IN DEFAULT
MAX_RUN_DURATION INTERVAL DAY TO SECOND IN DEFAULT
MAX_RUNS UNDEFINED IN DEFAULT
MAX_FAILURES UNDEFINED IN DEFAULT
LOGGING_LEVEL UNDEFINED IN DEFAULT
RESTARTABLE BOOLEAN IN DEFAULT
STOP_ON_WINDOW_EXIT BOOLEAN IN DEFAULT
RAISE_EVENTS UNDEFINED IN DEFAULT
COMMENTS VARCHAR2 IN DEFAULT
AUTO_DROP BOOLEAN IN DEFAULT
ENABLED BOOLEAN IN DEFAULT
FOLLOW_DEFAULT_TZ BOOLEAN IN DEFAULT
PARALLEL_INSTANCES BOOLEAN IN DEFAULT
AQ_JOB BOOLEAN IN DEFAULT
INSTANCE_ID UNDEFINED IN DEFAULT
SQL> select ename, job
2 from emp
3 where job != 'MANAGER';
ENAME JOB
---------- ---------
SMITH CLERK
ALLEN SALESMAN
WARD SALESMAN
MARTIN SALESMAN
SCOTT ANALYST
KING PRESIDENT
TURNER SALESMAN
ADAMS CLERK
JAMES CLERK
FORD ANALYST
MILLER CLERK
11 개의 행이 선택되었습니다.
SQL> select ename, job
2 from emp
3 where sal >= 1000;
ENAME JOB
---------- ---------
ALLEN SALESMAN
WARD SALESMAN
JONES MANAGER
MARTIN SALESMAN
BLAKE MANAGER
CLARK MANAGER
SCOTT ANALYST
KING PRESIDENT
TURNER SALESMAN
ADAMS CLERK
FORD ANALYST
MILLER CLERK
12 개의 행이 선택되었습니다.
SQL> select ename, job
2 from emp
3 where job = 'MANAGER' or 'ANALYST';
where job = 'MANAGER' or 'ANALYST'
*
3행에 오류:
ORA-00920: 관계 연산자가 부적합합니다
SQL> select ename from emp where job = 'MANAGER' or job = 'ANAlYST';
ENAME
----------
JONES
BLAKE
CLARK
SQL> help
HELP
----
Accesses this command line help system. Enter HELP INDEX or ? INDEX
for a list of topics.
You can view SQL*Plus resources at
and the Oracle Database Library at
SQL> select ename from emp where job in('MANAGER' , 'ANALYST');
ENAME
----------
JONES
BLAKE
CLARK
SCOTT
FORD
SQL> select ename from emp where job in('MANAGER' , 'ANALYST', 'CLECK');
ENAME
----------
JONES
BLAKE
CLARK
SCOTT
FORD
SQL> select ename from emp where job in('MANAGER' , 'CLECK');
ENAME
----------
JONES
BLAKE
CLARK
SQL> select ename, job, deptno from emp where deptno=10 or deptno=20;
ENAME JOB DEPTNO
---------- --------- ----------
SMITH CLERK 20
JONES MANAGER 20
CLARK MANAGER 10
SCOTT ANALYST 20
KING PRESIDENT 10
ADAMS CLERK 20
FORD ANALYST 20
MILLER CLERK 10
8 개의 행이 선택되었습니다.
SQL> select ename, job, deptno from emp where deptno in(10,20);
ENAME JOB DEPTNO
---------- --------- ----------
SMITH CLERK 20
JONES MANAGER 20
CLARK MANAGER 10
SCOTT ANALYST 20
KING PRESIDENT 10
ADAMS CLERK 20
FORD ANALYST 20
MILLER CLERK 10
8 개의 행이 선택되었습니다.
SQL> select ename, job, deptno from emp where deptno!=10 or deptno!=20;
ENAME JOB DEPTNO
---------- --------- ----------
SMITH CLERK 20
ALLEN SALESMAN 30
WARD SALESMAN 30
JONES MANAGER 20
MARTIN SALESMAN 30
BLAKE MANAGER 30
CLARK MANAGER 10
SCOTT ANALYST 20
KING PRESIDENT 10
TURNER SALESMAN 30
ADAMS CLERK 20
JAMES CLERK 30
FORD ANALYST 20
MILLER CLERK 10
14 개의 행이 선택되었습니다.
SQL> select ename, job, deptno from emp where deptno!=10 and deptno!=20;
ENAME JOB DEPTNO
---------- --------- ----------
ALLEN SALESMAN 30
WARD SALESMAN 30
MARTIN SALESMAN 30
BLAKE MANAGER 30
TURNER SALESMAN 30
JAMES CLERK 30
6 개의 행이 선택되었습니다.
SQL> select ename, job, deptno from emp where deptno in(40);
선택된 레코드가 없습니다.
SQL> select ename, job, deptno from emp where deptno not in(10, 20);
ENAME JOB DEPTNO
---------- --------- ----------
ALLEN SALESMAN 30
WARD SALESMAN 30
MARTIN SALESMAN 30
BLAKE MANAGER 30
TURNER SALESMAN 30
JAMES CLERK 30
6 개의 행이 선택되었습니다.
728x90
'행복한 프로그래밍' 카테고리의 다른 글
| select(2) (0) | 2018.05.24 |
|---|---|
| select (1) (0) | 2018.05.24 |
| id passport (0) | 2018.02.26 |
| mozart family (0) | 2018.02.26 |
| grade (0) | 2018.02.26 |