3. SQL 함수

• 함수 (function) : 데이터 검색을 좀 더 정교하게 하고자 할 때 유용한 기능

Ex ) 우리나라에서 등록금이 가장 비싼 학교가 어디인가

      서울시 물가 데이터에서 가장 비싼 품목은 무엇인가?

      가정 불화의 가장 큰 원인은 무엇인가?

      치킨집 폐업이 가장 많은 년도는 최근에 언제인가?

• 함수의 종류 2가지

1. 단일행 함수 (single row function)

 입력                   출력

-------------> 함수 ------------->

하나의 행(row)       하나의 값

종류 : 문자함수 : upper, Lower, Inicap, substr, instr, lpad, rpad, ltrim, rtrim, trim, replace

                      concat, length

  숫자함수 : round, trunc, mod, power

  날짜함수 : months_between

                add_months

                next_day

                last_dat

  변환함수 : to_char, to_number, to_date

  일반함수 : nvl, decode ,case

 

 

2. 복수행 함수 (multiple row function)

       입력                    출력

----------------->

-----------------> 함수 -------------->

----------------->           하나의 값

여러개의 행(row)

 

종류 : max

         min

         avg

         sum

         count

 

---

• 문자 함수 Length - 문자 길이를 출력하는 함수

ex) select ename, length(ename) from emp;

문제 58. 테이블에서 이름과 이메일과 이메일의 철자의 개수 출력, 철자 개수가 가장 많은 학생부터 출력

-> select ename, length(email) from emp11 order by length(email) desc;

 

@실행 순서 - from - select - order by

 

문제 59. 통신사, 이름, 나이 출력, 통신사를 오름차순으로 출력

-> select telecom, ename, age from emp11 order by telecom asc;

->select telecom, ename, age from emp11 order by 1 , 3 desc;

@ order by 뒤에 계속 붙이면 정렬 계속 가능 -> 통신사 asc, 나이 desc

@ 컬럼명에 인덱스 번호 붙여도 똑같이 실행된다. -> 통신사 인덱스 : 1, 나이 : 3

 

문제 60. 이름, 직업 월급 출력하는데 직업을 오름차순으로 정렬, 그것을 기준으로 월급을 descending하게 정렬

-> select ename, job, sal from emp11 order by job asc, sal desc;

---

• Lpad, Rpad 함수 - 막대 그래프처럼 데이터를 간단하게 시각화 할 수 있는 함수

ex) select ename, lpad(sal, 10, '*') as salary from emp;

@왼쪽 패딩으로 별 붙임, (붙일 컬럼 명, 전체 공간, 붙일 문자)

@ rpad 사용하면 오른쪽에 붙는다.

@월급 출력하는 데, 전체 10자리 잡고 출력하고 나머지 왼쪽에 별표(*) 를 채운다.

 

문제 61. 위의 결과 다시 출력, 숫자는 나오지 않게하고, ■ 하나를 100단위로 해서 출력

-> select ename, lpad('■', sal,'■') from emp;

@컬럼 ,Sal/100 -> 개수, 채울 문자

 

문제 62. 위의 결과를 다시 출력, 월급이 높은 사원부터 출력

-> select ename, lpad('■', sal/100,'■') as salary from emp order by sal desc;

 

문제 63. 이름과 나이와 나이의 막대그래프를 출력

(막대 '■' 한 개는 숫자 1로 가정)

-> select ename, age, lpad('■',age,'■') as 나이 from emp11 order by age desc;

---

 

• Trim - 특정 철자를 잘라내버리는 함수

Ex) select ename, trim('S' from ename) from emp;

@ 하나의 철자를 잘라낼 때는 trim을 사용

@ 여러개의 철자를 잘라낼때는 rtrim과 ltrim을 사용한다.

@ rtrim 은 오른쪽에 있는 철자를 잘라낸다

@ ltrim은 왼쪽에 있는 철자를 잘라내는 것이다.

 

문제 64. 이메일에서 .com 과 .net 잘라지게 출력

-> select email, rtrim(email,'.com, .net') from emp11;

 

@ ltrim(ename) --> 왼쪽 공백 제거

@ rtrim(ename) --> 오른쪽 공백 제거

@ trim(ename) --> 양쪽 공백 제거

 

문제 65. 아래의 데이터를 입력하고 데이터를 출력하는데 이름이 null이 아니거나 공백이 아닌 사원들의 이름과 월급 출력

 

Insert into emp(empno, ename, sal) values(1234, null, 3500);

Insert into emp(empno, ename, sal) values(2345,'    ',4500);

- 위의 insert문 실행 후 sql문 작성

 

-> select ename, sal emp where trim(ename) is not null; 

@ 공백 trim -> null값이 된다.

---

• 문자함수 replace - 특정 철자를 다른 철자로 대체하는 함수

 

Ex) 이름과 월급을 출력하는데, 월급을 출력할때 숫자 0을 *로 출력

-> select ename, replace(sal,0,'*') from emp;

문제 66. 우리반의 이름 출력, 와 병원 전광판으로 출력 (홍길동 - > 홍*동)

-> select ename, replace(ename, substr(ename, length(ename)-1, 1), '*') from emp11;

-> select ename, replace(ename,substr(ename, floor(length(ename)/2)+1,1),'*') from emp11;

 

 

