Question

数据库的题目

数据库的题目帮忙写出这些程序





Answer 1

、等级查询

题目：员工信息表中有员工ID、姓名、上级员工ID字段，现要求用一条语句，查询出全部员工的ID、姓名及级别——最高为1级，其下依次为2、3、4等，如下图所示：

员工信息表为Oracle数据库hr用户下的employees表，员工ID的字段名为employee_id，姓名为first_name || ' ' || last_name，上级员工ID为manager_id。在创建Oracle实例时，hr用户及该表自动生成。

通过对员工信息表的分析，发现如下线索：

1、本级员工的manager_id即为上一级员工的employee_id；

2、其中最高级员工的上级员工为空，其他级别都不为空。

如果一张数据库表中存在等级数据，则应使用START WITH … CONNECT BY [PRIOR] …关键字，进行等级查询，其中：

1、START WITH关键字标识数据表中最高等级的特征；

2、CONNECT BY关键字标识上下级行的关系；

3、PRIOR关键字标识此行是上一级行（原文是In a hierarchical query, one expression in condition must be qualified with the PRIOR operator to refer to the parent row.）；

4、使用LEVEL伪列显示层级关系。

综上，查询语句为：

SELECT EMPLOYEE_ID,
FIRST_NAME || ' ' || LAST_NAME AS NAME,
LEVELFROM EMPLOYEESSTART WITH MANAGER_ID IS NULLCONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

二、排序

题目：员工信息表中有员工ID、姓名、部门ID、工资字段，现要求用一条语句，查询出每个员工在整个公司以及本部门工资的排名（升序或降序排列均可），如下图所示：

对于排序，我原来只知道利用ROWNUM伪列：

SELECT E.*,
ROWNUM
FROM (SELECT *
FROM EMPLOYEES
ORDER BY SALARY DESC) E

然后再用这个结果集和其他表关联，针对本题，这种方法可以查出每个员工在整个公司以及本部门工资的排名，但在我看来有如下两个问题：

1、一张事实表关联两次，效率低下；

2、无法查询出每个员工在整个公司以及本部门工资的排名，除非用循环，但又不是一条语句了。

数据库表中对数值字段进行排名，应使用RANK函数，该函数的作用就是计算一个值在一组值中的排名，返回值为数值型，形式为RANK() OVER (PARTITION BY … ORDER BY …)，其中：

1、PARTITION BY关键字为用于排序的分组，也就是说如果查询全部数据中的排名，则该关键字可省略；

2、ORDER BY关键字即为按哪个字段进行排序，空或ASC为升序，DESC为降序，与标准SQL没区别；

3、如果两行数据用于排序的字段值完全相同，则二者的RANK返回值（序号）也相同，序号不连续（英文是Identical salary values receive the same rank and cause nonconsecutive ranks），还有类似的DENSE_RANK()函数，相同值的序号相同，序号连续；ROW_NUMBER()函数，每个值的序号唯一，相同值有可能按照ROWNUM的次序编号（这个不确定，官方文档没说，我推测的-_-），这三个函数的使用方法相同。