-- Natural Language: "Show all employees in IT department with salaries above average"
SELECT 
    e.employee_id,
    e.first_name,
    e.last_name,
    e.salary,
    d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'IT'
AND e.salary > (
    SELECT AVG(salary) 
    FROM employees
)
ORDER BY e.salary DESC

-- Advanced Oracle-specific features
WITH employee_stats AS (
    SELECT 
        department_id,
        COUNT(*) as emp_count,
        AVG(salary) as avg_salary,
        PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY salary) as median_salary
    FROM employees
    GROUP BY department_id
),
dept_rankings AS (
    SELECT 
        department_id,
        emp_count,
        avg_salary,
        median_salary,
        RANK() OVER (ORDER BY avg_salary DESC) as salary_rank
    FROM employee_stats
)
SELECT 
    d.department_name,
    r.emp_count,
    TO_CHAR(r.avg_salary, '$999,999.00') as average_salary,
    TO_CHAR(r.median_salary, '$999,999.00') as median_salary,
    r.salary_rank
FROM dept_rankings r
JOIN departments d ON

-- Query performance metrics
SELECT 
    sql_id,
    sql_text,
    executions,
    elapsed_time/1000000 as elapsed_seconds,
    (elapsed_time/1000000)/NULLIF(executions,0) as avg_seconds,
    buffer_gets/NULLIF(executions,0) as avg_buffer_gets,
    rows_processed/NULLIF(executions,0) as avg_rows
FROM v$sql
WHERE parsing_schema_name = USER
AND last_active_time > SYSDATE - 1
ORDER BY elapsed_time DESC

-- Index analysis and suggestions
SELECT 
    table_name,
    column_name,
    index_name,
    CASE 
        WHEN index_name IS NULL THEN 'Consider adding index'
        ELSE 'Index exists'
    END as index_status,
    num_distinct,
    num_nulls,
    density
FROM all_tab_columns
WHERE table_name = 'EMPLOYEES'
ORDER BY num_distinct DESC

-- AI-generated PL/SQL package
CREATE OR REPLACE PACKAGE emp_mgmt AS
    /* Package for employee management */
    
    -- Get employee details
    FUNCTION get_employee_details(
        p_employee_id IN employees.employee_id%TYPE
    ) RETURN SYS_REFCURSOR;
    
    -- Update salary
    PROCEDURE update_employee_salary(
        p_employee_id IN employees.employee_id%TYPE,
        p_new_salary IN employees.salary%TYPE,
        p_reason IN VARCHAR2
    );
    
    -- Department statistics
    FUNCTION get_dept_statistics(
        p_dept_id IN departments.department_id%TYPE
    ) RETURN SYS_REFCURSOR;
END

-- Robust error handling templates
CREATE OR REPLACE PROCEDURE safe_update_salary(
    p_employee_id IN NUMBER,
    p_new_salary IN NUMBER
) AS
    v_old_salary employees.salary%TYPE;
    e_invalid_salary EXCEPTION;
BEGIN
    -- Validate salary
    SELECT salary INTO v_old_salary
    FROM employees
    WHERE employee_id = p_employee_id;
    
    IF p_new_salary > v_old_salary * 1.5 THEN
        RAISE e_invalid_salary;
    END IF;
    
    -- Update salary
    UPDATE employees
    SET salary = p_new_salary
    WHERE employee_id = p_employee_id;
    
    COMMIT;
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        raise_application_error(-20001, 'Employee not found');
    WHEN e_invalid_salary THEN
        raise_application_error(-20002, 'Invalid salary increase');
    WHEN OTHERS THEN
        rollback;
        raise_application_error(-20999, 'Error: ' || SQLERRM);
END

-- Execution plan optimization
EXPLAIN PLAN FOR
SELECT /*+ GATHER_PLAN_STATISTICS */
    d.department_name,
    COUNT(*) as employee_count,
    AVG(e.salary) as avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)

-- Real-time performance metrics
SELECT 
    ss.username,
    ss.sql_id,
    ss.sql_text,
    ss.elapsed_time/1000000 as elapsed_seconds,
    ss.cpu_time/1000000 as cpu_seconds,
    ss.buffer_gets,
    ss.disk_reads,
    ss.rows_processed
FROM v$sqlarea sa
JOIN v$session ss ON sa.sql_id = ss.sql_id
WHERE ss.status = 'ACTIVE'
AND ss.username IS NOT NULL
ORDER BY ss.elapsed_time DESC