Advanced SQL syntax

-- Basic syntax
SELECT * FROM dictionary.users WHERE id = &user_id;
 
-- Different ways to declare variables
SELECT * FROM dictionary.products 
WHERE name = &product_name
  AND price BETWEEN &min_price AND &max_price
  AND category = &"product category"  -- Variable with spaces
  AND brand = &'brand name';         -- Alternative syntax
 
-- Using it in INSERT
INSERT INTO dictionary.orders (customer_id, total_amount, currency)
VALUES (&customer_id, &amount, &currency);
 
-- Complex variables with JSON
SELECT * FROM dictionary.settings 
WHERE config_data @> &json_filter::JSON;
 
-- Arrays in variables
SELECT * FROM dictionary.products 
WHERE category_id = ANY(&category_ids::INTEGER[]);

-- Positional parameters
SELECT $1 AS param1, $2 AS param2, $3 AS param3;
 
-- Using them in conditions
SELECT * FROM dictionary.users 
WHERE active = $1 
  AND role = $2
  AND created_at > $3;
 
-- In INSERT operations
INSERT INTO dictionary.logs (message, level, timestamp)
VALUES ($1, $2, $3);
 
-- Combining with custom variables
SELECT * FROM dictionary.orders 
WHERE customer_id = $1 
  AND status IN (&allowed_statuses)
  AND total_amount >= $2;

-- Basic type casts
SELECT 
    '123'::NUMBER AS numeric_value,
    'true'::BOOL AS boolean_value,
    'Hello World'::TEXT AS text_value,
    '550e8400-e29b-41d4-a716-446655440000'::UUID AS uuid_value,
    'deadbeef'::HEX AS hex_value;
 
-- Casting in queries
SELECT * FROM dictionary.products 
WHERE price::TEXT LIKE &price_pattern
  AND active::NUMBER = 1;
 
-- Casting JSON data
SELECT 
    metadata->>'price'::NUMBER AS price,
    metadata->>'active'::BOOL AS active,
    metadata->>'tags'::TEXT[] AS tags_array
FROM dictionary.products;
 
-- Casting for blockchain data
SELECT 
    "value"::NUMBER / 1e18 AS eth_amount,
    "gasPrice"::NUMBER AS gas_price,
    "blockNumber"::TEXT AS block_string
FROM ETHEREUM.TRANSACTIONS.DATA()
LIMIT 10 TYPE LIST;

-- Operator ::
SELECT name CASTOP TEXT, price CASTOP NUMBER
FROM dictionary.products;
 
-- Concat operator ||
SELECT 
    first_name || ' ' || last_name AS full_name,
    'Order #' || order_number::TEXT AS order_display
FROM dictionary.customers;

-- Generating a UUID
SELECT UUID() AS new_uuid;
 
-- Generating a UUID based on data  
SELECT UUID(&some_data) AS deterministic_uuid;
 
-- Using it in INSERT
INSERT INTO dictionary.users (ref, name, email)
VALUES (UUID(), &name, &email);
 
-- UUID as a key
CREATE DICTIONARY IF NOT EXISTS users (
    ref UUID DEFAULT UUID() NOT NULL,
    name TEXT NOT NULL,
    PRIMARY KEY (ref)
);

-- ISNULL - replacing NULL values
SELECT 
    name,
    ISNULL(description, 'No description') AS description,
    ISNULL(price, 0) AS price
FROM dictionary.products;
 
-- NULLREF - the system empty reference
SELECT 
    name,
    ISNULL(manager_ref, NULLREF()) AS manager_ref
FROM dictionary.projects;
 
-- Checking for empty references
SELECT * FROM dictionary.orders 
WHERE customer_ref IS NOT NULL 
  AND customer_ref != NULLREF();

-- NOW - current time
SELECT NOW() AS current_timestamp;
 
-- TODATETIME - creating a date/time
SELECT TODATETIME(2024, 12, 25, 15, 30, 0) AS christmas_datetime;
 
-- TODATE - creating a date  
SELECT TODATE(2024, 12, 25) AS christmas_date;
 
-- Using them in queries
SELECT * FROM dictionary.orders 
WHERE created_at BETWEEN TODATE(&year, &month, 1) 
                     AND TODATETIME(&year, &month + 1, 1, 0, 0, 0);
 
-- Calculations with dates
INSERT INTO dictionary.events (name, start_date, end_date)
VALUES (
    &event_name,
    TODATETIME(&year, &month, &day, &hour, &minute, 0),
    TODATETIME(&year, &month, &day, &hour + &duration, &minute, 0)
);

-- REF - the object's unique reference
SELECT REF, name, email FROM dictionary.users;
 
-- PARENT_REF - reference to the parent object
SELECT REF, name, PARENT_REF FROM dictionary.categories 
WHERE PARENT_REF IS NOT NULL;
 
-- OWNER_REF - reference to the owner
SELECT REF, name, OWNER_REF FROM dictionary.documents;
 
