Oracle_sql练习题3

这些题目涉及到的表通常包括：

**员工表（Employees）：** 包含员工的个人信息，如姓名、工资、入职日期等。可能包括列如下：
   - employee_id
   - employee_name
   - salary
   - hire_date 
   -department_name
   - manager_id （指向上级员工的ID）

 **部门表（Departments）：** 包含部门的信息，如部门名称、部门ID等。可能包括列如下：

   - department_id
   - department_name

1. **题目：** 查询每个部门中工资排名第二高的员工的姓名、工资以及部门名称。   **答案：**

   SELECT e.employee_name, e.salary, d.department_name
   FROM (
       SELECT employee_name, salary, department_id,
              ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
       FROM employees
   ) e
   JOIN departments d ON e.department_id = d.department_id
   WHERE e.salary_rank = 2;

**思路：** 使用窗口函数 `ROW_NUMBER()` 对每个部门的员工按工资降序排列，并给每个员工分配一个排名。然后，将结果作为子查询，筛选出排名为2的员工，并通过连接部门表获取部门名称。

2. **题目：** 查询员工表中工资比其所在部门平均工资高的员工姓名、工资以及部门名称。

   **答案：**

   SELECT e.employee_name, e.salary, d.department_name
   FROM employees e
   JOIN departments d ON e.department_id = d.department_id
   JOIN (
       SELECT department_id, AVG(salary) AS avg_salary
       FROM employees
       GROUP BY department_id
   ) avg_salaries ON e.department_id = avg_salaries.department_id
   WHERE e.salary > avg_salaries.avg_salary;

   **思路：** 首先，使用子查询计算每个部门的平均工资。然后，将员工表与部门表连接，并再次连接平均工资子查询，筛选出工资高于所在部门平均工资的员工。

3. **题目：** 查询员工表中连续工作了超过5年的员工姓名、入职日期和离职日期。

   **答案：**

   SELECT employee_name, hire_date, leave_date
   FROM (
       SELECT employee_name, hire_date,
              LEAD(hire_date, 5) OVER(PARTITION BY employee_id ORDER BY hire_date) AS leave_date
       FROM employees
   ) 
   WHERE leave_date IS NOT NULL;

   **思路：** 使用窗口函数 `LEAD()` 获取每个员工的后续入职日期，然后计算出与当前入职日期相差超过5年的日期作为离职日期。

4. **题目：** 查询员工表中入职日期最晚的员工信息。

   **答案：**

   SELECT * FROM employees WHERE hire_date = (SELECT MAX(hire_date) FROM employees);

   **思路：** 使用子查询获取入职日期最晚的日期，并将其与员工表进行比较，以获取相应的员工信息。

5. **题目：** 查询员工表中任意两个员工的工资差异最大的员工姓名和工资。

   **答案：**

   SELECT e1.employee_name, e1.salary, e2.employee_name, e2.salary, ABS(e1.salary - e2.salary) AS salary_difference
   FROM employees e1
   CROSS JOIN employees e2
   WHERE e1.employee_id < e2.employee_id
   ORDER BY salary_difference DESC
   FETCH FIRST ROW ONLY;

   **思路：** 使用交叉连接 `CROSS JOIN` 获取所有员工的组合，并计算他们之间的工资差异，然后按照工资差异降序排列，选取差异最大的一组。

6. **题目：** 查询员工表中每个月入职员工的数量，并按照月份升序排列。

   **答案：**

   SELECT TO_CHAR(hire_date, 'YYYY-MM') AS hire_month, COUNT(*) AS num_employees
   FROM employees
   GROUP BY TO_CHAR(hire_date, 'YYYY-MM')
   ORDER BY hire_month;

   **思路：** 使用 `TO_CHAR()` 函数将入职日期转换为年月格式，并使用 `GROUP BY` 按照月份分组，然后计算每月入职员工的数量。

7. **题目：** 查询员工表中工资处于前20%的员工的姓名、工资以及工资百分位排名。

   **答案：**

   SELECT employee_name, salary, PERCENT_RANK() OVER (ORDER BY salary) AS salary_percent_rank
   FROM employees
   WHERE PERCENT_RANK() OVER (ORDER BY salary) <= 0.2;

   **思路：** 使用窗口函数 `PERCENT_RANK()` 计算每个员工的工资百分位排名，并筛选出排名在前20%内的员工。PERCENT_RANK()用于计算当前行的值相对于所有值的排名百分比。

8. **题目：** 查询员工表中的员工信息，如果该员工有上级，则同时显示上级的姓名。

   **答案：**

   SELECT e.employee_name, e.salary, e.manager_id, m.employee_name AS manager_name
   FROM employees e
   LEFT JOIN employees m ON e.manager_id = m.employee_id;

   **思路：** 使用自连接（self-join）将员工表与自身连接，通过匹配员工表中的 `manager_id` 字段和员工表的 `employee_id` 字段，从而获取上级的姓名。

9. **题目：** 查询员工表中入职日期早于其上级的员工信息。

   **答案：**

   SELECT e.employee_name, e.hire_date, e.manager_id
   FROM employees e
   JOIN employees m ON e.manager_id = m.employee_id
   WHERE e.hire_date < m.hire_date;

   **思路：** 使用自连接将员工表与自身连接，并通过匹配员工表中的 `manager_id` 字段和员工表的 `employee_id` 字段，然后筛选出入职日期早于上级的员工信息。

10. **题目：** 查询员工表中连续工作了不少于3年的员工的数量。   

      **答案：**

    SELECT COUNT(*) AS num_employees
    FROM (
        SELECT employee_id
        FROM employees
        GROUP BY employee_id
        HAVING MIN(hire_date) < ADD_MONTHS(SYSDATE, -36)
    ) employees_worked_3_years;

    **思路：** 使用 `GROUP BY` 子句按照员工ID分组，并使用 `HAVING` 子句筛选出至少有一次入职日期早于当前日期3年之前的员工，然后统计符合条件的员工数量。