Oracle 정리 - Chapter.02

[연산자]

=  : 같다

!=,  ^=,  <> : 같지 않다

>=, <=, >, < : 크거나 같다, 작거나 같다, 크다, 작다

and, or, between and, in, like, is null, is not null

 

※ select 

select  [distinct] [컬럼1, 컬럼2.......][*]

from 테이블명

[where 조건절]

[order by 컬럼명 asc|desc ]

 

order by : 정렬

          asc  - 오름차순(생략가능)

          desc - 내림차순

컬럼명 : 숫자로도 가능

 

ex1) 사원명, 부서ID, 입사일을 부서별로 내림차순 정렬하시오

select last_name, department_id, hire_date

from employees

order by 2 desc;

 

ex2) 사원명, 부서ID, 입사일을  부서별로 내림차순 정렬하시오

       같은 부서가 있을때는  입사일순으로 정렬하시오

select last_name, department_id, hire_date

from employees

order by 2 desc, 3 asc;

= 2번째 컬럼으로 내림차순 하시오. 만약 같은 데이터가 존재하면 그때 3번째 컬럼으로 오름차순하시오. ~라는 뜻이다.

!! 그렇담 !! 아무때나 3 asc를 수행할까? 아니다. 

앞의 컬럼이 같은 데이터를 갖고 있을 때만 수행하고 외에는 수행하지 않는다.

 

 

[문제1] 사원들의 연봉을 구한 후 연봉 순으로 내림차순 정렬하시오

[단일행 함수]

1. 숫자함수 : mod, round, trunc, ceil  →  mod = 나머지 / round = 반올림 / trunc = 내림 / ceil = 올림

2. 문자함수 : lower, upper, length, substr, ltrim, rtrim, trim  →  lower = 소문자 / upper = 대문자 / length = 글자수 / substr = 부분문자열 추출 / ltrim = 왼쪽 공백 제거 / rtrim = 오른쪽 공백 제거 / trim = 전체 제거

** 만약 가운데에 공백이 있을 경우, 사용자의 몫이다. 개발자가 지울 수 없다.

3. 날짜함수 : sysdate, add_month, month_between  →  sysdate = 현재 시스템 날짜 / add_month = 지금으로부터 -달 뒤 / month_between = -부터 -까지

4. 변환함수

   (1) 암시적(implict)변환 : 자동

       VARCHAR2 또는 CHAR    ------>     NUMBER

       VARCHAR2 또는 CHAR    ------>     DATE

       NUMBER                  ------>    VARCHAR2

       DATE                     ------>    VARCHAR2   

 

    (2) 명시적(explict)변환 : 강제

               TO_NUMBER      TO_DATE

                <------            ------>

     NUMBER      CHARACTER           DATE

                ------>            <------

               TO_CHAR          TO_CHAR

 

- 날짜 형식 -

YYYY : 네자리 연도(숫자)         (ex.  2005)  

YEAR : 연도(문자)

MM : 두자리 값으로 나타낸 달    (ex.   01, 08, 12)

MONTH : 달 전체이름             (ex.   January) 

MON : 세자리 약어로 나타낸 달   (ex.   Jan)  

DY : 세자리 약어로 나타낸 요일  (ex. Mon) 

DAY : 요일전체                  (ex. Monday) 

DD : 숫자로 나타낸 달의 일      (ex. 31, 01) 

HH, HH24(24시간제)

MI

SS

 

- 숫자 형식 -

9 : 숫자를 표시

0 : 0을 강제로 표시

$ : 부동$기호를 표시

L : 부동 지역통화기호 표시

.  : 소수점출력

,  : 천단위 구분자 출력

 

5. 그룹(집합)함수 : avg, sum, max, min, count

6. 기타함수 : nvl, dcode, case

 

ex1) 이름을 소문자로 바꾼후 검색

'Higgins'사원의 사원번호, 이름, 부서번호를 검색하시오

select employee_id, last_name, department_id

from employees

where lower(last_name)='higgins';

**데이터는 반드시 대소문자를 가린다. 또한 single quotation marks(' ')을 붙여줘야 한다.

 

ex2) 10을 3으로 나눈 나머지 구하시오(mod)

select mod(10,3) from dual; → 가상의 테이블(테이블 명이 없을 때)

 

ex3) 35765.357을 반올림(round)

소숫점이 있는 자리가 0

