Oracle_sql练习题2

1. **题目：** 创建一个名为`departments`的表，包含部门的ID、名称和位置。编写一个SQL查询，列出每个位置有多少个部门。

    **答案和解析：**

    SELECT location, COUNT(*) AS department_count
    FROM departments
    GROUP BY location;

这个查询使用了`GROUP BY`子句按位置进行分组，并使用了`COUNT(*)`函数计算每个位置的部门数量。

2. **题目：** 编写一个SQL查询，找出每个部门中薪水排名前三的员工的姓名和薪水。

    **答案和解析：**

    SELECT department_id, employee_name, salary
    FROM (
        SELECT department_id, employee_name, salary,
               RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
        FROM employees
    ) ranked_employees
    WHERE salary_rank <= 3;

这个查询使用了分析函数`RANK()`来对每个部门的员工按薪水进行排名，并筛选出排名前三的员工，OVER（）用来限制RANK（）之类函数的作用范围。也可使用 ROW_NUMBER()代替RANK（），两者区别：

• ROW_NUMBER()：为结果集中的每一行分配一个唯一的连续整数值，即使值完全相同也会依次递增序号，不会重复。
• RANK()：当遇到相同的值时，会赋予相同的排名，并且下一个不同值的排名会跳过相应的数量。这意味着排名不是连续的。

 ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY salary DESC)

3. **题目：** 编写一个SQL查询，找出至少有两名员工的部门的部门ID和员工数量。

    **答案和解析：**

    SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id
    HAVING COUNT(*) >= 2;

这个查询使用了`GROUP BY`子句按部门ID进行分组，并使用了`HAVING`子句筛选出至少有两名员工的部门。

4. **题目：** 创建一个名为`orders`的表，包含订单的ID、客户ID、订单日期和订单总额。编写一个SQL查询，找出每个月的订单数量，并按订单数量降序排列。

    **答案和解析：**

    SELECT EXTRACT(MONTH FROM order_date) AS month, COUNT(*) AS order_count
    FROM orders
    GROUP BY EXTRACT(MONTH FROM order_date)
    ORDER BY order_count DESC;

这个查询使用`EXTRACT()`函数提取订单日期中的月份，并使用`GROUP BY`子句按月份进行分组，并按订单数量降序排列。

5. **题目：** 创建一个名为`products`的表，包含产品的ID、名称和价格。编写一个SQL查询，找出价格低于平均价格的产品。

    **答案和解析：**

    SELECT *
    FROM products
    WHERE price < (SELECT AVG(price) FROM products);

这个查询使用了子查询来计算产品价格的平均值，并通过`WHERE`子句筛选出价格低于平均价格的产品。你不能直接在WHERE子句中使用聚合函数（如AVG()）来过滤结果，因为WHERE子句在聚合操作之前执行。

6. **题目：** 编写一个SQL查询，找出至少购买了两种产品的客户的客户ID和姓名。

    **答案和解析：**

    SELECT customer_id, customer_name
    FROM (
        SELECT customer_id, customer_name, COUNT(DISTINCT product_id) AS product_count
        FROM orders
        JOIN customers ON orders.customer_id = customers.customer_id
        JOIN order_items ON orders.order_id = order_items.order_id
        GROUP BY customer_id, customer_name
    ) customer_product_count
    WHERE product_count >= 2;

这个查询使用了`COUNT(DISTINCT ...)`函数计算每个客户购买的不同产品数量，并通过子查询和`JOIN`筛选出购买了至少两种产品的客户。

7. **题目：** 创建一个名为`sales`的表，包含销售记录的ID、销售日期和销售金额。编写一个SQL查询，找出每个季度的销售总额。

    **答案和解析：**

    SELECT TO_CHAR(sales_date, 'Q') AS quarter, SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY TO_CHAR(sales_date, 'Q');

这个查询使用了`TO_CHAR()`函数将销售日期转换为季度，并使用`GROUP BY`子句按季度进行分组，并计算每个季度的销售总额。

8. **题目：** 编写一个SQL查询，找出每个部门中薪水高于该部门平均薪水的员工的姓名和薪水。

    **答案和解析：**

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

这个查询使用了子查询来计算每个部门的平均薪水，并通过`JOIN`将结果与员工表连接，然后筛选出薪水高于部门平均薪水的员工。

9. **题目：** 创建一个名为`products`的表，包含产品的ID、名称和创建日期。编写一个SQL查询，找出每个月创建的产品数量，并按月份升序排列。

    **答案和解析：**

    SELECT EXTRACT(MONTH FROM create_date) AS month, COUNT(*) AS product_count
    FROM products
    GROUP BY EXTRACT(MONTH FROM create_date)
    ORDER BY month;

这个查询使用`EXTRACT()`函数提取产品创建日期中的月份，并使用`GROUP BY`子句按月份进行分组，并按月份升序排列。

10. **题目：** 创建一个名为`orders`的表，包含订单的ID、客户ID、订单日期和订单状态。编写一个SQL查询，找出每种订单状态下的订单数量，并按数量降序排列。

    **答案和解析：**

    SELECT order_status, COUNT(*) AS order_count
    FROM orders
    GROUP BY order_status
    ORDER BY order_count DESC;

这个查询使用了GROUP BY子句按订单状态进行分组，并使用COUNT(*)函数计算每种订单状态下的订单数量。然后使用ORDER BY子句将结果按订单数量降序排列。