Oracle 典型编程练习题

1.对于emp中最低工资小于5000的部门，列出job为’IT_PROG’的员工的部门号，
最低工资，最高工资

select deptno,min(sal),max(sal)
 from emp
 where job like 'IT_PROG'
 group by deptno
 having min(sal) <5000

select deptno,min(sal),max(sal) 
      from emp where deptno in
  (select deptno
         from emp
         group by deptno
         having min(sal) < 5000) and job like 'IT_PROG'
   group by deptno; 

3.列出’Jason’所在部门中每个员工的姓名与部门号

 select ename,deptno
       from emp
       where deptno =
       (select deptno 
               from emp 
               where ename like 'Jason'
       );

4. 列出emp中工作为’IT_PROG’的员工的姓名，工作，部门号，部门名

select ename,emp.job,emp.deptno,dname
   from emp join dept on emp.deptno=dept.deptno
   where job like 'IT_PROG';

select ename,job,dept,deptno,dname
 from emp,dept
 where dept.deptno=emp.deptno
 and job ='IT_PROG'

5. 对于emp中有管理者的员工，列出姓名，管理者姓名

select e1.ename as 员工姓名,e2.ename as 管理者姓名
 from emp e1,emp e2 
 where e1.mgrno is not null and e1.mgrno=e2.empno;

select a.ename as 姓名,b.ename as 管理者
 from emp a,emp b 
 where a.mgrno is not null
       and a.mgrno=b.empno

6.对于dept表中，列出所有部门名，部门号，
同时列出各部门工作为’IT_PROG’的员工名与工作

select dname,dept.deptno,emp.ename,emp.job
 from dept join emp on emp.deptno = dept.deptno
 where job like 'IT_PROG';

7.对于工资高于本部门平均水平的员工，
列出部门号 ，姓名，工资，按部门号排序

select e1.deptno as 部门号,
        e1.ename as 员工姓名,
        e1.sal as 工资
  from emp  e1
  where e1.sal >
   (select avg(sal)
    from emp  e2
    where e1.deptno = e2.deptno)
   order by e1.deptno;

8.对于emp，
列出各个部门中 工资高于本部门平均水平的员工数和部门号，
按部门号排序

select deptno,count(*) as 高于本部门平均水平的员工数
 from emp e1
 where sal>
    (select avg(sal)
     from emp e2 
     where e1.deptno = e2.deptno
      group by deptno)
 group by deptno
 order by deptno;

9.对于emp中工资高于本部门平均水平，
人数多与1人的，列出部门号，人数，按部门号排序

select deptno,count(*) as quantity
 from emp e1
 where sal >
    (select avg(sal)
     from emp e2 
     where e1.deptno = e2.deptno
      group by deptno)
 having count(*) > 1     
 group by deptno
 order by deptno;

10.查询出emp 表中 sal 按升序排列后10 - 20 的员工信息
注意，这也是与mysql不同的一点:查询伪劣

select *
 from( 
  select rownum rn,empno,ename,job,sal,deptno
      from(
        select empno,ename,job,sal,deptno 
         from  emp
         order by sal
      )
 ) 
where rn <=20 and rn>=10;

11. oracle 不等于

select *
 from emp
 where sal < any(
       select sal
        from emp 
       where job='IT_PROG'
 )
 and job <> 'IT_PROG'

12.查询最低工资大于50号部门最低工资的 部门id 和其最低工资

select department_id,min(salary)
       from employees
       group by department_id
      having min(salary) > (
            select min(salary) from employees where department_id=50
      )  

13.部门最高工资比1万高的部门

select max(salary)
from  employees 
group by department_id
having max(salary) > 10000

14.显示各部门平均工资的最大值

select max(avg(salary)) as highest
from employees
group by department_id

15.公司工资最少的人

select last_name,job_id,salary
       from employees
      where salary =(
            select min(salary) from employees
      )

16.查询最低工资大于50号部门最低工资的 部门id 和其最低工资

select department_id,min(salary)
       from employees
       group by department_id
      having min(salary) > (
            select min(salary) from employees where department_id=50
      )