위치가 n일 때 n이 양수이면 (n+1)에서 반올림이 되고

                   n이 음수이면 n의 위치에서 반올림 된다

select round(35765.357, 2) from dual;   -- 35765.36

select round(35765.357, 0) from dual;   -- 35765

select round(35765.357, -3) from dual;  -- 36000

 

ex4) 35765.357을 내림(trunc)

위치가 n일 때 n이 양수이면 (n+1)에서 반올림이 되고

              n이 음수이면 n의 위치에서 반올림 된다

select trunc(35765.357, 2) from dual;   -- 35765.35

select trunc(35765.357, 0) from dual;   -- 35765

select trunc(35765.357, -3) from dual;   -- 35000

 

ex5) concat('문자열1', '문자열2) : 문자열의 결합(문자열1+문자열2)

*자바에서는 문자열과 문자열을 결합할 때 +를 썼다면 Oracle은 or연산자( || )를 사용한다. 함수 concat을 사용해도 된다.

select concat('Hello', '  World') from dual;

 

ex6) length('문자열')  : 문자열의 길이

       lengthb('문자열') : 문자열의 길이

 

create table text ( 

str1  char(20),

str2  varchar2(20));

char : 고정문자길이

varchar2 : 가변문자길이

 

Oracle은 한글 1자를 2byte로 계산

Oracle Express는 한글 1자를 3byte로 계산한다.

lengthb와 length의 차이

 

insert into text(str1,str2) values('angel', 'angel');

insert into text(str1,str2) values('사천사', '사천사');

commit;

select  lengthb(str1), lengthb(str2) from text;

20   5

20   9

 

select  length(str1), length(str2) from text;

20   5

14   3

 

ex7)

select length('korea') from dual; -- 5

select length('코리아') from dual; -- 3

 

select lengthb('korea') from dual; -- 5

select lengthb('코리아') from dual; -- 9

 

ex8) 지정한 문자열 찾기 : instr(표현식, 찾는 문자, [위치]) 1: 앞(생략가능), -1: 뒤

select instr('HelloWorld', 'W') from dual;   -- 6

select instr('HelloWorld', 'o', -5) from dual; -- 5

select instr('HelloWorld', 'o', -1) from dual; -- 7

 

ex9) 지정한 길이의 문자열을 추출 : substr(표현식, 시작, [개수])

select substr('I am very happy', 6, 4) from dual; -- very (= 6번부터 4개)

select substr('I am very happy', 6) from dual; -- very happy (= 6번부터 다 꺼내와라)

 

[문제2] 사원의 레코드를 검색하시오(concat, length)

      조건1) 이름과 성을 연결하시오(concat) 

      조건2) 구해진 이름의 길이를 구하시오(length)

      조건3) 성이 n으로 끝나는 사원(substr)

 

[문제2 - 정답] 

select employee_id, concat(first_name, ' '|| last_name), length(concat(first_name, ' ' || last_name))

from employees

where substr(last_name, -1, 1) = 'n';

 

ex10) 임의의 값이 지정된 범위 내에 어느 위치에 있는지를 찾는다

: width_bucket(표현식, 최소값, 최대값, 구간)

 

최소-최대값을 설정하고 10개의 구간을 설정 후 위치 찾기

0-100까지의 구간을 나눈 후 74가 포함되어 있는 구간을 표시하시오

select width_bucket(74, 0, 100, 10) from dual;  -- 8

( = 0에서 100까지 10씩 끊었을 때 74는 몇 번째 구간에 있는가 ~ 라는 의미이다.)

 

ex11) 공백제거 : ltrim(왼), rtrim(오른), trim(양쪽)

select rtrim('test   ') || 'exam' from dual; -- testexam

 

ex12) sysdate : 시스템에 설정된 시간표시

select sysdate from dual;

select to_char(sysdate, 'YYYY"년" MM"월" DD"일"') as 오늘날짜 from dual;

select to_char(sysdate, 'HH"시" MI"분" SS"초"') as 오늘날짜 from dual;

select to_char(sysdate, 'HH24"시" MI"분" SS"초"') as 오늘날짜 from dual;

 

ex13) add_months(date, 달수) : 날짜에 달수 더하기

select add_months(sysdate, 7) from dual; -- 금일(21/09/08) 기준, 결과값은 22/04/08로 나온다

 

ex14) last_day(date) : 해당달의 마지막 날

select last_day(sysdate) from dual; -- 30

select last_day('2004-02-01') from dual; -- 29

