oracle查询、连接查询练习、以及oracle中join on的使用(自连接、笛卡尔连接、左外连接、右外连接、全连接)

1. 查询最高工资员工的名字，工作岗位
   select ename,job,sal from emp where sal=(select max(sal) from emp);

2. 算出部门 30 中得到最多奖金的员工姓名
   select ename from emp where deptno=30 and comm=(select max(comm) from emp where deptno=30);

3. 找出工资比ford高的员工的信息
   select * from emp where sal>(select sal from emp where ename=‘FORD’);

4. 找出不在部门20，且比部门20的任何一个员工的工资都高的员工的姓名和所在部门名称。
   SELECT ename,dname FROM EMP e join dept d on e.deptno=d.deptno WHERE e.deptno<>20 and sal>all(select sal from emp where deptno=20);
   
   SELECT ename,dname FROM EMP e join dept d on e.deptno=d.deptno WHERE e.deptno<>20 and sal>(select max(sal) from emp where deptno=20);

说明：oracle中join on的使用。

5. 查询与SMITH的部门和岗位完全相同的所有雇员。
   select * from emp where deptno=(select deptno from emp where ename = ‘SMITH’) and job = (select job from emp where ename=‘SMITH’);
   
   select ename,job,sal,deptno from emp where (deptno,job)in(select deptno,job from emp where ename=‘SMITH’);
   
   select ename,job,sal,deptno from emp where(deptno,job)=(select deptno,job from emp where ename=‘SMITH’);

6. 查询每个部门工资最高的员工
   select ename,sal,deptno from emp where (deptno,sal) in(select deptno,max(sal) from emp group by deptno);
   
   select * from emp group by deptno having sal=max(sal);

7. 显示员工‘FORD’的上级
   select E.ename 员工,m.ename 领导 from emp e join emp m on e.mgr=m.empno where e.ename=‘FORD’;
   

8. 查询工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
   select ename,sal,deptno from emp where sal>all(select sal from emp where deptno=30);
   
   select ename,sal,deptno from emp where sal>(select max(sal) from emp where deptno=30);

9. 查询工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号
   select ename,sal,deptno from emp where sal > any(select sal from emp where deptno=30);

说明：任何和任意的意思不一样，有很大的区别。细细的品。任意一个指的是满足一个就可以，任何一个指的都要满足。

10. 查询emp表中各部门平均工资的最大值和其所在的部门编号
    select deptno,avg(sal) from emp group by deptno having avg(sal)=(select max(avg(sal)) from emp group by deptno);
    上面这个是求所有部门中平均工资最高的
    
    
    select avg(sal),deptno from emp e group by deptno having avg(sal)=(select max(avg(sal)) from emp where deptno=e.deptno group by deptno);
    上面的是求各个部门中平均工资最高的

11. 查询emp表中平均工资的最大值和其所在的部门编号及部门名称
    我觉得题目出的有问题，应该是：
    查询emp表中各部门平均工资中的最大值和其所在的部门编号及部门名称
    select e.deptno,dname,avg(sal) from emp e join dept d on e.deptno=d.deptno group by e.deptno,dname having avg(sal)=(select max(avg(sal)) from emp group by deptno);

12. 查询所有在任职职位上工资最高的员工
    select * from emp where (job,sal)in(select job,max(sal) from emp group by job);

13. 查找所有工资高于自己本部门平均工资的员工
    select e.*,avgsal from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) avge where e.deptno=avge.deptno and sal>avgsal;
    
    select * from emp e where sal>(select avg(sal) from emp where deptno=e.deptno);

14. 显示每个部门的信息和人员数量
    select dept.*, shul from dept,(select deptno,count(ename) shul from emp group by deptno) shul where dept.deptno=shul.deptno(+);

15. 查找工资大于同职位的平均工资的员工信息
    select * from emp,(select job,avg(sal) avgsal from emp group by job) j where emp.job=j.job and emp.sal>avgsal;
    
    select * from emp e where sal>(select avg(sal) from emp where job=e.job);

16. 查询员工表中是领导的员工
    select * from emp where empno in(select mgr from emp);

17. 查询员工表中不是领导的员工
    select * from emp where empno not in(select mgr from emp where mgr is not null);

---

---

---

4题的说明：

Oracle 中的JOIN:

概述
1、所有的join连接，都可以加上类似where a.id='1000’的条件，达到同样的效果。
2、除了cross join不可以加on外，其它join连接都必须加上on关键字，后都可加where条件。
3、虽然都可以加where条件，但是他们只在标准连接的结果集上查找where条件。比如左外连接的结果没有class的三班，所以如果加 where class.id='C003’虽然在表中有，但在左连接结果集中没有，所以查询后，是没有记录的。

实例，标准的join连接，（不加where条件的）
2.1、设有表如下：
学生表:

班级表，对应学生表中的classid:



2.2、自连接：join ，inner join

–自连接 ：只返回两张表连接列的匹配项。
–以下三种查询结果一样。
select * from student s inner join class c on s.classid=c.id;
select * from student s join class c on s.classid=c.id;
select * from student s,class c where s.classid=c.id;

自连接结果：



2.3、笛卡儿乘积：cross join

–笛卡儿乘积连接 ：即不加任何条件，达到 M*N 的结果集。
–以下两种查询结果一样。
select * from student s cross join class c;
select * from student,class;

笛卡尔结果：

注意：如果cross join加上where s.classid=c.id条件，会产生跟自连接一样的结果：

–加上条件，产生跟自连接一样的结果。
select * from student s cross join class c where s.classid=c.id;
自连接结果集的cross join连接结果:




2.4、左外连接：left join

–左连接 ：列出左边表全部的，及右边表符合条件的，不符合条件的以空值代替。
–在(+)计算时，哪个带(+)哪个需要条件符合的，另一个全部的。即放左即右连接，放右即左连接。
–以下结果集相同。
select * from student s left join class c on s.classid=c.id;
select * from student s,class c where s.classid=c.id(+);

左连接结果：



2.5、右外连接：right join

–右外连接 ：与左连接一样，列出右边表全部的，及左边表符合条件的，不符合条件
–的用 空值 替代。
–(+)一样，它的位置与连接相反。
select * from student s right join class c on s.classid=c.id;
select * from student s,class c where s.classid(+)=c.id;

右连接结果:



2.6、全连接：full join

–全连接 ：产生M+N的结果集，列出两表全部的，不符合条件的，以空值代替。
select * from student s full join class c on s.classid=c.id;

全连接结果集:

注：第4题的说明来自于：该博客(点击我直接进入)

---

---

---