oracle例题

oracle scott用户下emp表

注意查询：
记得缺失的表用+

查询雇员姓名,工作,领导姓名及部门名称

select e1.ename,e1.job,(select e2.ename from emp e2 where e1.mgr = e2.empno ),d.dname from emp e1 ,dept d where e1.deptno = d.deptno;

查询每个部门中工资最高的雇员姓名,工作,工资,部门名称,最后按工资从高到低排序，工资相同的情况下按姓名排升序

select e.ename,e.job,e.sal,d.dname from emp e ,dept d where  e.sal = any(select max(sal) from emp group by deptno) and e.deptno = d.deptno 
order by e.sal desc,e.ename;

例题如下：
查询emp和dept表,产生笛卡尔积(多表行相乘)

select * from emp,dept;
``
加where条件过滤查询emp和dept表产生的笛卡尔积

```java
select * from emp e ,dept d where e.deptno = d.deptno;

查询emp和dept表,产生笛卡尔积(多表行相乘),并为表取别名

select * from emp e ,dept d ;

查询雇员姓名,所在部门编号和名称

select e.ename,e.deptno,d.dname from emp e ,dept d where e.deptno = d.deptno(+);

查询所有雇员姓名,工作,领导的姓名

select e1.ename,e1.job,e2.ename from emp e1 ,emp e2 where e1.mgr = e2.empno(+);

查询雇员姓名,工作,领导姓名及部门名称

select e1.ename,e1.job,(select e2.ename from emp e2 where e1.mgr = e2.empno ),d.dname from emp e1 ,dept d where e1.deptno = d.deptno;

查询雇员姓名,工作,工资及工资等级

select ename,job,sal ,case when sal >= 700 and sal <= 1200 then '1'  when sal >= 1201 and sal <= 1400 then '2' when sal >= 1401 and sal <= 2000 then '3' when sal >= 2001 and sal <= 3000  then '4' when sal >= 3001 and sal <= 9999 then '5'  end  from emp  ;

查询雇员姓名,工作,工资及工资等级,要求工资等级显示为A B C D E

select ename,job,sal ,case when sal > 700 and sal < 1200 then 'A' when sal > 1201 and sal <= 1400 then 'B' when sal >= 1401 and sal <= 2000 then 'C' when sal >= 2001 and sal <= 3000  then 'D' when sal >= 3001 and sal <= 9999 then 'E'  end from emp  ;

查询雇员姓名,年薪（薪水+奖金）,按年薪从高到低排序

select ename,12*(sal+nvl(comm,0)) s from emp order by s desc

查询每个部门中工资最高的雇员姓名,工作,工资,部门名称,最后按工资从高到低排序，工资相同的情况下按姓名排升序

select e.ename,e.job,e.sal,d.dname from emp e ,dept d where  e.sal = any(select max(sal) from emp group by deptno) and e.deptno = d.deptno 
order by e.sal desc,e.ename;

查询每个部门的部门编号和雇员数量

select d.deptno,count(e.deptno)  from emp e,dept d where d.deptno= e.deptno(+) group by  d.deptno;

求出每个部门的部门名和平均工资(保留2位小数，截断)

select dname,trunc(avg(nvl(sal,0)),2) from emp e ,dept d where e.deptno(+) = d.deptno group by d.dname;

按部门分组，并显示部门的名称，以及每个部门的员工数

select d.dname,count(e.empno) from emp e,dept d where e.deptno(+) = d.deptno group by d.dname;

要求显示平均工资大于2000的部门编号和平均工资(保留2位小数，截断)

select deptno,trunc(avg(sal),2)  from emp e group by deptno having trunc(avg(sal),2)>=2000;

显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资大于$1500，输出结果按月工资的合计升序排列

select job ,sum(sal) from emp where job != 'SALESMAN'   and sal>1500
group by job  order by sum(sal)

求出平均工资最高的部门名称

select dname from  (select dname,avg(nvl(sal,0)) from emp e ,dept d 
where d.deptno = e.deptno(+)   group by dname order by avg(nvl(sal,0))  desc)
where rownum = 1

要求查询出比7654工资要高的全部雇员的信息

select * from emp where sal >(select sal from emp where empno = 7654)

要求查询工资比7654高，与7788从事相同工作的全部雇员信息

select * from emp where sal >=(select sal from emp where empno = 7654)
 and job = (select job from emp where empno = 7788) 

查询出工资最低的雇员姓名，工作，工资

select  ename, job, sal  from emp 
where sal = (select min(sal) from emp )

查询出各部门工资最低的雇员姓名，工作，工资

select ename,job,sal  from emp where sal = any(select  min(sal) from emp group by deptno) 

要求查询出部门名称，部门的员工数，部门的平均工资，部门的最低收入雇员姓名，要求显示所有部门名，如果该部门没有任何员工，则员工书和平均工资需显示0，员工姓名显示null即可。

select d.dname,count(e.empno) ,avg(nvl(sal,0)),ha
 from emp e,dept d ,(select ename ha,deptno he from emp where sal = any(select min(sal) from emp group by deptno)) f where e.deptno(+) = d.deptno and f.he(+) = d.deptno group by d.dname,ha;