《orcle中emp和dept常用命令》由会员分享，可在线阅读，更多相关《orcle中emp和dept常用命令（10页珍藏版）》请在金锄头文库上搜索。
1、select ename 姓名，job 工作 from EMP twhere ename like S%-在em表中，按DEPTNO统计各部门的职员数和各部门的平均工资，要求不统计没有分配职员的部门。select deptno,count(*),avg(sal)from empwhere deptno is not nullgroup by deptno-登录scott用户，从DEPT表中，查找DEPTNO 字段小于等于30，且DNAME以“A”开头的所有记录，-要求查询结果只显示DEPTNO和DNAME两个字段，DEPTNO和DNAME分别用“部门号”、“部门名称”-来显示。请写出详细SQL
2、语句。select deptno 部门号,dname 部门名称from dept where deptno0-选择部门30中的雇员select * from tb_empwhere deptno = 30-列出所有经理的姓名、编号和部门select ename 姓名,empno 编号,dname 部门from emp, deptwhere emp.deptno=dept.deptno and job = MANAGER -小写manager不行-列出佣金高于薪金的雇员.select e1.* from emp e1,emp e2where e1.empno=e2.empno and me1.s
3、al-找出佣金高于或等于薪金50%的雇员select e1.* from emp e1,emp e2where e1.empno=e2.empno and m=e1.sal*0.5-找出部门10中所有经理和部门20中所有办事员的详细信息select * from empwhere (deptno=10 and job=MANAGER) or (deptno=20 and job=CLERK)-找出部门10中所有经理和部门20中所有办事员以及即不是经理又不是办事员但薪金-大于或等于2000的所有雇员的详细资料.select * from empwhere (deptno=10 and job=M
4、ANAGER) or (deptno=20 and (job=CLERKor (job not in(MANAGER,CLERK) and sal=2000)-找出收取佣金的雇员的不同工作.select ename 姓名,job 工作,comm 佣金from empwhere comm is not null-找出不收取佣金或收取的佣金低于是200的雇员select * from emp where comm is null or comm is not null and comm31-查询每个工种的人数.并按工种的人数排序select job, count(*) from empgroup
5、by joborder by count(*)-查询MANAGER的平均工资。select job,avg(sal) from empgroup by jobhaving job=MANAGER-查询每个工种的人数,和平均工资,并按一均工资进行排序.select job, count(job), avg(sal)from empgroup by joborder by avg(sal)-查询部门名称是SALES的所有员工.select emp.* from emp, deptwhere emp.deptno=dept.deptno and dname=SALES-查询部门名称是SALES的所有
6、员工的平均工资.select dname, avg(sal) from emp, deptwhere emp.deptno=dept.deptno and sal in(select sal from emp, deptwhere emp.deptno=dept.deptno and dname=SALES)group by dname/select * from emp-查询每个工种中雇员的最高薪金;select job, max(sal) from emp group by job-查询职务是经理的雇员的最低薪金;select job, min(sal) 最低薪金 from empgrou
7、p by jobhaving job=MANAGER-查询平均工资高于1500的所有职务的名称和平均工资金额.select job, avg(sal) from empgroup by job having avg(sal)1500-查询所有雇员的实发工资(薪金+佣金)select ename, nvl(sal+comm,sal) as 实发工资 from emp-找出同名的后，进行删除，每个重名只留一个delete from ( select a.*,row_number() over(ORDER BY empno DESC) AS rn from tb_emp a where ename
8、in( select ename from tb_emp a group by ename having count(*)1)where rn1-查询每个员工的详细信息.包括部门编号,部门名称及部门所在城市.select * from emp,deptwhere emp.deptno=dept.deptno-按部门分组,查询出部门名称,及部门中员工的平均工资.select dname, avg(sal) from emp,deptwhere emp.deptno=dept.deptno group by dname-查询所有员工中工资数在前五的员工详细信息。并从高到低进行排序。select *
9、 from (select a.*,row_number() over(order by sal desc) as rn from emp a) where rn2000)-找到同名的所有员工select * from empwhere exists(select ename from emp a group by ename having count(*)1)-查询出工次级别是4级的所有员工。（salgrade为员工级别表）select * from salgradeselect ename,sal, case when 700sal and sal1200 then 1 when 1201sal and sal1400 then 2 when 1401sal and sal2000 then 3 when 2001sal and sal3000 then 4 else 5 end salgradefrom emporder by salgradeselect ename,sal, case when losalsal and salhisal then grade end grafrom emp,salgradeorder by gra
