oracle_select大全.docx

-1、列出至少有一个雇员的所有部门select deptno from emp group by deptno having count(deptno)1select e.deptno,d.dname from emp e,dept d where e.deptno=d.deptno group by e.deptno,d.dname having count(e.deptno)0-2、列出薪金比SMITH多的所有雇员SELECT ename,sal, deptno FROM emp WHERE sal(SELECT sal FROM emp WHERE ename=SMITH)-3、列出所有雇员的姓名及其直接上级的姓名SELECT e.ename as 职工, m.ename as 经理 FROM emp e left join emp m on (e.mgr=m.empno)SELECT e.ename as 职工, m.ename as 经理 FROM emp e left join emp m on (e.mgr=m.empno) group by m.ename,e.ename-4、列出入职日期早于其直接上级的所有雇员SELECT e.ename as 职工, m.ename as 经理 FROM emp e left join emp m on (e.mgr=m.empno) where e.hiredate1500-8、列出从事“SALES”（销售）工作的雇员的姓名，假定不知道销售部的部门编号select ename, from emp where dept=(select deptno from dept where dname=SALES)SELECT ename FROM emp where job=SALESMAN-9、列出薪金高于公司平均水平的所有雇员select ename, deptno from emp where sal(select avg(sal) from emp )-10、列出与“SCOTT”从事相同工作的所有雇员select deptno,ename from emp where job=(select job from emp where ename=SCOTT)-11、列出某些雇员的姓名和薪金，条件是他们的薪金等于部门30中任何一个雇员的薪金SELECT ename,sal from emp where sal in(select sal from emp where deptno=30)SELECT ename,sal from emp where sal in(select sal from emp where deptno=30) and deptno30-12、列出某些雇员的姓名和薪金，条件是他们的薪金高于部门30中所有雇员的薪金SELECT ename,sal from emp where sal all(select sal from emp where deptno=30)SELECT ename,sal from emp where sal all(select sal from emp where deptno=30) and deptno30-13、列出每个部门的信息以及该部门中雇员的数量select d.deptno,d.dname,d.loc ,count(e.deptno)from dept d left joinemp e on(d.deptno=e.deptno) group by d.deptno ,d.dname,d.locorder by d.deptno asc-14、列出所有雇员的雇员名称、部门名称和薪金select e.ename,d.dname,e.sal from emp e left join dept d on (e.deptno=d.deptno) select ename,dname,sal from emp natural join dept-15、列出从事同一种工作但属于不同部门的雇员的不同组合select job,deptno from emp group by job,deptno-16、列出分配有雇员数量的所有部门的详细信息，即使是分配有0个雇员select d.deptno,d.dname,d.loc ,count(e.deptno) from dept d left join emp e on (d.deptno=e.deptno) group by d.deptno,d.dname,d.loc having ( select count(e.deptno) from emp e )=0-17、列出各种类别工作的最低工资select job,min(sal) from emp group by job-18、列出各个部门的MANAGER（经理）的最低薪金select job,min(sal) from emp where job= group by job-19、列出按年薪排序的所有雇员的年薪select ename,(sal+nvl(comm,0)*12 as yearpay from emp order by yearpay desc -20、列出薪金水平处于第四位的雇员select sal,ename from emp order by sal desc-1、选择部门30中的雇员select ename,sal from emp where deptno=30 order by sal desc -2、列出所有办事员的姓名、编号和部门select e.ename,e.empno ,d.dname ,e.job from emp e left join dept d on (e.deptno=d.deptno ) group by d.dname, e.ename,e.empno ,e.job having e.job=CLERK-3、找出佣金高于薪金的雇员select * from emp where commsal-4、找出佣金高于薪金60%的雇员select * from emp where (1+0.6)*commsal-5、找出部门10中所有经理和部门20中的所有办事员的详细资料select e.ename,e.empno ,d.dname ,e.job from emp e left join dept d on (e.deptno=d.deptno ) where e.deptno in(10,20)group by d.dname, e.ename,e.empno ,e.jobhaving e.job=CLERK-6、找出部门10中所有经理、部门20中所有办事员，既不是经理又不是办事员但其薪金=2000的所有雇员的详细资料-7、找出收取佣金的雇员的不同工作select deptno,ename, comm from emp group by deptno,ename,comm having comm is not null-8、找出不收取佣金或收取的佣金低于100的雇员select deptno,ename, comm from emp group by deptno,ename,comm having (comm is not null and comm25-11、显示只有首字母大写的所有雇员的姓名select deptno,ename from emp where substr(ename,1,1)=Upper(substr(ename,1,1)select deptno,ename from emp where length(ename)=6-12、显示正好为6个字符的雇员姓名select deptno,ename from emp where length(ename)=6 -13、显示不带有R的雇员姓名select ename from emp where ename not like %R%-14、显示所有雇员的姓名的前三个字符select substr(ename,1,3) from emp-15、显示所有雇员的姓名，用a替换所有Aselect replace(ename,A,a) from emp-16、显示所有雇员的姓名以及满10年服务年限的日期select ename,hiredate,add_months(hiredate,10*12) from emp-17、显示雇员的详细资料，按姓名排序select * from emp order by ename asc-18、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面select empno,ename,job,mgr,hiredate,sal ,comm,deptno ,(sysdate-hiredate)/365 from emp order by (sysdate-hiredate)/365 desc-19、显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪金升序select ename,job,sal from emp order by job desc,sal asc-20、显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，将最早年份的项目排在最前面select ename,to_char(hiredate,YYYY) year1,to_char(hiredate,MON) month1 from emp order by year1 asc,month1-21、显示在一个月为30天的情况下所有雇员的日薪金select round(sal/30,2) as daypay from emp-22、找出在（任何年份的）2月受聘的所有雇员select * from emp where hiredate like %-2月%-23、对于每个雇员，显示其加入公司的天数select round(sysdate-hiredate) from emp-24、显示姓名字段的任何位置，包含 A 的所有雇员的姓名select ename from emp where ename like %A%-25、以年、月和日显示