oracle 单表查询练习

select ename,job,nvl(sal,0)+ nvl(comm,0) sal,dname from emp,dept where emp.deptno = dept.deptno and ename = ‘SMITH‘; select ename,dname from emp join dept on emp.deptno = dept.deptno where ename = ‘SMITH‘;工资高于3000的员工select * from emp where sal > 3000;工资在2500和3000之间的员工select * from emp where sal <= 3000 and sal >= 2500;指定日期后入职的员工select * from emp where hiredate > ‘1982-1-1‘;查找首字母是S的员工select * from emp where ename like ‘S%‘;查找第三个字母是O的员工select * from emp where ename like ‘__O%‘;in 关键字select * from emp where empno in(7788,7566);查找没有上级的员工select * from emp where mgr is null;查找工资大于500 或是岗位为 manager 的员工，同时首字母必须是大写的Jselect * from emp where (sal >= 500 or job = ‘MANAGE‘) AND ename like ‘J%‘;工资升序select * from emp order by sal asc;默认select * from emp order by sal desc;部门号升序，工资降序select * from emp order by deptno,sal desc;年薪降序select ename,(nvl(sal,0))*12 "年薪" from emp order by "年薪" desc;工资最高和最低的员工select * from emp where sal=(select max(sal) from emp) or sal=(select min(sal) from emp);所有员工的平均工资和工资总和以及最高工资select avg(sal) 平均工资,sum(sal) 工资总和,max(sal) 最高工资 from emp;工资高于平均工资的员工select * from emp where sal >= (select avg(sal) from emp);显示每个部门的平均工资和最高工资select deptno,avg(sal),max(sal) from emp group by deptno;显示每个部门每个岗位的平均工资和最高工资select deptno,max(sal) from emp group by deptno,job order by deptno;显示平均工资低于2000的部门的编号和平均工资分组函数只能出现在选择列、having及order by 子句中select deptno,avg(sal) from emp group by deptno having avg(sal) < 2000 order by avg(sal);

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!