-- Creating a table with JSON Relational Duality
CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    product_data JSON RELATIONAL DUALITY
);
-- Inserting JSON data
INSERT INTO products VALUES (1, 
    '{ "name": "iPhone 15", "price": 799, "stock": 50 }'
);

-- Querying JSON data using SQL
SELECT product_data.name, product_data.price
FROM products
WHERE product_data.stock > 10;

-- Creating an immutable table
CREATE IMMUTABLE TABLE bank_transactions (
    txn_id NUMBER PRIMARY KEY,
    account_no NUMBER,
    txn_amount NUMBER,
    txn_date DATE
) NO DROP NO DELETE;

-- Inserting a transaction
INSERT INTO bank_transactions VALUES (101, 1234567890, 5000, SYSDATE);

-- Trying to update or delete (This will fail!)
UPDATE bank_transactions SET txn_amount = 10000 WHERE txn_id = 101;
DELETE FROM bank_transactions WHERE txn_id = 101;

-- Creating a graph table
CREATE PROPERTY GRAPH social_network 
    VERTEX TABLE users
    EDGE TABLE friendships
    SOURCE KEY (user_id)
    DESTINATION KEY (friend_id);

-- Finding friends of friends (2nd-degree connections)
SELECT friend_of_friend 
FROM TABLE(SEARCH_GRAPH(
    social_network 
    START WITH (user_id = 101) 
    CONNECT BY NOCYCLE NODES(2)
));

-- Enabling automatic indexing
ALTER SESSION SET AUTO_INDEX_MODE = ON;

-- Checking auto-created indexes
SELECT * FROM DBA_AUTO_INDEXES;

-- Creating a user with passwordless login
CREATE USER admin IDENTIFIED EXTERNALLY AS 'biometric_auth';
GRANT CONNECT, DBA TO admin;