3장 단일 행 함수를 사용하여 출력 커스터마이즈
SQL 기본2017. 7. 5. 19:48
*3장 목차
- 문자함수
- 숫자함수
- 날짜 함수
-
함수의 두가지 종류
-
단일행 함수
- 하나의 값이 입력이 되어서 하나의 값으로 출력
-
단일행 함수
-
함수의 두가지 종류
- 복수행 함수,
- 여러개의 값들이 입력되어서 하나의 값으로 출력
문 47. 서울에서 사는 학생들의 이름과 나이와 주로를 출력하는데 나이가 높은 순서로
\
문 49. 우리반에서 naver 메일을 사용하는 학생들의 이름과 나이와 이메일을 출력하시오 !
select ename, age, email from emp2 where email like '%naver%';
문 50 . 컴퓨터학과가 아닌 학생들의 이름과 전공을 출력하시오 !
select ename , major from emp2 where major not like '컴%';
문 51. 성이 김씨와 이씨인 학생들의 이름과 나이를 출력하시오
select ename, age from emp2 where ename like '김%' or ename like '이%';
3.1 문자함수
- 대소문자 변환함수: upper. lower, initcap
- 문자조작함수 : concat, instr, lpad, rpad, trim, length, replace
*upper 함수 대문자로 변환하는 함수
문제51. 우리반 테이블에서 이메일을 출력하는데 이메일을 대문자로 출력하시오
select upper(email)
from emp2;
문52. 이름이 scott 인 사원의 이름과 월급을 출력하는데 이름을 소문자로 입력해도 출력되게 하시오
select ename ,sal
FROM EMP
WHERE lower(ename) = 'scott';
- initcap 함수 : 첫버째 철자는 대문자로 출력하고
나머지 철자는 소문자로 출력하는 함수
select initcap(ename)
from emp;
- concat 함수 : 두개의 컬럼값을 연결하는 함수
select concat(ename,sal)
from emp;
select ename ||' '|| sal escape 'm'
from emp;
- substr 함수 (★★★★★★★★★)
"특정 철자열을 잘래내서 가져오는 함수"
select ename, substr(ename,0,5)
from emp;
문제53. 우리반 테이브레엇 이름의 성씨만 출력하시오
select substr(ename,1,1)
from emp2;
문제54.성이 김씨인 학생들의 이름을 출력하시오
select ename
from EMP2
WHERE substr(ename,1,1) = '김';
문55. 성씨가 김씨, 이씨 , 조씨 인 학생들의 이름을 출력하라
select ename
from EMP2
WHERE substr(ename,1,1) in ('김','이','조');
문56. 주소가 부산인 학생들의 이름과 주소를 출력하시오
(like 사용하지 말고 substr함수로 수행하라)
select ename
from EMP2
WHERE substr(address,1,2) ='부산';
문57. 통신사가 null인 학생들의 이름과 통신사를 출력하시오
select ename, telecom from EMP2 WHERE telecom IS null ;
문58. 이름과 통신사를 출력하는데 통신사를 ASCENDING 하게 정렬해서 출력하시오
SELECT ENAME , TELECOM FROM EMP2 ORDER BY TELECOM;
- instr 함수
"문자안에 특정 철자의 자릿수를 출력하는 함수"
select ename, instr(ename,'M')
from emp;
문59. 이메일, 이메일에서 @ 가 몇번째 자리에 있는지
출력하시오!
select email, instr(email,'@')
from emp2;
- trim 함수
" 특정 철자를 잘라내는 함수 "
문제60. 이름이 JACK인 사원의 이름과 월급을 출력하시오
SELECT ENAME, SAL FROM EMP WHERE TRIM(ENAME) = 'JACK';
설명: 양쪽에 공백을 잘라 버리겠다.
문제61. 우리반 테이블에서 EMAIL.을 출력하는데 이메일 끝에 .COM을 잘라내고 출력하시오
select trim(email,'.com')
from emp2;
문제62. 위의 결과에서 .net도 잘라내시오
SELECT RTRIM(RTRIM(RTRIM(email, 'com'), 'net'), '.')
FROM EMP2;
SELECT RTRIM(RTRIM(LOWER(email), 'abcdefghijklmnopqrstuvwxyz'), '.')
FROM EMP2;
SELECT ename, RTRIM(email,SUBSTR(email,instr(email,'.'),LENGTH(email))) FROM EMP2;
SELECT ename, RTRIM(email,SUBSTR(email,instr(email,'.'))) FROM EMP2;
SELECT RTRIM(email, SUBSTR(email, instr(email, '.'),4)) FROM EMP2;
select rtrim(rtrim(email,'comnet'),'.') from emp2;
SELECT substr(email,1,INSTR(email,'.')-1) FROM EMP2;
SELECT ename, RTRIM(rtrim(email,'.com'),'.net') FROM EMP2;
문제63. 위의 출력된 email 의 결과에서 @가 몇번째 자리에 잇는지 출력하시오
SELECT ename, RTRIM(email,SUBSTR(email,instr(email,'.'))) , instr( RTRIM(email,SUBSTR(email,instr(email,'.'))),'@' ) FROM EMP2;
문제64. (난이도 상 : ★★★★★★★) 우리반 이메일에서 도메인만 출력하시오
select substr(email,instr(email,'@')+1,instr(email,'.') - instr(email,'@')-1) from emp2;
*length 함수
철자의 갯수를 세는 함수
select ename, length(ename)
from emp;
문제 65. 우리반에서 email이 가장 긴 학생이 누구인지 출력하세요
select ename email, length (email)
from emp2
order by length(email) desc;
같은 표현
select ename email, length (email) as a
from emp2
order by a desc;
- replace 함수
"특정 철자를 다른 철자로 변경하는 함수"
문제 66. 이름과 월급을 출력하는데 월급을 출력할 때
0이 아니라 * 로 출력하세요 !
select ename, replace(sal,0,'*')
from emp;
문제 67. 이름과 월급을 출력하는데 월급을 출력할 때
0,1,2를 이 아니라 * 로 출력하세요 !( 맨 마지막 장에서 배우는 정규식 함수를
사용해야 한다)
select ename, sal , regexp_replace(sal,'[0-2]','*')
from emp;
문제 68. 이름과 핸드폰 번호를 출력하는데 핸드폰 번호가 암호화 되게 출력하시오
select ename, mobile, regexp_replace(mobile,'[6-9]','*')
from emp2;
int INSTR( string, substring [, start_position [, th_appearance ] ] ) |
substring과 일치하는 위치를start_position 부터 시작해서 th_appearance 번 검색해서위치를 숫자로 반환한다.
|
int instr( String[원본], Char[문자])
|
문자의 위치를 숫자로 알려준다.
|
String substr(Stirng[원본], int[에서],int[까지])
|
i번째부터 j번째까지 잘라온다.
|
String substr(Stirng, int) |
i번째 부터 끝까지 잘라온다.
|
String trim (String) |
공백제거 |
String rtrim(String,String) |
오른쪽 끝에서 두번째 파라미터에 포함된 문자열을 제거한다. 순서 상관 없음
|
String ltrim(String,String) |
왼쪽 끝에서 파라미터에 포함된 문자열을 모두 제거한다.
|
- 2.2 숫자함수
1. round
-> 반올림하는 함수
2. trunc
-> 반올림하지 않고 잘라내는 함수
3. mod
-> 나눈 나머지 값을 출력하는 함수
4.power
-> 제곱승을 구하는 함수
체스 : 10 의 50승
바둑: 10의 171승
select power(10,63) from dual;
문제69. 이름, 월급*350 출력하시오.
select ename, sal*350 from emp;
문제70. 아래의 JONES의 월급이 10의 자리에서 반올림 되도록 하시오.
select ename, sal*350, round(sal*350, -2) from emp;
1 2 3 4 . 1 2 3 4
-2 -1 0 1 2
문제71. 이름, 월급 * 0.235를 출력하는데 소숫점이하는 출력되지 않고 반올림 되게 하시오.
select ename, round(sal*0.235,0) from emp;
문제 72. 이름, 월급 * 0.235를 출력하는데 소숫점 이하는 출력되지 않게 하고 반올림하는게 아니라 소수점 이하를 버리시오
* MOD 함수
10을 3으로 나눈 나머지값이 무엇인가?
select mod (10,3) from dual;
↑
결과값을 보기 위한 가상의 테이블
- 2.3 날짜 함수
날짜 - 날짜 = 숫자
날짜 + 숫자 = 날짜
날짜 - 숫자 = 날짜
- 오늘 날짜가를 보는 키워드
select sysdate from dual;
문제 74. 이름, 입사한 날짜부터 오늘까지 총 몇일 근무했는지 출력하시오
SELECT ename, ROUND(SYSDATE - hiredate) FROM EMP;
문제 75 , 이름 , 입사한 날짜부터 오늘까지 총 몇달 근무했는지 출력하시오
- months_between 함수
: 날짜와 날짜 사이의 개월수를 출력하는 함수
select ename, months_between(sysdate, hiredate) from emp;
- add_month
: 날짜에 개월수를 더했을때의 날짜를 출력하는 함수
문제76. 오늘 날짜에서 6개월 후의 날짜를 출력하시오!
select sysdate, add_months(sysdate, 6) from dual;
문제77. 이름, 입사일, 입사일에서 200달 후의 날짜를 출력하시오
SELECT ename, hiredate ,add_months(hiredate, 200)FROM EMP;
*날짜함수
- month_between
- add_months
- next_day
: 지정된 날짜에서 앞으로 돌아올 요일의 날짜를 출력하는 함수
- last_day
:지정된 날짜의 달에 마지막 날짜를 출력
문제78. 오늘 부터 앞으로 돌아올 금요일의 날짜를 출력하시오
select next_day(sysdate, 'friday')
from daul;
문제79. (난이도 상) 오늘부터 100달뒤에 돌아오는 월요일의 날짜를 출력하시오
select next_day(add_months(sysdate,100),'monday') from dual;
** 분석함수
select deptno,
listagg(ename,',') ||'(' sal||')' within group ( order by ename)
from emp
group by deptno;
listagg('컬럼명', '구분자')
가로로 출력해라
문제80. 오늘날짜의 달에 마지막 날짜를 출력하시오 !
select last_day(sysdate)
from dual;
문제81. 오늘 부터 이번달 말일까지 총 몇일 남았는지 출력하시오
select last_day(sysdate) - sysdate
from dual;
문제82. (점심시간문제) 아래의 결과를 출력하시오
SELECT deptno,listagg(ename || '('|| sal || ')',',')
within group ( order by ename)
from emp
group by deptno;
-
■ 함수
- 문자함수
- 숫자함수
- 날짜함수
- 변환함수
-
■ 함수
-
3.4 변환함수
-
"변환을 한다는 뜻은 데이터 유형을 변환한다는 것"
- 문자형
- 숫자형
- 날짜형
-
"변환을 한다는 뜻은 데이터 유형을 변환한다는 것"
-
3.4 변환함수
SELECT ename, sal FROM EMP WHERE sal = '3000';
에러가난다.
no low select 가 나온다
결과가 나온다
정답 3번 ㅎ
※ 암시적 형변환이 발생했다.
숫자 > 문자
숫자가 우선순위 높아서 둘이 비교할 때 문자를 숫자로 변환해줌
- 형변환 2가지
1. 암시적 형변환
set autot traceonly explain
설명: 지금 부터 수행할 SQL에 대해서 실행계획만 보여달라 데이터 결과는 안봐도 되고
select ename, sal from emp where sal = '3000';
SQL> select ename, sal
from emp
where sal = '3000';
왜 암시적 형변환이 발생한 것이 별로 좋지 않다는 것인가
SQL> select ename, sal
from emp
where sal like '30%';
숫자로 형변환을 하려니 %를 숫자로 못바꾸니 sal을 문자로 바꿈
인덱스가 풀려버려서 풀스캔합니다 이렇게 하면 안돼요
오라클은 친절하게도 암시적 형변환이 발생하지만
다른 rdbms 제품에서는에러가 난다.
2.. 명시적 형변환
- to_char : 문자로 형변환하는 함수
- to_number : 숫자로 형변환하는 함수
- to_date : 날짜로 형변환하는 함수
select ename, sal, to_char(sal,'999,999') from emp;
월급을 출력한느 문자로 형변환해서 출력하겠다.
위의 형식 (format) 에 맞춰서 출력해라
설명: 9 가 의미하는것은 9 자리에 어떠한 숫자가 와도 관계 없다 . 대신 자릿수는 한개여야한다.
select ename, sal, to_char(sal,'999,999') from emp;
문제 83. 이름 , 월급 * 30203455 를 출력하는데 숫자 단위가 ( , )
가 출력되게 하시오 ( 천단위 ,백만단위 등을 표시)
select ename, sal, to_char(sal* 30203455,'999,999,999,999') from emp;
설명 : 숫자 ---------> 문자
- 날짜 ---> 문자
문제84. 오늘이 무슨요일인지 출력하시오
select sysdate, to_char( sysdate, 'day') from dual;
select sysdate, to_char( sysdate, 'day') from dual;
문제85. 내가 무슨요일에 태어났느닞 출력하시오!
select SYSDATE,ename, to_char(BIRTH, 'day') from EMP2;
- 날짜 ->> 문자로 형변환 시 사용할 수 있는 format
- 년도 : RRRR, YYYY, RR, YY
- 달 : MM ,MON
- 일 : DD
- 요일 : DAY
- 시간 : HH, HH24
- 분 :MI
- 초 : SS
- 주(WEEK) : IW,WW
\
문제 86, 수요일에 입사한 사원들의 이름과 입사일과 입사한 요일을 출력하시오
select ename,HIREDATE, to_char(HIREDATE, 'day') from EMP WHERE to_char(HIREDATE, 'day') = 'wednesday';
- 명시적 형변환 함수 ( p 4-7)
문제 87. 이름 , 입사년도 ( 4자리) 로 출력하시오
select ename, to_char(hiredate, 'RRRR') from emp;
※ : RRRR, YYYY, RR, YY
select ename, to_char(hiredate, 'RRRR//MM/DD') from emp;
※ : RRRR, YYYY, RR, YY
문88. 1981 년도에 입사한 사원들의 이름과 입사한 년도를 출력하시오 (Like, substr사용하지 말고)
select ename,to_char(hiredate, 'RRRR') from EMP where to_char(hiredate, 'RRRR') = '1981';
문89. 오늘부터 100달 뒤에 돌아오는 날짜의 요일을 출력하시오
select to_char(add_months(sysdate,100),'day') from dual;
- 년도 : RRRR, YYYY, RR, YY
- 연도 형식인 RR과 YY의 차이
문제 90. 81년 12월 11일에 입사한 사원들의 이름과 입사일을 출력하시오 !
SELECT ename,HIREDATE FROM EMP WHERE hiredate= '81/12/11';
안뎀
DD-MON-RR
SELECT ename,HIREDATE FROM EMP WHERE hiredate= '11-DEC-81';
SELECT *
FROM nls_session_parameters;
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
문제91, 81년 12월 11일에 입사한 사원들의 이름과 입사일을 출력하는데
우리나라 날짜형식으로 작성해서 검색되게 하시오
alter session set nls_date_format='RR/MM/dd';
national language support
- RR과YY 의 차이 ??
RR |
YY |
|
81 1981 2081
2017
현재연도에서 가장
가까운 연도
|
81 1981 2081
2017
현재 세기의 연도
|
튜닝전:
SELECT ename, hiredate
FROM EMP
WHERE TO_CHAR(hiredate, 'RR/MM/DD') = '81/12/11';
악성 sql입니다 성능을 떨어뜨림 index를 활용하지 못함
풀스캔
튜닝후: 좌변을 가공하면 안됨
select ename, hiredate
from emp
where hiredate = to_date('81/12/11', 'RR/MM/DD');
직업군 : 데이터 분석가, 소프트웨어 엔지니어(코딩),
DBA, 빅데이터 엔지니어, SQL 튜너
-
■ 함수
- 문자
- 숫자
- 날짜
- 변환
- to_char
- to_date
- to_number
-
■ 함수
5. 일반함수 :
함수 nvl2 함수, decode 함수, case함수
문제 92. 이름과 커미션을 출력하는데 커미션이 null 인 사람들은 0으로 출력되게 하시오
SELECT ename , nvl(comm, 0) FROM EMP;
문제93.
이름과 커미션을 출력하는데 커미션이 null 인 사람들은 'no comm'으로 출력되게 하시오
SELECT ename , nvl(TO_CHAR(comm), 'no comm') FROM EMP;
- to_char ----> 문자로 형변환하는 함수
- to_number ---> 숫자로 형변환하는 함수
- to_date ---> 날짜로 형변환하는 함수
문94. 이름, 관리자의 사원번호 (mgr)를 출력하느데 mgr이 null인 사원은 no manager 란 글 씨로 출력되게 하시오
SELECT ename, NVL(TO_CHAR(mgr), 'no manager') FROM EMP;
SELECT ename, NVL(mgr || '', 'no manager') FROM EMP;
- null은 알 수 없는 값이다 null이다
null 대신에 다른 값을 출력하는 함수가 nvl함수이다.
- nvl2 함수
select ename, sal, comm, nvl2(comm, sal+com, sal) from emp;
↑
comm 이 null 이 아니면 sal + comm 출력
comm 이 null 이 면 sal을 출력하라
- decode 함수 (중요함수 ★★★★★★★★)
문제 95. 이름, 직업, 보너스를 출력하는데
직업이 SALESMAN 이면 보너스를 5000을 출력하고
직업이 SALESMAN이 아니면 보너스를 0으로 출력하시오
SELECT ENAME, JOB, DECODE(JOB, 'SALESMAN' , 5000, 0 ) "bonus"
from emp;
설명: coding 을 길게하지 않고 간단하게 함수로
결과를 출력할 수 있다.
문96. 이름,직업, 보너스를 출력하는데
직업이 SALESMAN 이면 보너스가 5000이 출력되게 하고
직업이 ANALYST 이면 보너스가 3000이 출력되게 하고
나머지 직업은 0으로 출력되게 하시오
SELECT ENAME, JOB, DECODE(JOB, 'SALESMAN' , 5000,
'ANALYST', 3000,
0) "bonus"
from emp;
문제97. 이름, 부서번호, 월급, 보너스를 출력하는데
보너스가 부서번호가 10번이면 월급의 20%로 출력
부서번호가 20번이면 월급의 40%로 출력
나머지 부서번호는 그냥 0 이 출력되게 하시오
SELECT ENAME, JOB, DECODE(DEPNO, 10 , SAL * 0.2
, 20 , SAL * 0.4,
0 ) "bonus"
from emp;
문98. 이름, 월급 보너스를 출력하는데
보너스가 월급이 3000 이상이면 보너스를 700으로 출력하고
월급이 3000보다 작으면 보너스를 0으로 출력하시오
SELECT ENAME, JOB, DECODE(DEPNO, SAL >=3000 ,700,
0 ) "bonus"
from emp;
설명 : DECODE는 등호비교만 가능하고 부등호 비교는 불가능하다.
부등호 비교를 표현하려면 CASE 문을 작성해야 한다.
SELECT ename, deptno, sal ,
case when sal > = 3000 then 7000
else 0 end as 보너스
from emp;
문제99. 이름 , 월급, 입사년도, 보너스를 출력하는데
입사한 년도가 1980년도면 보너스를 9000을 출력
입사한 년도가 1981년도면 보너스를 2000을 출력
나머니 년도면 그냥 0 을 출력하시오 .
select ename, sal, hiredate, decode(hiredate, to_date('1980', 'RRRR'), 9000,
,to_date('1980', 'RRRR'), 2000,
0)
FROM EMP;
문제 100. (오늘의 마지막 문제)
이메일메인별로 이름이 콤마로 구분되어서 리스트에그를 써서 나오게 하라
이메일 도메인 | 이름 ,이름,이름
구굴| 이름,이름
네이버| 이름,이름
SELECT substr(email,instr(email,'@')+1,instr(email,'.') - instr(email,'@')-1) AS "이메일 도메인" ,
listagg(ename ,',') within group ( order by ename) AS "이름"
from emp2
group by substr(email,instr(email,'@')+1,instr(email,'.') - instr(email,'@')-1)
ORDER BY COUNT("이메일 도메인") desc;
SELECT substr(email,instr(email,'@')+1,instr(email,'.') - instr(email,'@')-1) as "이메일 도메인",
listagg(ename ,',')
within group ( order by ename)
from emp2
group by email;
SELECT substr(email,instr(email,'@')DLF
listagg(ename ,',')
within group ( order by ename)
from emp2
group by substr(email,instr(email,'@')+1,instr(email,'.') - instr(email,'@')-1);
우리끼리 문제 . 학과별로 사람들은 ,으로 구분하여 listagg하라
SELECT RTRIM(major,'학과') ||'학과' AS "학과" , (ename ,',')
within group ( order by ename) AS "이름"
FROM EMP2
GROUP BY RTRIM(major,'학과');
SELECT * FROM EMP2;
- SQL이란 무엇인가?
-
SQL을 왜 배워야 하는지?
- 데이터를 다룰 줄 아는 기본 스킬
- 빅데이터 엔지니어, 데이터 분석가, 머신러닝
- 우리나라가 이 부분에 취약한 상환이고 강인공지능이 인류를
- 위협한다면 국내경제가 선진국에 지배를 당하는 현상이 일어날것
-
3. 기본 SQL 문
- 3 select
- 1 from
- 2 where
- 4 order by
-
3. 기본 SQL 문
-
4. 함수
- 단일행 함수 : 문자, 숫자, 날짜, 변환, 일반
- 복수행 함수 : max, min , avg , sum, count
-
4. 함수
'SQL 기본' 카테고리의 다른 글
| 6장 조인 문법 (0) | 2017.09.07 |
|---|---|
| 5장 복수행 함수 변환 함수 및 조건부 표현식 사용 (0) | 2017.09.07 |
| 4장 DECODE와 PIVOT 변환 함수 및 조건부 표현식 사용 (0) | 2017.07.05 |
| 2장 데이터 제한미 정렬 (0) | 2017.07.05 |
| 1장 SQL SELECT 문을 사용하여 데이터 검색 (0) | 2017.07.05 |
