Oracle SQL Extensions

• NVL(): Returns a default value if a column is NULL.
• DECODE(): Provides conditional logic similar to a CASE statement but with a more compact syntax.
• TO_DATE(), TO_CHAR(), TO_NUMBER(): Functions for explicit data type conversion.
• ROWNUM: A pseudo-column that assigns a unique number to each row returned by a query.
• CONNECT BY: Used for hierarchical queries (e.g., tree structures).

• CLOB, BLOB, NCLOB: For storing large text or binary data.
• RAW, LONG RAW: For storing binary data.
• ROWID, UROWID: Unique identifiers for rows in a table.
• XMLType: For storing and querying XML data.
• JSON: Native support for JSON data with functions like JSON_VALUE, JSON_QUERY, and JSON_TABLE.

• Stored procedures, functions, and triggers written in PL/SQL can be called directly from SQL queries.
• PL/SQL provides procedural constructs like loops, conditionals, and exception handling, which are not available in Standard SQL.

• ROW_NUMBER(), RANK(), DENSE_RANK(): For ranking rows within a result set.
• LAG(), LEAD(): For accessing data from previous or subsequent rows.
• FIRST_VALUE(), LAST_VALUE(): For retrieving the first or last value in a window.
• PARTITION BY: For dividing data into partitions for window functions.

        SELECT employee_id, manager_id, last_name
        FROM employees
        START WITH manager_id IS NULL
        CONNECT BY PRIOR employee_id = manager_id;
        

• AS OF TIMESTAMP: Query data as it existed at a specific point in time.
• VERSIONS BETWEEN: Retrieve all versions of rows between two points in time.

        CREATE MATERIALIZED VIEW sales_summary AS
        SELECT product_id, SUM(quantity) AS total_quantity
        FROM sales
        GROUP BY product_id;
        

        SELECT * FROM employees@remote_db;
        

• /*+ INDEX(table_name index_name) */: Forces the use of a specific index.
• /*+ FULL(table_name) */: Forces a full table scan.
• /*+ PARALLEL(table_name, degree) */: Enables parallel execution.

• Range partitioning
• List partitioning
• Hash partitioning
• Composite partitioning

        CREATE TABLE sales (
            sale_id NUMBER,
            sale_date DATE,
            amount NUMBER
        )
        PARTITION BY RANGE (sale_date) (
            PARTITION p1 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')),
            PARTITION p2 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
        );
        

        CREATE TYPE address_type AS OBJECT (
            street VARCHAR2(100),
            city VARCHAR2(50),
            state VARCHAR2(50),
            zip_code VARCHAR2(10)
        );

        CREATE TABLE customers (
            customer_id NUMBER,
            name VARCHAR2(100),
            address address_type
        );
        

• Virtual Private Database (VPD): Row-level security.
• Transparent Data Encryption (TDE): Encrypts data at rest.
• Data Redaction: Masks sensitive data in query results.