Basic Select Example (P2-3)
- emp 테이블에서 모든 정보 추출
- SQL> SELECT * FROM emp;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 80/12/17 800
20 - 7499 ALLEN SALESMAN 7698 81/02/20 1600 300
30 - 7521 WARD SALESMAN 7698 81/02/22 1250 500
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 81/04/02 2975
20- 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400
30 - 7698 BLAKE MANAGER 7839 81/05/01 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 81/06/09 2450
10- 7788 SCOTT ANALYST 7566 87/04/19 3000
20 - 7839 KING PRESIDENT 81/11/17 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 81/09/08 1500 0
30- 7876 ADAMS CLERK 7788 87/05/23 1100
20 - 7900 JAMES CLERK 7698 81/12/03 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 81/12/03 3000
20- 7934 MILLER CLERK 7782 82/01/23 1300
10
14 rows selected.
- 1000줄을 한 페이지로 설정 (P2-3)
- SQL> SET pagesize 1000
SQL> SELECT ename FROM emp; - ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER - 14 rows selected.
Arithmetic Expressions (P2-4)
- SELECT 구문에 연산자 사용 (P2-5)
- SQL> SELECT ename, sal+100 FROM emp;
- ENAME SAL+100
---------- ----------
SMITH 900
ALLEN 1700
WARD 1350
JONES 3075
MARTIN 1350
BLAKE 2950
CLARK 2550
SCOTT 3100
KING 5100
TURNER 1600
ADAMS 1200
JAMES 1050
FORD 3100
MILLER 1400 - 14 rows selected.
Null Value (P2-6)
- comm 컬럼에서 출력안되는 값은 null 값
- null 값은 0이나 space 등과는 다름
- SQL> SELECT ename, comm FROM emp;
- ENAME COMM
---------- ----------
SMITH
ALLEN 300
WARD 500
JONES
MARTIN 1400
BLAKE
CLARK
SCOTT
KING
TURNER 0
ADAMS
JAMES
FORD
MILLER - 14 rows selected.
NVL Function (P2-8)
- null 값에 계산을 하면 항상 null 값
- SQL> SELECT ename, comm, comm+100 FROM emp;
- ENAME COMM COMM+100
---------- ---------- ----------
SMITH
ALLEN 300 400
WARD 500 600
JONES
MARTIN 1400 1500
BLAKE
CLARK
SCOTT
KING
TURNER 0 100
ADAMS
JAMES
FORD
MILLER - 14 rows selected.
- 연봉 정보를 구하려고 했지만 null값이 있는 경우 계산안됨
- SQL> SELECT sal, comm, (sal+comm)*12 FROM emp;
- SAL COMM (SAL+COMM)*12
---------- ---------- -------------
800
1600 300 22800
1250 500 21000
2975
1250 1400 31800
2850
2450
3000
5000
1500 0 18000
1100
950
3000
1300 - 14 rows selected.
- comm 컬럼이 null인 경우 0을 반환 시킨 후 연봉계산
- SQL> SELECT sal, comm, (sal+NVL(comm, 0))*12 FROM emp;
- SAL COMM (SAL+NVL(COMM,0))*12
---------- ---------- --------------------
800 9600
1600 300 22800
1250 500 21000
2975 35700
1250 1400 31800
2850 34200
2450 29400
3000 36000
5000 60000
1500 0 18000
1100 13200
950 11400
3000 36000
1300 15600 - 14 rows selected.
- 아래 NVL() 사용시 오류 원인
- mgr은 정수형인데 NVL에서 'No Manager'는 문자열형이라서~
- SQL> SELECT NVL(mgr, 'No Manager') FROM emp;
SELECT NVL(mgr, 'No Manager') FROM emp
*
ERROR at line 1:
ORA-01722: invalid number
SQL> DESC emp;
Name Null? Type
----------------------------------------- -------- ------------- 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)
Column Alias (P2-10)
- 컬럼 Alias => (sal+comm)*12을 연봉으로 표시
- AS는 선택사항 ',' 없이 연속으로 쓰면 됨 => SELECT sal 봉급, comm 커미션, (sal+comm)*12 연봉 FROM emp;
- SQL> SELECT sal, comm, (sal+comm)*12 AS 연봉 FROM emp;
- SAL COMM 연봉
---------- ---------- ----------
800
1600 300 22800
1250 500 21000
2975
1250 1400 31800
2850
2450
3000
5000
1500 0 18000
1100
950
3000
1300 - 14 rows selected.
Concatenation Operator (P2-12)
- concat 연산자( '||' ) : 문자열 연결
- SQL> SELECT ename || job FROM emp;
- ENAME||JOB
-------------------
SMITHCLERK
ALLENSALESMAN
WARDSALESMAN
JONESMANAGER
MARTINSALESMAN
BLAKEMANAGER
CLARKMANAGER
SCOTTANALYST
KINGPRESIDENT
TURNERSALESMAN
ADAMSCLERK
JAMESCLERK
FORDANALYST
MILLERCLERK - 14 rows selected.
Literals (P2-14)
- SQL> SELECT 'emp# of' || ename || ' is' || empno FROM emp;
- 'EMP#OF'||ENAME||'IS'||EMPNO
------------------------------------------------------------
emp# ofSMITH is7369
emp# ofALLEN is7499
emp# ofWARD is7521
emp# ofJONES is7566
emp# ofMARTIN is7654
emp# ofBLAKE is7698
emp# ofCLARK is7782
emp# ofSCOTT is7788
emp# ofKING is7839
emp# ofTURNER is7844
emp# ofADAMS is7876
emp# ofJAMES is7900
emp# ofFORD is7902
emp# ofMILLER is7934 - 14 rows selected.
Duplicate Values (P2-16)
- 기본값 ALL 생략됨
- select deptno, job from emp; == select ALL deptno, job from emp;
- ALL : 모든 내용 출력
- SQL> SELECT ALL deptno, job FROM emp;
- DEPTNO JOB
---------- ---------
20 CLERK
30 SALESMAN
30 SALESMAN
20 MANAGER
30 SALESMAN
30 MANAGER
10 MANAGER
20 ANALYST
10 PRESIDENT
30 SALESMAN
20 CLERK
30 CLERK
20 ANALYST
10 CLERK - 14 rows selected.
- DISTINCT : 중복된 레코드는 하나만 출력
- SQL> SELECT DISTINCT deptno, job FROM emp;
- DEPTNO JOB
---------- ---------
10 CLERK
10 MANAGER
10 PRESIDENT
20 ANALYST
20 CLERK
20 MANAGER
30 CLERK
30 MANAGER
30 SALESMAN - 9 rows selected.
CASE (P2-18)
- depeno에 따라 sal 내용 달라짐
- new_sal -> alias
- SQL> SELECT ename, CASE deptno WHEN 10 THEN sal*1.1
2 WHEN 20 THEN sal*1.2
3 ELSE sal END new_sal
4 FROM emp; - ENAME NEW_SAL
---------- ----------
SMITH 960
ALLEN 1600
WARD 1250
JONES 3570
MARTIN 1250
BLAKE 2850
CLARK 2695
SCOTT 3600
KING 5500
TURNER 1500
ADAMS 1320
JAMES 950
FORD 3600
MILLER 1430 - 14 rows selected.
WHERE (2-19)
- 조건에 만족되는 데이터만 출력됨
- SQL> SELECT dname FROM dept WHERE dname = 'SALES';
- DNAME
--------------
SALES
- SQL> SELECT ename, sal FROM emp WHERE sal BETWEEN 1500 AND 2000;
- ENAME SAL
---------- ----------
ALLEN 1600
TURNER 1500
이 글은 스프링노트에서 작성되었습니다.
