Skip to content

Latest commit

 

History

History
178 lines (140 loc) · 5.96 KB

README.md

File metadata and controls

178 lines (140 loc) · 5.96 KB

SQL-CLI Examples

This directory contains example SQL queries showcasing the powerful features of sql-cli.

Running Examples

You can run any example using the -f flag:

sql-cli -f examples/prime_numbers.sql
sql-cli -f examples/string_functions.sql

Or test queries directly with sample data:

sql-cli data/test_simple_math.csv -f examples/math_functions.sql

Running Specific Statements

You can execute individual statements from a script using --execute-statement:

# Run statement #2 from the script
sql-cli data.csv -f examples/script.sql --execute-statement 2 -o table

This automatically handles dependencies (temp tables, CTEs, etc.) and only executes the specified statement.

Available Examples

🎨 Colorful Number Classification (color_numbers.sql) ⭐ NEW!

ANSI Color Functions Demo - Beautiful visualization of number theory with terminal colors!

Quick start:

# Run all 6 demos
./scripts/demo_colors.sh

# Run specific demo (recommended!)
./scripts/demo_colors.sh 2  # Twin Primes Showcase
./scripts/demo_colors.sh 5  # Number Rainbow 1-100

# Or directly with --execute-statement
./target/release/sql-cli data/numbers_1_100.csv \
  -f examples/color_numbers.sql --execute-statement 2 -o table

Six visual demos:

  1. Comprehensive Classification - All special numbers (twin primes, primes, squares) color-coded
  2. Twin Primes Showcase - Prime pairs (3&5, 11&13) with gap/sum/average stats
  3. Prime Density Visualization - Bar chart showing primes per decade (1-10, 11-20, etc)
  4. Perfect Squares - Elegant display with square roots and visual indicators
  5. Number Rainbow (1-100) - All 100 numbers color-coded by their primary property
  6. Properties Matrix - Compact multi-property checkmark table

Color scheme:

  • 🔴 RED (bold) - Twin Primes (primes differing by 2)
  • 🟡 GOLD - Regular Primes
  • 🔵 CYAN - Perfect Squares (1, 4, 9, 16, 25...)
  • ⚫ GRAY - Even Numbers
  • ⚪ WHITE - Odd Composites

Functions demonstrated:

  • ANSI_RGB(r, g, b, text) - 24-bit true color (16 million colors!)
  • ANSI_COLOR(name, text) - Named colors (red, cyan, green, etc)
  • ANSI_BOLD(text) - Bold text styling
  • IS_PRIME(n) - Prime number detection
  • Complex CTEs with multiple classification criteria

🔢 Prime Number Functions (prime_numbers.sql)

  • Check if numbers are prime with IS_PRIME()
  • Get the Nth prime number with NTH_PRIME()
  • Count primes up to N with PRIME_PI()
  • Find next/previous primes with NEXT_PRIME() and PREV_PRIME()

🌌 Physics Constants (physics_constants.sql)

  • Access fundamental constants: speed of light, Planck constant, gravitational constant
  • Astronomical masses: Sun, Earth, Moon, planets
  • Particle masses: electron, proton, neutron
  • Practical physics calculations using these constants

🧪 Chemical Formulas (chemical_formulas.sql)

  • Parse chemical formulas with ATOMIC_MASS()
  • Get formulas from common names with MOLECULE_FORMULA()
  • Calculate molecular weights
  • Analyze compound properties

📝 String Functions (string_functions.sql)

  • Text manipulation: UPPER, LOWER, TRIM, REVERSE
  • String extraction: SUBSTRING, LEFT, RIGHT, SPLIT_PART
  • Pattern matching: RLIKE, REGEXP_REPLACE, REGEXP_EXTRACT
  • Hashing: MD5, SHA1, SHA256
  • Advanced formatting and padding

📅 Date/Time Functions (date_time_functions.sql)

  • Current date/time: NOW(), TODAY()
  • Date arithmetic: DATEADD(), DATEDIFF()
  • Date extraction: EXTRACT() for year, month, day, etc.
  • Date formatting and parsing
  • Support for both US and European date formats

📊 GROUP BY and Aggregates (group_by_aggregates.sql)

  • Aggregation functions: COUNT, SUM, AVG, MIN, MAX, STDDEV
  • Multi-column GROUP BY
  • HAVING clause for post-aggregation filtering
  • Complex aggregations with CASE statements
  • Time-based grouping and analysis

🧮 Math Functions (math_functions.sql)

  • Arithmetic operations and functions
  • Trigonometric functions: SIN, COS, TAN, etc.
  • Logarithmic and exponential functions
  • Statistical functions
  • Bitwise operations
  • Financial calculations

🏷️ Utility Examples

  • least_label.sql - Find the smallest labeled value using LEAST_LABEL()
  • case_test_mass_fns.sql - CASE expressions with physics constants

Quick Feature Showcase

-- Combine multiple advanced features
SELECT 
    trader_name,
    COUNT(*) as trade_count,
    SUM(quantity) as total_volume,
    AVG(price) as avg_price,
    ATOMIC_MASS('C8H10N4O2') as caffeine_mass,  -- Chemical
    IS_PRIME(COUNT(*)) as is_prime_count,              -- Prime check
    DATEDIFF('day', MIN(trade_date), NOW()) as days_trading,  -- Date math
    MD5(trader_name) as trader_hash                    -- Hashing
FROM trades
WHERE trade_date >= DATEADD('month', -3, TODAY())
GROUP BY trader_name
HAVING COUNT(*) > 10
ORDER BY total_volume DESC;

Advanced Capabilities

sql-cli is not just a SQL query tool - it's a powerful data analysis platform with:

  • Scientific Computing: Built-in physics constants and prime number theory
  • Chemistry Support: Parse and analyze chemical formulas
  • Advanced Math: From basic arithmetic to complex statistical functions
  • Date Intelligence: Comprehensive date/time manipulation
  • Text Processing: Regex, hashing, and advanced string operations
  • GROUP BY with HAVING: Full aggregation support with post-filtering

Tips

  1. Non-interactive testing: Test queries quickly without entering TUI:

    sql-cli data.csv -q "SELECT * FROM data WHERE IS_PRIME(id)" -o csv

  2. Query plan inspection: Debug complex queries:

    sql-cli data.csv -q "YOUR_QUERY" --query-plan

  3. Combine features: Mix different function types for powerful analysis:

    SELECT 
    UPPER(name) as name,
    ATOMIC_MASS(compound) as mass,
    IS_PRIME(EXTRACT(DAY FROM date)) as prime_day
FROM experiments;

Contributing

Feel free to add more examples showcasing sql-cli's capabilities!