#6.SQL초보에서 Schema...

SQL초보에서 Schema Objects까지

3.2.2 산술 표현식(Arithmetic Expression)

칼럼 값에 산술 연산자를 적용하여 계산된 결과, 계산식을 출력할 수 있는 기능을 제공하며 숫

자 또는 날짜 타입에만 사용 가능하다. 우선 순위가 높은 연산을 먼저 수행하고 같은 우선 순위

연산자들은 왼쪽에서 오른쪽으로 계산해 나간다. 수학 산술 연산자의 우선순위와 동일하며 괄호

를 사용하여 우선순위 변경 가능하다.

[emp테이블에서 사원들의 연봉을 출력하라]

SQL> SELECT empno, ename, sal, sal*12 FROM emp;

EMPNO ENAME SAL SAL*12

---------------------------------------

7369 SMITH 800 9600

7499 ALLEN 1600 19200

……

7902 FORD 3000 36000

7934 MILLER 1300 15600

-- 수당(COMM)이 NULL인 사원은 연봉이 NULL값이 된다. NULL과 연산하면 NULL이 됨

SQL> SELECT empno, ename, sal, comm, sal*12 + comm AS “연봉” FROM emp;

EMPNO ENAME SAL COMM 연봉

-----------------------------------------------------------------------

7369 SMITH 800

7499 ALLEN 1600 300 19500

7521 WARD 1250 500 15500

……

7902 FORD 3000

7934 MILLER 1300

-- 수당이 정해지지 않아 NULL값을 가지는 경우 이를 0으로 처리하여 연봉 계산

SQL> SELECT EMPNO, ENAME, SAL, COMM, SAL*12 + NVL(COMM, 0) AS “연봉” FROM EMP;

EMPNO ENAME SAL COMM 연봉

------------------------------------------------

7369 SMITH 800 9600

7499 ALLEN 1600 300 19500

7521 WARD 1250 500 15500

……

7902 FORD 3000 36000

7934 MILLER 1300 15600

3.2.3 칼럼 별명(Column Alias)

칼럼별명을 설정하여 SQL*Plus 실행결과 출력 시 보이는 칼럼 제목을 다르게 설정할 수 있다.

프로그래밍 언어에서는 칼럼 별칭을 설정하면 그 별명으로 칼럼의 값을 다룰수 있다.

- 칼럼 별명(column alias)은 SELECT 절에 표현식(expression)을 사용할 때 유용하다.

- 칼럼 이름을 기술 후 공백 다음에 별명을 추가한다.

- 칼럼 이름 다음에 AS구를 사용 후 별명을 추가한다.(AS구는 옵션)

- 칼럼 이름 다음에 공백을 두고 쌍따옴표(“, Double Quotation)를 사용하여 별명 내에 공백이나

특수문자를 사용할 수 있다. (별명에 공백이나 특수문자 있으면 쌍따옴표로 싸야 한다.)

- ORDER BY 절에 칼럼 별명 사용가능 하지만 다른 쿼리 구에서는 사용 불가능 하다.

SQL> SELECT (sal+NVL(comm,0))*12 AS ANNUAL FROM EMP;

ANNUAL

----------

9600

22800

……

15600

14 개의 행이 선택되었습니다.

SQL> SELECT (sal+NVL(comm,0))*12 ANNUAL FROM EMP;

ANNUAL

----------

9600

22800

……

15600


SQL> SELECT (sal+NVL(comm,0))*12 "연봉" FROM EMP;

연봉

----------

9600

22800

……

15600


SQL> SELECT (sal + comm) "Annual Salary" FROM EMP;

Annual Salary

-------------

1900

1750

2650

1500


SQL> SELECT sal*12 Annual Salary FROM emp;

SELECT sal*12 Annual Salary FROM emp

*

1행에 오류:

ORA-00923: FROM 키워드가 필요한 위치에 없습니다.

3.2.4 합성 연산자(Concatenated Operator)

합성 연산자의 모습은 두 개의 수직바(||)의 모양이다. 하나의 칼럼과 다른 칼럼 또는 문자열을 연

