Oracle练习题

以下是10道较为复杂的Oracle SQL练习题目，并附带详细的解答。

1.  从employees表中找出工资高于其所在部门平均工资的员工信息。

解答：

SELECT e1.* 
FROM employees e1 
WHERE e1.salary > ( 
    SELECT AVG(e2.salary) 
    FROM employees e2 
    WHERE e2.department_id = e1.department_id 
);

解释：通过子查询计算每个部门的平均工资，然后在外层查询中比较员工工资是否高于该部门的平均工资。

2. 将employees表中的工资分为A、B、C三个等级，其中A表示工资高于5000，B表示工资在3000到5000之间，C表示工资低于3000。

解答：

SELECT   
    id,   
    name,   
    salary,  
    CASE   
        WHEN salary > 5000 THEN 'A'  
        WHEN salary BETWEEN 3000 AND 5000 THEN 'B'  
        ELSE 'C'  
    END AS salary_level  
FROM employees;

解释：使用CASE语句根据工资值进行条件判断，并返回相应的等级。

3. 从employees表中查询每个部门的最高工资和最低工资。

解答：

SELECT   
    department_id,   
    MAX(salary) AS max_salary,   
    MIN(salary) AS min_salary  
FROM employees  
GROUP BY department_id;

解释：使用聚合函数MAX和MIN找到每个部门的最高和最低工资，通过GROUP BY子句按部门进行分组。

4. 查询与ID为1001的员工同部门的所有员工信息。

解答：

SELECT e.*  
FROM employees e  
WHERE e.department_id = (  
    SELECT department_id  
    FROM employees  
    WHERE id = 1001  
);

解释：通过子查询找到ID为1001的员工的部门ID，然后在外层查询中查找该部门的所有员工。

5. 在employees表中，按部门分组并计算每个员工的工资排名（按工资降序排列）。

解答：

SELECT   
    id,   
    name,   
    department_id,   
    salary,  
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank  
FROM employees;

解释：使用RANK窗口函数为每个部门的员工按工资降序排名。PARTITION BY子句指定了分组的字段。

6. 假设salary_history表中记录了员工的历次加薪记录，每条记录包含员工ID、加薪日期和加薪后的工资。查找那些连续三次加薪的员工。

解答（这个题目相对复杂，需要自连接和窗口函数）：

WITH RankedSalary AS (  
    SELECT   
        id,   
        salary_date,   
        salary,  
        LAG(salary_date, 1) OVER (PARTITION BY id ORDER BY salary_date) AS prev_salary_date,  
        LAG(salary, 1) OVER (PARTITION BY id ORDER BY salary_date) AS prev_salary,  
        LEAD(salary_date, 1) OVER (PARTITION BY id ORDER BY salary_date) AS next_salary_date,  
        LEAD(salary, 1) OVER (PARTITION BY id ORDER BY salary_date) AS next_salary  
    FROM salary_history  
),  
ConsecutiveRaises AS (  
    SELECT   
        id  
    FROM RankedSalary  
    WHERE   
        salary > prev_salary AND salary > next_salary AND  
        (salary_date - prev_salary_date) = (next_salary_date - salary_date)  
)  
SELECT DISTINCT id  
FROM ConsecutiveRaises;

解释：

1. RankedSalary子查询：

   • 在这个子查询中，首先从salary_history表中选择员工的ID (id)、工资发放日期 (salary_date)、工资 (salary)。
   • 使用LAG窗口函数获取每个员工的前一个工资发放日期 (prev_salary_date) 和前一个工资 (prev_salary)。LAG函数在这里按照每个员工的ID进行分区，并按工资发放日期排序，以获取前一个记录的值。
   • 使用LEAD窗口函数获取每个员工的下一个工资发放日期 (next_salary_date) 和下一个工资 (next_salary)。与LAG类似，LEAD也是按照每个员工的ID进行分区，并按工资发放日期排序，但它是获取下一个记录的值。

2. ConsecutiveRaises子查询：

   • 这个子查询基于RankedSalary子查询的结果，选择那些当前工资高于前一个工资，同时也高于下一个工资的员工ID。
   • 另外，它还确保工资增长是连续的，即当前工资发放日期与前一个工资发放日期之间的间隔与下一个工资发放日期与当前工资发放日期之间的间隔相同。这确保了工资是连续增长的，没有间断。

3. 最终的查询：

   • 这个查询从ConsecutiveRaises子查询中选择不重复的员工ID。
   • 结果将是一个列表，其中列出了所有连续获得工资增长的员工的ID。