select last_day('2005-02-01') from dual; -- 28

 

[문제3] 오늘부터 이번 달 말까지 총 남은 날수를 구하시오

[문제 3 정답] 

select last_day(sysdate) - sysdate from dual;

*날짜와 날짜도 마이너스 계산이 가능하다.

 

ex15) months_between(date1, date2) : 두 날짜 사이의 달 수

select round(months_between('95-10-21', '94-10-20'), 0) from dual; ← 자동 형변환(숫자 형식으로 바뀐다)

 

명시적인 변환(강제)

select last_name, to_char(salary, 'L99,999.00') 

from employees

where last_name='King';

 

to_char(salary, 'L99,999.00') -- salary는 숫자인데, 'L99,999.00' 싱글따옴표가 붙었다는 건 문자형으로 바꿔버렸다는 의미

-- 'L99,999.00 : 소수점 이하 두자리는 무조건 나오게 하고, 세자리마다 쉼표를(,)를 찍어야 하며, L은 '통화기호'를 써야한다는 의미

'Oracle'은 반드시 자릿수를 맞춰줘야 한다. 자바는 앞에 있는 숫자의 개수를 신경 쓸 필요 없이 #,###만 붙이면 되지만,

'Oracle'은 자릿수가 부족할 경우, ########만 우르르 나온다

 

ex16) 

oracle은 년도의 앞 2자리는 시스템의 날짜를 따라가고, 뒤에 2자리만 기억했는데, 

1999에서 2000 바뀔 때 2000이 되는 것이 아닌, 1900으로 돌아가는 불상사가 발생한 적이 있었다.

하여, 세기가 바뀔 때는 YYYY가 아닌 RRRR 형식을 사용하게 되었다.

 

select to_char(to_date('97/9/30', 'YY-MM-DD') , 'YYYY-MON-DD') from dual; ← 2097

select to_char(to_date('97/9/30', 'RR-MM-DD') , 'RRRR-MON-DD') from dual; ← 1997

 

select to_char(to_date('17/9/30', 'YY-MM-DD') , 'YYYY-MON-DD') from dual; ← 2017 

select to_char(to_date('17/9/30', 'RR-MM-DD') , 'RRRR-MON-DD') from dual; ← 2017

 

[문제4] 2005년 이전에 고용된 사원을 찾으시오

[문제4 - 정답]

 

ex17) fm형식 : 형식과 데이터가 반드시 일치해야함(fm - fm사이값만 일치)

      fm를 표시하면 숫자 앞의 0을 나타나지 않는다.

select last_name, hire_date from employees where hire_date='05/09/30';

select last_name, hire_date from employees where hire_date='05/9/30';

 

select to_char(sysdate, 'YYYY-MM-DD') from dual;

select to_char(sysdate, 'YYYY-fmMM-DD') from dual;

 

select to_char(to_date('2011-03-01','YYYY-MM-DD'), 'YYYY-MM-DD') from dual;

← 2011-03-01

select to_char(to_date('2011-03-01','YYYY-MM-DD'), 'YYYY-fmMM-DD') from dual;

← 2011-3-1,,,,,갖고 있는 0을 다 빼버린다.

select to_char(to_date('2011-03-01','YYYY-MM-DD'), 'YYYY-fmMM-fmDD') from dual;

← 2011-3-01,,,,,fm을 다 붙여버리면 0이 살아난다. 조심!

 

ex18) count(컬럼명), max(컬럼명), min(컬럼명), avg(컬럼명), sum(컬럼명) 함수

employees테이블에서 급여의 최대, 최소, 평균, 합을 구하시오

조건) 평균은 소수이하절삭, 합은 세자리마다 콤마찍고 ￦표시

select max(salary), 

min(salary), 

trunc(avg(salary),0), 

to_char(sum(salary), 'L9,999,999') from employees;

 

 

[문제5] 커미션(commission_pct)을 받지 않은 사원의 인원수를 구하시오

 

[문제5 - 정답]

 

ex19) employees테이블에 없는 부서 포함해서 총 부서의 수를 구하시오

select department_id from employees; → 107

select count(department_id) from employees; → 106(데이터 값이 null인 것은 계산하지 않는다)

select count(*) from employees; → 107(모든 항목을 검색)

select count(distinct department_id) from employees; → 11 (distinct : 똑같은 데이터의 중복은 제거해라)