결시킨다. 프로그래밍 언어에서의 ‘+’, ‘&’ 와 같다고 보면 된다. 합성 연산자를 사용하여 만든 문

자 표현식은 새로운 칼럼을 만들게 된다. (SQL문자열 함수 concat 함수와 같은 역할을 한다)

SQL> SELECT ename as "성명",

sal || '(' || comm || ')' as "급여(커미션)"

FROM emp

WHERE ename = 'TURNER';

성명 급여(커미션)

-------------------------------------------------------------

TURNER 1500(0)

-- CONCAT 함수와 같은 기능을 한다.

SQL> SELECT ENAME "성명",

CONCAT(SAL,CONCAT('(', CONCAT(COMM, ')')))

FROM EMP

WHERE ENAME = 'TURNER';

성명 급여(커미션)

-------------------------------------------------------------

TURNER 1500(0)

리터럴잠 시 만 요

문자(Literal Character)는 SELECT 리스트에 포함되어 있는 것 중 칼럼 명과 별명(Alias)이

아닌 문자, 숫자 등을 가리킨다. 위의 예문에서 리터럴 문자는 ‘(‘, ‘)’ 등이다.

3.2.5 조건질의와 ORDER BY

조건 질의란 SELECT문장에 의해 선택되는 행을 제한 하는 것이다. WHERE 절을 이용하는데

FROM절 다음에 위치를 하게 된다. ORDER BY 절은 선택되는 행들을 정렬하기 위해서 사용하는

명령이며 SELECT구의 마지막에 위치한다. ORDER BY문 뒤에는 칼럼의 이름이나 칼럼의 나타나는

순서를 나타내는 숫자 등이 올 수 있다.

비교 연산자인 =(같다), >(크다), >=(크거나 같다), <(작다), <=(작거나 같다)는 다 아실 것이다. 논

리연산자로는 AND, OR, NOT(부정) 등이 있으며 같지않다라는 부정 표현을 위해 !=, <>, ^= 등이

사용되는 것을 기억하자.

[기본형식]

SELECT * | {[ALL | DISTINCT] column | expr [alias],...}

FROM table

[WHERE conditions]

[ORDER BY 칼럼 {, 칼럼} [ASC|DESC]]

ORDER BY 정렬의 기본 원칙

- 기본적으로 오름차순(ASC) 정렬이며 날짜 타입은 가장 과거부터, 숫자는 1에서 9, 문자는 알파

벳순, NULL값은 맨 뒤에 온다.

- ORDER BY에 명시한 칼럼 순서대로 정렬이 되며 반드시 ORDER BY절에 사용된 칼럼이 SEELCT절

에 출현 할 필요는 없다.

SELECT ename, job

FROM emp ORDER BY deptno, sal DESC;

SQL> select ename "성명", sal "급여"

from emp

where sal >= 3000;

성명 급여

---------------------------

SCOTT 3000

KING 5000

FORD 3000

SQL> i order by ename

SQL> /

성명 급여

---------- ----------

FORD 3000

KING 5000

SCOTT 3000

SQL> list

1 select ename "성명", sal "급여"

2 from emp

3 where sal >= 3000

4* order by ename

SQL> 4 order by ename desc

SQL> /

성명 급여

---------- ----------

SCOTT 3000

KING 5000

FORD 3000

SQL> 4 order by 1

SQL> /

성명 급여

-----------------------------

FORD 3000

KING 5000

SCOTT 3000

--> 아래의 세 질의는 같은 의미이다.

SQL> /

SQL> select count(*) from emp

2 where ename <> 'SMITH';

SQL> 2 where ename != 'SMITH';

SQL> /

SQL> 2 where ename ^= 'SMITH'

SQL> /

3.2.6 SQL 조건연산자를 이용한 조건검색

BETWEEN A AND B

A와 B의 사이의 값, 경계 A,B 도 포함

A보다 크거나 같고 B보다 적거나 같다.

(AND로 해석)

IN(A, B, …, N) A, B, … N 중의 하나와 일치하면 참

(OR로 해석)

-- MYEMP1 테이블에서 입사일자가 2013년 10월25일~2014년12월31일 사이의 입사자 수 출력,

먼저 두가지 쿼리를 보자.

