一, 基础练习:
1. 查询和 scott 相同部门的员工姓名 ename 和雇用日期 hiredate
SELECT ENAME,HIREDATE FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME='SCOTT');
2. 查询在部门的 loc 为 NEW YORK 的部门工作的员工的员工姓名 ename, 部门名称 dname 和岗位名称 job
SELECT E.ENAME,D.DNAME,E.JOB,D.LOC FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO AND D.LOC='NEW YORK';
3. 查询上司是 king 的员工姓名 (ename) 和工资(sal)
SELECT ENAME,SAL FROM EMP WHERE MGR=(SELECT EMPNO FROM EMP WHERE ENAME='KING');
4. 查询与姓名中包含字母 U 的员工在相同部门的员工信息
SELECT * FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM EMP WHERE ENAME LIKE '%U%');
5. 查询所有雇员姓名和部门名称(使用 left join,inner join, right join)
- SELECT E.ENAME,D.DNAME FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO;
- SELECT E.ENAME,D.DNAME FROM EMP E LEFT JOIN DEPT D ON E.DEPTNO=D.DEPTNO;
- SELECT E.ENAME,D.DNAME FROM DEPT D RIGHT JOIN EMP E ON E.DEPTNO=D.DEPTNO;
6. 显示每个员工的员工姓名, 部门名称, 职务, 工资, 和工资等级信息(使用 left join,inner join, right join)
- SELECT E.ENAME,D.DNAME,E.JOB,E.SAL,S.GRADE FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO INNER JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL;
- SELECT E.ENAME,D.DNAME,E.JOB,E.SAL,S.GRADE FROM EMP E LEFT JOIN DEPT D ON E.DEPTNO=D.DEPTNO LEFT JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL;
- SELECT E.ENAME,D.DNAME,E.JOB,E.SAL,S.GRADE FROM DEPT D RIGHT JOIN EMP E ON E.DEPTNO=D.DEPTNO RIGHT JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL;
二, 综合练习
1. 取得每个部门最高薪水的人员名称 2. 列出受雇日期早于其直接上级的所有员工的编号, 姓名, 部门名称
-- 使用相关子查询
SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP E WHERE E.SAL=(SELECT MAX(SAL) FROM EMP M WHERE M.DEPTNO=E.DEPTNO) ORDER BY DEPTNO;
-- 使用多表连接查询(渔舟唱晚同学的)
SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP Q, (SELECT E.DEPTNO 部门, MAX(E.SAL) 最高薪资 FROM EMP E GROUP BY E.DEPTNO)
R WHERE R. 部门 = Q.DEPTNO AND Q.SAL = R. 最高薪资 ORDER BY Q.DEPTNO;
-- 使用 DENSE_RANK()函数结合 ORDER BY
SELECT * FROM(SELECT EMPNO,ENAME,SAL,DEPTNO,DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC)RN FROM EMP) WHERE RN=1 ORDER BY DEPTNO;
-- 使用 IN 子查询(有 BUG)
SELECT EMPNO,ENAME,DEPTNO,SAL FROM EMP WHERE SAL IN(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO) ORDER BY DEPTNO;
-- 测试上面的 IN 子查询 BUG: 发现 10 部门的 NulluN 也显示出来了, 但其并非 10 部门最高工资, 10 部门最高工资为 5000
- INSERT INTO EMP(EMPNO,ENAME,DEPTNO,SAL) VALUES(1015,'NulluN',10,3000);
- SELECT EMPNO,ENAME,DEPTNO,SAL FROM EMP WHERE SAL IN(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO) ORDER BY DEPTNO;
2. 列出受雇日期早于其直接上级的所有员工的编号, 姓名, 部门名称
-- 左自连接和多表查询
SELECT E.EMPNO 员工编号, E.ENAME 员工姓名, M.ENAME 主管姓名, E.HIREDATE 员工受雇日期, M.HIREDATE 上级雇用日期, D.DNAME 部门名称
FROM EMP E,EMP M,DEPT D WHERE M.EMPNO(+)=E.MGR AND E.HIREDATE<M.HIREDATE AND E.DEPTNO=D.DEPTNO ORDER BY E.EMPNO;
-- 相关子查询和多表查询
- SELECT E.EMPNO,E.ENAME,D.DNAME FROM EMP E,DEPT D WHERE E.HIREDATE
- <(SELECT HIREDATE FROM EMP M WHERE M.EMPNO=E.MGR) AND E.DEPTNO=D.DEPTNO ORDER BY E.EMPNO;
3. 列出所有 "CLERK"(办事员)的姓名及其部门名称, 部门的人数
思路: 1. 先查询 JOB 为 CLERK 的所有部门编号, 将该子查询结果命名为 A;2. 再从 EMP 表查询与 A 查询中部门编号相同的员工所在的部门人数, 这一步的查询结果命名为 B;3. 最后从 EMP 表, DEPT 表和 B 查询中进行多表查询获取 JOB 为 CLERK 的所有员工的姓名, 部门名称和所在部门人数.
SELECT E.ENAME,D.DNAME,T. 部门人数, E.JOB FROM EMP E,DEPT D,(SELECT DEPTNO,COUNT(1) 部门人数 FROM EMP WHERE DEPTNO IN(
- SELECT DISTINCT DEPTNO FROM EMP WHERE JOB='CLERK') GROUP BY DEPTNO)T
- WHERE E.DEPTNO=D.DEPTNO AND E.JOB='CLERK' AND T.DEPTNO=E.DEPTNO;
4. 列出与 "SCOTT" 从事相同工作的所有员工及部门名称
SELECT E.*,D.DNAME FROM EMP E,DEPT D WHERE E.JOB=(SELECT JOB FROM EMP WHERE ENAME='SCOTT') AND E.DEPTNO=D.DEPTNO;
5. 查出某个员工的上级主管, 并要求出这些主管中的薪水超过 3000
SELECT E.EMPNO 员工编号, E.ENAME 员工姓名, M.ENAME 主管姓名, M.SAL 主管工资 FROM EMP E,EMP M WHERE M.EMPNO(+)=E.MGR AND M.SAL>3000;
6. 找出部门 10 中所有经理 (MANAGER) 和部门 20 中所有办事员 (CLERK) 的详细资料
- SELECT E.*,D.DNAME,D.LOC,S.* FROM EMP E,DEPT D,SALGRADE S WHERE E.DEPTNO=D.DEPTNO AND E.SAL BETWEEN S.LOSAL AND S.HISAL
- AND (E.DEPTNO=10 AND E.JOB='MANAGER' OR E.DEPTNO=20 AND E.JOB='CLERK');
-- 注意: E.DEPTNO=10 AND E.JOB='MANAGER' OR E.DEPTNO=20 AND E.JOB='CLERK' 要用括号括起来, 不然会与前面的 AND 条件混淆造成错误!
7. 找出早于 12 年前受雇的员工. 并且按受雇年份倒序排序
思路一: 用 MONTHS_BETWEEN 比较当前系统时间和受雇日期之前相差的月份, 然后除以 12, 如果值大于 12, 则是早于 12 前受雇的员工.
-- 有错误的语句
SELECT E.*,TO_CHAR(HIREDATE,'YYYY') 受雇年份, ROUND((MONTHS_BETWEEN(SYSDATE,HIREDATE)/12),2) 受雇年限 FROM EMP E WHERE 受雇年限 > 12 ORDER BY 受雇年份 DESC;
/* 为什么 "受雇年限" 会是无效的标识符呢? 因为 SELECT 语句在 WHERE 语句后面才执行, 而列的别名 (受雇年限) 是在 SELECT 时才生成的, 故在 WHERE 子句中看不到这个别名(受雇年限), 自然无法引用这个别名了.*/-- 排错后的正确语句
SELECT E.*,TO_CHAR(HIREDATE,'YYYY') 受雇年份, ROUND((MONTHS_BETWEEN(SYSDATE,HIREDATE)/12),2) 受雇年限 FROM EMP E WHERE (MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)>12 ORDER BY 受雇年份 DESC;
思路二: 用 ADD_MONTHS 判断,(受雇日期 + 12*12)得出的日期如果小于当前系统时间, 则是早于 12 前受雇的员工.
SELECT E.*,TO_CHAR(HIREDATE,'YYYY') 受雇年份, ADD_MONTHS(HIREDATE,12*12) 受雇十二周年日, ROUND((MONTHS_BETWEEN(SYSDATE,HIREDATE)/12),2) 受雇年限 FROM EMP E
WHERE ADD_MONTHS(HIREDATE,12*12)<SYSDATE ORDER BY 受雇年份 DESC;
-- 注意: 离当前日期越远的日期越小, 反之, 离当前日期越近的日期越大.
8. 列出从事同一种工作但属于不同部门的员工的一种组合
-- 不算完美但算比较接近题意的 SQL 语句
SELECT DISTINCT E.EMPNO,E.ENAME,E.JOB,E.DEPTNO FROM EMP E,EMP P WHERE E.DEPTNO!=P.DEPTNO AND E.JOB=P.JOB ORDER BY JOB,DEPTNO;
-- 其它两种不等于的写法
- SELECT DISTINCT E.EMPNO,E.ENAME,E.JOB,E.DEPTNO FROM EMP E,EMP P WHERE E.DEPTNO<>P.DEPTNO AND E.JOB=P.JOB ORDER BY JOB,DEPTNO;
- SELECT DISTINCT E.EMPNO,E.ENAME,E.JOB,E.DEPTNO FROM EMP E,EMP P WHERE E.DEPTNO^=P.DEPTNO AND E.JOB=P.JOB ORDER BY JOB,DEPTNO;
- /* 精妙之处: 使用 DISTINCT! 如果不使用 DISTINCT, 查询结果会出现很多一样的重复数据!*/
分析: 为什么说上面的 SQL 语句不算完美呢? 因为从上图可看出 JOB 为 CLERK, 且 DEPTNO=20 的记录有两条, 即分别是第 2 和第 3 条查询记录, 这就与题目要求的 "从事同一种工作但属于不同部门的员工" 不一致了, 故最理想的查询结果应该如下:
9. 查询有奖金的所有员工的姓名, 奖金以及所在部门名称
-- 如果奖金等于 0 也算有奖金, 那如下实现:
SELECT ENAME,COMM,DNAME FROM EMP E,DEPT D WHERE COMM IS NOT NULL AND E.DEPTNO=D.DEPTNO;
-- 如果奖金等于 0 不算有奖金, 则如下实现:
SELECT ENAME,COMM,DNAME FROM EMP E,DEPT D WHERE COMM IS NOT NULL AND COMM<>0 AND E.DEPTNO=D.DEPTNO;
10. 给任职日期超过 25 年的员工加薪 10%
SELECT E.ENAME,E.SAL 原薪水, E.SAL*1.1 加薪后薪水, ROUND((MONTHS_BETWEEN(SYSDATE,HIREDATE)/12),2) 受雇年限 FROM EMP E
WHERE (MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)>25;
来源: http://www.linuxidc.com/Linux/2018-10/154914.htm