CREATE TABLE employees (
    id BIGSERIAL PRIMARY KEY,
    full_name TEXT,
    email_address TEXT,
    dept_code VARCHAR(10),
    annual_salary NUMERIC(10,2),
    hire_date DATE,
    tags TEXT[]
);

INSERT INTO employees (full_name, email_address, dept_code, annual_salary, hire_date, tags) VALUES
('Alice Johnson', 'alice.j@company.com', 'IT', 85000, '2023-01-15', '{"dev","lead"}'),
('Bob Smith',     'bob.s@company.com',   'HR', 52000, '2022-06-10', '{"hr"}'),
('Carol White',   'carol.w@company.com', 'IT', 92000, '2024-03-20', '{"dev","ai"}'),
('Dave Brown',    NULL,                  'IT', 78000, '2021-11-05', '{"dev"}'),
('Eve Davis',     'eve.d@company.com',   'HR', 48000, '2023-12-01', '{"hr","recruit"}');

SELECT 
    full_name AS name,
    annual_salary AS salary,
    hire_date AS "Hire Date"  -- 保留空格/大小寫
FROM employees;

SELECT 
    full_name,
    annual_salary / 12 AS monthly_salary,
    UPPER(full_name) AS "FULL NAME",
    COALESCE(email_address, 'N/A') AS email
FROM employees;

SELECT 
    dept_code,
    COUNT(*) AS headcount,
    AVG(annual_salary) AS avg_salary,
    STRING_AGG(full_name, ', ') AS team_members
FROM employees
GROUP BY dept_code;

SELECT e.full_name, e.annual_salary
FROM employees AS e;
-- 等價(jià)于
FROM employees e;

-- 員工與經(jīng)理
SELECT 
    e.full_name AS employee,
    m.full_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

SELECT 
    e.full_name,
    d.name AS dept_name
FROM employees e
JOIN departments d ON e.dept_code = d.code;

WITH 
it_employees AS (
    SELECT * FROM employees WHERE dept_code = 'IT'
),
high_earners AS (
    SELECT full_name, annual_salary
    FROM it_employees
    WHERE annual_salary > 80000
)
SELECT * FROM high_earners;

SELECT 
    full_name,
    EXTRACT(YEAR FROM hire_date) AS hire_year,
    ROW_NUMBER() OVER (PARTITION BY dept_code ORDER BY annual_salary DESC) AS rank_in_dept
FROM employees;

SELECT 
    full_name,
    tags[1] AS primary_tag,
    config->>'level' AS "Employee Level"
FROM employees;

-- 推薦
SELECT e.full_name AS employee_name FROM employees e;

-- 不推薦
SELECT employees.full_name AS name FROM employees;  -- 太長

SELECT full_name, annual_salary AS salary
FROM employees
ORDER BY salary DESC;

ORDER BY 2  -- 模糊，易出錯

-- 性能相同
SELECT full_name AS name FROM employees;
SELECT full_name FROM employees;

-- 錯誤：WHERE 不能用別名
SELECT full_name, annual_salary AS salary
FROM employees
WHERE salary > 80000;  -- 語法錯誤！

-- 正確
SELECT full_name, annual_salary AS salary
FROM employees
WHERE annual_salary > 80000;

-- GROUP BY 別名錯誤
SELECT dept_code, COUNT(*) AS cnt
FROM employees
GROUP BY cnt;  -- 錯誤！

-- 正確
GROUP BY dept_code;
-- 或
GROUP BY 1;  -- 列序號

WITH active_employees AS (
    SELECT 
        id,
        full_name AS employee_name,
        email_address AS email,
        dept_code,
        annual_salary AS salary,
        hire_date
    FROM employees
    WHERE hire_date >= '2023-01-01'
),
dept_stats AS (
    SELECT 
        dept_code,
        COUNT(*) AS headcount,
        AVG(salary) AS avg_salary,
        MAX(salary) AS max_salary
    FROM active_employees
    GROUP BY dept_code
)
SELECT 
    ae.employee_name,
    ae.email,
    ae.salary,
    ds.headcount,
    ds.avg_salary,
    RANK() OVER (PARTITION BY ae.dept_code ORDER BY ae.salary DESC) AS salary_rank
FROM active_employees ae
JOIN dept_stats ds ON ae.dept_code = ds.dept_code
ORDER BY ds.avg_salary DESC, ae.salary DESC;

-- 1. 列別名
SELECT full_name AS name, annual_salary AS salary FROM employees;

-- 2. 表別名 + 自連接
SELECT e.full_name AS emp, m.full_name AS mgr 
FROM employees e 
LEFT JOIN employees m ON e.manager_id = m.id;

-- 3. CTE 別名
WITH it_team AS (SELECT * FROM employees WHERE dept_code = 'IT')
SELECT employee_name, salary FROM it_team;

-- 4. 窗口函數(shù)別名
SELECT 
    full_name,
    dept_code,
    ROW_NUMBER() OVER (PARTITION BY dept_code ORDER BY annual_salary DESC) AS rank
FROM employees;

-- 5. ORDER BY 別名
SELECT annual_salary * 12 AS yearly 
FROM employees 
ORDER BY yearly DESC;

-- 視圖替代 CTE 別名
CREATE VIEW v_active_employees AS
SELECT id, full_name AS name, annual_salary AS salary
FROM employees WHERE hire_date >= '2023-01-01';