[Oracle] DQL 함수, length lengthb, dual 테이블, instr, substr, 날짜 관련 함수, 형 변환 함수, 조건 비교 함수, 그룹 함수

디지털 컨버전스/Oracle

[Oracle] DQL 함수, length lengthb, dual 테이블, instr, substr, 날짜 관련 함수, 형 변환 함수, 조건 비교 함수, 그룹 함수

gimyeondong 2020. 3. 16. 09:18

SQL
DDL (Create / Drop / Alter)
DML (Insert / Select (DQL))
DCL (Grant / Revoke)
TCL (Rollback / Commit / Savepoint)

Select <column> from <table> where <조건> order by

-- 1. 근속년수가 10년 이상인 직원들의 이름, 급여, 근속연수 (소수점출력X)를 근속연수 오름차순으로 정렬하여 출력
-- 단, 여기서 급여는 50% 인상된 급여로 출력하시오
select emp_name 이름, salary*1.5 급여, floor((sysdate-hire_date)/365) 근속연수
from employee
where ((sysdate-hire_date)/365) > 10 
order by 3 ;

-- 2. 입사일이 99/01/01 ~ 10/01/01 인 사람 중에서
-- 급여가 2000000원 이하인 사람의
-- 이름, 주민번호, 이메일, 폰번호, 급여를 검색하세요
select emp_name 이름, emp_no 주민번호, email 이메일, salary 급여
from employee
where hire_date between '99/01/01' and '10/01/01' and salary <= 2000000;

--null value 함수 : 첫번째 인자값이 널이 아니면 그대로 반환, 널이면 두번째 인자값 반환
--출력할 대상 컬럼의 타입에 따라 변경할 타입을 정해줘야함 (bonus는 숫자형column)
select emp_name, nvl(bonus, 0) from employee;

-- 3. 급여가 2000000원 ~ 3000000원 인 여직원 중에서 4월 생일자를 검색하여
-- 이름, 주민번호, 급여, 부서코드를 주민번호 순으로(내림차순) 출력하세요.
-- 단, 부서코드가 null인 사람은 부서코드가 '없음'으로 출력하세요.
select emp_name 이름, emp_no 주민번호, salary 급여, nvl(dept_code, '없음') 부서코드
from employee
where (salary between 2000000 and 3000000) and emp_no like '_______2______' and emp_no like '__04__________'
order by 2 desc;

-- 4. 남자 사원 중 보너스가 없는 사원의 오늘까지의 근무일을 측정하여
-- 1000일 마다(소수점 제외) 급여의 10% 보너스를 계산하여
-- 이름, 특별 보너스 (계산 금액) 결과를 출력하세요.
-- 단, 이름 순으로 오름차순 정렬하여 출력하세요.
select emp_name 이름, salary*floor((sysdate-hire_date)/1000)*0.1 특별보너스
from employee
where emp_no like '_______1______' and bonus is null
order by 1 desc;

강사님 코드

-- 1. 근속년수가 10년 이상인 직원들의,
-- 이름, 급여, 근속년수 (소수점 출력 X)를 
-- 근속년수 오름차순으로 정렬하여 출력
-- 단 여기서 급여는 50% 인상된 급여로 출력하세요.

select emp_name,salary*1.5 as "인상 급여", floor((sysdate-hire_date)/365) as "근속년수"
from employee
where (sysdate-hire_date)/365 >= 10
order by 3;

-- 2. 입사일이 99/01/01 ~ 10/01/01 인 사람 중에서 
-- 급여가 2000000 원 이하인 사람의
-- 이름,주민번호,이메일,폰번호,급여를 검색하세요.

select emp_name,emp_no,email,phone,salary
from employee
where (hire_date between '99/01/01' and '10/01/01')
and salary <= 2000000;


-- 3. 급여가 2000000원 ~ 3000000원 인 여직원 중에서 4월 생일자를 검색하여 
-- 이름,주민번호,급여,부서코드를 주민번호 순으로(내림차순) 출력하세요.
-- 단, 부서코드가 null인 사람은 부서코드가 '없음' 으로 출력하세요.

select emp_name, emp_no, salary, nvl(dept_code,'없음')
from employee
where (salary between 2000000 and 3000000) and
emp_no like '___4__-2%' 
order by emp_no desc;


-- 4. 남자 사원 중 보너스가 없는 사원의 오늘까지 근무일을 측정하여 
-- 1000일 마다(소수점 제외) 급여의 10% 보너스를 계산하여 
-- 이름,특별 보너스 (계산 금액) 결과를 출력하세요.
-- 단, 이름 순으로 오름 차순 정렬하여 출력하세요.

