子查询练习

练习:
    查询s_emp表每个部门的最大工资数,并且显示出这个最大工资的员工名字以及该部门的名字和该部门所属区域,并且使用部门编号进行排序
    1.查询每个部门最大的工资数，及员工名字    
        select s2.dept_id,s2.last_name,max(s1.salary)
        from s_emp s1,s_emp s2
        group by s2.dept_id,s2.last_name,s2.salary,s1.dept_id
        having s2.salary=max(s1.salary) and s2.dept_id=s1.dept_id
        order by s2.dept_id;
    2.查询部门名字
        select s2.dept_id,s2.last_name,max(s1.salary),sd.name
        from s_emp s1,s_emp s2,s_dept sd
        where s2.dept_id=sd.id
        group by s2.dept_id,s2.last_name,s2.salary,s1.dept_id,sd.name
        having s2.salary=max(s1.salary) and s2.dept_id=s1.dept_id
        order by s2.dept_id;
    3.查询所属区域id
        select s2.dept_id,s2.last_name,max(s1.salary),sd.name,sr.id
        from s_emp s1,s_emp s2,s_dept sd,s_region sr
        where s2.dept_id=sd.id and sd.region_id=sr.id
        group by s2.dept_id,s2.last_name,s2.salary,s1.dept_id,sd.name,sr.id
        having s2.salary=max(s1.salary) and s2.dept_id=s1.dept_id
        order by s2.dept_id;
练习:注意:(可以把子查询的结果作为一张表来使用)
1.查询平均工资比 41号部门的平均工资 高的部门中员工的信息,并且显示出当前部门的平均工资        
    (1) 查询平均工资比 41号部门的平均工资 高的部门中员工的信息
        select last_name,salary,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
            )
        )
    (2) 查询部门的平均工资
        select dept_id,avg(salary) avgsalary
        from s_emp
        group by dept_id

    (3) 显示当前部门的平均工资
        select t.last_name,t.salary,t.dept_id,d.avgsalary
        from
            (select last_name,salary,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
                )
              )
              )t,
            (select dept_id,avg(salary) avgsalary
            from s_emp
            group by dept_id)d
        where t.dept_id=d.dept_id(+);


2.查询平均工资比 41号部门的平均工资 高的部门中员工的信息,并且显示出当前部门的平均工资,同时显示出部门的名字
    (1).查询员工信息并显示平均工资
    select t.last_name,t.salary,t.dept_id,d.avgsalary
    from
        (select last_name,salary,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
            )
          )
          )t,
        (select dept_id,avg(salary) avgsalary
        from s_emp
        group by dept_id)d
    where t.dept_id=d.dept_id(+);
    (2).显示部门名字
    select t.last_name,t.salary,t.dept_id,d.avgsalary,sd.name
    from
        (select last_name,salary,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
            )
          )
          )t,
        (select dept_id,avg(salary) avgsalary
        from s_emp
        group by dept_id)d,s_dept sd
    where t.dept_id=d.dept_id(+) and t.dept_id=sd.id;

3.查询员工信息,这些员工的工资要比自己所在部门的平均工资高
    (1).查询部门平均工资
        select avg(salary)
        from s_emp
        group by dept_id;    
    (2).查询员工信息
        select se.last_name,se.salary,se.dept_id,a.avgsalary
        from s_emp se,(select avg(salary) avgsalary,dept_id
            from s_emp
            group by dept_id)a
        where se.salary>a.avgsalary and se.dept_id=a.dept_id
        order by se.dept_id;
4.查询员工信息,这些员工的工资要比自己所在部门的平均工资高,同时显示部门的名称以及所在地区
    (1).查询员工信息
        select se.last_name,se.salary,se.dept_id,a.avgsalary
        from s_emp se,(select avg(salary) avgsalary,dept_id
            from s_emp
            group by dept_id)a
        where se.salary>a.avgsalary and se.dept_id=a.dept_id
        order by se.dept_id;
    (2).显示部门名称以及所在地区
        select se.last_name,se.salary,se.dept_id,a.avgsalary,sd.name,sr.id
        from s_emp se,(select avg(salary) avgsalary,dept_id
            from s_emp
            group by dept_id)a,s_dept sd,s_region sr
        where se.salary>a.avgsalary and se.dept_id=a.dept_id and se.dept_id=sd.id and sd.region_id=sr.id
        order by se.dept_id;

5.查询工资比 Ngao所在部门平均工资 要高的员工信息,同时这个员工所在部门的平均工资 也要 比Ngao所在部门的平均工资要高
    (1).查询Ngao所在部门的平均工资
        select avg(salary)
        from s_emp
        where dept_id=(
            select dept_id
            from s_emp
            where last_name='Ngao'
        )
    (2).查询员工信息
        select last_name,salary,dept_id,a.avgsalary avgsalary
        from s_emp,(
        select avg(salary) avgsalary
        from s_emp
        where dept_id=(
            select dept_id
            from s_emp
            where last_name='Ngao'
        ))a
        where salary>a.avgsalary
    (3).员工所在部门的平均工资
        select avg(salary),dept_id
        from s_emp
        group by dept_id;
    (4).查询员工信息和平均工资
        select t.last_name,t.salary,t.dept_id
        from (
            select last_name,salary,dept_id,a.avgsalary avgsalary
            from s_emp,(
            select avg(salary) avgsalary
            from s_emp
            where dept_id=(
                select dept_id
                from s_emp
                where last_name='Ngao'
            ))a
            where salary>a.avgsalary
             )t,(
            select avg(salary) avgsalary,dept_id
            from s_emp
            group by dept_id
             )s
        where t.dept_id=s.dept_id and s.avgsalary>t.avgsalary;
6.查询工资比 Ngao所在部门平均工资 要高的员工信息,同时这个员工所在部门的平均工资 也要 比Ngao所在部门的平均工资要高,显示当前部门的平均工资
    在第五题的基础上添加：s.avgsalary
    select t.last_name,t.salary,t.dept_id,s.avgsalary
    from (
        select last_name,salary,dept_id,a.avgsalary avgsalary
        from s_emp,(
        select avg(salary) avgsalary
        from s_emp
        where dept_id=(
            select dept_id
            from s_emp
            where last_name='Ngao'
        ))a
        where salary>a.avgsalary
         )t,(
        select avg(salary) avgsalary,dept_id
        from s_emp
        group by dept_id
         )s
    where t.dept_id=s.dept_id and s.avgsalary>t.avgsalary;

7.查询工资比 Ngao所在部门平均工资 要高的员工信息,同时这个员工所在部门的平均工资 也要 比Ngao所在部门的平均工资要高,显示当前部门的平均工资以及部门的名字和所在地区
    在6的基础上加：
    select m.last_name,m.salary,m.dept_id,m.avgsalary,sd.name,sr.name
    from (
        select t.last_name last_name,t.salary salary,t.dept_id dept_id,s.avgsalary avgsalary
        from (
            select last_name,salary,dept_id,a.avgsalary avgsalary
            from s_emp,(
            select avg(salary) avgsalary
            from s_emp
            where dept_id=(
                select dept_id
                from s_emp
                where last_name='Ngao'
            ))a
            where salary>a.avgsalary
             )t,(
            select avg(salary) avgsalary,dept_id
            from s_emp
            group by dept_id
             )s
          where t.dept_id=s.dept_id and s.avgsalary>t.avgsalary)m,
          s_dept sd,
          s_region sr
    where m.dept_id=sd.id and sd.region_id=sr.id;