select * from nls_session_parameters
set linesize 120
col parameter format a50
col value format a50
1 * select * from nls_session_parameters
/
-------------------------------------
<내가 풀이한>
<단일행 함수 문제&답안>
*** 단일행 함수 과제 ( scott계정의 테이블로 할 것)
1. 이름의 길이가 6자 이상인 사원의 정보를 이름, 이름 글자수, 업무를 검색
select ename, length(ename) as name_length, job from emp
where length(ename)>=6;
2. SCOTT의 사원번호, 성명(소문자로), 담당업무(대문자로) 검색
select empno, lower(ename), upper(job) from emp;
3. DEPT 테이블에서 Loc 컬럼의 첫 글자만 대문자로 변환하여 검색
select initcap(loc) as Loc_cap from dept;
4. 사원번호,이름,업무,급여를 검색하되 EMPNO와 ENAME을 줄 바꿔서 검색
select ename, empno, job, sal from emp;
5. sql에서 이름의 첫 글자가 ‘K’보다 크고 ‘Y’보다 작은 사원의 정보 검색
select ename from emp
where substr(ename, 1, 1)>'K' and substr(ename, 1, 1)<'Y';
6. 사원번호,이름,이름의 길이,급여,급여의 길이 검색
select empno, ename, length(ename) as name_length, sal, length(sal) as sal_length from emp;
7. 업무 중 sql에서 job column의 ‘A’자의 위치를 검색, 두 번째 ‘A’자의 위치도 검색
select job,
instr(job, 'A', 1) "first_location",
instr(job, 'A', 1, 2) "second_location" from emp;
8. 이름의 검색 자릿수를 20으로 하고 오른쪽 빈칸에 ‘*’을 채우고,
select rpad(ename, '20' , '*') "ename"
from emp;
9. 담당 업무 중 좌측에 ‘A’를 삭제하고 급여 중 좌측의 1을 삭제한 후 검색
select ltrim(job, 'A') "job_modi", ltrim(sal, '1') "sal_modi" from emp;
10. 담당 업무 중 우측에 ‘T’를 삭제하고 급여 중 우측의 0을 삭제한 후 검색
select rtrim(job, 'T') "job_modi", rtrim(sal, '0') "sal_modi" from emp;
11. 이름 중에 ‘A’,’B’,’C’는 소문자로 바꿔서 검색하고 급여를 숫자가 아닌 글자로 검색 (급여가 1425이면 일사이오)
select replace(replace(replace(ename,'A','a'),'B','b'),'C','c') name_lower,
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(sal,1,'일'),2,'이'),3,'삼'),4,'사'),5,'오'),6,'육'),7,'칠'),8,'팔'),9,'구'),0,'공') sal_letter
from emp;
12. JOB의 글자 중 ‘LE’를 ‘AL’로 바꿔서 검색
select replace(JOB, 'LE', 'AL') from emp;
13. 모든 사원의 이름과 급여를 연결(||)하여 검색하되 각 15자리씩 왼쪽을 공백으로 검색
SMITH 800
ALLEN 1600
select lpad(ename, 15) || lpad(sal, 15) 공백연결 from emp;
14. 숫자 1234.5678를 정수로 반올림하고 소수 첫째 자리까지 반올림과 절삭한 값을 검색
select
round(1234.5678, 0) 정수반올림,
round(1234.5678, 1) 소수첫째반올림,
trunc(1234.5678, 1) 소수첫째절삭
from dual;
15. 숫자 -456.789를 정수로 올림하고 -123.78을 내림한 정수를 검색
select
ceil(-456.789),
floor(-123.78)
from dual;
16. 급여를 30으로 나눈 나머지를 구하여 검색
select sal, mod(sal, 30) 나머지
from emp;
17. 모든 사원의 정보를 이름,업무,입사일, 입사한 요일을 검색
select ename, job, hiredate, to_char(hiredate, 'DY') 요일 from emp;
18. 현재까지의 근무일수가 몇 주 몇 일인가 검색하라. 근무일수가 많은 사람순서로 검색하라.
(예:총 근무일수가 20일이면 2주 6일이 검색되게 한다.)
select
ename,
trunc((sysdate-hiredate)/7) as weeks,
trunc(mod((sysdate-hiredate), 7)) as days
from emp
order by weeks desc, days desc;
19. 현재까지의 근무 월수를 계산하여 정수로 검색하라.
select
ename,
trunc(months_between(sysdate, hiredate)) 근무월수
from emp;
20. 입사일로부터 5개월이 지난 후 날짜를 ROUND함수와 TRUNC함수를 이용하여 월을 기준으로 검색하라
select
ename, hiredate,
round(add_months(hiredate, 5), 'month') as five_months_later_r,
trunc(add_months(hiredate, 5), 'month') as five_months_later_t
from emp;
21. 입사일자로부터 돌아오는 금요일을 검색하라
select ename, hiredate, next_day(hiredate, '금') next_friday from emp;
22. 입사한 달의 근무일수를 계산하여 검색하라, 단 토/일요일도 근무일수에 포함한다.
select hiredate, trunc(add_months(hiredate, 1), 'month')-hiredate working_day from emp;
23. 입사날짜를 ‘1 Jan 1981’ 과 ‘1981년 01월 01일’의 형태로 검색하라.
select ename, hiredate,
to_char(hiredate, 'dd Mon yyyy', 'NLS_DATE_LANGUAGE=ENGLISH') hd1,
to_char(hiredate, 'yyyy"년" mm"월" dd"일"') as hd2
from emp;
24. 입사일이 February 20, 1981과 May 1, 1981 사이에 입사한 사원의 이름,업무,입사일을 검색.
select ename, job, hiredate from emp
where hiredate>'1981/02/20' and hiredate<'1981/03/01';
25. 위의 문제를 현재 세션을 February 20, 1981에 맞게 변경한 후에 검색하라.
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자리마다 ,를 검색하라.
select ename, sal, nvl(comm,0)"comm", to_char(sal+nvl(comm,0),'fm$9,990') total from emp;
27. JOB이 ANALYST 이면 수당으로 급여의 10%를 지급하고
CLERK 이면 급여의 15% 지급, MANAGER이면 20% 지급하려고 한다.
다른 업무는 보너스가 없다.
사원번호,이름,업무,급여,수당을 검색하라. (CASE문)
select empno, ename, job, sal,
(case job
when 'ANALYST' then sal*0.1
when 'CLERK' then sal*0.15
when 'MANAGER' then sal*0.2
else 0
end) "bonus"
from emp;
28. 위 문제를 DECODE를 사용하여 검색하라.
select empno, ename, job, sal,
decode(job, 'ANALYST', sal*0.1, 'CLERK', sal*0.15, 'MANAGER', sal*0.2, 0) bonus
from emp;
29. 급여가 1000 이상 2000 이하이면 1500을 지급하고 그 외에는 800을 지급하라
select ename, sal,
(case
when sal>=1000 and sal<=2000 then 1500
else 800
end) bonus
from emp;
30. 현재 급여를 기준으로 입사한 달의 근무일수에 해당하는 급여를 산출하라.
(일일 급여액 = 연 급여액/365)
select ename, hiredate,
last_day(hiredate),
(last_day(hiredate)-hiredate+1) as workingday,
(sal/365)*(last_day(hiredate)-hiredate+1) as sudang
from emp;
31. 이름,입사일,입사일에서 6개월 뒤에 돌아오는 월요일을 구하여 검색
select ename, hiredate,
next_day(add_months(hiredate, 6), '월') "next_mon"
from emp;
32. 사원번호, 이름, 급여, 상사코드를 입력하고 상사코드가 NULL 이면 NO MGR을 검색하라.
select empno, ename, sal, nvl(to_char(mgr),'NO MGR') mgr from emp;
33. 사원번호, 이름, 급여, 보너스,급여와 보너스의 합을 검색하고 컬럼명은 INCOME으로 검색을 한다.
보너스가 NULL이면 급여만, NULL이 아니면 보너스와 급여의 합이 검색 되도록 하라. (NVL을 이용)
select empno, ename, sal, nvl(comm,0) "COMM", to_char(sal+nvl(comm,0)) INCOME
from emp;
34. 사원번호, 이름, 이름의 길이, 급여의 길이, 보너스 길이를 검색하고
급여는 보너스의 길이와 급여의 길이가 같으면 NULL을,
다르면 급여의 길이가 검색되게 하라.
select empno, ename, length(ename) "name_length", length(sal) "sal_length", length(comm) "bonus_length",
(case
when length(comm)=length(sal) then NULL
else length(sal)
end) sal_length
from emp;
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
……
select ename || ' salary: ' || to_char(sal, '$99,999.00') || ' Dream Salary : ' || to_char(sal*3, '$99,999') "Dream Salary" from emp;
-------------------------------------
<선생님이 정리해주신>
<단일행 함수 문제&답안>
1. 이름의 길이가 6자 이상인 사원의 정보를 이름, 이름 글자수, 업무를 검색
Select ename, length(ename), job from emp
Where length(ename) >= 6;
2. SCOTT의 사원번호, 성명(소문자로), 담당업무(대문자로) 검색
select empno, lower(ename), upper(job) from emp
where ename = 'SCOTT';
3. DEPT 테이블에서 Loc 컬럼의 첫 글자만 대문자로 변환하여 검색
select deptno, dname, initcap(loc) loc from dept;
4. 사원번호,이름,업무,급여를 검색하되 EMPNO와 ENAME을 줄 바꿔서 검색
select empno ename, ename empno, sal from emp;
5. 이름의 첫 글자가 ‘K’보다 크고 ‘Y’보다 작은 사원의 정보 검색
select * from emp
where substr(ename, 1, 1) >'K' and substr(ename, 1, 1) <'Y';
6. 사원번호,이름,이름의 길이,급여,급여의 길이 검색
Select empno, ename, length(ename) n_length, sal, length(sal) s_length from emp;
7. 업무 중 ‘A’자의 위치를 검색, 두 번째 ‘A’자의 위치도 검색
Select ename, job, instr(job,'A',1,1) first_A, instr(job,'A',1,2) second_A from emp;
8. 이름의 검색 자릿수를 20으로 하고 오른쪽 빈칸에 ‘*’을 채우고,
select rpad(ename, 20, '*') ename from emp;
9. 담당 업무 중 좌측에 ‘A’를 삭제하고 급여 중 좌측의 1을 삭제한 후 검색
Select ltrim(job, 'A') job, ltrim(sal, '1') sal from emp;
10.담당 업무 중 우측에 ‘T’를 삭제하고 급여 중 우측의 0을 삭제한 후 검색
Select rtrim(job, 'T') job, ltrim(sal, '0') sal from emp;
11. 이름 중에 ‘A’,’B’,’C’는 소문자로 바꿔서 검색하고 급여를 숫자가 아닌 글자로 검색
(급여가 1425 이면 일사이오 )
select replace(replace(ename, 'A', 'a'), 'B', 'b') "A,B 소문자로",
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’로 바꿔서 검색
select replace(job,'LE','AL') from emp;
13. 모든 사원의 이름과 급여를 연결(||)하여 검색하되 각 15자리씩 왼쪽을 공백으로 검색
SMITH 800
ALLEN 1600
select lpad(ename || sal,15,'*') "이름_급여" from emp;
14. 숫자 1234.5678를 정수로 반올림하고 소수 첫째 자리까지 반올림과 절삭한 값을 검색
select round(1234.5678,0), round(1234.5678,1), trunc(1234.5678,1) from dual;
15. 숫자 -456.789를 정수로 올림하고 -123.78을 내림한 정수를 검색
select ceil(-456.789), floor(-123.78) from dual;
16. 급여를 30으로 나눈 나머지를 구하여 검색
select mod(sal, 30) from emp;
17. 모든 사원의 정보를 이름,업무,입사일, 입사한 요일을 검색
select ename, job, hiredate, to_char(hiredate, 'day') from emp;
18. 현재까지의 근무일수가 몇 주 몇 일인가 검색하라. 근무일수가 많은 사람순서로 검색하라.
(예:총 근무일수가 20일이면 2주 6일이 검색되게 한다.)
select
trunc((to_char(hiredate,'dd')/12)*365/7,'0') || '주 ' ||
trunc(mod((to_char(hiredate,'dd')/12)*365,7),'0') || '일' "근무일수"
from emp;
19. 현재까지의 근무 월수를 계산하여 정수로 검색하라.
select trunc(months_between(sysdate, hiredate)) "근무개월수" from emp;
20. 입사일로부터 5개월이 지난 후 날짜를 ROUND 함수와 TRUNC함수를 이용하여 월을 기준으로 검색하라
select hiredate, round(add_months(hiredate,'5'),'month'), trunc((add_months(hiredate,'5')),'month') from emp;
21. 입사일자로부터 돌아오는 금요일을 검색하라
select next_day(hiredate,'금'), to_char(next_day(hiredate,'금'),'day') from emp;
22. 입사한 달의 근무일수를 계산하여 검색하라, 단 토/일요일도 근무일수에 포함한다.
select to_char(last_day(hiredate), 'dd') - to_char(hiredate, 'dd') +1 "근무일수" from emp;
23. 입사날짜를 ‘1 Jan 1981’ 과 ‘1981년 01월 01일’의 형태로 검색하라.
select
to_char(hiredate, 'fm dd Mon yyyy', 'nls_date_language = english') "영문날짜",
to_char(hiredate, 'yyyy"년" mm"월" dd"일"') "한글날짜"
from emp;
24. 입사일이 February 20, 1981과 May 1, 1981 사이에 입사한 사원의 이름,업무,입사일을 검색.
select ename, job, hiredate from emp
where hiredate>'1981.02.20' and hiredate<'1981.03.01';
25. 위의 문제를 현재 세션을 February 20, 1981에 맞게 변경한 후에 검색하라.
select ename, job, to_char(hiredate, 'Mon dd, yyyy', 'nls_date_language=english') "입사일" from emp
where hiredate>'1981.02.20' and hiredate<'1981.03.01';
26. 연봉에 보너스를 합한 금액을 $를 삽입하고 3자리마다 ,를 검색하라.
select ename, lpad((to_char(sal+comm, '9,990')||'$'),'5','') "개인 총급여" from emp;
27. JOB이 ANALYST 이면 수당으로 급여의 10%를 지급하고 CLERK 이면 급여의 15% 지급, MANAGER이면 20% 지급하려고 한다. 다른 업무는 보너스가 없다. 사원번호,이름,업무,급여,수당을 검색하라. (CASE문)
select empno, ename, job, sal,
(case job
when 'ANALYST' then sal*0.1
when 'CLERK' then sal*0.15
when 'MANAGER' then sal*0.2 else 0 end) "수당"
from emp;
28. 위 문제를 DECODE를 사용하여 검색하라.
select empno, ename, job, sal,
decode(job,'ANALYST',sal*0.1,'CLERK',sal*0.15,'MANAGER',sal*0.2,'0') "수당"
from emp;
29. 급여가 1000 이상 2000 이하이면 1500을 지급하고 그 외에는 800을 지급하라
select empno, ename, job, sal,
(case when sal>=1000 and sal<=2000 then 1500 else 800 end) "수당"
from emp;
30. 현재 급여를 기준으로 입사한 달의 근무일수에 해당하는 급여를 산출하라.
(일일 급여액 = 연 급여액/365)
select ename, sal, hiredate,
(to_char(last_day(hiredate), 'dd') - to_char(hiredate, 'dd') +1)*sal/365 "첫달급여"
from emp;
31. 이름,입사일,입사일에서 6개월 뒤에 돌아오는 월요일을 구하여 검색
select ename, hiredate,
next_day(add_months(hiredate,'6'),'월') "d_nextmdy"
from emp;
32. 사원번호, 이름, 급여, 상사코드를 입력하고 상사코드가 NULL 이면 NO MGR을 검색하라.
select empno, ename, sal, nvl(to_char(mgr),'NO MGR') mgr from emp;
33. 사원번호, 이름, 급여, 보너스,급여와 보너스의 합을 검색하고 컬럼명은 INCOME으로 검색을 한다. 보너스가 NULL이면 급여만, NULL이 아니면 보너스와 급여의 합이 검색 되도록 하라. (NVL을 이용)
select empno, ename, sal, nvl(comm,'0'), sal+nvl(comm,'0') "INCOME" from emp;
34. 사원번호, 이름, 이름의 길이, 급여의 길이, 보너스 길이를 검색하고 급여는 보너스의 길이와 급여의 길이가 같으면 NULL을, 다르면 급여의 길이가 검색되게 하라.
select empno, ename,
length(ename) "이름길이", length(sal) "급여길이", length(comm) "보너스길이",
(case
when length(sal)!=length(comm) then length(sal) else null end) "조건부급여길이"
from emp;
select empno, ename,
length(ename) "이름길이", length(sal) "급여길이", length(comm) "보너스길이",
decode(length(sal), length(comm), null, length(sal)) "조건부급여길이"
from emp;
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 | ||||
| …… | ||||
select ename || ' salary:'||to_char(sal, '$9,990.99')||' Dream Salary :'||to_char(sal*3, '$9,990') "Dream Salary" from emp;
-----------------------------------------
1. 이름의 길이가 6자 이상인 사원의 정보를 이름, 이름 글자수, 업무를 검색
```sql
select
ename, length(ename) "이름 글자수", job
from emp
where length(ename) >= 6;
ENAME 이름 글자수 JOB
---------- ----------- ---------
MARTIN 6 SALESMAN
TURNER 6 SALESMAN
MILLER 6 CLERK
```
2. SCOTT의 사원번호, 성명(소문자로), 담당업무(대문자로) 검색
```sql
select
empno, lower(ename) "성명", upper(job) "담당업무"
from emp
where ename = 'SCOTT';
EMPNO 성명 담당업무
---------- ---------- ---------
7788 scott ANALYST
```
3. DEPT 테이블에서 Loc 컬럼의 첫 글자만 대문자로 변환하여 검색
```sql
select
initcap(loc) "LOC"
from dept;
LOC
-------------
New York
Dallas
Chicago
Boston
```
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';
ENAME
----------
SMITH
WARD
MARTIN
SCOTT
TURNER
MILLER
6 개의 행이 선택되었습니다.
```
6. 사원번호,이름,이름의 길이,급여,급여의 길이 검색
```sql
select
empno,
ename,
length(ename) "이름의 길이",
sal,
length(sal) "급여의 길이"
from emp;
EMPNO ENAME 이름의 길이 SAL 급여의 길이
---------- ---------- ----------- ---------- -----------
101 홍동우 3
7369 SMITH 5 800 3
7499 ALLEN 5 1600 4
7521 WARD 4 1250 4
7566 JONES 5 2975 4
7654 MARTIN 6 1250 4
7698 BLAKE 5 2850 4
7782 CLARK 5 2450 4
7788 SCOTT 5 3000 4
7839 KING 4 5000 4
7844 TURNER 6 1500 4
```
7. 업무 중 ‘A’자의 위치를 검색, 두 번째 ‘A’자의 위치도 검색
```sql
select
distinct(job),
instr(job, 'A', 1, 1) "첫 번째 A",
instr(job, 'A', 1, 2) "두 번째 A"
from emp;
JOB 첫 번째 A 두 번째 A
--------- ---------- ----------
PRESIDENT 0 0
CLERK 0 0
ANALYST 1 3
MANAGER 2 4
SALESMAN 2 7
```
8. 이름의 검색 자릿수를 20으로 하고 오른쪽 빈칸에 ‘*’을 채우고,
```sql
select
rpad(ename, 20, '*')
from emp;
RPAD(ENAME,20,'*')
----------------------
홍동우**************
SMITH***************
ALLEN***************
WARD****************
JONES***************
MARTIN**************
BLAKE***************
CLARK***************
SCOTT***************
KING****************
TURNER**************
```
9. 담당 업무 중 좌측에 ‘A’를 삭제하고 급여 중 좌측의 1을 삭제한 후 검색
```sql
select
ltrim(job, 'A') "job",
ltrim(sal, 1) "sal"
from emp;
job sal
--------- ----------------------------------------
CLERK 800
SALESMAN 600
SALESMAN 250
MANAGER 2975
SALESMAN 250
```
10. 담당 업무 중 우측에 ‘T’를 삭제하고 급여 중 우측의 0을 삭제한 후 검색
```sql
select
rtrim(job, 'T') "job",
rtrim(sal, 0) "sal"
from emp;
job sal
--------- ----------------------------------------
CLERK 8
SALESMAN 16
SALESMAN 125
MANAGER 2975
SALESMAN 125
```
11. 이름 중에 ‘A’,’B’,’C’는 소문자로 바꿔서 검색하고 급여를 숫자가 아닌 글자로 검색 (급여가 1425 이면 일사이오 )
```sql
select
translate(ename, 'ABC', 'abc') "이름",
translate(to_char(sal), '0123456789', '영일이삼사오육칠팔구') "급여",
from emp;
select
translate(ename, 'ABC', 'abc') "이름",
translate(sal, 1234567890, '일이삼사오육칠팔구영') "급여"
from emp;
이름 급여
-------------------- --------------------------------------------------------------------------------
홍동우
SMITH 팔영영
aLLEN 일육영영
WaRD 일이오영
JONES 이구칠오
MaRTIN 일이오영
bLaKE 이팔오영
```
12. JOB의 글자 중 ‘LE’를 ‘AL’로 바꿔서 검색
```sql
select
replace(job, 'LE', 'AL') "JOB"
from emp;
JOB
------------------
CALRK
SAALSMAN
SAALSMAN
MANAGER
SAALSMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SAALSMAN
```
13. 모든 사원의 이름과 급여를 연결(||)하여 검색하되 각 15자리씩 왼쪽을 공백으로 검색
SMITH 800
ALLEN 1600
```sql
select
lpad(ename, 15, ' ') || lpad(sal, 15, ' ') "이름+급여"
from emp;
이름+급여
----------------------------------------
홍동우
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
```
14. 숫자 1234.5678를 정수로 반올림하고 소수 첫째 자리까지 반올림과 절삭한 값을 검색
```sql
select
round(1234.5678, 0) "round 0",
round(1234.5678, 1) "round 1",
trunc(1234.5678, 1) "trunc"
from dual
round 0 round 1 trunc
---------- ---------- ----------
1235 1234.6 1234.5
```
15. 숫자 -456.789를 정수로 올림하고 -123.78을 내림한 정수를 검색
```sql
select
ceil(-456.789) "ceil",
floor(-123.78) "floor"
from dual;
ceil floor
---------- ----------
-456 -124
```
16. 급여를 30으로 나눈 나머지를 구하여 검색
```sql
select
mod(sal, 30) "mod"
from emp;
mod
----------
20
10
20
5
20
```
17. 모든 사원의 정보를 이름,업무,입사일, 입사한 요일을 검색
```sql
select
ename,
job,
hiredate,
to_char(hiredate, 'day') "입사요일"
from emp;
ENAME JOB HIREDATE 입사요일
---------- --------- -------- ---------
홍동우
SMITH CLERK 80/12/17 수요일
ALLEN SALESMAN 81/02/20 금요일
WARD SALESMAN 81/02/22 일요일
JONES MANAGER 81/04/02 목요일
MARTIN SALESMAN 81/09/28 월요일
```
18. 현재까지의 근무일수가 몇 주 몇 일인가 검색하라. 근무일수가 많은 사람순서로 검색하라.
(예:총 근무일수가 20일이면 2주 6일이 검색되게 한다.)
```sql
select
ename,
floor((sysdate - hiredate) / 7)||'주 '||
floor(mod((sysdate - hiredate), 7))||'일'
"근무일수"
from emp
where hiredate is not null
order by "근무일수" desc;
ENAME 근무일수
---------- -------------------------------------------------------------------------------------
SMITH 2170주 0일
ALLEN 2160주 5일
WARD 2160주 3일
JONES 2154주 6일
BLAKE 2150주 5일
CLARK 2145주 1일
```
19. 현재까지의 근무 월수를 계산하여 정수로 검색하라.
```sql
select
ename,
floor(months_between(sysdate, hiredate)) "근무 월수"
from emp
where hiredate is not null;
ENAME 근무 월수
---------- ----------
SMITH 499
ALLEN 497
WARD 496
JONES 495
MARTIN 489
BLAKE 494
```
20. 입사일로부터 5개월이 지난 후 날짜를 ROUND 함수와 TRUNC함수를 이용하여 월을 기준으로 검색하라
```sql
select
ename,
round(add_months(hiredate, 5), 'month')
from emp;
```
21. 입사일자로부터 돌아오는 금요일을 검색하라
```sql
select
ename,
hiredate,
next_day(hiredate, '금')
from emp;
ENAME HIREDATE NEXT_DAY
---------- -------- --------
SMITH 80/12/17 80/12/19
ALLEN 81/02/20 81/02/27
WARD 81/02/22 81/02/27
JONES 81/04/02 81/04/03
MARTIN 81/09/28 81/10/02
BLAKE 81/05/01 81/05/08
CLARK 81/06/09 81/06/12
SCOTT 87/04/19 87/04/24
KING 81/11/17 81/11/20
TURNER 81/09/08 81/09/11
```
22. 입사한 달의 근무일수를 계산하여 검색하라, 단 토/일요일도 근무일수에 포함한다.
```sql
select
hiredate,
last_day(hiredate) - hiredate + 1 "입사월근무일수"
from emp;
HIREDATE 입사월근무일수
-------- --------------
80/12/17 15
81/02/20 9
81/02/22 7
81/04/02 29
81/09/28 3
81/05/01 31
81/06/09 22
87/04/19 12
81/11/17 14
81/09/08 23
```
23. 입사날짜를 ‘1 Jan 1981’ 과 ‘1981년 01월 01일’의 형태로 검색하라.
```sql
select
to_char(hiredate, 'fmdd mon yyyy') "입사일 1",
to_char(hiredate, 'yyyy"년" mm"월" dd"일"') "입사일 2"
from emp;
```
24. 입사일이 February 20, 1981과 May 1, 1981 사이에 입사한 사원의 이름,업무,입사일을 검색.
```sql
select
ename,
job,
hiredate
from emp
where hiredate >= to_date('81/02/20') and hiredate < to_date('81/05/02');
ENAME JOB HIREDATE
---------- --------- --------
ALLEN SALESMAN 81/02/20
WARD SALESMAN 81/02/22
JONES MANAGER 81/04/02
BLAKE MANAGER 81/05/01
```
25. 위의 문제를 현재 세션을 February 20, 1981에 맞게 변경한 후에 검색하라.
```sql
ALTER SESSION SET NLS_DATE_FORMAT = 'month dd, yyyy';
ALTER SESSION SET NLS_DATE_LANGUAGE = 'ENGLISH';
select
ename,
job,
hiredate
from emp
where hiredate >= to_date('February 20, 1981') and hiredate < to_date('May 02, 1981');
ENAME JOB HIREDATE
---------- --------- ----------------------
ALLEN SALESMAN february 20, 1981
WARD SALESMAN february 22, 1981
JONES MANAGER april 02, 1981
BLAKE MANAGER may 01, 1981
```
26. 연봉에 보너스를 합한 금액을 $를 삽입하고 3자리마다 ,를 검색하라.
```sql
select
ename,
sal,
to_char((sal + nvl(comm, 0)), '$9,999,990') "연봉+보너스"
from emp;
ENAME SAL 연봉+보너스
---------- ---------- -----------
SMITH 800 $800
ALLEN 1600 $1,900
WARD 1250 $1,750
JONES 2975 $2,975
MARTIN 1250 $2,650
BLAKE 2850 $2,850
```
27. JOB이 ANALYST 이면 수당으로 급여의 10%를 지급하고 CLERK 이면 급여의 15% 지급, MANAGER이면 20% 지급하려고 한다. 다른 업무는 보너스가 없다. 사원번호,이름,업무,급여,수당을 검색하라. (CASE문)
```sql
select
empno,
ename,
job,
sal,
case job when 'ANALYST' then sal*0.1
when 'CLERK' then sal*0.15
when 'MANAGER' then sal*0.2
else 0 end
"수당"
from emp;
EMPNO ENAME JOB SAL 수당
---------- ---------- --------- ---------- ----------
101 홍동우 0
7369 SMITH CLERK 800 120
7499 ALLEN SALESMAN 1600 0
7521 WARD SALESMAN 1250 0
7566 JONES MANAGER 2975 595
7654 MARTIN SALESMAN 1250 0
7698 BLAKE MANAGER 2850 570
7782 CLARK MANAGER 2450 490
7788 SCOTT ANALYST 3000 300
7839 KING PRESIDENT 5000 0
7844 TURNER SALESMAN 1500 0
EMPNO ENAME JOB SAL 수당
---------- ---------- --------- ---------- ----------
7876 ADAMS CLERK 1100 165
7900 JAMES CLERK 950 142.5
7902 FORD ANALYST 3000 300
7934 MILLER CLERK 1300 195
```
28. 위 문제를 DECODE를 사용하여 검색하라.
```sql
select
empno,
ename,
job,
sal,
decode(job, 'ANALYST', sal*0.1, 'CLERK', sal*0.15, 'MANAGER', sal*0.2, 0) "수당"
from emp;
EMPNO ENAME JOB SAL 수당
---------- ---------- --------- ---------- ----------
101 홍동우 0
7369 SMITH CLERK 800 120
7499 ALLEN SALESMAN 1600 0
7521 WARD SALESMAN 1250 0
7566 JONES MANAGER 2975 595
7654 MARTIN SALESMAN 1250 0
7698 BLAKE MANAGER 2850 570
7782 CLARK MANAGER 2450 490
7788 SCOTT ANALYST 3000 300
7839 KING PRESIDENT 5000 0
7844 TURNER SALESMAN 1500 0
EMPNO ENAME JOB SAL 수당
---------- ---------- --------- ---------- ----------
7876 ADAMS CLERK 1100 165
7900 JAMES CLERK 950 142.5
7902 FORD ANALYST 3000 300
7934 MILLER CLERK 1300 195
```
29. 급여가 1000 이상 2000 이하이면 1500을 지급하고 그 외에는 800을 지급하라
```sql
select
ename,
sal,
case when sal >= 1000 and 2000 <= 2000 then 1500
else 800 end
"보너스"
from emp;
ENAME SAL 보너스
---------- ---------- ----------
홍동우 800
SMITH 800 800
ALLEN 1600 1500
WARD 1250 1500
JONES 2975 1500
MARTIN 1250 1500
BLAKE 2850 1500
CLARK 2450 1500
SCOTT 3000 1500
KING 5000 1500
TURNER 1500 1500
ENAME SAL 보너스
---------- ---------- ----------
ADAMS 1100 1500
JAMES 950 800
FORD 3000 1500
MILLER 1300 1500
```
30. 현재 급여를 기준으로 입사한 달의 근무일수에 해당하는 급여를 산출하라.
(일일 급여액 = 연 급여액/365)
```sql
select
ename,
floor((last_day(hiredate) - hiredate + 1) * (sal/365)) "입사월 급여"
from emp;
ENAME 입사월 급여
---------- -----------
SMITH 32
ALLEN 39
WARD 23
JONES 236
MARTIN 10
BLAKE 242
CLARK 147
SCOTT 98
KING 191
TURNER 94
```
31. 이름,입사일,입사일에서 6개월 뒤에 돌아오는 월요일을 구하여 검색
```sql
select
ename,
hiredate,
next_day(add_months(hiredate, 6), 'Monday') "6개월 뒤 월요일"
from emp;
ENAME HIREDATE 6개월 뒤 월요일
---------- ---------------------- ----------------------
홍동우
SMITH december 17, 1980 june 22, 1981
ALLEN february 20, 1981 august 24, 1981
WARD february 22, 1981 august 24, 1981
JONES april 02, 1981 october 05, 1981
MARTIN september 28, 1981 march 29, 1982
BLAKE may 01, 1981 november 02, 1981
CLARK june 09, 1981 december 14, 1981
SCOTT april 19, 1987 october 26, 1987
KING november 17, 1981 may 24, 1982
TURNER september 08, 1981 march 15, 1982
```
32. 사원번호, 이름, 급여, 상사코드를 입력하고 상사코드가 NULL 이면 NO MGR을 검색하라.
```sql
select
empno,
ename,
sal,
nvl(to_char(MGR), 'NO MGR') "MGR"
from emp;
EMPNO ENAME SAL MGR
---------- ---------- ---------- ----------------------------------------
101 홍동우 NO MGR
7369 SMITH 800 7902
7499 ALLEN 1600 7698
7521 WARD 1250 7698
7566 JONES 2975 7839
7654 MARTIN 1250 7698
7698 BLAKE 2850 7839
7782 CLARK 2450 7839
7788 SCOTT 3000 7566
7839 KING 5000 NO MGR
```
33. 사원번호, 이름, 급여, 보너스,급여와 보너스의 합을 검색하고 컬럼명은 INCOME으로 검색을 한다. 보너스가 NULL이면 급여만, NULL이 아니면 보너스와 급여의 합이 검색 되도록 하라. (NVL을 이용)
```sql
select
empno,
ename,
sal,
comm,
nvl2(comm, sal + comm, sal) "INCOME"
from emp;
EMPNO ENAME SAL COMM INCOME
---------- ---------- ---------- ---------- ----------
101 홍동우
7369 SMITH 800 800
7499 ALLEN 1600 300 1900
7521 WARD 1250 500 1750
7566 JONES 2975 2975
7654 MARTIN 1250 1400 2650
7698 BLAKE 2850 2850
7782 CLARK 2450 2450
7788 SCOTT 3000 3000
7839 KING 5000 5000
7844 TURNER 1500 0 1500
```
34. 사원번호, 이름, 이름의 길이, 급여의 길이, 보너스 길이를 검색하고 급여는 보너스의 길이와 급여의 길이가 같으면 NULL을, 다르면 급여의 길이가 검색되게 하라.
```sql
select
empno,
ename,
length(ename),
length(sal),
length(comm),
case when length(sal) = length(comm) then null
else length(sal) end
"급여"
from emp;
EMPNO ENAME LENGTH(ENAME) LENGTH(SAL) LENGTH(COMM) 급여
---------- ---------- ------------- ----------- ------------ ----------
101 홍동우 3
7369 SMITH 5 3 3
7499 ALLEN 5 4 3 4
7521 WARD 4 4 3 4
7566 JONES 5 4 4
7654 MARTIN 6 4 4
7698 BLAKE 5 4 4
7782 CLARK 5 4 4
7788 SCOTT 5 4 4
7839 KING 4 4 4
7844 TURNER 6 4 1 4
EMPNO ENAME LENGTH(ENAME) LENGTH(SAL) LENGTH(COMM) 급여
---------- ---------- ------------- ----------- ------------ ----------
7876 ADAMS 5 4 4
7900 JAMES 5 3 3
7902 FORD 4 4 4
7934 MILLER 6 4 4
```
35. 다음과 같은 형태로 모든 사원 검색
```sql
select
rpad(ename, 6, ' ') ||
' salary: ' ||
lpad(to_char(sal, '$99,999.00'), 10, ' ') ||
'Dream Salary: ' ||
lpad(to_char(sal*3, '$99,999.00'), 10, ' ')
"Dream Salary"
from emp
where sal is not null;
Dream Salary
---------------------------------------------------------------------------
SMITH salary: $800.0Dream Salary: $2,400.0
ALLEN salary: $1,600.0Dream Salary: $4,800.0
WARD salary: $1,250.0Dream Salary: $3,750.0
JONES salary: $2,975.0Dream Salary: $8,925.0
MARTIN salary: $1,250.0Dream Salary: $3,750.0
BLAKE salary: $2,850.0Dream Salary: $8,550.0
CLARK salary: $2,450.0Dream Salary: $7,350.0
SCOTT salary: $3,000.0Dream Salary: $9,000.0
KING salary: $5,000.0Dream Salary: $15,000.0
TURNER salary: $1,500.0Dream Salary: $4,500.0
ADAMS salary: $1,100.0Dream Salary: $3,300.0
```
'DataBase' 카테고리의 다른 글
| <DataBase_231102목> (그룹 함수,조인) 문제&답안 (0) | 2023.11.02 |
|---|---|
| <DataBase_231101수> 다중행 함수 (0) | 2023.11.01 |
| <DataBase_231030월> 단일행 함수 (0) | 2023.10.30 |
| <DataBase_231027금> 테이블, DDL 제약조건 (2) | 2023.10.27 |
| <DataBase_231026목> 연산자 정렬, 연산자 과제&답안 (2) | 2023.10.26 |