-- ID - numeric identifier
SELECT REF, ID, name FROM dictionary.products 
ORDER BY ID;
 
-- STATE - object state
SELECT REF, name, STATE FROM dictionary.orders 
WHERE STATE IN ('active', 'pending');

-- Top-level categories (no CTE needed)
SELECT REF, name, PARENT_REF
FROM dictionary.categories 
WHERE PARENT_REF = NULLREF()
ORDER BY name
TYPE LIST;
 
-- Finding related objects
SELECT 
    o.REF AS order_ref,
    o.order_number,
    c.name AS customer_name,
    u.name AS created_by
FROM dictionary.orders o
JOIN dictionary.customers c ON o.customer_ref = c.REF
JOIN dictionary.users u ON o.created_by = u.REF
WHERE o.STATE = 'confirmed'
TYPE LIST;

-- DO UPDATE - update on conflict
INSERT INTO dictionary.users (id, name, email, phone)
VALUES (&id, &name, &email, &phone)
ON CONFLICT(id) DO UPDATE SET
    name = EXCLUDED.name,
    email = EXCLUDED.email,
    phone = EXCLUDED.phone,
    updated_at = NOW();
 
-- DO NOTHING - ignore the conflict
INSERT INTO dictionary.tags (name, color)
VALUES (&tag_name, &tag_color)
ON CONFLICT(name) DO NOTHING;
 
-- DO ERROR - raise an error on conflict
INSERT INTO dictionary.products (sku, name, price)
VALUES (&sku, &name, &price)
ON CONFLICT(sku) DO ERROR;
 
-- Multiple constraints
INSERT INTO dictionary.user_roles (user_id, role_id, granted_by)
VALUES (&user_id, &role_id, &granted_by)
ON CONFLICT(user_id, role_id) DO UPDATE SET
    granted_by = EXCLUDED.granted_by,
    updated_at = NOW();

-- Upsert with conditions
INSERT INTO dictionary.inventory (product_id, warehouse_id, quantity)
VALUES (&product_id, &warehouse_id, &quantity)
ON CONFLICT(product_id, warehouse_id) DO UPDATE SET
    quantity = CASE 
        WHEN EXCLUDED.quantity > 0 THEN inventory.quantity + EXCLUDED.quantity
        ELSE EXCLUDED.quantity
    END,
    last_updated = NOW()
WHERE inventory.quantity + EXCLUDED.quantity >= 0;
 
-- Using it with RETURNING
INSERT INTO dictionary.sessions (user_id, token, expires_at)
VALUES (&user_id, &session_token, &expires_at)
ON CONFLICT(user_id) DO UPDATE SET
    token = EXCLUDED.token,
    expires_at = EXCLUDED.expires_at,
    updated_at = NOW();

-- TYPE OBJECT - return a single object
SELECT id, name, email FROM dictionary.users 
WHERE id = &user_id
TYPE OBJECT;
 
-- TYPE LIST - return an array of objects
SELECT id, name, price FROM dictionary.products 
WHERE active = true
ORDER BY name
TYPE LIST;
 
-- TYPE API LIST - API-formatted list
SELECT 
    p.id,
    p.name,
    p.price,
    c.name AS category_name
FROM dictionary.products p
JOIN dictionary.categories c ON p.category_id = c.id
WHERE p.active = true
TYPE API LIST;
 
-- TYPE API OBJECT - API-formatted object
SELECT 
    COUNT(*) AS total_products,
    AVG(price) AS average_price,
    MAX(price) AS max_price,
    MIN(price) AS min_price
FROM dictionary.products
WHERE active = true
TYPE API OBJECT;
 
-- Custom types
SELECT * FROM dictionary.products
TYPE custom_type[];
 
-- Standard return types
SELECT * FROM dictionary.users
WHERE active = true
TYPE LIST;

-- FSUBSTRING - substring
SELECT FSUBSTRING(name, 1, 10) AS short_name
FROM dictionary.products;
 
-- FTRIM - trimming whitespace
SELECT FTRIM(description) AS clean_description
FROM dictionary.products;
 
-- Concatenation
SELECT name || ' - ' || description AS full_description
FROM dictionary.products;
 
-- UPPER/LOWER operations
SELECT UPPER(name) AS name_upper, LOWER(email) AS email_lower
FROM dictionary.users;

-- JSON operations
SELECT 
    metadata->>'title' AS title,
    metadata->'tags' AS tags_json,
    metadata->>'price'::NUMBER AS price_number
FROM dictionary.products
WHERE metadata @> '{"featured": true}'::JSON;
 
-- Building JSON for individual records
SELECT 
    category_id,
    ('{"id":' || id || ',"name":"' || name || '","price":' || price || '}')::JSON AS product_json
FROM dictionary.products
WHERE category_id = &target_category
TYPE LIST;
 
-- JSON path operations
SELECT 
    settings->'ui'->>'theme' AS theme,
    settings->'notifications'->>'email' AS email_notifications