select emp_name,floor((sysdate-hire_date)/1000)*(salary*0.1) as "특별 보너스" from employee
where emp_no like ('______-1%') and bonus is null
order by emp_name;

DQL 함수

-- 함수 ( function ) - 자바의 메서드와 동일한 동작 방식
-- 단일 행 함수 : 행 하나하나 마다 적용되는 함수
-- floor / nvl
select emp_name, nvl(dept_code, '부서없음')
from employee;

-- 그룹 함수 : 모든 행의 값을 추출해서 하나의 결과로 만들기
-- sum
select sum(salary) from employee;

length lengthb

-- length
select emp_name, length(emp_name), email, length(email)
    from employee;
-- lengthb : 바이트 길이
select emp_name, lengthb(emp_name), lengthb('AB')
    from employee;

dual 테이블

-- dual 테이블 : 오라클 사용자가 테스트 목적으로 사용하는 단일행 단일열 테이블 
-- 한번만 출력하고 싶다면 행이 하나인 테이블 필요
select 'Hello World' from employee;
select sysdate from dual;

instr

-- instr(String, STR, ?, ?) : 인자값 (타겟, 찾을문자열, 어디서부터, 몇번째것인지)

select instr('Hello World Hi High','H',1, 2) from dual;
-- 13 리턴

select instr('Hello World Hi High','H',2, 1) from dual;
-- 13 리턴

select instr('Hello World Hi High','H',2, 2) from dual;
-- 16 리턴

select instr('Hello World Hi High','H',2, 3) from dual;
-- 0 리턴 (찾지 못함)

select instr('Hello World Hi High','H',-1, 1) from dual;
-- 16 리턴 (세번째 인자값이 -1이면 뒤에서부터 찾기)

select instr('Hello World Hi High','H',-5, 1) from dual;
-- 13 리턴

--employee 테이블의 email에서 @문자의 위치를 출력하세요.
select email, instr(email,'@',1, 1) from employee;

substr

--substr

select
    substr('Hello Java Programmer',7,4)
from
    dual;
-- Java    

select
    substr('Hello Java Programmer',-10,7)
from
    dual;
-- Program

select
    substr('안녕 자바 프로그래머',4,2)
from
    dual;
-- 자바

-- 1. Employee테이블에서 직원들의 성씨만 중복없이 출력하세요
select
    distinct substr(emp_name,1,1)
from
    employee;

-- 2. Employee 테이블에서 남자직원의 사원번호, 사원명, 주민번호, 연봉을 출력하세요.
-- 주민번호의 뒷6자리는 *처리하세요
select
    emp_id 사원번호, emp_name 사원명, substr(emp_no,1,8)||'******' 주민번호, salary*12 연봉
from
    employee
where
    substr(emp_no,8,1)=1;

-- replace (대상, 찾을것, 변경할것)
select replace('Python Programmer','Python','Java') from dual;

숫자 관련 함수

-- abs ( absolute )
select abs(-10) from dual;

--mod ( 나머지연산 )

select mod(10,3) from dual;
-- 1

select mod(10,4) from dual;
-- 2

--round : 반올림함수

select round(126.456 , 3) from dual;
-- 126.456
-- 3번째 자리로 반올림 (cf. 3번째 자리에서 반올림 아님)

select round(126.456 , 2) from dual;
-- 126.46

select round(126.456 , 1) from dual;
-- 126.5

select round(126.456 , 0) from dual;
-- 126

select round(126.456 , -1) from dual;
-- 130

select round(126.456 , -2) from dual;
-- 100

-- floor : 버림함수
select floor(126.456) from dual;

-- trunc : 소수점 자리수에서 버림 함수
select trunc (126.456 , 1) from dual;
-- 126.4

select trunc (126.456 , 2) from dual;
-- 126.45

select trunc (126.456 , -1) from dual;
-- 120

select trunc (126.456 , 0) from dual;
-- 126

날짜 관련 함수

(예제 실행시 sysdate 20/03/16 (월) )

-- sysdate 현재 날짜를 반환하는 키워드
select sysdate from dual;

-- months_between : 두개의 시간값을 인자로 받아 두 시간 사이의 개월 수 반환
select emp_name, floor(months_between(sysdate, hire_date)) 근무개월
from employee;
-- 뺄셈으로는 일수가 나온다!

