oracle中emp表习题

### Oracle中EMP表习题详解 #### 1. 列出至少有一个员工的所有部门 - **知识点**: 使用子查询和`GROUP BY`结合`HAVING`条件筛选部门。 ```sql SELECT dname FROM dept WHERE deptno IN ( SELECT deptno FROM emp GROUP BY deptno HAVING COUNT(*) > 1 ); ``` **解析**: 上述SQL语句通过子查询找出至少有一个员工的部门编号，并在外层查询中根据这些部门编号获取部门名称。 #### 2. 列出薪金比“SMITH”多的所有员工 - **知识点**: 自连接查询，用于比较不同行的数据。 ```sql SELECT ename FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename LIKE 'SMITH'); ``` **解析**: 此SQL语句利用自连接查询的方式，通过内层子查询找到名为“SMITH”的员工的薪金，并在外层查询中筛选出薪金更高的员工。 #### 3. 列出所有员工的姓名及其直接上级的姓名 - **知识点**: 使用嵌套查询处理上下级关系。 ```sql SELECT e.ename, (SELECT ename FROM emp WHERE empno = e.mgr) AS manager_name FROM emp e; ``` **解析**: 这个SQL语句通过外层查询获取每个员工的信息，并通过内层查询找到对应员工的直接上级姓名。 #### 4. 列出受雇日期早于其直接上级的所有员工 - **知识点**: 使用JOIN操作进行关联查询。 ```sql SELECT e1.ename, e1.hiredate, e2.ename AS manager_name, e2.hiredate AS manager_hiredate FROM emp e1 JOIN emp e2 ON e1.mgr = e2.empno AND e1.hiredate < e2.hiredate; ``` **解析**: 通过JOIN操作将员工表自身连接起来，比较员工与其上级的入职日期，筛选出符合条件的记录。 #### 5. 列出所有“CLERK”（办事员）的姓名及其部门名称 - **知识点**: 使用JOIN进行多表连接查询。 ```sql SELECT e.ename, d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno WHERE e.job LIKE 'CLERK'; ``` **解析**: 该查询使用JOIN连接员工表和部门表，并通过WHERE子句筛选出职位为“CLERK”的员工。 #### 6. 列出最低薪金大于1500的各种工作 - **知识点**: 使用DISTINCT去除重复记录。 ```sql SELECT DISTINCT job FROM emp WHERE sal > 1500; ``` **解析**: 该查询通过DISTINCT关键字去重，筛选出最低薪金大于1500的不同工作类型。 #### 7. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门 - **知识点**: 使用LEFT JOIN进行左连接。 ```sql SELECT d.dname, e.* FROM dept d LEFT JOIN emp e ON d.deptno = e.deptno; ``` **解析**: 左连接保留所有部门信息，并尝试匹配对应的员工记录，即使部门没有员工也能显示出来。 #### 8. 列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号 - **知识点**: 使用子查询获取未知部门编号。 ```sql SELECT e.ename FROM emp e WHERE e.deptno = (SELECT deptno FROM dept WHERE dname LIKE 'SALES'); ``` **解析**: 子查询找到部门名为“SALES”的部门编号，并在外层查询中筛选出对应部门的员工姓名。 #### 9. 列出薪金高于公司平均薪金的所有员工 - **知识点**: 使用子查询计算平均值。 ```sql SELECT ename FROM emp WHERE sal > (SELECT AVG(sal) FROM emp); ``` **解析**: 通过子查询计算出所有员工的平均薪金，并筛选出薪金高于平均值的员工。 #### 10. 列出与“SCOTT”从事相同工作的所有员工 - **知识点**: 排除指定条件下的记录。 ```sql SELECT ename FROM emp WHERE job IN (SELECT job FROM emp WHERE ename LIKE 'SCOTT') AND ename != 'SCOTT'; ``` **解析**: 查询与“SCOTT”相同工作类型的员工，但不包括“SCOTT”本人。 #### 11. 列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金 - **知识点**: 使用ANY进行比较。 ```sql SELECT ename, sal FROM emp WHERE sal = ANY(SELECT sal FROM emp WHERE deptno = 30) AND deptno != 30; ``` **解析**: 比较当前员工的薪金是否等于部门30中任一员工的薪金，但排除本部门的员工。 #### 12. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金 - **知识点**: 使用MAX获取最大值。 ```sql SELECT ename, sal FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30); ``` **解析**: 通过子查询找出部门30中的最高薪金，并筛选出薪金高于该值的员工。 #### 13. 列出在每个部门工作的员工数量、平均工资和平均服务期限 - **知识点**: 使用GROUP BY分组统计数据。 ```sql SELECT deptno, COUNT(*), AVG(sal), AVG(TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) - TO_NUMBER(TO_CHAR(hiredate, 'YYYY'))) FROM emp GROUP BY deptno; ``` **解析**: 对每个部门的工作数据进行统计分析，包括员工数量、平均薪金以及平均服务年限。 #### 14. 列出所有员工的姓名、部门名称和工资 - **知识点**: 使用JOIN进行连接查询。 ```sql SELECT e.ename, e.sal, d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno; ``` **解析**: 连接员工表和部门表，展示员工姓名、薪资和所在部门的名称。 #### 15. 列出所有部门的详细信息和部门人数 - **知识点**: 使用LEFT JOIN进行左连接，并使用COUNT聚合函数。 ```sql SELECT d.*, COUNT(e.empno) AS department_count FROM dept d LEFT JOIN emp e ON d.deptno = e.deptno GROUP BY d.deptno; ``` **解析**: 通过左连接保留所有部门信息，并通过GROUP BY分组统计每个部门的人数。