Database - 실습 1,2,3,4,5

==실습1==
SMITH라는 사람을 검색하고 싶을 때 소문자일지 대문자일지
모를 경우 검색할 수 있는 쿼리 작성

select * 
from emp
where lower(ename) = 'smith';

select *
from emp
where upper(ename) = 'SMITH';

 

==실습2==
이름의 세번째 자리가 R인 직원을 검색해보시오.

select *
from emp
where ename Like('__R%'); 

select *
from emp
where substr(ename,3,1) = 'R';

select *
from emp
where instr(ename,'R',3,1) = 3;

 

==실습3==
이름의 끝자리가 N으로 끝나는 직원을 검색해보세요. 

select *
from emp
where substr(ename,length(ename),1) ='N';

select *
from emp
where substr(ename,-1,1) = 'N';

 

==실습4==

AND나 BETWEEN을 사용하지 않고
81년도 입사한 직원을 검색해봅시다.

select *
from emp
where substr(hiredate,3,2) = '81';

select *
from emp
where hiredate like '%81%'; 

 

==실습5==

직원 중 이름이 5글자인 직원을 검색하되
이름은 소문자로 출력하세요.

select EMPNO,lower(ename) ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
from emp
where length(ename) = 5;