Oracle 练习题及答案

根据提供的信息，我们可以总结出以下Oracle数据库练习题及其解析，主要涵盖了SQL查询、子查询、聚合函数等知识点。 ### 1. 查询所有员工的姓名和部门编号 ```sql SELECT ename, deptno FROM emp; ``` 这个查询非常简单，通过`SELECT`语句直接从`emp`表中选取`ename`（员工名）和`deptno`（部门编号）两列数据。 ### 2. 查询每个员工所在的部门名称和地点 ```sql SELECT ename, emp.deptno, loc FROM emp, dept WHERE emp.deptno = dept.deptno; ``` 此查询涉及到两个表：`emp`和`dept`，通过`INNER JOIN`的方式（这里虽然没有明确写出来，但效果相同）将两个表连接起来，并筛选出部门编号相匹配的数据，最终输出员工姓名、部门编号和地点。 ### 3. 查询每个部门经理的下属员工信息 ```sql SELECT a.empno, a.ename, a.deptno, b.ename FROM emp a, emp b WHERE a.deptno = b.deptno AND b.job = 'MANAGER'; ``` 此查询通过比较两个`emp`表中的部门编号来找到经理（`job = 'MANAGER'`）及其下属员工的信息。这里采用了别名`a`和`b`来区分两个`emp`表实例。 另一种方式是： ```sql SELECT a.empno, a.ename, a.deptno, b.ename FROM emp a, emp b WHERE a.mgr = b.empno AND b.job = 'MANAGER'; ``` 这种方式是通过比较员工的管理者编号(`mgr`)与另一个员工的编号(`empno`)来确定上下级关系。 ### 4. 查询1981年入职的所有员工信息 ```sql SELECT * FROM scott.emp WHERE TO_CHAR(hiredate, 'yyyy') = '1981'; ``` 使用`TO_CHAR`函数将日期转换为字符串格式，以便按年份筛选1981年入职的员工。 ### 5. 查询不存在任何员工的部门信息 ```sql SELECT deptno, dname FROM dept WHERE deptno NOT IN (SELECT DISTINCT deptno FROM emp); ``` 另一种写法： ```sql SELECT dname FROM ( SELECT * FROM emp, dept WHERE emp.deptno (+) = dept.deptno ) WHERE ename IS NULL; ``` 这两种查询均用于找出没有任何员工所属的部门，即那些在`emp`表中未被提及的部门。 ### 6. 查询各部门的平均薪资 ```sql SELECT dname, AVG(sal) FROM emp, dept WHERE emp.deptno = dept.deptno GROUP BY dname; ``` 该查询首先将`emp`表和`dept`表连接，然后根据部门名称进行分组，计算每个部门的平均薪资。 ### 7. 查询每个部门的员工数量 ```sql SELECT COUNT(DISTINCT deptno) FROM emp; ``` 这里计算了不同部门的数量，但实际上要获取每个部门的具体员工数量，可以使用更精确的查询： ```sql SELECT deptno, COUNT(*) FROM emp GROUP BY deptno; ``` ### 8. 查询员工的奖金总数和平均值 ```sql SELECT COUNT(comm), SUM(comm) FROM emp; ``` 这里分别计算了非空奖金的员工数量以及所有员工的奖金总和。 ### 9. 查询每个部门的平均薪资超过2000的部门 ```sql SELECT dname, AVG(sal) FROM emp, dept WHERE emp.deptno = dept.deptno GROUP BY dname HAVING AVG(sal) > 2000; ``` 通过`HAVING`子句筛选出平均薪资超过2000的部门。 ### 10. 查询30号部门以外其他部门的平均薪资 ```sql SELECT deptno, AVG(sal) FROM emp WHERE deptno <> 30 GROUP BY deptno; ``` 另一种写法是： ```sql SELECT deptno, AVG(sal) FROM emp GROUP BY deptno HAVING deptno <> 30; ``` 两种方法都能得到30号部门以外其他部门的平均薪资。 ### 11. 查询最低薪资的员工姓名 ```sql SELECT ename FROM emp WHERE sal = (SELECT MIN(sal) FROM emp); ``` 通过子查询找出最低薪资，并在外部查询中筛选出符合条件的员工姓名。 ### 12. 查询与Smith从事相同工作的员工 ```sql SELECT ename FROM emp WHERE job = (SELECT job FROM emp WHERE ename = 'SMITH') AND ename <> 'SMITH'; ``` 这里先通过子查询找出Smith的工作职位，再在外部查询中选出与之相同职位的其他员工。 ### 13. 查询薪资低于所在部门平均薪资的员工 ```sql SELECT ename FROM emp WHERE sal < (SELECT AVG(sal) FROM emp); ``` 这个查询通过子查询获取所有员工的平均薪资，然后找出薪资低于该平均值的员工。 ### 14. 查询30号部门平均薪资低于公司平均薪资的部门 ```sql SELECT deptno, AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) < (SELECT AVG(sal) FROM emp WHERE deptno = 30); ``` 通过`HAVING`子句过滤出30号部门平均薪资低于公司平均薪资的其他部门。 ### 15. 查询名字以S或A开头的员工信息 ```sql SELECT * FROM emp WHERE ename LIKE 'S%' OR ename LIKE 'A%'; ``` 或者使用`UNION`操作符合并两个查询结果： ```sql SELECT * FROM emp WHERE ename LIKE 'S%' UNION SELECT * FROM emp WHERE ename LIKE 'A%'; ``` ### 16. 查询未选修B2课程的学生信息 假设我们有三个表：学生表`s`、课程表`c`和成绩表`sc`，可以通过以下查询实现： ```sql SELECT s.sno, s.sname FROM s WHERE s.sno NOT IN (SELECT sc.sno FROM sc WHERE sc.cno = 'B2'); ``` 这个查询通过`NOT IN`子句找出未选修B2课程的学生。 以上这些练习题涵盖了Oracle数据库中常见的SQL查询技巧，包括基本的`SELECT`、`JOIN`、`GROUP BY`、`HAVING`、子查询等多种操作，对于掌握Oracle数据库查询是非常有帮助的。