sql> 오라클db 수업 3일차

--TO_NUMBER : 문자 -> 숫자

SELECT TO_NUMBER('100','999') FROM dual;

SELECT TO_NUMBER('100') FROM dual; --포멧문자 생략 가능

--일반 함수

-- NVL(value1,value2) : v1이 null 이면 v2를 사용 , val1과 val2의 자료형을 일치해야함

SELECT ename,sal,NVL(comm,0) commission, NVL((comm*12)+(sal*12),0) "Annual Salary" FROM emp ORDER BY commission;

-- SELECT ename,NVL(comm,'NO commission') commission FROM emp; --자료형이 맞지 않아 사용이 불가능하다

SELECT ename, NVL(TO_CHAR(comm),'NO commission') commission FROM emp; --TO_CHAR로 자료형을 맞춰줘서 사용이 가능함.

-- NVL2(value1,value2,value3)valuel1 이 null 인지 평가 null 이면 value3 , null이 아니면 value2 자료형이 일치하지 않아도 됨.

SELECT ename, NVL2(comm,'commission','no commission') commission FROM emp; 

--NULLIF(value1,value2) : 두개의 값이 일치하면 null , 두개의 값이 일치하지 않으면 value1

SELECT  NULLIF(LENGTH(ename),LENGTH(job)) "NULLIF" FROM emp; --길이가 같으면 NULL

-- coalesce(value1,value2,value3) : null 값이 아닌 값을 사용 // 자료형을 일치시켜야함

SELECT comm,sal,COALESCE(comm,sal,0) FROM emp; --널을 제외하여 사용 

--안시 표준

--case 컬럼 when 비교값 then 결과값

--             when          then

--             when          then

--             (else 결과값)

-- end

SELECT ename, sal,job, CASE job WHEN 'SALESMAN' THEN sal*0.1 WHEN 'MANAGER' THEN sal*0.2 WHEN 'ANALYST' THEN sal*0.3 ELSE sal*0.4 END "Bonus" FROM emp; --셀러리맨 10프로 매니저 20프로 아날리스트 30프로 나머지 40프로 ="보너스"로 출력

SELECT ename,sal,job, CASE WHEN sal>=4000 AND sal <=5000 THEN 'A' WHEN sal>=3000 AND sal < 4000 THEN 'B' WHEN sal>=2000 AND sal <3000 THEN 'C' WHEN sal>=1000 AND sal <2000 THEN 'D' ELSE 'F' END "GRADE" FROM emp;

-- 오라클 전용

--decode : = 비교만 가능

--decomde(컬럼, 비교값, 반환값,

--                    비교값, 반환값,

--                    비교값, 반환값,

--                    반환값)

SELECT ename, sal ,job, DECODE(job,'SALESMAN',sal*0.1,'MANAGER',sal*0.2,'ANALYST',sal*0.3,sal*0.4) "Bonus" FROM emp;

SELECT ename, sal ,job, DECODE (TRUNC(sal/1000),5,'A',4,'B',3,'C',2,'D',1,'E','F') "GRADE" FROM emp;

-- [실습문제]

-- 9) 이름(소문자로 표시) ,업무, 근무연차(MONTH_BETWEEN)를 출력하시오.

-- 10) 사원이름,월급,월급과 커미션을 더한값을 컬럼명: 실급여라고 해서 출력 단,Null값은 나타나지 않게 .

-- 11) 월급과 커미션을 합친 금액이 2,000이상인 급여를 받는 사원의 이름 업무 월급 커미션 고용날짜를 출력하시오.  단,고용날짜는 1980-12-17 형식으로 출력하시오.

SELECT LOWER(ename), job ,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) 근무연차 FROM emp;

SELECT ename,sal,sal+NVL(comm,0) 실급여 FROM emp; 

SELECT ename,job,sal,NVL(comm,0) 커미션,TO_char(HIREDATE,'YYYY-MM-DD') 고용날짜 FROM emp where sal+ NVL(comm,0)>=2000 ;

-- 12) DEcode 함수를 사용하여 다음 데이터에 따라 job 열의 값을 기준으로 모든 사원의 등급을 표시하시오. 업무 PRESIDENT  등급 A , 업무 ANALYST  등급 B  업무 MANAGER  등급 C  업무 SALESMAN  등급 D  업무 CLERK  등급 E 기타 F or '0'

