sql> 오라클 sql 수업 2일차

--ORDER BY * 정렬

SELECT * FROM emp ORDER BY sal; --기본 오름차순 정렬

SELECT * FROM emp ORDER BY sal asc; --asc 오름차순 정렬

SELECT * FROM emp ORDER BY sal desc; --desc 내림차순 정렬

SELECT * FROM emp ORDER BY sal asc , ename desc; --선 월급 후 이름 ?

--열 alias를 기준으로 정렬

SELECT empno,ename,sal*12 annsal FROM emp ORDER BY sal*12; 

--열의 숫자 위치를 사용하여 정렬

SELECT ename,job,deptno,hiredate FROM emp ORDER BY 3; --열의 순서값

--NULLS FIRST 또는 NULLS LAST 키워드를 사용하여 반환된 행 중 null값을 포함하는 행이 정렬 순서상 맨 처음에 나타나거나 마지막에 나타나도록 지정 가능.

SELECT * FROM emp ORDER BY comm NULLS FIRST;

SELECT * FROM emp ORDER BY comm NULLS LAST;

--특정 컬럼의 특정값으 먼저 오게 정렬하는 방법

--1) order by (case 컬럼명 when 비교값 then 결과값(순서)

--                           when 비교값 then 결과값

--                          else 결과값

--                    end)

-- order by decode (컬럼명,비교값,결과값,(순서),

--                         비교값,결과값,결과값, 

--                         end)

--사번으로 정렬하는데 7698인 경우는 가장 먼저 정렬

SELECT empno,ename,sal FROM emp ORDER BY (CASE empno when 7698 then 1 END), empno;

SELECT empno,ename,sal FROM emp ORDER BY DECODE(empno,7698,1), empno;

SELECT empno,ename,sal FROM emp ORDER BY DECODE(empno,7698,1,7521,2), empno; --2번으로 점프시키키

SELECT empno,ename,sal FROM emp ORDER BY DECODE(empno,7698,1,7521,2) DESC, empno; --맨뒤로 보내기

--[실습문제]

--1)사원번호,사원이름,입사일을 출력하는데 입사일이 빠른 사람순으로 정렬하시오.

--2)사원이름,급여,연봉을 구하고 연봉이 많은 순으로 정렬하시오.

--3)10번부서와 20번부서에서 근무하고 있는 사원의 이름과 부서번호를 출력하고 이름을 영문자순(a가 먼저오게 끔) 으로 표시

--4)커미션을 받는 모든 사원의 이름, 급여 , 커미션을 출력하는데 커미션을 기준으로 내림차순 정렬하시오. (커미션을 받는사람만)

SELECT empno,ename,hiredate FROM emp ORDER BY hiredate;

SELECT ename,sal,sal*12 ee FROM emp ORDER BY ee desc;

SELECT ename,deptno FROM emp where deptno IN(10, 20) ORDER BY ename;

SELECT ename,sal,comm FROM emp where comm is NOT NULL ORDER BY comm desc ;

--문자함수 

--대소문자 조작함수 : LOWER, UPPER , INITCAP

--LOWER : 소문자로 변경

SELECT LOWER('HELLO') FROM dual;

SELECT LOWER(ename) FROM emp;

-- UPPER : 대문자로 변경

SELECT UPPER('sky') FROM dual;

SELECT UPPER (LOWER(ename)) FROM emp; --소문자로 만든 함수를 대문자로 만드는 함수로 감싸서 사용 가능 -함수중첩가능

-- INITCAP : 첫글자를 대문자로 변경 나머지를 소문자 변경

SELECT INITCAP('hello woRLD') from dual;

SELECT INITCAP(ename) from emp;

SELECT INITCAP(ename), INITCAP(job) FROM emp;

-- 문자 조작 함수 : CONCAT, SUBSTR, INSTR, LENGTH, LPAD, TRIM, REPALCE

--CONCAT(문자열1, 문자열2) //연결하여 하나의 문자열로 반환

SELECT CONCAT('hello' , ' world') FROM dual;

SELECT CONCAT(ename, job) FROM emp;

--SUBSTR(대상문자열,인덱스) : 대상문자열에서 지정한 인덱스부터 문자열을 추출 [주의사항] 인덱스가 1부터 시작함.

SELECT SUBSTR('hello worold',3) FROM dual; --3번째부터

SELECT SUBSTR('HEllo World',3,3)FROM dual; --3번째부터 3개만