-- add_months : 특정 날짜에 개월수를 더해서 반환

select emp_name, add_months(hire_date,6)
from employee;

select add_months(sysdate, 50) from dual;
-- 24/05/16

-- 일수 구하기
select add_months(sysdate, 18)-sysdate 일 from dual;
-- 549

-- next_day : 인자로 전달받은 날짜에서 두번째 인자로 전달받은 요일 중, 가장 가까운 다음 요일을 출력
-- (숫자표현 : 1=일요일 ... 7=토요일)

select next_day(sysdate, '화요일') from dual;
-- 20/03/17
select next_day(sysdate, '월') from dual;
-- 20/03/23
select next_day(sysdate, 7) from dual;
-- 20/03/21

-- last_day : 첫번째 인자로 전달받은 날짜의 달에 해당하는 마지막 날짜 반환

select last_day(sysdate) from dual;
-- 20/03/31

select last_day(sysdate+16) from dual;
-- 20/04/30

-- 다음달의 마지막 날짜 구하기
select last_day(add_months(sysdate,1)) from dual;
-- 20/04/30

-- extract : 인자값으로 전달된 날짜에서 원하는 값을 추출

select extract (year from sysdate) from dual;
-- 2020
select extract (month from sysdate) from dual;
-- 3
select extract (day from sysdate) from dual;
-- 16

-- Employee 테이블에서 사원의 이름, 입사년, 입사월, 입사일, 년차를 출력하세요.
-- 각 입사날짜는 YYYY년 M월 D일로 출력하도록 하세요.
-- 년차 출력은 소수점일 경우 올림으로 하여 출력하세요 (28.144 -> 29년차)
-- (출력시 정렬은 입사년 기준으로 오름차순)

select  
    emp_name 이름,
    extract (year from hire_date) 입사년,
    extract (month from hire_date)입사월,
    ceil((sysdate - hire_date)/365)  년차
from employee;

-- 2. 특별 보너스를 지급하기 위하여 자료가 필요합니다.
-- 입사일을 기점으로 다음달 1일 부터 6개월 후를 출력하세요.
-- 이름, 입사일, 기준일, 기준일+6, 기준달(월)
-- ex) 90년2월6일 입사 -> 90년3월1일 부터 계산
-- ex) 90년2월26일 입사 -> 90년3월1일 부터 계산
-- ex) 97년12월1일 입사 -> 98년1월1일 부터 계산
-- 출력시 입사일 기준으로 정렬하세요.

select 
    emp_name as "이름", 
    hire_date as "입사일", 
    last_day(hire_date+1) as "기준일",
    add_months(last_day(hire_date+1),6) as "6개월 후",
    extract (month from add_months(last_day(hire_date+1),6)) as "기준달"
from employee
order by 2;

형 변환 함수

-- 현 변환 함수
-- to_char : 날짜 또는 숫자를 문자형으로 변경하는
-- date to char

select to_char(sysdate, 'yyyy@mm@dd') from dual;
--2020@03@16

select to_char(sysdate, 'yyyy/mm/dd/day') from dual;
--2020/03/16/월요일

select to_char(sysdate, 'yyyy/month/dd/day') from dual;
--2020/3월 /16/월요일

select to_char(sysdate, 'yyyy/mm/dd hh:mi:ss') from dual;
--2020/03/16 02:23:28

select to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss') from dual;
--2020/03/16 14:24:28

select to_char(sysdate, 'yyyy/mm/dd hh12:mi:ss') from dual;
--2020/03/16 02:24:46 (뒤에12 안넣어도 디폴트)

select to_char(sysdate, 'yyyy/mm/dd hh시mi분ss초') from dual;
--01821. 00000 -  "date format not recognized"

select to_char(sysdate, 'yyyy/mm/dd hh"시"mi"분"ss"초"') from dual;
--2020/03/16 02시25분51초

select to_char(sysdate, 'yyyy/mm/dd(dy) hh"시"mi"분"ss"초"') from dual;
--2020/03/16(월) 02시27분18초

select emp_name, to_char(hire_date, 'yyyy/mm/dd(dy)') from employee;

문자로 바꾸기 (형식 주기)

-- number to char
select to_char(12345678, '000,000,000') from dual;
-- 012,345,678
select to_char(12345678, '999,999,999') from dual;
--  12,345,678
select to_char(12345678, '999,999') from dual;
--######## (형식이 부족하면 에러)

