[SQL]DECODE문 , CASE WHEN문
-- DECODE -- 10번 부서는 20% 인상, 80번 부서 15%, 50번 부서 10% 그이외 동결 SELECT LAST_NAME, SALARY, DECODE (DEPARTMENT_ID,10,SALARY*1.2, 80,SALARY*1.15, 50,SALARY*1.1, SALARY) AS "REVISED SALARY", DEPARTMENT_ID FROM EMPLOYEES; --10번부서 사원은 50%,20번부서 사원은 40% 30번부서사원 -10% select last_name, salary, decode(department_id,10,salary*1.5, 20,salary*1.4, 30,salary*0.9) as 변경급여 from employees; --searched case:CASE WHEN s..
2020. 9. 11.
[SQL]그룹화 관련 함수 (ROLLUP,CUBE)
/*그룹화와 관련된 여러함수 */ /*ROLLUP, CUBE*/ /* SELECT [조회할 컬럼1],[컬럼2].. FROM [조회할 테이블 이름] WHERE [조회할 행을 선별하는 조건식] GROUP BY ROLLUP [그룹화 열 지정(여러개 가능)] GROUP BY CUBE [그룹화 열 지정(여러개 가능)] ->그룹화 데어티의 합계를 출력하는데 사용 */ /*ROLLUP*/ SELECT DEPTNO,JOB,COUNT(*),MAX(SAL),ROUND(AVG(SAL),2) FROM EMP GROUP BY ROLLUP(DEPTNO,JOB) ORDER BY DEPTNO,JOB; /*CUBE*/ SELECT DEPTNO,JOB,COUNT(*),MAX(SAL),ROUND(AVG(SAL),2) FROM EMP GR..
2020. 9. 11.
[SQL] Oracle조인,ANSI조인(cross,equi,non-equi,outer,self)
-- 오라클 조인 --크로스조인,카티션 프로덕트 select last_name,department_name from employees,departments order by last_name; -- equi join select e.last_name, e.department_id,d.department_id,d.department_name from employees e,departments d where e.department_id=d.department_id order by last_name; --non equi join select e.last_name, e.salary,j.grade_level from employees e, job_grades j where j.lowest_sal
2020. 9. 10.