@ 테이블 수정 - alter table 테이블 명 modify ename 속성;

-> alter table emp11 modify ename varchar2(20);

 

---

• Concat 함수 - 두 개의 컬럼의 데이터를 붙여서 출력하는 함수

Ex) select concat(ename, sal) from emp; -> 2개밖에 안됨

-> select ename||sal from emp;

@ from dual -> 가상의 테이블로 함수의 결과값만 보기 위한 가상의 테이블 생성

Ex) smith -> smi 만 출력

-> select substr('smith',1,3) from dual;

-> select substr('smith', -2,1) from dual;

 

---

 

• 숫자 함수

 # round - 반올림 하는 함수

 # trunc - 반올림하지 않고 버리는 함수

 # mod -> 나머지 값 ㅜ하는 함수

 # power -> 지수함수

 # log -> 로그함수

 # ln -> 밑수가 자연상수(e) 인 로그함수

 

 

• round

ex) select '787.567', round(787.567,-1), round(787.567, 0), round(787.567, 1), round(787.567, 2) from dual;

@ 소수점 기준 인덱스 0, 1인경우에는 소수점 둘째 자리에서 첫 번째 자리로 반올림

문제 67. 이름, 월급 출력하는데 십단위에서 반올림되게 하시오

->  select ename, sal, round(sal,-2) as 월급 from emp;

 

• Trunc

-> select '787.567', Trunc(787.567,-1), Trunc(787.567, 0), Trunc(787.567, 1), Trunc(787.567, 2) from dual;

@ 선언 인덱스 기준 뒤의 값을 버린다.

 

• Mod (나머지 연산) - 나눈 나머지 값을 출력하는 함수

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

 

문제 68. 이름, 나이, 나이가 짝수이면 0 출력, 홀수면 1

->  select ename, age, mod(age,2) from emp11;

 

• 지수함수, 로그함수 (power, log, ln)

Ex)  select power(2,3) from dual; => 2^3

Ex)  select log(2,8) from dual; @ log(밑수, 진수)

 

---

• 날짜 함수

1. Months_between : 날짜와 날짜 사이의 개월수를 출력하는 함수
2. Add_months : 날짜에 개월수를 더한 날짜를 출력하는 함수
3. Next_day : 특정 날짜에서 앞으로 돌아올 특정 요일의 날짜를 출력하는 함수
4. Last day : 특정 날짜의 달의 말일을 출력하는 함수

오늘날짜를 보는 함수 sysdate

->  select sysdate from dual;

->  select systimestamp from dual; # 시분초 같이 나온다.

 

날짜 - 숫자 = 날짜

날짜 + 숫자 = 날짜

날짜 - 날짜 = 숫자

 

Ex) select sysdate, sysdate + 1, sysdate - 1 from dual;

문제 73. 이름, 입사한 날짜부터 오늘까지 총 몇 일 근무했는지 출력

-> select ename, round(sysdate-hiredate) from emp;

 

문제 74. 이름, 입사한 날짜부터 오늘까지 총 몇 주 근무했는지

-> select ename, round((sysdate- hiredate)/7) from emp;

 

문제 75. 이름, 입사한 날짜부터 오늘까지 총 몇 달 근무했는지

-> select ename, round(Months_between(sysdate, hiredate)) from emp;

 

문제 76. 내가 그동안 태어나서 몇달을 살아왔는지 출력

-> select ename,round(Months_between(sysdate,birth)) from emp11;

 

• Add_months : 특정날짜에서 개월 수를 더한 날짜를 출력하는 함수

Ex) 오늘 날짜에서 10일 후의 날짜를 출력

->  select add_months(sysdate,10) from dual;

 

• Next_day : 특정 날짜에서 앞으로 돌아올 요일의 날짜를 출력하는 함수

Ex) 오늘날짜에서 앞으로 돌아올 월요일의 날짜를 출력

-> select next_day(sysdate, '월요일') from dual;

 

문제 77. 오늘부터 100달 뒤에 돌아오는 금요일 날짜

-> select next_day(add_months(sysdate, 100),'금요일') from dual;

 

• Last_day : 특정 날짜의 달에 말일을 출력하는 함수

Ex) select sysdate, last_day(sysdate) from dual;

 

문제 78. 오늘부터 요번달 말일까지 총 몇일이 남았는지 출력

-> select last_day(sysdate) - sysdate from dual;

---

• 변환 함수 : 데이터 유형을 다른 데이터 유형으로 변환하는 함수 (문자형, 숫자형, 날짜형)

• To_char : 문자형으로 변환하는 함수

   숫자 -> 문자형 <- 날짜형

• to_number : 숫자형으로 변환하는 함수

   숫자 <- 문자형

• to_date : 날짜형으로 변환하는 함수

   문자형 -> 날짜형

   Ex) select ename, to_char(birth,'day') from emp11;

 

 

문제 79. 우리반 테이블에서 이름과 태어난 요일을 출력, 월화수목금토일 순으로 출력

-> select ename, to_char(birth,'dy')  from emp11 order by to_char(birth-1,'d') asc;

 

-> select ename, to_char(birth, 'dy') from emp11 order by mod(to_char(birth, 'd')+5, 7);