oracle常用网站

oracle-base 官方文档   http://www.oracle-base.com/

                       http://www.oracle.com/pls/db111/portal.all_books#index-SQL

小练习：

1.显示每个部门其最低薪水 大于部门编号为 20 的最低薪水的部门号和最低薪水

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

2.显示所有雇员的ename和 hiredate，他们在他们的经理之前进入本公司，
连同他们的经理的名字和受雇日期一起显示。
select  e1.first_name yuangong,e1.hire_date,e2.first_name jingli,e2.hire_date
from employees e1,employees e2
where e1.hire_date<e2.hire_date and e1.manager_id=e2.employee_id
/

3.写一个查询显示每个部门的名字、人数和部门中所有雇员的平均薪水。四舍五入薪水到两位小数。

select d.department_name,count(*) renshu,round(avg(salary),2)
from employees e,departments d
where e.department_id=d.department_id
group by department_name;

 
4.打印出工资低于本部门平均工资的员工的姓名,工资.

  方法一：
   select first_name,salary
   from employees e1,
         (select department_id,avg(salary) avg_sal
          from employees
          group by department_id) e2
   where e1.department_id=e2.department_id and e1.salary<e2.avg_sal;
  方法二：
   select firt_name,salary
   from employees e1 join
         (select department_id,avg(salary) avg_sal
          from employees
          group by department_id) e2
    on e1.department_id=e2.department_id
   where e1.salary<e2.avg_sal

  select first_name,salary
  from employees e1
  where salary <
    (select avg(salary)
     from employees e2
     where e2.department_id = e1.department_id
    )

5.求平均薪水最高的部门的部门名称
  select e1.department_id
  from (select *
        from (select avg(salary) avg_sal,department_id
                from employees e1
                group by department_id
                order by avg_sal desc)
        where rownum=1)e2
  where e1.department_id = e2.department_id
 
6.求部门平均薪水的等级(scott用户里面)
   select d.deptno,d.avg_sal,s.grade
   from salgrade s,(select avg(sal) avg_sal
                    from emp
                    group by deptno) d
   where d.avg_sal between s.losal and s.hisal

7.请打印公司入职最早的五个员工
  
   select e3.last_name,e3.hire_date
   from(
   select e2.*,rownum r
   from
   (select e1.*
   from employees e1
   order by hire_date) e2
   ) e3
   where e3.r<=5
方法二
 select e3.last_name,e3.hire_date
   from (select e1.*
   from employees e1
   order by hire_date) e3
   where rownum<=5

8.打印公司入职最早的第五个到第十个
   方法一：
   select e3.last_name,e3.hire_date
   from(
   select e2.*,rownum r
   from
   (select e1.*
   from employees e1
   order by hire_date) e2
   ) e3
   where e3.r<=10 and e3.r>=5
  
   方法二
   select e3.last_name,e3.hire_date
   from(
   select e2.*,rownum r
   from
   (select e1.*
   from employees e1
   order by hire_date) e2 where rownum <=10) e3
   where r>=5

9. 求每个部门中薪水最高的前两个人的名字
   
    方法一：
    步骤一：(得到按部门,薪水降序,行号)
    select e2.department_id,e2.last_name,e2.salary,rownum r
    from
    (select department_id,last_name,salary
    from employees
    order by department_id,salary desc)e2
    步骤二:(得到部门最高工资的最小行号)
    select department_id,min(r)
    from (select e2.department_id,e2.last_name,e2.salary,rownum r
          from
               (selct department_id,last_name,salary
                from employees
                order by department_id,salary desc) e2) e3
    group by department_id
    步骤三:(得到工资最高的两个员工的姓名)
    a.先创建两个视图，简化代码:
    create view e_num AS
    select e2.department_id,e2.last_name,e2.salary,rownum r
    from
    (select department_id,last_name,salary
    from employees
    order by department_id,salary desc)e2
   
    create view e_min_num AS
    select department_id,min(r) min_r
    from (select e2.department_id,e2.last_name,e2.salary,rownum r
          from
               (selct department_id,last_name,salary
                from employees
                order by department_id,salary desc) e2) e3
    group by department_id
   
    b.找到符合条件的员工的名字
    select e1.department_id ,e1.last_name,e1.salary
    from e_num e1,e_min_num e2
    where e1.r>=e2.min_r and e1.r<=e2.min_r+1
 
    方法二：
    select e1.department_id,e1.last_name,e1.salary
    from (select department_id,last_name,salary,
                 rank() over (partition by department_id order by salary desc) num
          from employees) e1
    where e1.num<=2
 
10. 以如下格式显示工资:(当工资大于等于1500 等级显示A,小于1500显示B)

    ename   sal     grade
   
    WARD    1250     B
    KING    5000     A

 select last_name,salary,decode(sign(salary-1500),1,'A','B') "grade"
from employees