ORACLE

오라클 2일

[연산자]

= : 같다

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

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

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;

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

이 름 연 봉

King 28800

[단일행 함수]

숫자함수 : mod, round, trunc, ceil
문자함수 : lower, upper, length, substr, ltrim, rtrim, trim
Data는 대,소문자 가림 왼,오른쪽 공백제거
날짜함수 : sysdate, add_month, month_between
시스템에있는날짜, 지금부터 3달 뒤,몇달부터 몇달사이

변환함수

* 암시적(implict)변환:자동
  
   VARCHAR2 또는 CHAR   ------>   NUMBER
   VARCHAR2 또는 CHAR   ------>   DATE
   NUMBER        ------>    VARCHAR2
   DATE         ------>    VARCHAR2
  
* 명시적(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 : 숫자를 표시    9,999.99   9.999.00<->JAVA #,###.## #.###.00
  0 : 0을 강제로 표시
  $ : 부동$기호를 표시 
  L : 부동 지역통화기호 표시
  . : 소수점출력
  , : 천단위 구분자 출력

그룹(집합)함수 : avg, sum, max, min, count
기타함수 : nvl, dcode, case

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

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

select employee_id, last_name, department_id
from employees
where lower(last_name)='higgins';

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

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

ex3 ) 35765.357을 반올림(round)

위치가 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)

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

ex6 ) length(‘문자열’) : 문자열의 길이

lengthb(‘문자열’) : 문자열의 길이

create table text ( 

str1 char(20),
str2  varchar2(20));
 

char : 고정문자길이
varchar2 : 가변문자길이
 

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

commit;


select length(str1), length(str2) from text;
14  3
20  5

select lengthb(str1), lengthb(str2) from text;
20  9
20  5

ex7 )

select length('korea') from dual; 
select length('코리아') from dual;

select lengthb('korea') from dual;
select lengthb('코리아') from dual; 

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

select instr('HelloWorld', 'W') from dual;  //6
              123456789   
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)

employee_id   name     length

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

 102       LexDe Haan    10

ex10 ) 임의의 값이 지정된범위내에 어느 위치 : width_bucket(표현식,최소값,최대값,구간)

최소-최대값을 설정하고 10개의 구간을 설정후 위치찾기
0-100까지의 구간을 나눈후 74가 포함되어있는구간을 표시하시오
select width_bucket(74, 0, 100, 10) from dual;  //8

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

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

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, 달수):날짜에 달수 더하기

현재 달수 + 7month
select add_months(sysdate, 7) from dual;

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

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

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

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

초 단위 계산,소숫점이하까지 다 계산 -> Round(반올림)소숫점이하 다 잘라라
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';

ex16 )

                       현재년도     현재년도
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

1997  2017  2097 
    20   80    --> 가까운년도를 가져온다.

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

last_name        hire_date

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

King        17-6월 -2003           

De Haan     13-1월 -2001           

Greenberg   17-8월 -2002

          (앞에 0을 붙인다.)

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                     --fm1개 둘다빠짐
select to_char(to_date('2011-03-01','YYYY-MM-DD'), 'YYYY-fmMM-fmDD') from dual;← 2011-3-01                --fm2개 1개빠지고 다시붙음

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)을 받지 않은 사원의 인원수를 구하시오

ex19 ) employees테이블에서 없는부서포함해서,총 부서의 수를 구하시오(답 : 12개)

(nvl사용)--비어있는 부서ID에 NULL->0을 강제로 채운다. 
select department_id from employees; ← 107
select count(department_id) from employees; ← 106
select count(*) from employees;
select count(distinct department_id) from employees; ← 11
select count(distinct nvl(department_id, 0)) from employees; ← 12
select distinct nvl(department_id, 0) from employees;← nvl은 null값을 0으로 대치

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

표현식과 검색을 비교하여 결과 값을 반환 다르면 default
decode(a, 'A',1, 'B',2 , 'C',3 ,7)

 

  ② case value when 표현식 then 구문1
               when 표현식 then 구문2
           else 구문3
      end case
      
업무 id가 'SA_MAN' 또는 ‘SA_REP'이면 'Sales Dept' 그 외 부서이면 'Another'로 표시


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

select job_id, decode(job_id, 
           'SA_MAN', 'Sales Dept',
           'SA_REP', 'Sales Dept',
           'Another') "분류" --as없어도 ""오면 됨
from employees
order by 2;

if(job_id == 'SA_MAN') 'Sales Dept'
else if(job_id == 'SA_REP')'Sale Dept'
else 'Another'

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

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미만이면 중급 그 외면 고급을 출력하시오 (case 사용)

조건1) 컬럼명은 ‘구분’으로 하시오

조건2) 제목은 사원번호, 사원명, 구 분

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

데이터가 많을때는 유의

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

rank(표현식) within group(order by 표현식)
rank() over(쿼리파티션) → 전체순위를 표시
 
급여가 3000인 사람의 상위 급여순위를 구하시오
select rank(3000) within group(order by salary desc) "rank" from employees;                          --Sort를 먼저 시킨다.(시간이오래걸림)

전체사원의 급여순위를 구하시오
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,       --같은부서로 그룹을 잡음.Sort(부서별sort)
first_value(salary) over(partition by department_id order by salary desc) "highsal_deptID" 
from employees;

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

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

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

EMPLOYEE_ID  LAST_NAME  SALARY  DEPARTMENT_ID  RNUM

------------ --------- -------- ------------- -----
     Whalen     4400         10         1

     Hartstein  13000        20         1

     Fay        6000         20         2

     Raphaely   11000        30         1

     Khoo       3100         30         2

     Baida      2900         30         3

     Tobias     2800         30         4

     Himuro     2600         30         5

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

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

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

조건1) 연봉은 $ 표시와 세자리마다 콤마를 사용하시오
조건2) 연봉 = 급여 * 12 + (급여 * 12 * 커미션)
조건3) 커미션을 받지 않는 사원도 포함해서 출력하시오

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

조건1) 제목은 사원번호,이름,매니저ID
조건2) 모든 사원을 표시하시오    

    사원번호   이름   매니저ID
    -----------------------
      100    King    1000 

[DB] ORACLE - 2일

ORACLE

오라클 2일

🐯

ORACLE

오라클 2일

Share this post