SQL> SELECT COUNT(*) FROM MYEMP1

WHERE TO_CHAR(HIREDATE,'RR/MM/DD') >= '13/10/26'

AND TO_CHAR(HIREDATE,'RR/MM/DD') <= '14/12/31';

COUNT(*)

----------

1440288

경 과: 00:00:16.60


WHERE HIREDATE >= '13/10/26'

AND HIREDATE < '15/01/01';

COUNT(*)

----------

1440288

경 과: 00:00:17.20

-- 위 두 쿼리의 차이는 WHERE절 HIREDATE칼럼에 SQL함수 TO_CHAR가 적용되었느냐의 여부

다. 현재 MYEMP1의 HIREDATE 칼럼에는 검색을 빠르게 해주는 인덱스가 생성되어 있지 않다.

인덱스라는 것을 만든 후 두 쿼리를 다시 실행하여 실행시간의 차이에 대해 확인하자.

SQL> CREATE INDEX IDX_MYEMP1_HIREDATE ON MYEMP1(HIREDATE);

-- 아래 쿼리는 HIREDATE 칼럼에 SQL함수 TO_CHAR가 적용되어 칼럼값에 변형이 생겨 만들어

놓은 인덱스를 이용하지 못한다.


WHERE TO_CHAR(HIREDATE,'RR/MM/DD') >= '13/10/26'

AND TO_CHAR(HIREDATE,'RR/MM/DD') <= '14/12/31';

COUNT(*)

----------

1440288

경 과: 00:00:16.53

Execution Plan

--------------------------------------------------------------------------------------------

Id Operation Name Rows Bytes Cost (%CPU) Time

---------------------------------------------------------------------------------------------

0 SELECT STATEMENT 1 9 25678 00:05:09

1 SORT AGGREGATE 1 9 |

* 2 | TABLE ACCESS FULL MYEMP1 25000 219K 25678 00:05:09

---------------------------------------------------------------------------------------------

-- 아래 쿼리의 실행계획을 보면 인덱스를 경유하여 데이터를 추출한 것을 알 수있다. 두 쿼리중

어떤 쿼리를 사용해야 할지 고민해 보자.


WHERE HIREDATE >= '13/10/26'

AND HIREDATE < '15/01/01';

COUNT(*)

----------

1440288

경 과: 00:00:00.71 -- 1초도 걸리지 않는다.

Execution Plan

------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 9 | 123 (1)| 00:00:02 |

| 1 | SORT AGGREGATE | | 1 | 9 | | |

|* 2 | FILTER | | | | | |

|* 3 | INDEX RANGE SCAN| IDX_MYEMP1_HIREDATE | 25000 | 219K| 123 (1)| 00:00:02

|

------------------------------------------------------------------------------------------

-- BETWEEN ~ AND ~는 AND연산으로 변형할 수 있다.

SQL> select empno, ename, sal

from emp

where sal between 1000 and 5000;

EMPNO ENAME SAL

---------- -------------------- --------

7499 ALLEN 1,600

7521 WARD 1,250

7566 JONES 2,975

7654 MARTIN 1,250


from emp

where sal >= 1000

and sal <= 5000;

-- EMP 테이블에서 부서코드가 10 or 20 or 30인 사원의 사번, 이름, 급여를 출력하라. IN 연산자

는 or로 풀어쓸 수 있다.


from emp

where deptno in (10, 20, 30);

EMPNO ENAME SAL

---------- -------------------- --------

7369 SMITH 800

7499 ALLEN 1,600

7521 WARD 1,250

……


from emp

where deptno = 10

or deptno = 20

or deptno = 30;

-- MYEMP1, MYSUGANG1 테이블에서 두과목 이상 수강한 사원의 사번, 이름을 출력하라.

SQL> SELECT EMPNO, ENAME FROM MYEMP1 E

WHERE EMPNO IN (

SELECT EMPNO FROM MYSUGANG1 S

GROUP BY EMPNO, LECTURE_ID

HAVING COUNT(*) > 1

)

LIKE 패턴 비교,부분적으로 일치(%, _ )하면 참

IS NULL NULL 이면 참

IS NOT NULL NULL이 아니면 참

