6-6 Oracle表复杂查询 -合并查询-增删改数据

-- 假设我们已经有了一个简单的表结构和数据
CREATE TABLE employees (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    department_id NUMBER
);
 
-- 插入一些示例数据
INSERT INTO employees (id, name, department_id) VALUES (1, 'Alice', 10);
INSERT INTO employees (id, name, department_id) VALUES (2, 'Bob', 20);
INSERT INTO employees (id, name, department_id) VALUES (3, 'Charlie', 10);
INSERT INTO employees (id, name, department_id) VALUES (4, 'Dave', 20);
 
-- 查询每个部门的员工人数
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;
 
-- 将department_id为20的所有员工调整到新部门30
UPDATE employees
SET department_id = 30
WHERE department_id = 20;
 
-- 删除id为2的员工信息
DELETE FROM employees
WHERE id = 2;
 
-- 插入一个新的员工到部门10
INSERT INTO employees (id, name, department_id) VALUES (5, 'Eve', 10);
 
-- 查询每个部门的员工人数，并按员工数降序排列
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
ORDER BY employee_count DESC;

这个例子展示了如何在Oracle数据库中进行复杂的查询操作，包括合并查询结果、更新数据和删除数据，并最终以降序方式排列结果。这些操作对于数据库管理和数据分析非常重要。