-- Create database and table
CREATE DATABASE ecommerce;
USE ecommerce;

CREATE TABLE products (
    id INT,
    name VARCHAR,
    price INT,
    category VARCHAR
);

-- Insert data
INSERT INTO products VALUES (1, 'Laptop', 999, 'Electronics');
INSERT INTO products VALUES (2, 'Mouse', 29, 'Electronics');
INSERT INTO products VALUES (3, 'Desk', 299, 'Furniture');

-- Vectorized analytical query
SELECT category, COUNT(*) as count, AVG(price) as avg_price
FROM products
GROUP BY category
HAVING count > 0
ORDER BY avg_price DESC;

-- Query transaction history (system table bootstrap feature)
SELECT version, operation, table_id, file_path
FROM sys.delta_log
ORDER BY version DESC
LIMIT 10;

-- Atomicity: Multi-row inserts either all succeed or all fail
BEGIN TRANSACTION;
INSERT INTO orders VALUES (1, 100, '2024-01-01');
INSERT INTO orders VALUES (2, 200, '2024-01-02');
COMMIT;  -- Atomic commit to Delta Log

-- Consistency: Constraint checking
CREATE UNIQUE INDEX idx_id ON products (id);
INSERT INTO products VALUES (1, 'Item1', 100);
INSERT INTO products VALUES (1, 'Item2', 200);  -- Violates unique constraint, rejected

-- Isolation: Snapshot isolation
-- Session 1: Reading snapshot version=10
-- Session 2: Concurrently writing to create version=11
-- Session 1 still reads consistent version=10 data

-- Durability: fsync guarantee
-- Data is immediately persisted to Parquet files
INSERT INTO products VALUES (3, 'Item3', 150);
-- After server crash and restart, data still exists

-- Scenario 1: Query by source IP
SELECT * FROM network_logs WHERE source_ip = '192.168.1.100';

-- Scenario 2: Query by destination IP
SELECT * FROM network_logs WHERE dest_ip = '10.0.0.50';

-- Scenario 3: Query by time
SELECT * FROM network_logs WHERE timestamp > '2024-01-01';

-- Enable Z-Order
OPTIMIZE TABLE network_logs ZORDER BY (source_ip, dest_ip, timestamp);

-- Query performance comparison (100GB dataset)
Scenario 1 (source_ip):  10s → 0.5s  (20x speedup) ✅
Scenario 2 (dest_ip):    10s → 0.8s  (12.5x speedup) ✅
Scenario 3 (timestamp):  10s → 1.2s  (8.3x speedup) ✅
Average file skip rate: 54% → Half the data read

-- Numeric comparisons (INT/FLOAT)
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE age BETWEEN 20 AND 30;

-- String comparisons
SELECT * FROM users WHERE name = 'Alice';
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- Compound conditions
SELECT * FROM logs
WHERE source_ip = '192.168.1.1'
  AND timestamp > '2024-01-01'
  AND status = 'error';

-- Query transaction history directly with SQL
SELECT
    version,
    timestamp,
    operation,
    table_id,
    file_path,
    row_count
FROM sys.delta_log
WHERE table_id = 'ecommerce.products'
ORDER BY version DESC;

-- Result:
┌─────────┬──────────────┬───────────┬────────────┬──────────┐
│ version │  timestamp   │ operation │  table_id  │ row_count│
├─────────┼──────────────┼───────────┼────────────┼──────────┤
│    10   │ 1730000000   │    ADD    │ ecommerce. │   1000   │
│     9   │ 1729999000   │  REMOVE   │ ecommerce. │   500    │
│     8   │ 1729998000   │    ADD    │ ecommerce. │   500    │
└─────────┴──────────────┴───────────┴────────────┴──────────┘

-- 1. Database metadata
SELECT * FROM sys.db_metadata;

-- 2. Table metadata
SELECT db_name, table_name, schema_json
FROM sys.table_metadata;

-- 3. Column information
SELECT table_name, column_name, data_type, is_nullable
FROM sys.columns_metadata
WHERE db_name = 'ecommerce';

