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.DEPTNO
15、列出从事同一种工作但属于不同部门的员工的一种组合。
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;
