假设有如下三张表
1.部门表
2.员工表
3.职位表
结合以上三张表,可有如下查询
--分组统计各部门下平均工资大于3000的员工的平均工资
select deptId,avg(salary) 平均工资
from employee
group by deptid
having avg(salary)>3000;
---统计各部门下平均工资大于5000的部门
select t.avgsal,t.did,d.deptname from
(select avg(salary) avgsal,deptid did
from employee
group by deptid
having avg(salary)>4000) t,dept d
where t.did=d.deptid;
--算出每个职位的员工数量和最低工资
select j.jobname,t.c,t.m from
(select jobid,count(empid) c,min(salary) m
from employee
group by jobid) t,jobs j
where t.jobid=j.jobid;
select * from jobs t;
--列出员工表中每个部门的员工数和部门编号
select count(*),deptid
from employee
group by deptid;
--得到工资大于自己部门平均工资的员工信息
select t.*,e.empname,e.salary
from
( select deptid,avg(salary) avgsal
from employee
group by deptId ) t,employee e
where e.salary>t.avgsal
and e.deptid=t.deptid;
insert into employee (hiredate) values(to_date('2014-02-14','yyyy-mm-dd')) where empid=1;
--查询入职时间比自己部门领导入职时间早的员工信息
select e.*
from
(select empname,manager,hiredate,empid
from employee
where manager=empname) t,employee e
where t.manager=e.manager and e.hiredate<t.hiredate
--列出员工表中每个部门的员工数(员工数必须大于3)和部门名称
select deptname,t.* from
(select deptid,count(*)
from employee
group by deptid
having count(*)>2) t,dept d
where t.deptid=d.deptid;
--以职位分组,找出平均工资最高的两位职位
select p.* from
(select t.jobid td,t.av ta,j.jobname jj from
(select jobid,avg(salary) av
from employee
group by jobid ) t,jobs j
where t.jobid=j.jobid
order by ta desc) p
where rownum<=2 order by p.ta desc;
--找出不在部门20,且比部门20中任何一个人工资都高的员工姓名,部门名称
select t.*,deptname from
(select salary,deptid,empname
from employee
where deptid not in 5 and salary>
(select max(salary) from employee where deptid=5)) t,dept d
where d.deptid=t.deptid;
--得到平均工资大于 2000 的工作职种
select t.*,j.jobname from
(select avg(salary) avgsal,jobid
from employee
group by jobid) t,jobs j
where j.jobid=t.jobid and t.avgsal>4000 order by t.avgsal desc;
--分部门得到工资大于5000的所有员工的平均工资,并且平均工资还要大于5000
select avg(salary),deptid from
(select salary ,deptid from employee where salary>5000)
group by deptId
having avg(salary)>5000
--得到每个月工资总数最少的那个部门的部门编号,部门名称,部门位置
select t.did,d.deptname,d.deptloc from
(select sum(salary) susal,deptid did
from employee
group by deptid
order by susal) t,dept d
where rownum=1 and t.did=d.deptid;
--查找出部门 1 和部门 5 中,工资最高第 2名到工资第 4 名的员工的员工名字,部门名字,部门位置
select p.*,d.deptname,d.deptloc from
(select t.*,t.deptid tid from
(select deptid,empname,salary
from employee
where deptid in(1,5)
order by salary desc) t
where rownum<=4) p,dept d
where p.tid=d.deptid and rownum<=2;
--查找出收入,下级比自己上级还高的员工编号,员工名字,员工收入
select t.salary 上司工资,e.empname,e.salary from
(select empid,empname,salary
from employee
where empname=manager) t,employee e
where e.salary>t.salary
and e.manager=t.empname;
--查找出工资不为6000的员工的员工名字,部门名字,部门位置
select employee.*,dept.deptname,dept.deptloc
from employee,dept
where salary not in
(select salary from employee where salary=6000)
and employee.deptid=dept.deptid;
--查找出不属于任何部门的员工
select * from employee where deptid is null;