[Oracle] SQL Class 6 - [고급함수] 연습문제1

*** 단일행 함수 과제 ( scott계정의 테이블로 할 것)

1.     이름의 길이가 6자 이상인 사원의 정보를 이름, 이름 글자수, 업무를 검색
SQL> select ename, length(ename) from emp where length(ename)>=6;

 

2.     SCOTT의 사원번호, 성명(소문자로), 담당업무(대문자로) 검색

SQL> select deptno, ename, lower('scott'), upper('scott') deptno from emp;

 

3.     DEPT 테이블에서 Loc 컬럼의 첫 글자만 대문자로 변환하여 검색

SQL> select deptno, initcap(dname), initcap(loc) from dept;

 

4.     사원번호,이름,업무,급여를 검색하되 EMPNO와 ENAME을 줄 바꿔서 검색

SQL> select ename, empno, job, sal from emp;

 

5.     이름의 첫 글자가 ‘K’보다 크고 ‘Y’보다 작은 사원의 정보 검색

SQL> select ename from emp where substr(ename,1,1)>'K' and substr(ename,1,1)<'Y';

 

6.     사원번호,이름,이름의 길이,급여,급여의 길이 검색

SQL> select empno, ename, length(ename), sal, length(sal) from emp;

 

7.     업무 중 ‘A’자의 위치를 검색, 두 번째 ‘A’자의 위치도 검색

SQL> select job, instr(job,'A',1,1), instr(job,'A',1,2) from emp;

 

8.     이름의 검색 자릿수를 20으로 하고 오른쪽 빈칸에 ‘*’을 채우고

SQL> select rpad(ename, '20', '#') from emp;

 

9.     담당 업무 중 좌측에 ‘A’를 삭제하고 급여 중 좌측의 1을 삭제한 후 검색

SQL> select deptno, job, ltrim(job, 'A'), sal, ltrim(sal, '1') from emp;

 

10.    담당 업무 중 우측에 ‘T’를 삭제하고 급여 중 우측의 0을 삭제한 후 검색

SQL> select deptno, job, rtrim(job, 'T'), sal, rtrim(sal, '0') from emp;

 

11.  이름 중에 ‘A’,’B’,’C’는 소문자로 바꿔서 검색하고 급여를 숫자가 아닌 글자로 검색(급여가 1425 이면 일사이오 )

SQL> select replace(replace(replace(ename,'A','a'),'B','b'),'C','c') 소문자

,replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(sal,1,'일'),2,'이'),3,'삼'),4,'사'),5,'오'),6,'육'),7,'칠'),8,'팔'),9,'구'),0,'공') 한글표기 from emp;

 

12.  JOB의 글자 중 ‘LE’를 ‘AL’로 바꿔서 검색

SQL> select job, replace(job, 'LE', 'AL') from emp;

 

13.  모든 사원의 이름과 급여를 연결(||)하여 검색하되 각 15자리씩 왼쪽을 공백으로 검색

          SMITH            800

          ALLEN           1600
SQL> select ename||sal, lpad(length(ename), '15', '*') from emp;

 

14.  숫자 1234.5678를 정수로 반올림하고 소수 첫째 자리까지 반올림과 절삭한 값을 검색

SQL> select round(1234.5678,0), trunc(1234.5678, 1) from emp;

 

15.  숫자 -456.789를 정수로 올림하고 -123.78을 내림한 정수를 검색

SQL> select ceil(-456.789), floor(-123.78) from emp;

 

16.  급여를 30으로 나눈 나머지를 구하여 검색

SQL> select sal, mod(round(sal/30), 30) "급여" from emp;

 

17.  모든 사원의 정보를 이름,업무,입사일, 입사한 요일을 검색

SQL> select ename, job, hiredate, to_char(hiredate,'YY/MM/DD') 입사일, to_char(hiredate, 'DAY') as 요일 from emp;

 

18.  현재까지의 근무일수가 몇 주 몇 일인가 검색하라. 근무일수가 많은 사람순서로 검색하라.

(예:총 근무일수가 20일이면 2주 6일이 검색되게 한다.)

SQL> select ename, hiredate, trunc(months_between(sysdate, hiredate)/7) "몇주", floor(mod(months_between(sysdate, hiredate),0)) "몇일" from emp;

 

19.  현재까지의 근무 월수를 계산하여 정수로 검색하라.

SQL> select ename, hiredate, trunc(months_between(sysdate, hiredate)/12,0)||'개월' from emp;

 

