본문 바로가기

Oracle

 (14)
13일차 1 부서별 집계, 전체집계 만들기 예) DEPTNO JOB SUM(SUM_SAL) 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 소계 8750 20 ANALYST 1000 20 AAA 1000 소계 2000 총합 10750 카르테시안 조인을 이용한다.. SCOTT 계정 SELECT deptno, job, SUM(sal) FROM emp GROUP BY deptno, job ORDER BY 1,2; DEPTNO JOB SUM(SAL) ------ --------- -------- 10 MANAGER 2450 10 PRESIDENT 5000 20 ANALYST 3000 20 CLERK 800 20 MANAGER 2975 30 CLERK 950 30 MANAGER 2850 ..
13일차 2 sub query Abel보다 급여 많이 받는 사람 구하기 ABel의 급여 SELECT salary FROM employees WHERE last_name='Abel'; SALARY ------ 11000 Abel의 급여인 11000보다 큰사람 SELECT last_name, salary FROM employees WHERE salary>11000; LAST_NAME SALARY --------- ------ King 24000 Kochhar 17000 De_Haan 17000 Hartstein 13000 Higgins 12000 둘을 합친거 SELECT last_name, salary FROM employees WHERE salary > (SELECT salary FROM employees WHERE ..
12일차 1 > Three ways join - 세개이상의 table 조인 먼저 두개를 조인하고 그 다음 조인하고 이런식으로 만들어.. scott 계정에서 보면 --사원명, 부서명 출력 SELECT e.ename as 사원, d.dname as 부서 FROM emp e, dept d WHERE e.deptno = d.deptno(+); --사원명,부서명에 사원등급까지 출력. SELECT e.ename as 사원, d.dname as 부서, s.grade as 등급 FROM emp e, dept d, salgrade s WHERE e.deptno = d.deptno(+) AND e.sal BETWEEN s.losal and s.hisal; oraclejava계정으로 변경하고.. 사원이름(employees.last_na..
12일차 2 > Set Operator (집합연산) 먼저 테스트를 위한 table을 만들자 create table set_a(a number); create table set_b(b number); commit begin for i in 1..10 loop insert into set_a values(i); end loop; end; begin for i in 6..15 loop insert into set_b values(i); end loop; end; select * from set_a; select * from set_b; A B ---- 1 6 2 7 3 8 4 9 510 611 712 813 914 1015 1) 합집합 (1) UNION 컬럼의 갯수와 컬럼의 데이터타입이 일치해야해. 중복제거. 속도가 약간..
12일차 3 >> Oracle Functions http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions.htm#i1482196 > Numeric Functions 1) CEIL : 올림 SELECT CEIL(3.141592), CEIL(3.98), CEIL(3) FROM DUAL; CEIL(3.141592) CEIL(3.98) CEIL(3) -------------- ---------- ------- 4 4 3 2) FLOOR : 내림 SELECT FLOOR(15.7), FLOOR(15.1), FLOOR(16) FROM DUAL; FLOOR(15.7) FLOOR(15.1) FLOOR(16) ----------- ----------- --------- 15 1..
12일차 4 집계함수 SELECT SUM(salary), COUNT(salary), AVG(salary), MAX(salary), MIN(salary) FROM employees; SUM(SALARY) COUNT(SALARY) AVG(SALARY) MAX(SALARY) MIN(SALARY) ----------- ------------- ----------- ----------- ----------- 175500 20 8775 24000 2500 집계함수의 NULL값 처리. SELECT SUM(commission_pct) AS SUM, COUNT(commission_pct), COUNT(*), AVG(commission_pct) AS AVG, MAX(commission_pct) AS MAX, MIN(commission..
12일차 5 흐름제어 (decode, simpled case, searched case) 내년 급여인상 IT_PROG *1.2, ST_CLERK *1.1, AD_VP* 1.15, etc *1.05 뽑아보기 SELECT last_name, job_id, salary AS 올해급여, DECODE(job_id, 'IT_PROG', salary * 1.2, 'ST_CLERK', salary * 1.1, 'AD_VP', salary * 1.15, salary * 1.05) as 내년급여 FROM employees; SELECT last_name, job_id, salary as 올해급여, CASE job_id WHEN 'IT_PROG' THEN salary * 1.2 WHEN 'ST_CLERK' THEN salary * 1...
12일차 6 집계 scott계정으로 접속 년도별 입사자수 출력 SELECT DISTINCT TO_CHAR(hiredate, 'YYYY') FROM emp; TO_CHAR(HIREDATE,'YYYY') ------------------------ 1980 1982 1981 SELECT hiredate, TO_CHAR(hiredate, 'YYYY') as "1980", TO_CHAR(hiredate, 'YYYY') as "1981", TO_CHAR(hiredate, 'YYYY') as "1982" FROM emp; HIREDATE 1980 1981 1982 --------------------- ---- ---- ---- 1980-12-17 00:00:00.0 1980 1980 1980 1981-02-20 00:00:0..

티스토리툴바