(Oracle Database Server) 중앙정보처리학원 - 2012년 11월 06일 화요일 2/2

 

1. What is 'SELECT STATEMENT' ???

 - 원하는 집합(결과)을 정의(요청, 묘사)하는 언어

 - TO DESCRIBE DESIRED DATA

 

SQL> SELECT SUM(SAL), AVG(SAL), MAX(SAL), MIN(SAL)

         FROM EMP

         ;

SQL> SELECT DEPTNO, SUM(SAL) "집계"

FROM EMP

WHERE SAL >= 1000

GROUP BY DEPTNO

HAVING SUM(SAL) >= 8500

ORDER BY DEPTNO

;

 

2. Graphic syntax Diagrams 읽는 방법

 http://docs.oracle.com/cd/B19306_01/server.102/b14200/ap_syntx.htm#i624534

 

3. SQL 작성 지침

 http://orapybubu.blog.me/40023835579

 

4. 가공 - 연산 : 산술, 연결, 논리, ...

           - 함수 : BUILT-IN FUCTION (단일행, 복수행)

                       USER-DEFINED FUNCTION

 

1) 산술 연산

SQL> SELECT EMPNO

, SAL

, SAL*0.1

, SAL*12

  FROM EMP

;

 

2) 연결 연산

SQL> SELECT EMPNO

, '***'

, ENAME

FROM EMP

;

 

SQL> SELECT EMPNO ||  '***'

 , ENAME

FROM EMP

;

 

SQL> SELECT SUBSTR(EMPNO, 1, 2) || '***'

, ENAME

FROM EMP

;

 

SQL> SELECT SUBSTR(EMPNO, 1, 2) || '***' AS EMPNO

, ENAME

FROM EMP

;

 

3) BUILT-IN FUNCTION : 단일행 함수

SQL> SELECT EMPNO

, ENAME

, ENAME

, ENAME

FROM EMP

;

 

SQL> SELECT EMPNO

, ENAME

, SUBSTR(ENAME, 1, 1)

, SUBSTR(ENAME, 2)

FROM EMP

;

 

SQL> SELECT EMPNO

, ENAME

, SUBSTR(ENAME, 1, 1)

, SUBSTR(ENAME, 2, 1)

, SUBSTR(ENAME, 3)

FROM EMP

;

 

SQL> SELECT EMPNO

, ENAME

, SUBSTR(ENAME, 1, 1)

, SUBSTR(ENAME, 2, 1)

, SUBSTR(ENAME, 3)

FROM EMP

ORDER BY 4

;

 

4) BUILT-IN FUNCTION : 복수행 함수

SQL> SELECT EMPNO

, ENAME

, JOB

, COMM

, COMM

FROM EMP

;

 

SQL> SELECT EMPNO

, ENAME

, JOB

, COMM

, NVL(COMM, 0)

FROM EMP

;

 

SQL> SELECT EMPNO

, ENAME

, JOB

, COMM

, NVL(COMM, 0)

FROM EMP

;

 

SQL> SELECT EMPNO

, ENAME

, JOB

, AVG(COMM) AS AVG1, -- 커미션이 있는 사원들의 평균 커미션

, AVG(NVL(COMM, 0)) AS AVG2 -- 사원 1인당 평균 커미션

FROM EMP

;

 

5) USER-DEFINED FUNCTION

SQL> CREATE OR REPLACE FUNCTION TAX(I NUMBER) RETURN NUMBER

IS

BEGIN

RETURN I*0.1

;

END

;

/

 

SQL> SELECT EMPNO

, ENAME

, SAL

, TAX(SAL) AS TAX

FROM EMP

;