Writing Queries

-- Basic query structure
SELECT 
    column1,
    column2,
    column3
FROM 
    table_name
WHERE 
    condition = 'value'
ORDER BY 
    column1 DESC;

-- Get recent invoices
SELECT 
    INVOICE_NUM,
    INVOICE_DATE,
    SUPPLIER_NAME,
    INVOICE_AMOUNT
FROM 
    AP_INVOICES_ALL
WHERE 
    CREATION_DATE >= SYSDATE - 30
    AND ROWNUM <= 100
ORDER BY 
    CREATION_DATE DESC;

-- Good: Limits rows for performance
SELECT * FROM large_table WHERE ROWNUM <= 1000;

-- Bad: No limit on large table
SELECT * FROM large_table;

-- Good: Select only needed columns
SELECT employee_id, first_name, last_name FROM employees;

-- Bad: Select all columns unnecessarily
SELECT * FROM employees;

SELECT 
    emp.EMPLOYEE_ID AS "Employee ID",
    emp.FIRST_NAME || ' ' || emp.LAST_NAME AS "Full Name",
    dept.DEPARTMENT_NAME AS "Department"
FROM 
    PER_EMPLOYEES emp
    JOIN HR_DEPARTMENTS dept ON emp.DEPARTMENT_ID = dept.DEPARTMENT_ID;

-- Employees
SELECT * FROM PER_ALL_PEOPLE_F WHERE ROWNUM <= 10;

-- Assignments  
SELECT * FROM PER_ALL_ASSIGNMENTS_M WHERE ROWNUM <= 10;

-- Jobs
SELECT * FROM PER_JOBS WHERE ROWNUM <= 10;

-- Invoices
SELECT * FROM AP_INVOICES_ALL WHERE ROWNUM <= 10;

-- Suppliers
SELECT * FROM POZ_SUPPLIERS WHERE ROWNUM <= 10;

-- Payments
SELECT * FROM AP_INVOICE_PAYMENTS_ALL WHERE ROWNUM <= 10;

-- Purchase Orders
SELECT * FROM PO_HEADERS_ALL WHERE ROWNUM <= 10;

-- Inventory Items
SELECT * FROM EGP_SYSTEM_ITEMS_B WHERE ROWNUM <= 10;

-- Shipments
SELECT * FROM RCV_SHIPMENT_HEADERS WHERE ROWNUM <= 10;

WITH recent_invoices AS (
    SELECT 
        INVOICE_ID,
        INVOICE_NUM,
        INVOICE_AMOUNT
    FROM 
        AP_INVOICES_ALL
    WHERE 
        INVOICE_DATE >= ADD_MONTHS(SYSDATE, -3)
)
SELECT 
    ri.*,
    s.SUPPLIER_NAME
FROM 
    recent_invoices ri
    JOIN POZ_SUPPLIERS s ON ri.VENDOR_ID = s.VENDOR_ID
WHERE 
    ROWNUM <= 100;

SELECT 
    EMPLOYEE_ID,
    SALARY,
    DEPARTMENT_ID,
    AVG(SALARY) OVER (PARTITION BY DEPARTMENT_ID) AS dept_avg_salary,
    RANK() OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS salary_rank
FROM 
    PER_EMPLOYEES
WHERE 
    ROWNUM <= 100;

-- Common date filters
SELECT * FROM table_name WHERE:
    -- Last 30 days
    creation_date >= SYSDATE - 30
    
    -- Current month
    EXTRACT(MONTH FROM creation_date) = EXTRACT(MONTH FROM SYSDATE)
    
    -- Date range
    creation_date BETWEEN DATE '2024-01-01' AND DATE '2024-12-31'
    
    -- Fiscal year
    creation_date >= ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -3)

-- Check available indexes
SELECT 
    INDEX_NAME,
    TABLE_NAME,
    COLUMN_NAME
FROM 
    USER_IND_COLUMNS
WHERE 
    TABLE_NAME = 'AP_INVOICES_ALL';

-- Bad: Function prevents index use
SELECT * FROM employees 
WHERE UPPER(last_name) = 'SMITH';

-- Good: Use exact match when possible
SELECT * FROM employees 
WHERE last_name = 'Smith';

-- Better performance with EXISTS
SELECT * FROM invoices i
WHERE EXISTS (
    SELECT 1 FROM invoice_lines il
    WHERE il.invoice_id = i.invoice_id
);

-- Use bind variables for dynamic queries
SELECT 
    INVOICE_NUM,
    INVOICE_DATE,
    INVOICE_AMOUNT
FROM 
    AP_INVOICES_ALL
WHERE 
    SUPPLIER_NAME = :supplier_name
    AND INVOICE_DATE >= :start_date
    AND ROWNUM <= :max_rows;

SELECT 
    -- Employee Information
    emp.EMPLOYEE_NUMBER,
    emp.FIRST_NAME,
    emp.LAST_NAME,
    emp.EMAIL_ADDRESS,
    
    -- Job Information
    job.JOB_TITLE,
    job.JOB_CODE,
    
    -- Department Information
    dept.DEPARTMENT_NAME,
    dept.DEPARTMENT_CODE,
    
    -- Assignment Details
    asg.ASSIGNMENT_STATUS,
    asg.EFFECTIVE_START_DATE,
    asg.EFFECTIVE_END_DATE

FROM 
    PER_ALL_PEOPLE_F emp
    INNER JOIN PER_ALL_ASSIGNMENTS_M asg 
        ON emp.PERSON_ID = asg.PERSON_ID
        AND SYSDATE BETWEEN asg.EFFECTIVE_START_DATE 
                       AND asg.EFFECTIVE_END_DATE
    LEFT JOIN PER_JOBS job 
        ON asg.JOB_ID = job.JOB_ID
    LEFT JOIN HR_DEPARTMENTS dept 
        ON asg.ORGANIZATION_ID = dept.ORGANIZATION_ID

WHERE 
    emp.PERSON_TYPE = 'EMPLOYEE'
    AND asg.ASSIGNMENT_TYPE = 'E'
    AND asg.PRIMARY_FLAG = 'Y'
    AND ROWNUM <= 100

ORDER BY 
    emp.EMPLOYEE_NUMBER;

-- Audit trail query template
SELECT 
    TABLE_NAME,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY
FROM 
    your_table
WHERE 
    LAST_UPDATE_DATE >= SYSDATE - 7
ORDER BY 
    LAST_UPDATE_DATE DESC;

-- Summary statistics template
SELECT 
    CATEGORY_COLUMN,
    COUNT(*) AS record_count,
    SUM(AMOUNT_COLUMN) AS total_amount,
    AVG(AMOUNT_COLUMN) AS avg_amount,
    MIN(DATE_COLUMN) AS earliest_date,
    MAX(DATE_COLUMN) AS latest_date
FROM 
    your_table
GROUP BY 
    CATEGORY_COLUMN
HAVING 
    COUNT(*) > 10
ORDER BY 
    record_count DESC;