Oracle表连接与子查询示例

--把雇员按部门分组， 求最高薪水， 部门号， 过滤掉名字中第二个字母是'A'的， 要求分组后的平均薪水

>1500， 按照部门编号倒序排列

--求每个人的名字和他的经理人的名字，用外连接把KING也取出来

--哪些人的薪水在部门的平均薪水之上

--求部门中哪些人的薪水最高

--求部门平均薪水的等级

--求部门平均的薪水等级

--雇员中有哪些人是经理人

--不准用组函数,求薪水的最高值(面试题)

--求平均薪水最高的部门的部门编号

--求平均薪水最高的部门的部门名称

--求平均薪水的等级最低的部门的部门名称

--求部门经理人中平均薪水最低的部门名称 (思考题)

--求比普通员工的最高薪水还要高的经理人名称

--求薪水最高的前5名雇员

--求薪水最高的第6到第10名雇员(重点掌握)

--练习: 求最后入职的5名员工

--求每个部门中薪水最高的前两名雇员

--面试题: 比较效率

--求分段显示薪水的个数
如：
scale  total
<800  0
800-1000 2
1001-2000 3
2001-5000 6
>5000  8

或者显示成为
800-1000 1001-2000 2000-5000
2  3  6

或者显示成为
DEPTNO   800-2000  2001-5000
------ ---------- ----------
    30          5          1
    20          2          3
    10          1          2
--

========================第一份答案=======================

--把雇员按部门分组， 求最高薪水， 部门号， 过滤掉名字中第二个字母是'A'的，
要求分组后的平均薪水>1500， 按照部门编号倒序排列

select e1.ename, e2.ename
from emp e1 left join emp e2 on (e1.mgr = e2.empno)

--哪些人的薪水在部门的平均薪水之上

select ename, sal
from emp join
  (select deptno, max(sal) max_sal
   from emp
   group by deptno) t
on (emp.deptno = t.deptno and emp.sal = t.max_sal)

 

--求部门平均薪水的等级

select deptno, avg_sal, grade
from salgrade s join
  (select deptno, avg(sal) avg_sal
   from emp
   group by deptno) t
on (t.avg_sal between s.losal and s.hisal)

sql 代码

 

--求部门平均的薪水等级

select ename from emp where empno in (select distinct mgr from emp)

 

--不准用组函数,求薪水的最高值(面试题)

sql 代码

select ename from emp where empno not in
(select distinct e1.empno from emp e1 join emp e2 on (e1.sal < e2.sal))

排序，取第一个

--求平均薪水最高的部门的部门编号

select deptno from
(select deptno, avg(sal) avg_sal from emp group by deptno) t

where avg_sal =

(select max(avg_sal) from
(select deptno, avg(sal) avg_sal from emp group by deptno) t)
select max(avg(sal)) from emp group by deptno －－组函数嵌套

--求平均薪水最高的部门的部门名称

select dname from dept where deptno =
(select deptno from
  (select deptno, avg(sal) avg_sal from emp group by deptno) t

  where avg_sal =

  (select max(avg_sal) from
    (select deptno, avg(sal) avg_sal from emp group by deptno) t)
)

 

--求平均薪水的等级最低的部门的部门名称

select dept.deptno,dname  from
   (
   select deptno, avg_sal, grade from salgrade s join
           (select deptno, avg(sal) avg_sal from emp group by deptno) t
   on (t.avg_sal between s.losal and s.hisal)
   ) t1 join dept on
(t1.deptno = dept.deptno)
where grade =
(select min(grade) from
   (
   select deptno, avg_sal, grade from salgrade s join
           (select deptno, avg(sal) avg_sal from emp group by deptno) t
   on (t.avg_sal between s.losal and s.hisal)
   )
)

reate view v$_dept_info as
select deptno, avg_sal, grade from salgrade s join
           (select deptno, avg(sal) avg_sal from emp group by deptno) t
   on (t.avg_sal between s.losal and s.hisal)

elect deptno from v$_dept_info where grade = (select min(grade) from v$_dept_info);

 

--求部门经理人中平均薪水最低的部门名称 (思考题)
//....

--求比普通员工的最高薪水还要高的经理人名称

sql 代码

 select empno, ename, sal from emp where sal >
 (
    select max(sal) from
            (select empno,ename, sal
                    from emp where empno not in (select distinct nvl(mgr, -1) from emp ))
 ) and empno in (select distinct mgr from emp)

select distinct mgr from emp where mgr is not null

 

--求薪水最高的前5名雇员

--求薪水最高的第6到第10名雇员(重点掌握)

select ename, sal from
(
  select ename, sal, rownum r from
    (
      select ename, sal from emp order by sal desc
    )
  where rownum <=10
)
where r > 5 and r <= 10

 

--求每个部门中薪水最高的前两名雇员 

select deptno, ename, sal from emp order by deptno, sal desc

select deptno, ename, sal , rownum r from
(select deptno, ename, sal from emp order by deptno, sal desc);

create view v as select deptno, ename, sal , rownum r from
(select deptno, ename, sal from emp order by deptno, sal desc);

select deptno, min(r) min_r from v group by deptno;

 select v.deptno, ename, sal , r from v join
 (
 select deptno, min(r) min_r from v group by deptno
 ) t
 on (v.deptno = t.deptno and v.r >= t.min_r and v.r <= t.min_r + 1)
 order by v.deptno, sal desc;

--练习: 求最后入职的5名员工

select ename, hiredate from
(
select ename ,hiredate from emp order by hiredate desc
)
where rownum <= 5

 

 

--面试题: 比较效率

select * from emp where deptno = 10 and ename like '%A%';--这种是最高的
select * from emp where ename like '%A%' and deptno = 10;

 

--求分段显示薪水的个数
如：

scale      total
<800  0
801-1000 2
1001-2000 3
2001-5000 6
>5000  8

select '<800' as scale , count(*) as total from emp where sal < 800
union
select '800-1000' as scale , count(*) as total from emp where sal between 800 and 1000
union
select '2000-5000' as scale , count(*) as total from emp where sal between 2000 and 5000;
...

 

或者显示成为
800-1000 1001-2000 2000-5000
2  3  6

sql 代码

 select * from
 ( select count(*) as "800-1000" from emp where sal between 800 and 1000 )
 ,
 ( select count(*) as "1001-2000" from emp where sal between 1001 and 2000 );
...

或者显示成为
DEPTNO   800-2000  2001-5000
------ ---------- ----------
    30          5          1
    20          2          3
    10          1          2

sql 代码

select t1.deptno, "800-2000", "2001-5000" from
(
select deptno , count(*) as "800-2000" from emp where sal between 800 and 2000
group by deptno
) t1
join
(
select deptno , count(*) as "2001-5000" from emp where sal between 2001 and 5000
group by deptno
) t2
on
t1.deptno = t2.deptno

 

select ename, sal
from
 (select ename, sal from emp order by sal desc)
where rownum <=5;

 

sql 代码

select deptno, avg(grade) from
  (select deptno, sal , grade
    from emp e join salgrade s on
    (e.sal between s.losal and s.hisal)
  ) t
group by deptno

--雇员中有哪些人是经理人

sql 代码

select ename, sal
from emp join
  (select deptno, avg(sal) avg_sal
   from emp
   group by deptno) t
on (emp.deptno = t.deptno and emp.sal > t.avg_sal)

--求部门中哪些人的薪水最高

sql 代码

select max(sal), deptno from emp
where ename not like '_A%'
group by deptno
having avg(sal) > 1500
order by deptno desc;

--求每个人的名字和他的经理人的名字，用外连接把KING也取出来

sql 代码