Oracle之多表查询

--1．列出至少有一个员工的所有部门。
--方法一
select d.deptno,count(e.empno) from emp e right join dept d on e.deptno=d.deptno group by d.deptno having count(e.empno)>1 ;
--方法二
select distinct emp.deptno from emp,dept where emp.deptno=dept.deptno


--2．列出薪金比“SMITH”多的所有员工。
--方法一
select e2.* from emp e1 join emp e2 on 
e1.ename='SMITH' and e2.sal>e1.sal；
--方法二
select * from emp where sal>
(select sal from emp where ename='SMITH')；


--3．列出所有员工的姓名及其直接上级的姓名。
--方法一
select e1.ename,e2.ename from emp e1 left join emp e2 on e1.mgr=e2.empno ;
--方法二
select ename,a.ename from emp a where emp.empno = a.mgr；


--4．列出受雇日期早于其直接上级的所有员工。
--方法一
select e1.* from emp e1 left join emp e2 on e1.mgr=e2.empno and 
e1.hiredate<e2.hiredate where e2.empno is not null;
--方法二
select empno,ename from emp a where hiredate>
(select hiredate from emp where empno=a.mgr)；


--5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
--方法一
select * from dept d left join emp e on d.deptno=e.deptno ;
--方法二
select dname,emp.* from dept,emp where dept.deptno=emp.deptno;


--6．列出所有“CLERK”（办事员）的姓名及其部门名称。
--方法一
select * from emp e join dept d on e.deptno=d.deptno AND e.JOB='CLERK'；
--方法二
select ename,dname from emp,dept where emp.deptno = dept.deptno and job = 'CLERK'；


--7．列出最低薪金大于1500的各种工作。
select job from emp where sal>1500；




--8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
--方法一
SELECT * FROM DEPT D JOIN EMP E ON  D.DNAME = 'SALES'AND D.DEPTNO=E.DEPTNO;
--方法二
SELECT * FROM EMP E WHERE E.DEPTNO=(SELECT D.DEPTNO FROM DEPT D WHERE D.DNAME='SALES');


--9．列出薪金高于公司平均薪金的所有员工。
--方法一
SELECT * FROM EMP WHERE SAL>
(SELECT AVG(E2.SAL) FROM EMP E2);
--方法二
SELECT * FROM EMP E  JOIN 
(SELECT AVG(E2.SAL) AVGSAL FROM EMP E2)T
ON E.SAL>T.AVGSAL;


--10．列出与“SCOTT”从事相同工作的所有员工。
--方法一
SELECT * FROM EMP E1 WHERE JOB=
(SELECT E2.JOB FROM EMP E2 WHERE E2.ENAME='SCOTT' ) 
AND E1.ENAME<>'SCOTT'  
--方法二
SELECT * FROM EMP E1  JOIN  
(SELECT * FROM EMP E2 WHERE E2.ENAME='SCOTT')T
ON E1.JOB=T.JOB AND E1.ENAME<>T.ENAME;


--11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。7876
--方法一
SELECT DISTINCT EMP.* FROM EMP JOIN
(SELECT * FROM EMP E2 WHERE E2.DEPTNO=30)T
ON EMP.SAL=T.SAL  
--方法二
select ename,sal from emp where sal in 
(select sal from emp deptno = 30)


--12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
--方法一
SELECT emp.* FROM EMP  JOIN
(SELECT max(sal)maxsal FROM EMP where DEPTNO=30)T
ON EMP.SAL>T.MAXSAL
--方法二
select * from emp where sal > 
(select max(nvl(sal,0)) from emp where deptno =30);


--13．列出在每个部门工作的员工数量、平均工资和平均服务期限。


SELECT DEPT.DEPTNO, NVL(CNT,0),NVL(AVGS,0) FROM DEPT LEFT JOIN 
(SELECT DEPTNO,COUNT(EMPNO)CNT,AVG(NVL(SAL,0)+NVL(COMM,0)) AVGS FROM EMP GROUP BY DEPTNO)T
ON DEPT.DEPTNO=T.DEPTNO
--部门是所有部门，员工表里面的只是一部分部门。




--14．列出所有员工的姓名、部门名称和工资。
--方法一
SELECT E1.ENAME,D.DNAME,NVL(SAL,0)+NVL(COMM,0) FROM EMP E1 LEFT JOIN DEPT D ON E1.DEPTNO=D.DEPTNO;
--方法二
select ename,dname,(sal+nvl(comm,0)) 工资 from emp,dept where emp.deptno = dept.deptno;


--15．列出从事同一种工作但属于不同部门的员工的一种组合。
--方法一
SELECT * FROM EMP E1 JOIN EMP E2 ON 
E1.JOB=E2.JOB AND E1.DEPTNO<>E2.DEPTNO AND E1.EMPNO<>E2.EMPNO;
--方法二
  select e1.ename,e1.job,e1.deptno,e2.job,e2.deptno from emp e1,emp e2 where 
e1.job = e2.job and e1.deptno<>e2.deptno


--16．列出所有部门的详细信息和部门人数。
--方法一
SELECT * FROM DEPT D LEFT JOIN 
(SELECt E.DEPTNO,COUNT(E.EMPNO)CNT FROM EMP E GROUP BY E.DEPTNO )T
ON D.DEPTNO=T.DEPTNO;
--方法二
select d.*,(select count(*) from emp where deptno=d.deptno) cnt from dept d;


--17．列出各种工作的最低工资。
select job， min(sal+nvl(comm,0)) from emp group by job


--18．列出各个部门的MANAGER（经理）的最低薪金。
--方法一
SELECT e.deptno,S.LOSAL FROM SALGRADE S JOIN EMP E ON 
  E.SAL BETWEEN S.LOSAL AND S.HISAL AND E.JOB='MANAGER';
--方法二
select s.losal from salgerade s where exists 
(select * from emp e where e.sal between s.losal and s.hisal and e.job='MANAGER'
)


--19．列出所有员工的年工资,按年薪从低到高排序。


SELECT EMP.EMPNO,EMP.ENAME,(NVL(SAL,0)+NVL(COMM,0))*12 YEARSAL FROM EMP ORDER BY YEARSAL