-- 4. Index information
SELECT index_name, column_name, is_unique, index_type
FROM sys.index_metadata;

-- 5. Transaction log
SELECT version, operation, file_path
FROM sys.delta_log;

-- 6. File inventory
SELECT file_path, file_size, row_count, status
FROM sys.table_files
WHERE table_name = 'products';

-- Database management
CREATE DATABASE ecommerce;
DROP DATABASE ecommerce;
USE ecommerce;
SHOW DATABASES;

-- Table management with all data types
CREATE TABLE products (
    id INTEGER,
    name VARCHAR(100),
    price DOUBLE,
    quantity INTEGER,
    in_stock BOOLEAN,
    created_at TIMESTAMP,
    category VARCHAR
);

-- Table with column constraints
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    age INTEGER DEFAULT 18,
    active BOOLEAN DEFAULT 1
);

-- Table with table-level PRIMARY KEY constraint
CREATE TABLE orders (
    order_id INTEGER,
    user_id INTEGER NOT NULL,
    amount DOUBLE,
    PRIMARY KEY (order_id)
);

-- Table with partitioning (HASH)
CREATE TABLE logs_hash (
    id INTEGER,
    message VARCHAR,
    timestamp TIMESTAMP
) PARTITION BY HASH (id);

-- Table with partitioning (RANGE)
CREATE TABLE logs_range (
    id INTEGER,
    region VARCHAR,
    data VARCHAR
) PARTITION BY RANGE (id);

-- Drop tables
DROP TABLE products;
DROP TABLE users;
SHOW TABLES;

-- Index management
CREATE INDEX idx_category ON products (category);
CREATE UNIQUE INDEX idx_id ON products (id);
CREATE INDEX idx_composite ON products (category, name);
DROP INDEX idx_category ON products;
SHOW INDEXES ON products;
SHOW INDEXES FROM products;

-- Single row insertion
INSERT INTO products VALUES (1, 'Laptop', 999.99, 10, 1, '2024-01-01', 'Electronics');

-- Batch insertion (multiple rows)
INSERT INTO products VALUES
    (2, 'Mouse', 29.99, 50, 1, '2024-01-02', 'Electronics'),
    (3, 'Desk', 299.99, 15, 1, '2024-01-03', 'Furniture'),
    (4, 'Chair', 199.99, 20, 1, '2024-01-04', 'Furniture');

-- Insert with column list
INSERT INTO products (id, name, price, category)
VALUES (5, 'Monitor', 399.99, 'Electronics');

-- Insert multiple rows with column list
INSERT INTO users (id, username, email, age) VALUES
    (1, 'alice', 'alice@example.com', 25),
    (2, 'bob', 'bob@example.com', 30),
    (3, 'charlie', 'charlie@example.com', 35);

-- Basic queries
SELECT * FROM products;
SELECT name, price FROM products;

-- Queries with column aliases
SELECT name AS product_name, price AS product_price FROM products;
SELECT name product_name, price product_price FROM products;

-- Queries with table aliases
SELECT p.name, p.price FROM products AS p;
SELECT p.name, p.price FROM products p;

-- WHERE with comparison operators
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price >= 100;
SELECT * FROM products WHERE price < 500;
SELECT * FROM products WHERE price <= 500;
SELECT * FROM products WHERE price = 299.99;
SELECT * FROM products WHERE price != 299.99;
SELECT * FROM products WHERE name = 'Laptop';

-- WHERE with logical operators (AND, OR)
SELECT * FROM products WHERE category = 'Electronics' AND price < 1000;
SELECT * FROM products WHERE category = 'Electronics' OR category = 'Furniture';
SELECT * FROM products WHERE price > 100 AND price < 500 AND in_stock = 1;

-- WHERE with LIKE (pattern matching)
SELECT * FROM products WHERE name LIKE '%top';
SELECT * FROM products WHERE name LIKE 'M%';
SELECT * FROM products WHERE category LIKE '%onic%';
SELECT * FROM products WHERE name NOT LIKE 'Desk';