SELECT SUBSTR('hello worold',-3) FROM dual; --뒤3번째부터 끝까지

SELECT SUBSTR('hello worold',-3,2) FROM dual; --뒤3번째부터 2개만

-- LENGTH (대상문자열) : 문자의 갯수 

SELECT LENGTH('Hello Wolrd') FROM dual;

SELECT ename ,LENGTH(ename) FROM emp;

--INSTR(대상문자열,검색문자) : 검색문자의 위치값 검색

SELECT INSTR('hello world', 'e')FROM dual;

SELECT INSTR('hello world', 'o')FROM dual; --대상문자열,검색문자,검색인덱스:해당위치부터 검색

SELECT INSTR('hello world', 'o')FROM dual;

SELECT INSTR('hello world', 'o',1,2)FROM dual;

 -- LPAD ,RPAD ( '문자열','문자열+갯수','추가문자')

 

 SELECT LPAD('Hello',10,'*') FROM dual;

 SELECT RPAD('Hello',10,'*') FROM dual;

--TRIM (공백을 제거하거나 문자를 제거한 다음 값 반환)

--방향 leadin : 왼쪽 , trailing : 오른쪽 both : default

SELECT TRIM(both 'h' from 'habchh') FROM dual; --habchh 에서 h를 모두 제거

--REPLACE (대상문자열,old문자,new문자)

SELECT REPLACE('hello','he','He') FROM dual; --he 를 He 로

SELECT REPLACE('010.1234.5678','.','-')FROM dual; -- .을 -으로

--[실습문제]

--1)업무(job)를 첫글자는 대문자 나머지는 소문자로 출력

--2)사원이름 중 A가 포함된 사원이름을 구하고 그 이름중 앞에서 3자만 추출

--3)이름의 세번째 문자가 A인 모든 사원의 이름을 표시

--4)이름이 J,A 또는 M으로 시작하는 모든 사원의 이름을 첫글자는 대문자

--나머지는 소문자로 표시 및 이름의 길이도 표시

SELECT INITCAP(job) FROM emp;

SELECT SUBSTR(ename,1,3) FROM emp where ename like '%A%';

SELECT ename FROM emp where SUBSTR(ename,3,1) = 'A';

SELECT INITCAP(ename),LENGTH(ename) FROM emp where ename LIKE 'J%' OR ename LIKE 'A%' OR ename LIKE 'M%'; 

--SELECT INITCAP(ename),LENGTH(ename) FROM emp where SUBSTR(ename,1,1) in('A','J','M');

--5)이름의 글자수가 6자 이상인 사원의 이름을 소문자로 이름만 출력하시오.

--6)모든 사원의 이름과 급여를 표시하는 질의를 작성. 조건) 급여는 15자길이로 왼쪽에 $기호가 채워진 형식으로 표시하고 열 레이블을 SALARY로 지정

SELECT LOWER(ename) FROM emp where LENGTH(ename)>=6;

SELECT ename, LPAD(sal,15,'$') "SALARY"   FROM emp;

--숫자함수

--CEIL() : 올림 처리한 정수값을 반환

SELECT CEIL(1.4) FROM dual;

--ROUND(대상숫자,지정자릿수)  : 반올림

SELECT ROUND(45.926,2)FROM dual;

SELECT ROUND(45.926)FROM dual; --지정자릿수가 없으면 첫째자리에서 반올림

SELECT empno,ename,sal,ROUND(sal*1.15,0) "NEW Salary" FROM emp;

-- TRUNC(대상숫자,지정자릿수) : 숫자 절삭

SELECT TRUNC(45.926,2) FROM dual;

SELECT TRUNC(45.926) FROM dual;

--MOD(대상숫자,나눌숫자) : 나머지값 구할때

SELECT MOD(17,2) FROM dual;

--날짜 함수

--SYSDATE(연/월/일 만 나옴) 

SELECT SYSDATE FROM dual;

--날짜에 산술 연산자 이용

SELECT ename, ROUND (((SYSDATE-hiredate)/ 7)) WEEKS FROM emp WHERE deptno=10;

--MONTHS_BETWEEN : 두 날짜 간의 월 수

SELECT MONTHS_BETWEEN('2018-11-15','2018-07-15') FROM dual; --큰 날짜가 앞으로 

SELECT ename,ROUND(MONTHS_BETWEEN(SYSDATE,hiredate)) months_work FROM emp; --입사일부터 현날짜까지 몇달인지  

