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)>(selectmin(salary) from employees where department_id=20);
2.显示所有雇员的ename和 hiredate，他们在他们的经理之前进入本公司，
连同他们的经理的名字和受雇日期一起显示。
select e1.first_nameyuangong,e1.hire_date,e2.first_name jingli,e2.hire_date
from employees e1,employees e2
where e1.hire_date<e2.hire_date ande1.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
wheree1.department_id=e2.department_id ande1.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
one1.department_id=e2.department_id
wheree1.salary<e2.avg_sal
select first_name,salary
from employees e1
where salary <
(selectavg(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用户里面)
selectd.deptno,d.avg_sal,s.grade
from salgrade s,(selectavg(sal) avg_sal
from emp
group by deptno) d
where d.avg_sal betweens.losal and s.hisal
7.请打印公司入职最早的五个员工
selecte3.last_name,e3.hire_date
from(
select e2.*,rownum r
from
(select e1.*
from employees e1
order by hire_date) e2
) e3
wheree3.r<=5
方法二
select e3.last_name,e3.hire_date
from (select e1.*
from employees e1
order by hire_date) e3
whererownum<=5
8.打印公司入职最早的第五个到第十个
方法一：
selecte3.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<=10and e3.r>=5
方法二
selecte3.last_name,e3.hire_date
from(
select e2.*,rownum r
from
(select e1.*
from employees e1
order by hire_date) e2 whererownum <=10) e3
where r>=5
9. 求每个部门中薪水最高的前两个人的名字
方法一：
步骤一：(得到按部门,薪水降序,行号)
selecte2.department_id,e2.last_name,e2.salary,rownum r
from
(selectdepartment_id,last_name,salary
fromemployees
order bydepartment_id,salary desc)e2
步骤二:(得到部门最高工资的最小行号)
selectdepartment_id,min(r)
from (selecte2.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 bydepartment_id
步骤三:(得到工资最高的两个员工的姓名)
a.先创建两个视图，简化代码:
create viewe_num AS
selecte2.department_id,e2.last_name,e2.salary,rownum r
from
(selectdepartment_id,last_name,salary
fromemployees
order bydepartment_id,salary desc)e2
create viewe_min_num AS
selectdepartment_id,min(r) min_r
from (selecte2.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 bydepartment_id
b.找到符合条件的员工的名字
selecte1.department_id ,e1.last_name,e1.salary
from e_nume1,e_min_num e2
wheree1.r>=e2.min_r ande1.r<=e2.min_r+1
方法二：
selecte1.department_id,e1.last_name,e1.salary
from (selectdepartment_id,last_name,salary,
rank() over (partition by department_id order by salary desc)num
from employees) e1
wheree1.num<=2
10. 以如下格式显示工资:(当工资大于等于1500 等级显示A,小于1500显示B)
ename sal grade
WARD 1250 B
KING 5000 A
selectlast_name,salary,decode(sign(salary-1500),1,'A','B') "grade"
from employees
