SELECT Queries

Comprehensive guide to querying data in AVA Database.

Basic SELECT

Select All Columns

-- Select all rows and columns
SELECT * FROM prices;

-- Select all for specific ticker
SELECT * FROM prices WHERE TICKER = 'AAPL';

Select Specific Columns

-- Select specific columns
SELECT TICKER, DATE, PRICE FROM prices;

-- Select with column aliases
SELECT
    TICKER as Symbol,
    DATE as TradeDate,
    PRICE as ClosePrice
FROM prices;

-- Calculated columns
SELECT
    TICKER,
    DATE,
    PRICE,
    PRICE * 1.02 as TargetPrice
FROM prices;

DISTINCT Values

-- Get unique tickers
SELECT DISTINCT TICKER FROM prices;

-- Get unique dates
SELECT DISTINCT DATE FROM prices ORDER BY DATE DESC;

WHERE Clause - Filtering

Comparison Operators

-- Equal to
SELECT * FROM prices WHERE TICKER = 'AAPL';

-- Not equal to
SELECT * FROM prices WHERE TICKER != 'AAPL';

-- Greater than
SELECT * FROM prices WHERE PRICE > 100;

-- Less than or equal
SELECT * FROM prices WHERE PRICE <= 50;

-- Between range
SELECT * FROM prices
WHERE PRICE BETWEEN 50 AND 150;

-- In list
SELECT * FROM prices
WHERE TICKER IN ('AAPL', 'MSFT', 'GOOGL');

Logical Operators (AND, OR, NOT)

-- AND operator
SELECT * FROM prices
WHERE TICKER = 'AAPL' AND PRICE > 150;

-- OR operator
SELECT * FROM prices
WHERE TICKER = 'AAPL' OR TICKER = 'MSFT';

-- Combined conditions
SELECT * FROM prices
WHERE (TICKER = 'AAPL' OR TICKER = 'MSFT')
  AND PRICE > 100
  AND DATE >= '2025-01-01';

-- NOT operator
SELECT * FROM prices
WHERE TICKER NOT IN ('AAPL', 'MSFT');

NULL Handling

-- Check for NULL values
SELECT * FROM prices WHERE PRICE IS NULL;

-- Check for non-NULL values
SELECT * FROM prices WHERE PRICE IS NOT NULL;

-- Filter out NULLs
SELECT TICKER, DATE, PRICE
FROM prices
WHERE TICKER IS NOT NULL
  AND DATE IS NOT NULL
  AND PRICE IS NOT NULL;

ORDER BY - Sorting Results

-- Sort ascending (default)
SELECT * FROM prices
ORDER BY DATE ASC;

-- Sort descending
SELECT * FROM prices
ORDER BY PRICE DESC;

-- Sort by multiple columns
SELECT * FROM prices
ORDER BY TICKER ASC, DATE DESC;

-- Sort with calculation
SELECT TICKER, DATE, PRICE, PRICE * 100 as PriceCents
FROM prices
ORDER BY PriceCents DESC;

-- Get top N rows with LIMIT
SELECT * FROM prices
ORDER BY PRICE DESC
LIMIT 10;

Row Limiting - TOP, BOTTOM, and LIMIT

AVA provides three ways to limit the number of rows returned: TOP (SQL Server style), BOTTOM (AVA-specific), and LIMIT (MySQL/PostgreSQL style).

TOP N - Get First N Rows

SQL Server style syntax that returns the first N rows.

-- Get first 100 rows
SELECT TOP 100 * FROM securities;

-- Get top 10 most expensive prices
SELECT TOP 10 * FROM prices
ORDER BY PRICE DESC;

-- TOP with specific columns
SELECT TOP 50 TICKER, DATE, PRICE FROM prices
WHERE TICKER = 'AAPL';

BOTTOM N - Get Last N Rows

AVA-specific syntax that returns the last N rows without sorting.

-- Get last 100 rows
SELECT BOTTOM 100 * FROM securities;

-- Get last 10 prices (most recent inserts)
SELECT BOTTOM 10 * FROM prices;

-- BOTTOM with specific columns
SELECT BOTTOM 50 TICKER, DATE, PRICE FROM prices
WHERE TICKER = 'MSFT';

LIMIT N - MySQL/PostgreSQL Style

Standard LIMIT clause that returns the first N rows (equivalent to TOP).