SELECT ename ,DECODE(job,'PRESIDENT','A','ANALYST','B','MANAGER','C','SALESMAN','D','CLERK','E','F') 등급 FROM emp;

-- 13) TO_CHAR 를 사용 각 사원에 대한 사원번호, 이름 , 급여 및 15% 인상된 급여를 정수(반올림)로 표시 인상된 급여 레이블 'New Salary' 로 표시

SELECT TO_CHAR(empno,9999) 사원번호,ename 이름,TO_CHAR(sal,9999) 급여,TO_CHAR(sal*1.15,9999) "New Salary" FROM emp;

-- 그룹 함수 : 행 집합 연산을 수행하여 그룹별로 하나의 결과를 산출

--AVG() : null을 제외한 모든 값들의 평균을 반환 / null값은 평균 계산에서 무시.

SELECT ROUND(AVG(sal)) FROM emp;

SELECT AVG(comm) FROM emp;

SELECT AVG(NVL(comm,0)) FROM emp;

-- COUNT() : null을 제외한 값을 가진 모든 레코드의 수를 반환, count(*)형식을 사용하면 널도 계산에 포함

SELECT COUNT(empno) FROM emp;

SELECT COUNT(comm) FROM emp;

SELECT COUNT(NVL(comm,0)) FROM emp;

SELECT COUNT(*) FROM emp;

SELECT COUNT(*) FROM emp WHERE deptno =10;

-- MAX() : 레코드 내에 있는 여러 값 중 가장 큰 값을 반환

SELECT MAX(sal) FROM emp;

SELECT MAX(ename) FROM emp;

-- MIN() : 레코드 내에 있는 여러 값 중 가장 작은 값을 반환

SELECT MIN(sal) FROM emp;

SELECT MIN(ename) FROM emp;

-- SUM() : 레코드들이 포함하고 있는 모든 값을 더하여 반환

SELECT SUM(sal) FROM emp;

SELECT ROUND(AVG(sal)),MAX(sal),MIN(sal),SUM(sal) FROM emp;

--SELECT deptno,ROUND(AVG(sal)),MAX(sal),MIN(sal),SUM(sal) FROM emp; // 사용불가 개별함수라서 

--GROUP BY : HAVING

SELECT deptno,ROUND(AVG(sal)),MAX(sal),MIN(sal),SUM(sal) FROM emp GROUP BY deptno;

--다중 열에서 GROUP BY 사용

SELECT deptno,job,sum(sal)  FROM emp GROUP BY deptno,job ORDER BY deptno; --부서별로 묶고 직업별로 묶고 

SELECT deptno,job,sum(sal) FROM emp WHERE deptno=10 GROUP BY deptno,job ORDER BY deptno; 

--//그룹 처리 결과를 제한하고자 할 때 having 절 이용 where 절에서는 집합함수를 사용할 수 없고 having 절을 이용

--조건을 체크하기 위해서 where절을 사용하는데 where 절에는 그룹함수를 사용할 수 없음.

--오류 발생

--SELECT deptno,ROUND(AVG(sal)) FROM emp WHERE ROUND(AVG(sal))>=2000 GROUP BY deptno;

SELECT deptno,ROUND(AVG(sal)) FROM emp  GROUP BY deptno HAVING ROUND(AVG(sal))>=2000;

-- HAVING절 이용 : HAVING 절에 알리아스 사용 x 

SELECT deptno,MAX(sal) "MaxSal" FROM emp GROUP BY deptno HAVING MAX(sal) >3000;

--그룹 함수 중첩

SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno;

-- 그룹함수 이용하기 분기별로 입사한 사원의 수

-- EXTRACT() 함수를 이용하여 입사 일자로부터 월을 구함.

-- 3으로 나눈 값을 올림 처리하여 각 월의 분기를 계산

SELECT CEIL( EXTRACT(MONTH FROM HIREDATE)/3) As quarter, COUNT(empno) as count_member FROM emp GROUP BY CEIL( EXTRACT(MONTH FROM HIREDATE)/3); --1번방법

