ORACLE习题（三）

1. 查看部门平均工资大于32号部门平均工资的部门id

   select dept_id,avg(salary)
   from s_emp GROUP BY dept_id having avg(salary) >( select avg(salary)
   from s_emp where dept_id = 32 ) 
   

2. 查询工资大于Smith所在部门平均工资的员工的信息

   select last_name,salary
   from s_emp where salary>( select avg(salary)
   from s_emp where dept_id in( select dept_id
   from s_emp where last_name = 'Smith' )) 
   

3. 查看薪资高于Chang员工经理的经理薪资的员工信息

    select last_name,salary
   from s_emp 
   where salary>( select e3.salary
   from s_emp e1,s_emp e2,s_emp e3 where e1.manager_id = e2.id and e1.last_name = 'Chang' and e2.manager_id = e3.id )
   

4. 查看和Patel在同一个部门的员工的信息

   select last_name
   from s_emp 
   where dept_id in( select dept_id
   from s_emp 
   where last_name = 'Patel' )
   

5. 查询工资比Smith工资高的员工信息(员工信息包括:last_name,salary,dept_id)

   select Tid, last_name, salary
   from s_emp 
   where salary > ( select salary from s_emp where last_name = 'Smith' );
   

6. 查询平均工资比 41号部门的平均工资高的部门中员工的信息

   select last_name,dept_id
   from s_emp where dept_id in( select dept_id
   from s_emp GROUP BY dept_id having avg(salary)>( select avg(salary)
   from s_emp where dept_id = 41 )) 
   

7. 查询平均工资比 Ngao所在部门的平均工资高的部门中员工的信息

   select last_name
   from s_emp where dept_id in( select dept_id
   from s_emp GROUP BY dept_id having avg(salary)>( select avg(salary)
   from s_emp where dept_id=(select dept_id from s_emp where last_name='Ngao') ))
   

8. 查询平均工资比 41号部门的平均工资高的部门的平均工资

   select dept_id
   from s_emp GROUP BY dept_id having avg(salary)>( select avg(salary)
   from s_emp where dept_id = 41 ) 
   

9. 查询平均工资比 41号部门的平均工资高的部门中员工的信息,同时显示出部门的名字

   select e.id,e.last_name,d.name
   from s_emp e,s_dept d where dept_id in( select dept_id
   from s_emp GROUP BY dept_id having avg(salary)>( select avg(salary)
   from s_emp where dept_id = 41 )) and e.dept_id = d.id
   

10. 查询员工信息,这些员工的工资要比自己所在部门的平均工资高,同时显示部门的名称 以及所在地区

    select e1.id,e1.last_name,d.name,r.name
    from s_emp e1,( select avg(salary) avgSalary,dept_id
    from s_emp GROUP BY dept_id )e2,s_dept d,s_region r where e1.salary> e2.avgSalary and e1.dept_id = e2.dept_id and e1.dept_id = d.id and d.region_id = r.id
    

11. 查询工资比 Ngao所在部门平均工资要高的员工信息,同时这个员工所在部门的平均工 资也要 比Ngao所在部门的平均工资要高

    select last_name,dept_id,salary
    from s_emp
    where dept_id in
    (
    select dept_id
    from s_emp
    group by dept_id
    having avg(salary)>
    (
    select avg(salary)
    from s_emp
    where dept_id = 41
    )
    )
    and salary >
    (
    select avg(salary)
    from s_emp
    where dept_id =
    (
    select dept_id
    from s_emp
    where last_name = 'Ngao'
    )
    );
    

12. 查询工资比 Ngao所在部门平均工资 要高的员工信息,同时这个员工所在部门的平均工 资也要比Ngao所在部门的平均工资要高,显示当前部门的平均工资以及部门的名字和所 在地区,并且按员工工资升序排序

    select se.id,se.last_name,sm.avg,sd.name adname,sr.name srname
    from s_emp se,(select avg(salary) avg,dept_id
    from s_emp group by dept_id) sm,s_dept sd,s_region sr where se.salary>(select avg(salary)
    from s_emp where dept_id=(select dept_id
    from s_emp where last_name='Ngao')) and se.dept_id=sm.dept_id and sm.avg>(select avg(salary)
    from s_emp where dept_id=(select dept_id
    from s_emp where last_name='Ngao')) and se.dept_id=sd.id and sd.region_id=sr.id order by salary;