关于Oracle数据库删除、查询(二)

续关于Oracle数据库删除、查询(一)

查询emp表

select ename from emp where (sal>500 or job='manager') and ename like 'J%';

select * from emp order by sal;

select * from emp order by sal desc;（降序）

：

(默认为升序,asc可写可不写)

select * from emp order by empno asc ,sal desc;

查询每个员工的年薪：

select (sal+nvl(comm,0))*12 as "年薪" from emp order by "年薪"；（as可省，"年薪"为列别名）

select ename  sal from emp where sal=(select max(sal) from emp);

查询高于平均工资的人：

select ename sal from emp where sal>(select avg(sal) from emp);

***1.select avg(sal),max(sal),deptno from emp group by deptno; 

***2.select avg(sal),max(sal),deptno, job from emp group by ,deptno,job; 

***3.select avg(sal),deptno, from emp group by deptno having avg(sal),<2000;

group by--->order by(使用顺序)