select count(distinct nvl(department_id, 0)) from employees; → 12 (nvl : null값이 있는 데이터를 0으로 대체해라)

select distinct nvl(department_id, 0) from employees; → 중복을 제거하고, 만약 null값이 있다면 0으로 대체해라

 

ex20) 다중 if문

① decode(표현식, 검색1,결과1, 검색2,결과2....[default])

          : 표현식과 검색을 비교하여 결과 값을 반환 다르면 default

 

 

② case  value  when  표현식  then  구문1

                      when  표현식  then  구문2

                      else  구문3

         end case

ex)

 

업무 id가 'SA_MAN' 또는 ‘SA_REP'이면 'Sales Dept' 그 외 부서이면 'Another'로 표시

조건) 분류별로 오름차순 정렬

select job_id, decode(job_id, 

                     'SA_MAN', 'Sales Dept',

                     'SA_REP', 'Sales Dept',

                     'Another') "분류"

from employees

order by 2;

**(" ")가 붙으면 컬럼명으로 분류한다. as가 생략된 것이다.

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

select job_id, case job_id

                    when 'SA_MAN' then 'Sales Dept'

                    when 'SA_REP' then 'Sales Dept'

                    else 'Another'

              end "분류"

from employees

order by 2;       

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

select job_id, case 

                    when job_id='SA_MAN' then 'Sales Dept'

                    when job_id='SA_REP' then 'Sales Dept'

                    else 'Another'

              end "분류"

from employees

order by 2;                                          

 

[문제6] 급여가 10000 미만이면 초급, 20000 미만이면 중급 그 외면 고급을 출력하시오

 조건1) 제목은 사원번호, 사원명, 구분으로 표시하시오

 조건2) 구분 컬럼으로 오름차순 정렬하고, 같으면 사원명 컬럼으로 오름차순 하시오 

 조건3) case 사용하시오

 

[문제6 - 정답]

ex21) rank함수 : 전체 값을 대상으로 순위를 구함

      rank(표현식) within group(order by 표현식)

      rank() over(쿼리파티션)  → 전체순위를 표시

 

급여가 3000인 사람의 상위 급여순위를 구하시오

select rank(3000) within group(order by salary desc) "rank" from employees;

= 월급으로 내림차순을 한 뒤 그 그룹 안에서 급여가 3000인 사람의 순위는 몇 인가? ~ 라는 뜻

 

전체사원의 급여순위를 구하시오

select employee_id, salary, rank() over(order by salary desc)"rank" from employees;

 

ex22) first_value함수 : 정렬된 값 중에서 첫 번째 값 반환

      first_value(표현식) over(쿼리파티션)

전체사원의 급여와 함께 각부서의 최고급여를 나타내고 비교하시오

 

select employee_id, 

salary,

department_id,

first_value(salary) over(partition by department_id order by salary desc) "highsal_deptID" 

from employees;

 

★ PARTITION BY 절은 GROUP BY 절과 동일한 역할을 진행 합니다.

단, GROUP BY 절을 사용하지 않고 필요한 집합으로 행들을 그룹화 시킴

Partition by 절을 사용 함으로 GROUP BY 절 없이 다양한 GROUPING 집합의 집계 결과들을 함께 출력 할 수 있습니다.

ORDER BY 절은 Partition by 로 정의된 WINDOW 내에서의 행들의 정렬 순서를 정의 한다.

select  employee_id,

        last_name,

        salary,

        department_id,

  row_number( ) over ( PARTITION BY department_id ORDER BY salary DESC ) rnum 

from employees ; 

 

파티션을 부서별로 부서별 급여를 내림차순으로 정렬 했을 경우 Row Number

부서 번호가 바뀔 때 Row Number 는 새로 시작 되는 것을 확인 할 수 있습니다.

NULL 값은 정렬 시 가장 큰 값으로 인식 (기본설정)

[문제7] 사원테이블에서 사원번호, 이름, 급여, 커미션, 연봉을 출력하시오

        조건1) 연봉은 $ 표시와 세자리마다 콤마를 사용하시오

        조건2) 연봉 = 급여 * 12 + (급여 * 12 * 커미션)

        조건3) 커미션을 받지 않는 사원도 포함해서 출력하시오

 

[문제7 - 정답]

 

 

 

[문제8] 매니저가 없는 사원의 MANAGER_ID를 1000번으로 표시

        조건1) 제목은 사원번호, 이름, 매니저ID

        조건2) 모든 사원을 표시하시오