-- WHERE with IN operator
SELECT * FROM products WHERE category IN ('Electronics', 'Furniture');
SELECT * FROM products WHERE id IN (1, 2, 3);
SELECT * FROM products WHERE category NOT IN ('Obsolete', 'Discontinued');

-- WHERE with qualified column references
SELECT * FROM products WHERE products.price > 200;

-- WHERE with parenthesized expressions
SELECT * FROM products WHERE (price > 100 AND category = 'Electronics') OR (price > 200 AND category = 'Furniture');

-- Single column update
UPDATE products SET price = 1099 WHERE id = 1;

-- Multiple column update
UPDATE products SET price = 349.99, quantity = 25, in_stock = 1 WHERE id = 3;

-- Update with expressions
UPDATE products SET price = price * 1.1 WHERE category = 'Electronics';
UPDATE products SET quantity = quantity + 10 WHERE in_stock = 1;

-- Update without WHERE (updates all rows)
UPDATE products SET in_stock = 1;

-- Deletions with WHERE
DELETE FROM products WHERE price < 50;
DELETE FROM products WHERE category = 'Obsolete';
DELETE FROM products WHERE id = 5;

-- DELETE without WHERE (deletes all rows - use with caution)
DELETE FROM products;

-- Aggregate functions
SELECT COUNT(*) FROM products;
SELECT COUNT(name) FROM products;
SELECT SUM(price) FROM products;
SELECT AVG(price) FROM products;
SELECT MIN(price) FROM products;
SELECT MAX(price) FROM products;

-- Aggregate functions with aliases
SELECT
    COUNT(*) AS total_products,
    SUM(price) AS total_value,
    AVG(price) AS avg_price,
    MIN(price) AS min_price,
    MAX(price) AS max_price
FROM products;

-- GROUP BY with aggregations
SELECT category, COUNT(*) FROM products GROUP BY category;
SELECT category, AVG(price) FROM products GROUP BY category;
SELECT category, SUM(quantity) FROM products GROUP BY category;

-- GROUP BY with multiple aggregations
SELECT
    category,
    COUNT(*) AS product_count,
    SUM(price) AS total_value,
    AVG(price) AS avg_price,
    MIN(price) AS min_price,
    MAX(price) AS max_price
FROM products
GROUP BY category;

-- GROUP BY with HAVING clause
SELECT category, COUNT(*) AS cnt FROM products GROUP BY category HAVING cnt > 5;
SELECT category, AVG(price) AS avg_price FROM products GROUP BY category HAVING avg_price > 100;
SELECT category, SUM(price) AS total FROM products GROUP BY category HAVING total > 1000;

-- ORDER BY (ascending is default)
SELECT * FROM products ORDER BY price;
SELECT * FROM products ORDER BY price ASC;
SELECT * FROM products ORDER BY name ASC;

-- ORDER BY descending
SELECT * FROM products ORDER BY price DESC;
SELECT * FROM products ORDER BY quantity DESC;

-- ORDER BY multiple columns
SELECT * FROM products ORDER BY category ASC, price DESC;
SELECT * FROM products ORDER BY in_stock DESC, price ASC, name ASC;

-- ORDER BY with expressions
SELECT name, price FROM products ORDER BY price * quantity DESC;

-- LIMIT clause
SELECT * FROM products LIMIT 10;
SELECT * FROM products ORDER BY price DESC LIMIT 5;
SELECT name, price FROM products WHERE category = 'Electronics' ORDER BY price LIMIT 3;

-- Combined: WHERE + GROUP BY + HAVING + ORDER BY + LIMIT
SELECT
    category,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price
FROM products
WHERE in_stock = 1
GROUP BY category
HAVING product_count > 2
ORDER BY avg_price DESC
LIMIT 10;

-- INNER JOIN
SELECT u.name, o.amount, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- JOIN (equivalent to INNER JOIN)
SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.age > 25;

-- LEFT JOIN (LEFT OUTER JOIN)
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