LIKE : Query 조건으로 와일드카드를 사용 할 때 이용 한다. 와일드 카드인 ‘%’는 0개 이

상의 문자, ‘_’(Under bar)는 한 개의 문자를 의미 한다. 또한 비교하려는 문자에 ‘%’, ‘_’등

이 포함되어 있어 와일드 카드로서의 의미보다는 문자로서(리터럴값 자체)의 의미를 나타내기 위

해서는 ESCAPE 옵션을 이용 한다.

--아래는 이름이 S로 시작하는 사원의 이름, 급여를 선택하는 질의임.

SQL> select ename, sal from emp

where ename like 'S%';

ENAME SAL

---------- ----------

SMITH 800

SCOTT 3000

SQL> select ename from emp;

ENAME

----------

SMITH

……

JONES

JAMES

ADAMS


where ename like 'J___S'; -- 언더바 3개(첫글자는 J, 이후 세 글자 오고 마지막은 S)

ENAME SAL

---------- ----------

JONES 2975

JAMES 950

SQL> insert into emp (empno, ename, sal) values (7777, 'J_SARA',700);

SQL> insert into emp (empno, ename, sal) values (7778, 'J_PAGE',800);

SQL> commit;

-- 이름이 J_로 시작하는 직원의 이름, 급여를 출력하세요

(잘못된 문장)


where ename like 'J_%';

ENAME SAL

---------- ----------

JONES 2975

JAMES 950

J_SARA 700

J_PAGE 800

-- 아래처럼 ESCAPE를 사용해야 한다.


where ename like 'J\_%' escape '\';

ENAME SAL

---------- ----------

J_SARA 700

J_PAGE 800

SQL> 2 where ename like 'J|_%' escape '|';

SQL> /

ENAME SAL

---------- ----------

J_SARA 700

J_PAGE 800

-- MYEMP1 테이블에서 이름이 ‘홍’으로 시작하는 사원들의 급여 최대값을 구하라.

SQL> SELECT MAX(SAL) FROM MYEMP1

WHERE ENAME LIKE '홍%'

MAX(SAL)

----------

5999995

IS[NOT] NULL : NULL 값인지의 여부를 검사, NULL을 검사하기 위한 유일한 연산자, 원래 NULL을

가지고 어떠한 연산이라도 할 수 없다. NULL인지 비교하기 위해 ‘=’, ‘!=’ 연산자를 이용하는 것은

잘못된 방식이다.

SQL> select count(*) from emp

2 where comm is null;

COUNT(*)

----------

11

SQL> 2 where comm = NULL

SQL> /

COUNT(*)

----------

0

-- MYEMP1 테이블의 COMM 칼럼에 인덱스를 만들자.

SQL> CREATE INDEX IDX_MYEMP1_COMM ON MYEMP1(COMM);

-- MYEMP1 테이블에서 COMM이 NULL인 사원의 수를 출력하세요. IS NULL은 인덱스를 사용하

지 못한다. 인덱스 영역에는 NULL값은 보관하지 않는다.


WHERE COMM IS NULL;

COUNT(*)

----------

8333335

경 과: 00:00:16.63

Execution Plan

---------------------------------------------------------------------------------------------


---------------------------------------------------------------------------------------------


1 SORT AGGREGATE 1 13

* 2 TABLE ACCESS FULL MYEMP1 500K 6347K 25257 00:05:04

-- MYEMP1 테이블에서 COMM이 NULL이 아닌 사원의 수를 출력하세요. IS NOT NULL인 경우에

는 인덱스를 사용함을 확인하고 실행시간도 위 쿼리와 비교하라.


WHERE COMM IS NOT NULL;

COUNT(*)

----------

1666667

경 과: 00:00:00.49

Execution Plan

-----------------------------------------------------------------------------------------------------------


-----------------------------------------------------------------------------------------------------------


1 SORT AGGREGATE 1 13

* 2 INDEX FAST FULL SCAN IDX_MYEMP1_COMM 500K 6347K 1056 00:00:13

------------------------------------------------------------------------------------------------------------

#6.SQL초보에서 Schema...

Education

Transcript of #6.SQL초보에서 Schema...