posted by 생존본능 2011.05.18 12:10

-- order by 오름차순 desc입력시 내림차순.
select ename, sal
from emp
order by sal

select job, avg(sal) as [avgsal]
from EMP
group by JOB
order by avgsal desc

--top 상위 5개만 보여줌.
select top 5 ename, SAL
from emp
order by SAL desc

select top 30 percent ename, SAL
from EMP
order by SAL desc

--with ties 동률. 같은값이 있는경우에는 같이 표시.
select top 2 with ties ename, SAL
from EMP
order by sal desc


--group by 이후 처리가 필요할경우 having으로 처리하여줌.
select job , AVG(sal) as [평균연봉]
from emp
where job != 'clerk'
group by job
having avg(SAL) >= 3000
order by avg(sal)

--with rollup 시 이전 값에대한 전체값을 리턴.
select job, deptno, avg(sal)
from emp
group by deptno,job with rollup


select deptno, job, sal
from emp
order by DEPTNO desc
compute avg(sal) by deptno

'개발 > SQL' 카테고리의 다른 글

MS-SQL 조건문  (0) 2011.05.18
MS-SQL 기본문제 9문  (0) 2011.05.18
MS-SQL 정렬문법 [order by , top ]  (0) 2011.05.18
MS-SQL 기초문제  (0) 2011.05.18
MS-SQL 문법 [ DATE ]  (0) 2011.05.18
MS-SQL 숫자,텍스트,문법 [ in , like , Math ]  (0) 2011.05.18

티스토리 툴바