SQL: sql function
숫자와 관련하여 오라클이 미리 만들어놓은 함수들
abs(숫자[숫자가 있는 칼럼|숫자가 있는 칼럼의 수식])
=> 음수이던지 양수이던지 절대값을 반환
select abs(-78), abs(78) from dual;
-
모든 주문에 대하여 도서의 가격 - 주문가격을 출력 select orderid, abs(price-saleprice) from book, orders where book.bookid = orders.bookid;
-
도서의 가격과 주문가격의 차이가 가장 큰 값을 출력 select max(abs(price-saleprice)) from book b, orders o where b.bookid = o.bookid;
MAX(ABS(PRICE-SALEPRICE)) ————————- 10000
round(실수값,소수점위치)
실수값을 반올림하여 소수점위치까지 출력
- 소수점위치 1 : 소수점 첫번째 자리까지 표시
- 소수점위치 0: 소수점 이하 표시하지 말고 정수만 출력(즉, 일의자리까지 표시하라는 의미)
- 소수점위치 -1: 일의자리에서 반올림해서 십의자리까지 표시
select round(4.875,1) from dual;
ROUND(4.875,1) ————– 4.9select round(4.845,0) from dual;
ROUND(4.845,0) ————– 5select round(6469,-1) from dual;
ROUND(6469,-1)
————–
6470
select round(6469,-2) from dual;
select ename,dname,round((salary + comm) *0.5,-1) bonus from emp, dept where emp.dno = dept.dno and dname like ‘개발%’;
select name, round(sum(saleprice)/count(*),-2) 평균주문금액 from customer, orders where customer.custid = orders.custid group by name;
ceil(실수값)
무조건 올림수를 취한 정수를 반환
floor(실수값)
무조건 버림수를 취한 정수를 반환
select ceil(56.4791) from dual; CEIL(56.4791) ————- 57
select floor(56.4791) from dual; FLOOR(56.4791) ————– 56
replace( 문자열,원래문자,바꿀문자)
문자열 데이터 중에서 특정 문자를 다른 문자로 변경
select bookid, replace(bookname,’야구’,’농구’) bookname, publisher, price from book;
update book100 set bookname=replace(bookname,’야구’,’농구’) where bookname like ‘%야구%’;
length(문자열)
문자열 길이 반환(공백 포함)
lengthb(문자열)
문자열의 byte길이를 반환(공백 포함)
select length(‘김’) from dual; LENGTH(‘김’) ————- 1
select lengthb(‘김’) from dual; LENGTHB(‘김’) ————- 3 —
select bookname, length(bookname), lengthb(bookname) from book where publisher = ‘굿스포츠’;
select bookname, publisher, length(bookname), price from book where length(publisher)<=3;
substr(문자열, 시작위치, 글자수)
문자열데이터에서 시작위치에서부터 글자수만큼 잘라온다.
select substr(‘hello oracle’,3,5) from dual;
- 직원들의 성씨별로 인원수를 출력 select substr(ename,1,1) “성”,count() “인원수” from emp group by substr(ename,1,1) order by count() desc;
alter table emp add email varchar2(50); alter table emp add jumin varchar2(14);
update emp set email = ‘liver_pool9@naver.com’ where eno = 1000; update emp set jumin=’950729-1234567’ where eno=1000;
update emp set email =’jayoung1222@naver.com’ where eno=1010; update emp set jumin=’961222-2015436’ where eno=1010;
update emp set email=’ksy03170317@naver.com’ where eno=1001; update emp set jumin=’940427-2222333’ where eno=1001; update emp set email=’hyeonho0712@nate.com’ where eno=1002; update emp set jumin=’960712-1077155’ where eno=1002; update emp set email= ‘tennyyun@naver.com’ where eno = 1003; update emp set jumin=’970209-122448’where eno=1003; update emp set email=’pagy1218@hanmail.net’ where eno=1004; update emp set jumin=’951218-2000000’ where eno=1004; update emp set email = ‘anjgkwl123@naver.com’ where eno = 1005; update emp set jumin = ‘950901-2123456’ where eno = 1005; update emp set jumin =’990120-2011111’ where eno=1006; update emp set email =’youlovelive@daum.net’ where eno = 1006; update emp set email=’ibisevic@naver.com’ where eno=1007; update emp set jumin=’971021-2234598’ where eno=1007; update emp set email = ‘envayu00@gmail.com’ where eno = 1008; update emp set jumin = ‘950709-2000000’ where eno = 1008; update emp set email = ‘ysa_1010@naver.com’ where eno = 1009; update emp set jumin = ‘931123-2000000’ where eno = 1009; update emp set email = ‘vipwhy12@naver.com’ where eno = 1011; update emp set jumin = ‘971216-2222222’ where eno = 1011; update emp set email=’psm5590@naver.com’ where eno =1012; update emp set jumin=’940223-2345678’ where eno=1012; update emp set email = ‘hkhaoo@naver.com’ where eno = 1013; update emp set jumin = ‘950207-1031234’ where eno = 1013; update emp set email = ‘yoon@naver.com’ where eno = 1014; update emp set jumin = ‘650207-1031234’ where eno = 1014; update emp set email =’jayoung1222@naver.com’ where eno=1010; update emp set jumin=’961222-2015436’ where eno=1010;
복습 셀프조인 주의 select e.eno, e.ename,d.dno,d.dname, m.ename, (e.salary+e.comm)120.3 “상여금” from emp e, emp m, dept d where e.mgr = m.eno and e.dno=d.dno and dname like ‘개발%’ and substr(e.jumin,8,1)=’2’ order by (e.salary+e.comm)120.3 desc, e.ename;
instr(문자열,찾을문자)
문자열안에서 찾을 문자의 위치를 반환
select instr(‘hello oracle’,’r’) from dual;
select ename, substr(email,1,instr(email,’@’)-1) from emp;
select ename, to_char(sysdate,’yyyy’) - (substr(jumin,1,2)+1900) from emp;
select e.eno, e.ename, to_char(sysdate,’yyyy’) - (substr(e.jumin,1,2)+1900) age, dname, dloc, e.salary, m.ename “관리자이름” from emp e, emp m, dept d where e.mgr=m.eno and d.dno = e.dno and dname like ‘개발%’ and to_char(sysdate,’yyyy’)-(substr(e.jumin,1,2)+1900) >=25 and substr(e.jumin,8,1)=’2’ order by m.ename, e.ename;
update emp set salary=salary1.1, comm=comm1.1 where dno in ( select dno from dept where dname like ‘개발%’ ) and to_char(sysdate,’yyyy’)-(substr(jumin,1,2)+1900) >=25 and substr(jumin,8,1)=’2’;
update emp e1 set salary=(select max(avg(salary)) from emp group by dno), comm=(select max(avg(comm)) from emp group by dno) where dno in (select dno from dept where dname like ‘개발%’) and to_char(sysdate,’yyyy’)-(substr(jumin,1,2)+1900) >=25 and substr(jumin,8,1)=’2’;
부서별 평균급여의 최대값 select max(avg(salary)) from emp group by dno; select max(avg(comm)) from emp group by dno;
lower(문자열)
문자열을 모두 소문자로 변환
upper(문자열)
문자열을 모두 대문자로 변환
insert into customer values(10,’TIGER’,’대한민국 전주’,’010-3333-3333’); insert into customer values(11,’tiger’,’대한민국 부산’,’010-2222-2222’); insert into customer values(12,’Tiger’,’대한민국 인천’,’010-1234-1234’);
select * from customer where lower(name)=’tiger’;
영문데이터의 경우 대.소문자 구별없이 판별해야하는 경우라면 모두 대문자로 혹은 모두 소문자로 변한한 후에 판별한다.
ltrim(문자열)
문자열 데이터의 왼쪽에 있는 공백을 제거
rtrim(문자열)
문자열 데이터의 오른쪽에 있는 공백을 제거
trim(문자열)
문자열 데이터의 왼쪽과 오른쪽에 있는 공백을 제거
| select ‘*’ | ltrim(‘ hello ‘) | ‘*’ from dual; | ||
| select ‘*’ | rtrim(‘ hello ‘) | ‘*’ from dual; | ||
| select ‘*’ | trim(‘ hello ‘) | ‘*’ from dual; |
insert into customer values(13,’ tiger’,’대한민국 인천’,’010-1234-1234’); insert into customer values(14,’ Tiger ‘,’대한민국 부산’,’010-2222-2222’); insert into customer values(15,’TIGER ‘,’대한민국 전주’,’010-3333-3333’);
select * from customer where lower(trim(name)) = ‘tiger’;
lpad(칼럼 ,자릿수,문자)
컬럼의 값을 자릿수만큼 잡아서 오른쪽 맞추어 출력하되 왼쪽 빈칸은 문자로 채워서 출력 select lpad(‘hello’, 10, ‘@’) from dual;
LPAD(‘HELLO’,10,’@’) ——————– @@@@@hellorpad(칼럼 ,자릿수,문자)
컬럼의 값을 자릿수만큼 잡아서 왼쪽 맞추어 출력하되 오른쪽 빈칸은 문자로 채워서 출력
select rpad(‘hello’, 10, ‘*’) from dual;
RPAD('HELLO',10,'*')
----
hello*****
연습) select e.eno,e.ename, e.hiredate, (e.salary+e.comm)12 연봉, rpad(substr(e.jumin,1,8),14,’’) jumin, substr(e.email,1,instr(e.email,’@’)-1) 아이디 from emp e, emp m where e.mgr = m.eno and m.ename=’김시아’;
select length(‘hello’) from dual;
select e.eno, e.ename, m.ename, d.dno,d.dname, substr(e.email,1,instr(e.email,’@’)-1) 아이디 from emp e, emp m, dept d where e.mgr = m.eno and d.dno = e.dno and length(substr(e.email,1,instr(e.email,’@’)-1)) <8;
날짜와 관련한 함수들
to_date(문자열,형식지정문자)
문자열을 Date로 반환
to_char(날짜,형식지정문자)
날짜를 문자열로 반환
select e.ename, e.hiredate, m.ename from emp e, emp m, dept d where e.mgr = m.eno and d.dno = e.dno and dname like ‘개발%’ and to_char(e.hiredate,’yyyy’) <=2020;
months_between(날짜1, 날짜2)
두 날짜 사이의 개월수를 반환하는 함수 날짜1이 더 최근의 날짜여야 한다.
select months_between(sysdate,’1995/07/29’) from dual;
select eno, ename, hiredate, months_between(sysdate, hiredate) from emp order by months_between(sysdate, hiredate) desc;
** 날짜 사이의 연산 가능** select sysdate-1, sysdate, sysdate +1 from dual;
update orders set orderdate = orderdate - 365 where custid=(select custid from customer where name=’박지성’);
update orders set orderdate = orderdate -(365*2) where custid=(select custid from customer where name=’추신수’);
** 복습 **
연습) 최근 6개월간 구매이력이 없는 고객에게 이벤트 문자를 발송 하려고 합니다. 대상자의 이름과, 전화번호, 가장최근의 구매일, 구매하지 않은 개월수를 출력합니다. (단, 구매하지 개월수는 소수점이하 절삭하여 표현합니다.)->floor함수
select distinct name, phone, ( select max(orderdate) from orders o2 where o1.custid = o2.custid ), floor(months_between(sysdate, (select max(orderdate) from orders o2 where o1.custid = o2.custid ))) from customer c, orders o1 where c.custid = o1.custid and floor(months_between(sysdate, (select max(orderdate) from orders o2 where o1.custid = o2.custid ))) >= 6;
가장 최근 구매일 select max(orderdate) from orders o2 where o1.custid = o2.custid
- 관리자의 이름,근무개월수를 입력받아 입력한 관리자의 부하직원들 중 근무개월수 이상인 직원들의 사원번호,사원명,입사일,연봉,주민번호,아이디 출력
- 주민번호는 14자리 중 8자리까지만 제대로 출력하고 나머지는 *로 채워 출력
select e.eno, e.ename, e.hiredate, e.salary, rpad(substr(e.jumin,1,7),14,’*’) jumin, substr(e.email,1,instr(e.email,’@’)-1) id from emp e, emp m where e.mgr = m.eno and m.ename = ‘이건우’ and months_between(sysdate,e.hiredate) >= 20;
숙제
1) 테이블생성
create table Employee(empno number, name varchar2(10), phoneno varchar2(20), address varchar2(30), sex varchar2(10), position varchar(10), deptno number);
alter table Employee add primary key(empno);
alter table Employee
add foreign key(deptno) references Department(deptno);
create table Department(deptno number, deptname varchar2(10), manager varchar2(10));
alter table Department add primary key(deptno);
alter table Department modify manager number;
create table Project(projno number primary key, projname varchar2(10), deptno number);
alter table Project add foreign key(deptno) references Department(deptno);
create table Works(empno number, projno number, hoursworked number, primary key(empno, projno));
1-2) 데이터 삽입
insert into Department values(1,’개발1팀’,’홍길동’); insert into Department values(2,’전산팀’,’정자영’); insert into Department values(3,’개발2팀’,’이건우’);
insert into Employee values(1,’정자영’,’010-1111-1111’,’서울’,’여’,’팀장’,1); insert into Employee values(1,’정자영’,’010-1111-1111’,’서울’,’여’,’팀장’,1);
2) select name from Employee;
3) select name from Employee where sex=’여’; 4) select name from Employee where position=’팀장’;???/ 5) select name, address from Employee e, Department d where e.deptno = d.deptno and deptname= ‘IT’;
6) select deptno from Department d where manager=’홍길동’;
select count(*) from Employee where deptno = (select deptno from Department d where manager=’홍길동’);
7)
select e.deptno, hoursworked
from Project p, Employee e
where p.deptno = e.deptno
group by deptno
order by name;
8)?? select count(empno) from Project p, Works w where p.projno = w.projno and group by projno;
select projno, projname, (select count(empno) from Project p, Works w where p.projno = w.projno and group by projno) 사원의 수 from Project p where (select count(empno) from Project p, Works w where p.projno = w.projno and group by projno)>=2;
9)?// select deptno, name from Employee e group by deptno having count(*) >=3;