--ADD_MONTHS : 특정 날짜의 월에 정수를 더한 다음 해당 날짜를 반환하는 함수

SELECT ADD_MONTHS('2018-11-15',8)FROM dual;

--NEXT_DAT : 지정된 요일의 다음 날짜

SELECT NEXT_DAY('2018-11-15','월요일') FROM dual; --1(일요일) ~7(토요일)

SELECT NEXT_DAY('2018-11-15',1) FROM dual;

SELECT LAST_DAY('2018-11-15') FROM dual;

--ROUND() 를 이용해서 반올림되는 형식 //연도는 7월 1일 이상일 경우 올림 ,그 이전 날짜의 경우는 잘려나감

SELECT ROUND(SYSDATE,'year') FROM dual;

--월은 16일 이상일 경우 올리고 그 이전 날짜의 경우는 잘려나감

SELECT ROUND(SYSDATE,'month') FROM dual;

--시간은 12시를 넘어서면 1을 증가시킴

SELECT ROUND(SYSDATE,'dd') FROM dual;

--TRUNC() : 날짜 절삭

SELECT TRUNC(SYSDATE,'year') FROM dual;

SELECT TRUNC(SYSDATE,'month') FROM dual;

SELECT TRUNC(SYSDATE,'dd') FROM dual;

--extract() : 날짜정보에서 특정한 연도,월,일,시간,분,초 등을 추출

SELECT EXTRACT(YEAR FROM SYSDATE),EXTRACT(DAY FROM SYSDATE) FROM dual;

--[실습문제]

--7)오늘부터 이번달의 마지막 날짜까지의 남은 날 수를 구하기

--8)각 사원에 대해 사원번호, 이름 ,급여 및 15% 인상된 급여를 정수(반올림)로 표시하시오. 인상된 급여 New Salary 로 지정

SELECT LAST_DAY(SYSDATE)-SYSDATE FROM dual;

SELECT empno, ename,sal ,ROUND(sal+sal*0.15) "New Salary" FROM emp;

-- 변환 함수

-- to_char() : 숫자를 문자로 바꿔주거나 날짜를 문자로 바꿔줌

-- 날짜 -> 문자

SELECT to_char(SYSDATE,'YYYY-MM-DD') FROM dual; --뒤에 포맷문자 갯수 = 표기 자릿수 

--SELECT to_char(SYSDATE,'YYYY년-MM월-DD일') FROM dual;  날짜표기에 한글표기하면 오류

SELECT to_char(SYSDATE,'YYYY-MM-DD') FROM dual;

SELECT to_char(SYSDATE,'RR-MON-DD') FROM dual; --뒤에 포맷문자 갯수 = 표기 자릿수 

-- 숫자 -> 문자 

-- 실제 자리수와 일치

SELECT TO_CHAR(1234,9999) FROM dual; -- 9999-> 자리수를 나타내는 포멧 문자

SELECT TO_CHAR(1234,'9999') FROM dual; -- 1234으로 표기

SELECT TO_CHAR(1234,'0000') FROM dual; --1234로 표기

SELECT TO_CHAR(1234,0000) FROM dual; -- ##으로 표기 = 자릿수가 모자람

SELECT TO_CHAR(123,0000) FROM dual; -- ##으로 표기 = 자릿수가 모자람

SELECT TO_CHAR(1234,99999) FROM dual; -- 1234로 표기

SELECT TO_CHAR(1234,999) FROM dual; --####로 표기

SELECT TO_CHAR(123,'0000') FROM dual; --0123 표기

SELECT TO_CHAR(123,'9999') FROM dual; --123 표기

-- 소숫점 자리

SELECT TO_CHAR(1234,'9999.99') FROM dual; --1234.00

SELECT TO_CHAR(1234,'0000.00') FROM dual; --1234.00

-- 반올림해서 소수점 둘째자리까지 표시

SELECT TO_CHAR(25.897,'99.99')FROM dual;

SELECT TO_CHAR(sal*1.15,'9,999.0')FROM emp;

통화표시 

SELECT TO_CHAR(1234, '$0000') FROM dual;

SELECT TO_CHAR(1234, 'L0000') FROM dual;

-- to_date : 문자 ->날짜

SELECT TO_DATE('20181115','YYYY-MM-DD')FROM dual; -- (-)없이도 자릿수 인식가능

SELECT TO_DATE('2018-11-15','RR-MM-DD')FROM dual;