SELECT TO_CHAR(hiredate,'Q') As quarter, COUNT(empno) AS count_member FROM emp GROUP BY TO_CHAR(hiredate,'Q') ORDER BY quarter; --2번방법 Q는 분기를 나타냄

--[실습문제]

-- 1) 모든 사원의 급여 최고액,최저액,총액 및 평균액을 표시 MAXIMUM MINIMUM SUM 밑 average로 지정, 결과를 정수로 반올림하고 세자리 단위로 ,를 명시하시오.

--2) 업무가 동일한 사원 수를 표시하는 질의어를 작성하시오

 

 SELECT TO_CHAR(MAX(sal),'9,999') "MAXIMUM", TO_CHAR(MIN(sal),'9,999') "MINIMUM" ,TO_CHAR(SUM(sal),'99,999') "sum",TO_CHAR(ROUND(AVG(sal)),'9,999') "average" FROM emp ;

 SELECT job,count(job) FROM emp GROUP BY job;

-- 3) 30번 부서의 사원수를 구하기

-- 4) 업무별 최고 월급을 구하고 업무,최고 월급을 출력

-- 5) 20번부서의 급여 합계를 구하고 급여 합계 금액 출력

-- 6) 부서별로 지급되는 총월급에서 금액이 7,000이상인 부서번호,총월급을 출력

SELECT count(*) FROM emp WHERE deptno=30;

--SELECT deptno,count(deptno) FROM emp GROUP BY deptno HAVING deptno=30; // 부서명까지 표기

SELECT job,Max(sal) FROM emp GROUP BY job ;

SELECT deptno,sum(sal) FROM emp GROUP BY deptno HAVING deptno=20;

SELECT deptno,sum(sal) FROM emp GROUP BY deptno HAVING sum(sal) >= 7000; 

-- 7)업무별로 사원번호가 제일 늦은 사람을 구하고 그 결과 내에서 사원번호가 79로 시작하는 결과만 보여주시오.

SELECT job, MAX(EMPNO) FROM emp WHERE EMPNO like '79%'  GROUP BY job; --1번방법

SELECT job, MAX(EMPNO) FROM emp  GROUP BY job HAVING MAX(EMPNO) like '79%' ; --2번방법

-- 8)업무별 총월급을 출력하는데 업무가 MANAGER인 사원들은 제외하고 총월급이 5,000보다 큰 업무의 총월급만 출력

SELECT job,sum(sal) FROM emp GROUP BY job HAVING sum(sal) > 5000 and  job !='MANAGER';  --1번방법

SELECT job,sum(sal) FROM emp WHERE job NOT IN 'MANAGER' GROUP BY job HAVING sum(sal) > 5000 ; --2번방법

SELECT job,sum(sal) FROM emp WHERE job <> 'MANAGER' GROUP BY job HAVING sum(sal) > 5000 ; --3번방법

-- 9)업무별로 사원의 수가 4명이상인 업무와 인원수를 출력

SELECT job,count(empno) FROM emp GROUP BY job HAVING COUNT(empno)>=4;

-- 10)사원수가 5명이 넘는 부서의 부서번호와 사원수를 구하기

SELECT deptno,count(empno) FROM emp GROUP BY deptno HAVING COUNT(empno)>5;

-- 11)부서별 급여평균을 구할 때 소수점 3자리에서 반올림해서 2자리까지 구하고 부서번호, 부서평균을 출력하시오.

SELECT deptno,TO_CHAR(AVG(sal),'9999.99') FROM EMP GROUP BY deptno  ;

SELECT deptno,ROUND(AVG(sal),2) FROM EMP GROUP BY deptno  ;

-- 12)분기별로 입사한 사원수를 구하는데 2분기에 입사한 사원수만 구하시오. (TO_CHAR() 사용하기)

SELECT  TO_CHAR(hiredate,'Q') 분기 ,COUNT(empno) "입사한 사원수" FROM emp GROUP BY TO_CHAR(hiredate,'Q') HAVING TO_CHAR(hiredate,'Q')=2 ; --q는 분기별로 나타냄

SELECT  TO_CHAR(hiredate,'Q') 분기 ,COUNT(empno) "입사한 사원수" FROM emp WHERE TO_CHAR(hiredate,'Q')=2 GROUP BY TO_CHAR(hiredate,'Q') ; --q는 분기별로 나타냄