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， 按照部门编号倒序排列

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

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

1. select ename, sal    
2. from emp join    
3.   (select deptno, max(sal) max_sal    
4.    from emp    
5.    group by deptno) t   
6. on (emp.deptno = t.deptno and emp.sal = t.max_sal)  

 

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

1. select deptno, avg_sal, grade    
2. from salgrade s join    
3.   (select deptno, avg(sal) avg_sal    
4.    from emp    
5.    group by deptno) t   
6. on (t.avg_sal between s.losal and s.hisal)   

sql 代码

 

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

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

 

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

sql 代码

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

排序，取第一个

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

1. select deptno from    
2. (select deptno, avg(sal) avg_sal from emp group by deptno) t   
3.   
4. where avg_sal =    
5.   
6. (select max(avg_sal) from    
7. (select deptno, avg(sal) avg_sal from emp group by deptno) t)   
8. select max(avg(sal)) from emp group by deptno －－组函数嵌套   

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

1. select dname from dept where deptno =    
2. (select deptno from    
3.   (select deptno, avg(sal) avg_sal from emp group by deptno) t   
4.   
5.   where avg_sal =    
6.   
7.   (select max(avg_sal) from    
8.     (select deptno, avg(sal) avg_sal from emp group by deptno) t)   
9. )   

 

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

1. select dept.deptno,dname  from  
2.    (   
3.    select deptno, avg_sal, grade from salgrade s join  
4.            (select deptno, avg(sal) avg_sal from emp group by deptno) t   
5.    on (t.avg_sal between s.losal and s.hisal)   
6.    ) t1 join dept on  
7. (t1.deptno = dept.deptno)   
8. where grade =   
9. (select min(grade) from       
10.    (   
11.    select deptno, avg_sal, grade from salgrade s join  
12.            (select deptno, avg(sal) avg_sal from emp group by deptno) t   
13.    on (t.avg_sal between s.losal and s.hisal)   
14.    )   
15. )   
16.   
17. reate view v$_dept_info as    
18. select deptno, avg_sal, grade from salgrade s join  
19.            (select deptno, avg(sal) avg_sal from emp group by deptno) t   
20.    on (t.avg_sal between s.losal and s.hisal)   
21.   
22. elect deptno from v$_dept_info where grade = (select min(grade) from v$_dept_info);   

 

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

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

sql 代码

1.  select empno, ename, sal from emp where sal >   
2.  (   
3.     select max(sal) from  
4.             (select empno,ename, sal   
5.                     from emp where empno not in (select distinct nvl(mgr, -1) from emp ))   
6.  ) and empno in (select distinct mgr from emp)   
7.   
8. select distinct mgr from emp where mgr is not null  

 

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

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

1. select ename, sal from  
2. (   
3.   select ename, sal, rownum r from  
4.     (   
5.       select ename, sal from emp order by sal desc  
6.     )    
7.   where rownum <=10   
8. )    
9. where r > 5 and r <= 10  

 

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

1. select deptno, ename, sal from emp order by deptno, sal desc  
2.   
3. select deptno, ename, sal , rownum r from  
4. (select deptno, ename, sal from emp order by deptno, sal desc);   
5.   
6. create view v as select deptno, ename, sal , rownum r from  
7. (select deptno, ename, sal from emp order by deptno, sal desc);   
8.   
9. select deptno, min(r) min_r from v group by deptno;   
10.   
11.  select v.deptno, ename, sal , r from v join  
12.  (   
13.  select deptno, min(r) min_r from v group by deptno   
14.  ) t   
15.  on (v.deptno = t.deptno and v.r >= t.min_r and v.r <= t.min_r + 1)   
16.  order by v.deptno, sal desc;   

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

1. select ename, hiredate from  
2. (   
3. select ename ,hiredate from emp order by hiredate desc  
4. )   
5. where rownum <= 5  

 

 

--面试题: 比较效率

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

 

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

1. scale      total   
2. <800  0   
3. 801-1000 2   
4. 1001-2000 3   
5. 2001-5000 6   
6. >5000  8   
7.   
8. select '<800' as scale , count(*) as total from emp where sal < 800   
9. union  
10. select '800-1000' as scale , count(*) as total from emp where sal between 800 and 1000   
11. union  
12. select '2000-5000' as scale , count(*) as total from emp where sal between 2000 and 5000;   
13. ...   

 

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

sql 代码

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

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

sql 代码

1. select t1.deptno, "800-2000", "2001-5000" from  
2. (   
3. select deptno , count(*) as "800-2000" from emp where sal between 800 and 2000   
4. group by deptno   
5. ) t1   
6. join    
7. (   
8. select deptno , count(*) as "2001-5000" from emp where sal between 2001 and 5000   
9. group by deptno   
10. ) t2   
11. on  
12. t1.deptno = t2.deptno  

 

1. select ename, sal    
2. from    
3.  (select ename, sal from emp order by sal desc)   
4. where rownum <=5;  

 

sql 代码

1. select deptno, avg(grade) from    
2.   (select deptno, sal , grade   
3.     from emp e join salgrade s on    
4.     (e.sal between s.losal and s.hisal)   
5.   ) t   
6. group by deptno   

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

sql 代码

1. select ename, sal    
2. from emp join    
3.   (select deptno, avg(sal) avg_sal    
4.    from emp    
5.    group by deptno) t   
6. on (emp.deptno = t.deptno and emp.sal > t.avg_sal)   

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

sql 代码

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

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

sql 代码