select to_char(12345678, 'L999,999,999') from dual;
--￦12,345,678 (local 통화)

select emp_name, to_char(salary, 'L999,999,999') from employee;

-- ltrim rtrim 왼쪽 공백지우기 / 오른쪽 공백 지우기
select emp_name, ltrim(to_char(salary, 'L999,999,999'),' ') from employee;

-- 1. employee 테이블에서 2000년도 이후에 입사한 사원의 사번, 이름, 입사일을 조회

select emp_id 사번,emp_name 이름, hire_date 입사일 
from employee
where hire_date > to_date(20000101,'YYYYMMDD')
order by 3;

--내가 푼 방식
select emp_id 사번,emp_name 이름, hire_date 입사일 
from employee
where to_char(hire_date,'yyyy')>=2000
order by 3;

조건 비교 함수

-- dcode
-- 매서드의 인자 부분이 가변 인자로 되어있다 : 인자개수 변함
select 
    emp_name ,  
    decode(substr(emp_no,8,1),1,'남',2,'여') 성별 
from employee;

-- case 
select
    emp_name, 
    case
        when substr(emp_no,8,1) = 1 then '남'
        when substr(emp_no,8,1) = 2 then '여'
        else '미정'
    end "성별" 
 from employee;
 
 -- 부등호 표기 가능 when substr(emp_no,8,1) >= 1 then '남'

 -- 60년대생 직원들 중, 65년생 이상의 직원은 60년생 후반
 -- 65년생 미만의 직원은 60년생 초반 이라고 출력하세요
 -- 이름 / 주빈번호 / 정보 출력
 
 select 
    emp_name 이름,
    emp_no 주민번호,
    case
        when substr(emp_no,1,2) >= 65 then '60년생 후반'
        when substr(emp_no,1,2) < 65 then '60년생 초반'
    end "정보"
from employee
where substr(emp_no,1,2) between 60 and 69;

그룹 함수

sum 합계 / avg 평균 / count 행의 개수

총합sum 합계

select ltrim(to_char(sum(salary), 'L999,999,999'),' ') 직원급여총합
from employee;

-- 1. 부서코드 D5 인 직원들의 급여 총합
select ltrim(to_char(sum(salary), 'L999,999,999'),' ') "D5부서 급여총계"
from employee
where dept_code ='D5';

-- 2. 여직원들의 급여 총합
select ltrim(to_char(sum(salary), 'L999,999,999'),' ') "여직원 급여총계"
from employee
where substr(emp_no,8,1) = 2;

avg 평균

-- avg 평균
select ltrim(to_char(avg(salary), 'L999,999,999'),' ')||'원' "직원급여평균"
from employee;

count 행의 개수

select count(emp_name) from employee;
-- 23
select count(dept_code) from employee;
-- 21 (null값 세지 않음)
select count(*) from employee;
-- 23 (행의 개수)
select count(*) from employee where dept_code='D5';
-- 6 (D5인 행수)
select count(distinct dept_code) from employee;
-- 6 (부서의 개수)

max / min - 촤대값, 최소값 함수

-- max / min - 촤대값, 최소값 함수
select max(salary), min(salary) from employee;

-- 1. 2020년4월15일까지 몇일 남았는지 출력
select floor(to_date(20200415,'YYYYMMDD')-sysdate)||'일' "4월15일까지" from dual;
-- 29일

-- 2. 2021년3월1일은 무슨 요일일까?
select to_char(to_date(20210301,'YYYYMMDD'),'day') "내년3월1일은" from dual;
-- 월요일

-- 3. 직원의 이름과 이메일 주소중 아이디 부분만 출력하시오

select 
    emp_name 이름, 
    substr(email,1,instr(email,'@',1)-1) 아이디
from employee;

-- 4. 70년대생 직원들의 급여총합
select ltrim(to_char(sum(salary), 'L999,999,999'),' ')  "70년대생 급여총계" 
from employee  
where substr(emp_no,1,2) between 70 and 79;
--￦21,316,240

-- 5. 60년생의 직원명과 년생, 보너스 값을 출력하시오
-- 그떄 보너스 값이 null인 경우에는 0이라고 출력 되게 만드시오
select 
    emp_name 직원명, 
    substr(emp_no,1,2) 년생, 
    case 
        when bonus is null then 0
        when bonus is not null then bonus
    end  "보너스"
from employee  
where substr(emp_no,1,2) between 60 and 69;

DDL 위주로 공부

서술형 / 문제해결 시나리오