select deptno , substr(min(lpad(sal,4,0)||ename),5)
ename,min(lpad(sal,5,0)||ename) value from emp group by deptno
// 필드를 합쳐서 사용 substr(value, number) number 앞에서 부터 자를 글자수
select a.ename, a.deptno, b.loc, a.job from emp a, dept b where
a.deptno=b.deptno and a.deptno = 10;
rowid 모든 데이타 마다 유니크 하게 가지는 아이디 .
rownum 데이타의 row 넘버 .
order by는 모든select가 실행되는 가운데에서 매일 마지막에 실행된다.
frimary key 를 만들면 자동 목차를 만든다.
decode(조건, 참 , 참 값, 거짓)
select deptno, decode(job,'clerk',job,'') m,decode(job,'clerk',job,'') M,
decode(job,'clerk',job,'') s,decode(job,'clerk',job,'')p,
Sum(sal) from emp group by deptno, job;
ex)
deptno clerk manager salesman president sum(sal)
10 1 1 0 1
20 1 1 0 0
30 1 1 1 0
select deptno, job, count(empno) from emp group by deptno, job
select deptno, job, decode(job,'CLERK',COUNT(EMPNO),0) CLERK,
decode(job,'MANAGER',COUNT(EMPNO),0) MANAGER,
decode(job,'ALALYST',COUNT(EMPNO),0) ALALYST,
decode(job,'SALESMAN',COUNT(EMPNO),0) SALESMAN,
decode(job,'PRESIDENT',COUNT(EMPNO),0) PRESIDENT
SUM(SAL) FROM EMP GROUP BY DEPTNO, JOB
인라인 뷰.. : select 문에 table위치에 select로 나온 테이블을 테이블로 쓰는것.
SELECT DEPTNO SUM(CLERK),SUM(MANAGER), SUM(ALALYST),SUM(SALESMAN),SUM(PRESIDENT),SUM(SUM(SAL))
FROM (select deptno, job, decode(job,'CLERK',COUNT(EMPNO),0) CLERK,
decode(job,'MANAGER',COUNT(EMPNO),0) MANAGER,
decode(job,'ALALYST',COUNT(EMPNO),0) ALALYST,
decode(job,'SALESMAN',COUNT(EMPNO),0) SALESMAN,
decode(job,'PRESIDENT',COUNT(EMPNO),0) PRESIDENT
SUM(SAL) FROM EMP GROUP BY DEPTNO, JOB)
WHERE GROUP BY DEPTNO