Oracle SQL 常用指令、操作

CREATE TABLE employees (
    emp_id      NUMBER PRIMARY KEY,
    emp_name    VARCHAR2(50),
    dept_id     NUMBER,
    salary      NUMBER,
    hire_date   DATE
);

INSERT INTO employees VALUES (1, 'Alice',   10, 6000, TO_DATE('2015-01-10', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (2, 'Bob',     10, 8000, TO_DATE('2016-03-12', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (3, 'Charlie', 20, 5000, TO_DATE('2017-06-01', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (4, 'David',   30, 9000, TO_DATE('2014-08-20', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (5, 'Eva',     20, 7000, TO_DATE('2018-11-15', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (6, 'Frank',   30, 8000, TO_DATE('2015-12-01', 'YYYY-MM-DD'));

SELECT * FROM employees;

CREATE TABLE departments (
    dept_id   NUMBER PRIMARY KEY,
    dept_name VARCHAR2(50)
);

INSERT INTO departments VALUES (10, 'Sales');
INSERT INTO departments VALUES (20, 'IT');
INSERT INTO departments VALUES (30, 'HR');

SELECT * FROM departments;

CREATE TABLE projects (
    project_id NUMBER PRIMARY KEY,
    project_name VARCHAR2(50),
    emp_id NUMBER,
    start_date DATE,
    end_date DATE
);

INSERT INTO projects VALUES (101, 'ERP Upgrade', 1, TO_DATE('2020-01-01','YYYY-MM-DD'), TO_DATE('2020-12-31','YYYY-MM-DD'));
INSERT INTO projects VALUES (102, 'Mobile App', 3, TO_DATE('2021-03-01','YYYY-MM-DD'), TO_DATE('2021-09-30','YYYY-MM-DD'));
INSERT INTO projects VALUES (103, 'Data Migration', 5, TO_DATE('2019-05-01','YYYY-MM-DD'), TO_DATE('2020-03-31','YYYY-MM-DD'));
INSERT INTO projects VALUES (104, 'Cloud Migration', 2, TO_DATE('2021-06-01','YYYY-MM-DD'), TO_DATE('2022-02-28','YYYY-MM-DD'));

SELECT * FROM projects;

SELECT emp_id, emp_name, salary FROM employees ORDER BY salary DESC;

-- 查詢薪資超過 7000 的員工，按薪資降序排列
SELECT emp_id, emp_name, salary
FROM employees
WHERE salary > 7000
ORDER BY salary DESC;

SELECT emp_name, dept_id FROM employees ORDER BY dept_id;

-- 查詢員工所屬的所有部門，去除重複值
SELECT DISTINCT dept_id
FROM employees;

SELECT emp_name FROM employees ORDER BY emp_name;

-- 將員工姓名轉為大寫，並計算姓名長度
SELECT emp_name, UPPER(emp_name) AS upper_name, LENGTH(emp_name) AS name_len
FROM employees;

SELECT emp_name, hire_date FROM employees ORDER BY hire_date;

-- 計算員工雇用日期後 6 個月的日期，以及工作月數
SELECT emp_name,
       hire_date,
       ADD_MONTHS(hire_date, 6) AS after_6_months,
       MONTHS_BETWEEN(SYSDATE, hire_date) AS months_worked
FROM employees;

SELECT emp_name, dept_id, salary FROM employees ORDER BY dept_id, salary DESC;

-- 按部門分組，計算各部門的員工數、總薪資和平均薪資
SELECT dept_id,
       COUNT(*) AS emp_count,
       SUM(salary) AS total_salary,
       ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY dept_id
ORDER BY dept_id;

-- 員工表
SELECT emp_name, dept_id FROM employees ORDER BY dept_id, emp_name;

-- 部門表
SELECT dept_id, dept_name FROM departments ORDER BY dept_id;

-- 連接員工表和部門表，顯示員工姓名和部門名稱
SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

-- 員工表
SELECT emp_name, dept_id FROM employees ORDER BY dept_id, emp_name;

-- 部門表
SELECT dept_id, dept_name FROM departments ORDER BY dept_id;

-- 左連接員工表和部門表，保留所有員工資料
SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

-- 員工表
SELECT emp_id, emp_name, dept_id FROM employees ORDER BY emp_name;

-- 部門表
SELECT dept_id, dept_name FROM departments ORDER BY dept_id;

-- 專案表
SELECT emp_id, project_name FROM projects ORDER BY emp_id;

-- 三表連接：員工表、部門表、專案表，顯示員工的部門和專案資訊
SELECT e.emp_name, d.dept_name, p.project_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
LEFT JOIN projects p ON e.emp_id = p.emp_id
ORDER BY e.emp_name;

-- 按部門分組，對每個部門內的員工按薪資進行排名
SELECT emp_name,
       dept_id,
       salary,
       RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS rank_salary,
       DENSE_RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS dense_rank_salary,
       ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS row_num
FROM employees;

SELECT emp_name, dept_id, salary FROM employees ORDER BY dept_id, salary DESC;

-- 在每行顯示部門的總薪資、平均薪資和員工數
SELECT emp_name,
       dept_id,
       salary,
       SUM(salary) OVER(PARTITION BY dept_id) AS dept_salary_total,
       AVG(salary) OVER(PARTITION BY dept_id) AS dept_avg_salary,
       COUNT(*) OVER(PARTITION BY dept_id) AS dept_emp_count
FROM employees;

SELECT emp_name, dept_id, salary FROM employees ORDER BY dept_id, salary DESC;

-- 計算每個部門內按薪資降序的累計總和，區分不同累計方式
SELECT emp_name,
       dept_id,
       salary,
       SUM(salary) OVER(PARTITION BY dept_id ORDER BY salary DESC) AS running_total,
       SUM(salary) OVER(PARTITION BY dept_id ORDER BY salary DESC ROWS UNBOUNDED PRECEDING) AS cumulative_total
FROM employees;

SELECT emp_name, dept_id, salary FROM employees ORDER BY dept_id, salary DESC;

-- 計算每個員工薪資在部門內和整體的佔比百分比
SELECT emp_name,
       dept_id,
       salary,
       ROUND(salary * 100.0 / SUM(salary) OVER(PARTITION BY dept_id), 2) AS salary_percentage,
       ROUND(salary * 100.0 / SUM(salary) OVER(), 2) AS total_percentage
FROM employees
ORDER BY dept_id, salary DESC;

SELECT emp_name, dept_id, salary FROM employees ORDER BY dept_id, salary DESC;

-- 計算 3 點移動平均和累計移動平均，用於趨勢分析
SELECT emp_name,
       dept_id,
       salary,
       ROUND(AVG(salary) OVER(PARTITION BY dept_id ORDER BY salary DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), 2) AS moving_avg_3,
       ROUND(AVG(salary) OVER(PARTITION BY dept_id ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS moving_avg_all
FROM employees;

SELECT emp_name, dept_id, salary FROM employees ORDER BY dept_id, salary DESC;

-- 將每個部門的員工按薪資分成 3 等份，並計算百分位排名
SELECT emp_name,
       dept_id,
       salary,
       NTILE(3) OVER(PARTITION BY dept_id ORDER BY salary DESC) AS salary_tier,
       PERCENT_RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS percentile_rank
FROM employees;

SELECT emp_name, dept_id, salary FROM employees ORDER BY dept_id, salary DESC;

-- 使用子查詢和 RANK 函數，找出每個部門薪資最高的員工
SELECT emp_name, dept_id, salary
FROM (
    SELECT emp_name,
           dept_id,
           salary,
           RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS rk
    FROM employees
)
WHERE rk = 1;

SELECT emp_name, dept_id, salary FROM employees ORDER BY dept_id, salary DESC;

-- 使用子查詢和 DENSE_RANK 函數，找出每個部門薪資排名前 2 的員工
SELECT emp_name, dept_id, salary
FROM (
    SELECT emp_name,
           dept_id,
           salary,
           DENSE_RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS rnk
    FROM employees
)
WHERE rnk <= 2
ORDER BY dept_id, salary DESC;

SELECT emp_id, project_name, start_date, end_date FROM projects ORDER BY emp_id, start_date;

-- 自連接專案表，找出同一個員工負責的時間重疊專案
SELECT p1.emp_id, p1.project_name, p2.project_name
FROM projects p1
JOIN projects p2
  ON p1.emp_id = p2.emp_id
 AND p1.project_id < p2.project_id
 AND p1.start_date <= p2.end_date
 AND p1.end_date >= p2.start_date;

SELECT emp_name, dept_id, salary FROM employees ORDER BY dept_id, salary DESC;

-- 結合 GROUP BY 和 PARTITION BY，創建部門薪資綜合分析報表
SELECT 
    dept_id,
    COUNT(*) AS dept_emp_count,
    SUM(salary) AS dept_total_salary,
    ROUND(AVG(salary), 2) AS dept_avg_salary,
    MAX(salary) AS dept_max_salary,
    MIN(salary) AS dept_min_salary,
    ROUND(
        (MAX(salary) - MIN(salary)) * 100.0 / AVG(salary), 2
    ) AS salary_range_percentage,
    RANK() OVER(ORDER BY AVG(salary) DESC) AS dept_rank_by_avg,
    ROUND(
        AVG(salary) * 100.0 / SUM(AVG(salary)) OVER(), 2
    ) AS dept_avg_percentage
FROM employees
GROUP BY dept_id
ORDER BY dept_id;