FROM dictionary.user_preferences;

-- Bitwise operations
SELECT 
    permissions & 7 AS read_permissions,
    permissions | 8 AS write_permissions,
    permissions ^ 4 AS toggle_execute
FROM dictionary.user_roles;
 
-- Shift operations
SELECT 
    value << 2 AS left_shift,
    value >> 1 AS right_shift
FROM dictionary.bit_operations;
 
-- MOD and DIV operations
SELECT 
    price MOD 10 AS price_remainder,
    quantity DIV 12 AS dozens
FROM dictionary.products;
 
-- XOR operations
SELECT * FROM dictionary.flags 
WHERE flag1 XOR flag2;

-- BETWEEN operations
SELECT * FROM dictionary.orders
WHERE total_amount BETWEEN &min_amount AND &max_amount
  AND order_date BETWEEN &start_date AND &end_date;
 
-- LIKE and patterns
SELECT * FROM dictionary.products
WHERE name LIKE &search_pattern
   OR description LIKE '%' || &keyword || '%';
 
-- IN operations with subqueries
SELECT * FROM dictionary.orders
WHERE customer_id IN (
    SELECT id FROM dictionary.customers 
    WHERE region = &target_region
);
 
-- EXISTS operations
SELECT * FROM dictionary.customers c
WHERE EXISTS (
    SELECT 1 FROM dictionary.orders o 
    WHERE o.customer_id = c.id 
      AND o.created_at > &recent_date
);

-- FCOUNT with conditions
SELECT 
    category_id,
    FCOUNT(*) AS total_products,
    FCOUNT(CASE WHEN active = true THEN 1 END) AS active_products,
    FCOUNT(DISTINCT supplier_id) AS unique_suppliers
FROM dictionary.products
GROUP BY category_id;
 
-- Statistical functions
SELECT 
    AVG(price) AS avg_price,
    MIN(price) AS min_price,
    MAX(price) AS max_price,
    COUNT(*) AS total_products
FROM dictionary.products;
 
-- Ranking and grouping
SELECT 
    p.name,
    p.price,
    p.category_id,
    cat_stats.avg_price,
    cat_stats.total_products
FROM dictionary.products p
JOIN (
    SELECT 
        category_id,
        AVG(price) AS avg_price,
        COUNT(*) AS total_products
    FROM dictionary.products
    GROUP BY category_id
) cat_stats ON p.category_id = cat_stats.category_id
ORDER BY p.category_id, p.price DESC;

-- Simple CASE
SELECT 
    name,
    CASE status
        WHEN 'active' THEN 'Active'
        WHEN 'pending' THEN 'Pending'
        WHEN 'inactive' THEN 'Inactive'
        ELSE 'Unknown'
    END AS status_name
FROM dictionary.users;
 
-- Conditional CASE
SELECT 
    name,
    price,
    CASE 
        WHEN price < 100 THEN 'Cheap'
        WHEN price BETWEEN 100 AND 1000 THEN 'Medium'
        WHEN price > 1000 THEN 'Expensive'
        ELSE 'No price'
    END AS price_category
FROM dictionary.products;
 
-- CASE in aggregates
SELECT 
    category_id,
    SUM(CASE WHEN active = true THEN price ELSE 0 END) AS active_total,
    COUNT(CASE WHEN price > 500 THEN 1 END) AS expensive_count
FROM dictionary.products
GROUP BY category_id;

-- Sales analysis over a period
SELECT 
    COUNT(*) AS total_orders,
    SUM(total_amount) AS total_revenue,
    AVG(total_amount) AS avg_order_value,
    COUNT(DISTINCT customer_id) AS unique_customers,
    CASE 
        WHEN SUM(total_amount) > 100000 THEN 'High volume'
        WHEN SUM(total_amount) > 50000 THEN 'Medium volume'
        ELSE 'Low volume'
    END AS volume_category
FROM dictionary.orders
WHERE order_date BETWEEN &start_date AND &end_date
TYPE OBJECT;

-- Dynamic filters with variables
SELECT 
    p.ID,
    p.name,
    p.price,
    c.name AS category_name
FROM dictionary.products p
JOIN dictionary.categories c ON p.category_id = c.REF
WHERE (ISNULL(&category_filter, '') = '' OR p.category_id = &category_filter)
  AND (ISNULL(&price_min, 0) = 0 OR p.price >= &price_min)
  AND (ISNULL(&price_max, 0) = 0 OR p.price <= &price_max)
  AND (ISNULL(&search_text, '') = '' OR p.name LIKE '%' || &search_text || '%')
ORDER BY 
    CASE 
        WHEN &sort_field = 'name' THEN p.name
        WHEN &sort_field = 'price' THEN p.price::TEXT
        ELSE p.name
    END
LIMIT ISNULL(&limit, 50) OFFSET ISNULL(&offset, 0)
TYPE LIST;