oracle高级查询（二）

select * from salgrade;


select * from emp;


--显示员工的名字、工资及部门的名字
select e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno;


--显示部门编号为10的部门的员工的名字、工资及部门的名称
select e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno and d.deptno=10;


--显示员工的名字、工资及部门的名称,并按部门名称升序排序
select e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno order by d.deptno;


--显示员工的名字、工资及工资级别
select e.ename,e.sal,g.grade from emp e,salgrade g where e.sal between g.losal and g.hisal;


--显示SMITH上级领导的姓名(自连接,对同一张表进行连接查询)
select ename from emp where empno=(select mgr from emp where ename='SMITH');


--显示各个员工的姓名和他的上级领导的名字
select e.ename,m.ename from emp e,emp m where e.mgr=m.empno;


select e.*,m.* from emp e,emp m where e.mgr=m.empno;


--显示SMITH同一部门的所有员工
select * from emp where deptno=(select deptno from emp where ename='SMITH');


select * from emp where deptno in(select deptno from emp where ename='SMITH');


--显示SMITH同一部门的所有员工，但不包含SMITH
select * from emp where deptno=(select deptno from emp where ename='SMITH')
and ename<>'SMITH';


select * from emp where deptno=(select deptno from emp where ename='SMITH')
and ename!='SMITH';




--显示部门平资低于2000的部门号和它的平均工资


select deptno,avg(sal) from emp group by deptno having avg(sal)<2000;


--查询部门号为10员工所参加的工作岗位相同的工作岗位的员工名字、岗位、工资、部门号


select * from emp where job in (select job from emp where deptno=10);


select * from emp where job in(select job from emp where deptno=10);


--查询比30号部门所有员工的工资高的员工姓名、工资、部门号


select * from emp where sal>(select max(sal) from emp where deptno=30);


select * from emp where sal >all (select sal from emp where deptno=30);




select * from emp where sal >(select max(sal) from emp where deptno=30);
select * from emp where sal > all (select sal from emp where deptno=30);


--查询比30号部门任意一个员工的工资高的员工姓名、工资、部门号
select * from emp where sal >(select min(sal) from emp where deptno=30);
select * from emp where sal > any (select sal from emp where deptno=30);




select * from emp where sal>(select min(sal) from emp where deptno=30);
select * from emp where sal> any (select sal from emp where deptno=30);
--查询与SMITH部门和工作岗位完全相同的所有员工信息(多列子查询)


select * from emp where deptno=(select deptno from emp where ename='SMITH') 
and job=(select job from emp where ename='SMITH');




select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');






select * from emp where deptno=(select deptno from emp where ename='SMITH') 
and job=(select job from emp where ename='SMITH');


select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');


--如何显示工资高于本部门平均工资的员工的信息(from中使用子查询)




select avg(sal),deptno from emp group by deptno;




select e.* from emp e,(select avg(sal) avgSal,deptno from emp group by deptno) d where e.deptno=d.deptno and e.sal>d.avgSal;






select avg(sal),deptno from emp group by deptno; 


select * from emp e,(select avg(sal) avgSal,deptno from emp group by deptno) d where e.deptno=d.deptno and e.sal>d.avgSal;