1、列出至少有一个员工的所有部门名称。

SELECT D.DNAME,COUNT(E.EMPNO) FROM EMP E,DEPT D WHERE E.DEPTNO(+) = D.DEPTNO GROUP BY D.DNAME HAVING COUNT(E.EMPNO)>0;SELECT D.DNAME,COUNT(E.EMPNO) FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO GROUP BY D.DNAME;

2、列出薪金比“SMITH”多的所有员工。（大于最大薪水SMITH员工）

SELECT * FROM EMP E WHERE E.SAL > (SELECT SAL FROM EMP E2 WHERE E2.ENAME ='SMITH');

3、列出所有员工的姓名及其直接上级的姓名。

SELECT E.ENAME,M.ENAME FROM EMP E ,EMP M WHERE E.MGR = M.EMPNO;

4、列出受雇日期早于其直接上级的所有员工。

SELECT E.ENAME,E.HIREDATE,M.ENAME,M.HIREDATE FROM EMP E,EMP M WHERE E.MGR = M.EMPNO AND E.HIREDATE < M.HIREDATE;

5、列出部门名称和这些部门的员工信息，包括那些没有员工的部门。

SELECT D.DNAME,E.* FROM EMP E,DEPT D WHERE E.DEPTNO(+) = D.DEPTNO;

6、列出所有job为“CLERK”（办事员）的姓名及其部门名称。

SELECT D.DNAME,E.ENAME FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.JOB = 'CLERK';

7、列出最低薪金大于1500的各种工作。

SELECT JOB,MIN(SAL) FROM EMP E GROUP BY JOB HAVING MIN(SAL)>1500;

8、列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。 SELECT D.DNAME,E.ENAME FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO AND D.DNAME = 'SALES';

9、列出薪金高于公司平均薪金的所有员工。

SELECT * FROM EMP E WHERE SAL > (SELECT AVG(SAL) FROM EMP);

10、列出与“SCOTT”从事相同工作的所有员工。

SELECT * FROM EMP E WHERE E.JOB = (SELECT JOB FROM EMP E2 WHERE E2.ENAME = 'SCOTT') AND E.ENAME <> 'SCOTT';11、列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。 SELECT * FROM EMP E WHERE E.SAL IN (SELECT SAL FROM EMP E2 WHERE E2.DEPTNO = 10) AND E.DEPTNO <> 10;12、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。 SELECT * FROM EMP E WHERE E.SAL > ALL(SELECT SAL FROM EMP E2 WHERE E2.DEPTNO = 30) AND E.DEPTNO <> 30; 13、列出在每个部门工作的员工数量、平均工资和平均服务期限。 SELECT DEPTNO,COUNT(E.EMPNO),AVG(E.SAL),AVG(MONTHS_BETWEEN(SYSDATE,HIREDATE)) FROM EMP E WHERE DEPTNO IS NOT NULL GROUP BY DEPTNO;14、列出所有员工的姓名、部门名称和工资。 SELECT D.DNAME,E.ENAME,E.SAL FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO15、列出从事同一种工作但属于不同部门的员工的一种组合。 SELECT E1.ENAME,E1.JOB,E1.DEPTNO,E2.ENAME,E2.JOB,E2.DEPTNO FROM EMP E1,EMP E2 WHERE E1.JOB = E2.JOB AND E1.DEPTNO > E2.DEPTNO;

16、列出所有部门的详细信息和部门人数。

SELECT D.DNAME,D.DEPTNO,D.LOC,COUNT(E.EMPNO) FROM EMP E,DEPT D WHERE E.DEPTNO(+) = D.DEPTNO GROUP BY D.DNAME,D.DEPTNO,D.LOC; SELECT D.DEPTNO,E.* FROM EMP E,DEPT D WHERE E.DEPTNO(+) = D.DEPTNO;

17、列出各种工作的最低工资。

SELECT JOB,MIN(SAL) FROM EMP E GROUP BY JOB;18、列出各个部门的MANAGER（经理）的最低薪金（job为MANAGER）。 SELECT DEPTNO,MIN(SAL) FROM EMP E WHERE JOB = 'MANAGER' GROUP BY DEPTNO;19、列出所有员工的年工资，按年薪从低到高排序。 SELECT E.ENAME,(SAL+NVL(COMM,0))*12 FROM EMP E ORDER BY SAL+NVL(COMM,0); SELECT E.ENAME,(SAL+NVL(COMM,0))*12 FROM EMP E ORDER BY 2;

查询发津贴员工的部门名称SELECT D.DNAME FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.COMM IS NOT NULL;

SELECT D.DNAME FROM EMP E JOIN DEPT D USING(DEPTNO)

WHERE E.COMM IS NOT NULL; 查询工作为clerk的员工都在那个部门SELECT D.DNAME FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.JOB = 'CLERK';SELECT D.DNAME FROM EMP E JOIN DEPT D USING(DEPTNO) WHERE E.JOB = 'CLERK';

查询薪资大于3000的员工都在那个部门SELECT D.DNAME FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.SAL>3000;SELECT D.DNAME FROM EMP E JOIN DEPT D USING(DEPTNO) WHERE E.SAL >3000;

查询部门名称和部门人数（外连接）

SELECT D.DNAME,COUNT(*) FROM EMP E ,DEPT D WHERE E.DEPTNO = D.DEPTNO GROUP BY D.DNAME;SELECT D.DNAME,COUNT(*) FROM EMP E JOIN DEPT D USING(DEPTNO) GROUP BY D.DNAME;

查询部门名称和部门平均薪资

SELECT D.DNAME,COUNT(*) FROM EMP E ,DEPT D WHERE E.DEPTNO = D.DEPTNO GROUP BY D.DNAME;SELECT D.DNAME,AVG(SAL) FROM EMP E JOIN DEPT D USING(DEPTNO) GROUP BY D.DNAME;

查询部门10薪资大于3000的人的工作和部门名称

SELECT D.DNAME,E.JOB,E.SAL,E.ENAME FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.DEPTNO = 10 AND E.SAL > 3000; 查询名字里面有S的人薪资等级是多少 SELECT E.ENAME,S.GRADE FROM EMP E ,SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL AND E.ENAME LIKE '%S%'; SELECT * FROM EMP E JOIN SALGRADE S ON(E.SAL BETWEEN S.LOSAL AND S.HISAL) WHERE E.ENAME LIKE '%S%'; 按照部门名称和工作分组求出平均薪资 SELECT D.DNAME,E.JOB,AVG(SAL) FROM EMP E JOIN DEPT D USING(DEPTNO) GROUP BY D.DNAME ,E.JOB; 按照部门名称和工作分组求出平均薪资而且平均薪资要大于2000 SELECT D.DNAME,E.JOB,AVG(SAL) FROM EMP E JOIN DEPT D USING(DEPTNO) GROUP BY D.DNAME ,E.JOB HAVING AVG(SAL)>2000;