-- Get first 100 rows
SELECT * FROM securities LIMIT 100;

-- Get top 10 most expensive prices
SELECT * FROM prices
ORDER BY PRICE DESC
LIMIT 10;

-- LIMIT with WHERE clause
SELECT * FROM prices
WHERE TICKER = 'GOOGL'
LIMIT 25;

Comparison and Use Cases

-- All three are equivalent for getting first N rows:
SELECT TOP 100 * FROM prices;        -- SQL Server style
SELECT * FROM prices LIMIT 100;      -- MySQL/PostgreSQL style

-- BOTTOM is unique - gets last N rows:
SELECT BOTTOM 100 * FROM prices;     -- Last 100 rows (no sorting)

-- Combine with ORDER BY for specific results:
SELECT TOP 10 * FROM prices ORDER BY PRICE DESC;     -- 10 highest prices
SELECT BOTTOM 10 * FROM prices ORDER BY DATE;        -- Last 10 by insert, sorted by date
SELECT * FROM prices ORDER BY DATE DESC LIMIT 5;     -- 5 most recent dates

GROUP BY - Grouping and Aggregation

Basic Grouping

-- Group by single column
SELECT
    TICKER,
    COUNT(*) as RecordCount
FROM prices
GROUP BY TICKER;

-- Group by multiple columns
SELECT
    TICKER,
    DATE,
    COUNT(*) as DailyRecords
FROM prices
GROUP BY TICKER, DATE;

-- AVA syntax with aggregate aliases
SELECT
    avg_price: AVG(PRICE),
    max_price: MAX(PRICE),
    min_price: MIN(PRICE)
FROM prices
GROUP BY TICKER;

HAVING Clause

-- Filter grouped results
SELECT
    TICKER,
    COUNT(*) as RecordCount,
    AVG(PRICE) as AvgPrice
FROM prices
GROUP BY TICKER
HAVING COUNT(*) > 100;

-- Multiple HAVING conditions
SELECT
    TICKER,
    AVG(PRICE) as AvgPrice,
    MAX(PRICE) as MaxPrice
FROM prices
GROUP BY TICKER
HAVING AVG(PRICE) > 50
  AND MAX(PRICE) < 200;

Subqueries

Subquery in WHERE

-- Select prices above average
SELECT * FROM prices
WHERE PRICE > (SELECT AVG(PRICE) FROM prices);

-- Select most recent date's prices
SELECT * FROM prices
WHERE DATE = (SELECT MAX(DATE) FROM prices);

-- Prices for tickers with high average
SELECT * FROM prices
WHERE TICKER IN (
    SELECT TICKER
    FROM prices
    GROUP BY TICKER
    HAVING AVG(PRICE) > 100
);

Subquery in FROM

-- Query from derived table
SELECT
    TICKER,
    AvgPrice,
    CASE
        WHEN AvgPrice > 100 THEN 'High'
        WHEN AvgPrice > 50 THEN 'Medium'
        ELSE 'Low'
    END as PriceCategory
FROM (
    SELECT
        TICKER,
        AVG(PRICE) as AvgPrice
    FROM prices
    GROUP BY TICKER
) AS ticker_stats;

Advanced SELECT Features

CROSS Product

-- Cartesian product
SELECT * FROM
    (SELECT DISTINCT TICKER FROM prices) AS tickers
CROSS
    (SELECT DISTINCT DATE FROM prices) AS dates;

-- Create all combinations
CREATE TABLE all_ticker_dates AS (
    SELECT TICKER, DATE
    FROM (SELECT DISTINCT TICKER FROM prices)
    CROSS (SELECT DISTINCT DATE FROM prices)
);

Set Operations

-- UNION - Combine results (remove duplicates)
SELECT TICKER FROM prices WHERE PRICE > 100
UNION
SELECT TICKER FROM prices WHERE DATE = '2025-01-02';

-- UNION ALL - Keep duplicates
SELECT TICKER FROM prices WHERE PRICE > 100
UNION ALL
SELECT TICKER FROM prices WHERE DATE = '2025-01-02';

-- INTERSECT - Common rows
SELECT TICKER FROM prices WHERE PRICE > 100
INTERSECT
SELECT TICKER FROM prices WHERE DATE = '2025-01-02';

-- EXCEPT - Rows in first but not second
SELECT TICKER FROM prices
EXCEPT
SELECT TICKER FROM prices WHERE PRICE < 50;