SELECT u.name, o.amount
FROM users u
LEFT OUTER JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN (RIGHT OUTER JOIN)
SELECT u.name, o.amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

SELECT u.name, o.amount
FROM users u
RIGHT OUTER JOIN orders o ON u.id = o.user_id;

-- FULL OUTER JOIN
SELECT u.name, o.amount
FROM users u
FULL JOIN orders o ON u.id = o.user_id;

SELECT u.name, o.amount
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- Multiple JOINs
SELECT u.name, o.amount, p.name AS product_name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id;

-- JOIN with WHERE clause
SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100 AND u.age > 25;

-- JOIN with aggregations
SELECT u.name, COUNT(*) AS order_count, SUM(o.amount) AS total_spent
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

-- Subquery in FROM clause
SELECT sub.category, sub.avg_price
FROM (SELECT category, AVG(price) AS avg_price FROM products GROUP BY category) AS sub
WHERE sub.avg_price > 100;

-- Subquery with JOIN
SELECT u.name, sub.total
FROM users u
JOIN (SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id) AS sub ON u.id = sub.user_id;

-- SELECT with function calls in expressions
SELECT name, price, price * 1.1 AS price_with_tax FROM products;
SELECT name, price, quantity, price * quantity AS total_value FROM products;
SELECT UPPER(name) AS upper_name FROM products;
SELECT COUNT(*), AVG(price) FROM products WHERE category = 'Electronics';

-- Query all databases
SELECT * FROM sys.db_metadata;

-- Query all tables
SELECT db_name, table_name FROM sys.table_metadata;

-- Query table structure
SELECT column_name, data_type, is_nullable
FROM sys.columns_metadata
WHERE db_name = 'ecommerce' AND table_name = 'products';

-- Query indexes
SELECT index_name, column_name, is_unique
FROM sys.index_metadata
WHERE db_name = 'ecommerce' AND table_name = 'products';

-- Query transaction history
SELECT version, operation, table_id, file_path, row_count
FROM sys.delta_log
WHERE table_id LIKE 'ecommerce%'
ORDER BY version DESC
LIMIT 20;

-- Query table files
SELECT file_path, file_size, row_count, status
FROM sys.table_files
WHERE db_name = 'ecommerce' AND table_name = 'products';

-- System tables with aggregations
SELECT db_name, COUNT(*) AS table_count
FROM sys.table_metadata
GROUP BY db_name;

-- System tables with JOINs
SELECT t.table_name, COUNT(c.column_name) AS column_count
FROM sys.table_metadata t
JOIN sys.columns_metadata c ON t.table_name = c.table_name
GROUP BY t.table_name;

-- Start a transaction
START TRANSACTION;

-- Commit changes
COMMIT;

-- Rollback changes
ROLLBACK;

-- Transaction example with multiple operations
START TRANSACTION;
INSERT INTO products VALUES (10, 'Keyboard', 79.99, 30, 1, '2024-01-10', 'Electronics');
UPDATE products SET price = price * 0.9 WHERE category = 'Electronics';
DELETE FROM products WHERE quantity = 0;
COMMIT;

-- View execution plan for SELECT query
EXPLAIN SELECT * FROM products WHERE category = 'Electronics';

-- EXPLAIN with JOIN
EXPLAIN SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.age > 25;

-- EXPLAIN with complex query
EXPLAIN SELECT
    category,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price
FROM products
WHERE in_stock = 1
GROUP BY category
HAVING product_count > 2
ORDER BY avg_price DESC
LIMIT 10;

-- Analyze table statistics (all columns)
ANALYZE TABLE products;

-- Analyze specific columns
ANALYZE TABLE products (price, quantity);
ANALYZE TABLE users (age, active);

-- Output example of EXPLAIN:
Query Execution Plan:
--------------------
Select
  Filter (category = 'Electronics')
    TableScan (products)
      Predicate Pushdown: ✓
      Estimated Files: 1/10 (90% skipped)

-- Direct SQL query
SELECT * FROM sys.delta_log WHERE table_id = 'products';