20.  입사일로부터 5개월이 지난 후 날짜를 ROUND 함수와 TRUNC함수를 이용하여 월을 기준으로 검색하라

SQL> select ename, hiredate, round(trunc(add_months(hiredate,5)),'MONTH') "경과월" from emp;

 

21.  입사일자로부터 돌아오는 금요일을 검색하라

SQL> select ename, hiredate, next_day(hiredate, '금요일'), to_char(hiredate, 'DAY') as 요일 from emp;

 

22.  입사한 달의 근무일수를 계산하여 검색하라, 단 토/일요일도 근무일수에 포함한다.

SQL> select ename, hiredate, last_day(hiredate)-hiredate "근무일수" from emp;

 

23.  입사날짜를 ‘1 Jan 1981’ 과 ‘1981년 01월 01일’의 형태로 검색하라.

SQL> select hiredate, to_char(hiredate, 'dd month yyyy', 'NLS_DATE_LANGUAGE=ENGLISH'), to_char(hiredate, 'YYYY"년" MM"월" DD"일') from emp;

 

24.  입사일이 February 20, 1981과 May 1, 1981 사이에 입사한 사원의 이름,업무,입사일을 검색.

SQL> select ename, job, hiredate from emp where hiredate >='81/02/20' and hiredate<='81/05/01';

 

25.  위의 문제를 현재 세션을 February 20, 1981에 맞게 변경한 후에 검색하라.

SQL> select ename, job, to_char(hiredate,'month dd, yyyy','NLS_DATE_LANGUAGE=ENGLISH') from emp

where hiredate >= '81/02/20' and hiredate <= '81/05/01';

 

26.  연봉에 보너스를 합한 금액을 $를 삽입하고 3자리마다 ,를 검색하라.

SQL> select sal, comm, nvl(to_char(sal, '$999,999,999'), sal+comm) from emp;

 

27.  JOB이 ANALYST 이면 수당으로 급여의 10%를 지급하고 CLERK 이면 급여의 15% 지급, MANAGER이면 20% 지급하려고 한다. 다른 업무는 보너스가 없다. 사원번호,이름,업무,급여,수당을 검색하라. (CASE문)

SQL> select empno, ename, job, sal, comm, case job when 'ANALYST' then sal*0.1 when 'CLERK' then sal*0.15 when 'MANAGER' then sal*0.2 end from emp;

 

28.  위 문제를 DECODE를 사용하여 검색하라.

SQL> select empno, ename, job, sal, comm, decode(job, 'ANALYST', sal*1.1, 'CLERK', 1, sal*1.15, 'MANAGER', sal*1.2, sal) 보너스 from emp;

 

29.  급여가 1000 이상 2000 이하이면 1500을 지급하고 그 외에는 800을 지급하라

SQL> select ename, sal, case when sal>=1000 then 1500 when sal<=2000 then 1500 else 800 end from emp;

 

30.  현재 급여를 기준으로 입사한 달의 근무일수에 해당하는 급여를 산출하라.(일일 급여액 = 연 급여액/365)
SQL> select ename, hiredate, last_day(hiredate), last_day(hiredate)-hiredate "근무일수", ((sal*12)/365)*(last_day(hiredate)-hiredate) "급여" from emp;

 

 

(지각조) 이수정, 장경희, 김광섭

31.  이름,입사일,입사일에서 6개월 뒤에 돌아오는 월요일을 구하여 검색

32.  사원번호, 이름, 급여, 상사코드를 입력하고 상사코드가 NULL 이면 NO MGR을 검색하라.

33.  사원번호, 이름, 급여, 보너스,급여와 보너스의 합을 검색하고 컬럼명은 INCOME으로 검색을 한다. 보너스가 NULL이면 급여만, NULL이 아니면 보너스와 급여의 합이 검색 되도록 하라. (NVL을 이용)

34.  사원번호, 이름, 이름의 길이, 급여의 길이, 보너스 길이를 검색하고 급여는 보너스의 길이와 급여의 길이가 같으면 NULL을, 다르면 급여의 길이가 검색되게 하라.

35.  다음과 같은 형태로 모든 사원 검색

Dream Salary
------------------------------------------------
SMITH salary:   $800.00  Dream Salary : $2,400
ALLEN salary: $1,600.00  Dream Salary : $4,800
WARD salary: $1,250.00  Dream Salary : $3,750