SQL多表查询
--1,列出至少有一个雇员的所有部门
SELECT E.DEPTNO, COUNT(E.EMPNO)
FROM EMP E
WHERE E.EMPNO IS NOT NULL
GROUP BY E.DEPTNO
HAVING COUNT(E.EMPNO) > 0;
--2,列出薪金比‘SMITH’多的所有雇员
SELECT *
FROM EMP E
WHERE E.SAL >
(
SELECT P.SAL
FROM EMP P
WHERE P.ENAME = 'SMITH'
);
--3,列出所有雇员的姓名及其上级的姓名
SELECT E.ENAME , P.ENAME
FROM EMP E, EMP P
WHERE E.MGR = P.EMPNO(+);
--4,列出入职日期早于其直接上级的所有雇员
SELECT E.ENAME, P.ENAME
FROM EMP E, EMP P
WHERE E.MGR = P.EMPNO(+)
AND E.HIREDATE < P.HIREDATE;
--5,列出部门名称和这些部门的雇员,同时列出那些
--没有雇员的部门
SELECT E.ENAME, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO;
--6,列出所有‘CLERK’(办事员)的姓名及其部门名称
SELECT E.ENAME, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND E.JOB = 'CLERK';
--7,列出各种岗位的最低薪金(?),并显示最低薪金
--大于1500所有工作岗位及其最低薪资
SELECT MIN(E.SAL), E.JOB
FROM EMP E
GROUP BY E.JOB
HAVING MIN(E.SAL) > 1500;
--8,列出从事'SALES'(销售)工作的雇员的姓名,
--假定不知道销售部的部门编号。
SELECT E.ENAME,D.DEPTNO
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND E.JOB = 'SALESMAN';
--9,列出薪金高于公司平均的所有雇员
SELECT E.ENAME
FROM EMP E
WHERE E.SAL >
(
SELECT AVG(P.SAL)
FROM EMP P
);
--10,列出与'SCOTT'从事相同工作的所有雇员
SELECT E.NAME
FROM EMP E
WHERE E.ENAME != 'SCOTT'
AND E.JOB =
(
SELECT P.JOB
FROM EMP P
WHERE P.ENAME = 'SCOTT'
);
--11,列出薪金等于在部门30工作的所有雇员的
--薪金的雇员的姓名和薪金。
SELECT E.ENAME, E.SAL
FROM EMP E
WHERE E.SAL = ANY
(
SELECT P.SAL
FROM EMP P
WHERE P.DEPTNO = 30
);
--12,列出薪金高于在部门30工作的所有雇员的薪金
--的雇员的姓名和薪金
SELECT E.ENAME, E.SAL
FROM EMP E
WHERE E.SAL > ALL
(
SELECT P.SAL
FROM EMP P
WHERE P.DEPTNO = 30
);
--13,列出每个部门工作的雇员的数量以及其它信息
SELECT D.DEPTNO, D.DNAME, D.LOC, COUNT(E.EMPNO)
FROM EMP E, DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO
GROUP BY D.DEPTNO, D.DNAME, D.LOC;
--14,列出所有雇员的雇员名称、部门名称和薪金
SELECT E.ENAME, D.DNAME, E.SAL
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO(+);
--15,列出从事同一种工作但属于不同部门
--雇员的不同组合
SELECT DISTINCT E.EMPNO, E.ENAME, E.JOB, E.DEPTNO
FROM EMP E,
(
SELECT P.JOB, P.DEPTNO
FROM EMP P
GROUP BY P.JOB, P.DEPTNO
) M
WHERE E.DEPTNO != M.DEPTNO
AND E.JOB = M.JOB;
--16,列出分配有雇员数量的所有部门的详细信息
--即使是分配有0个雇员
SELECT COUNT(E.EMPNO), D.DEPTNO, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO
GROUP BY D.DEPTNO, D.DNAME, D.LOC;
--17,列出各种类别工作的最低工资
SELECT MIN(E.SAL)
FROM EMP E
GROUP BY E.JOB;
--18,列出各种部门的MANAGER(经理)的最低薪金
SELECT E.DEPTNO, MIN(E.SAL)
FROM EMP E
WHERE E.JOB = 'MANAGER'
GROUP BY E.DEPTNO;
--19,列出按计算的字段排序的所有雇员的年薪
SELECT E.ENAME,
(E.SAL + NVL(E.COMM,0))*12 SALBYYEAR
FROM EMP E
ORDER BY SALBYYEAR;