Entering interactive mode.
Choose one of the following startup options:
1. Start dedicated Qwery Server
2. Start CLI with embedded Qwery Server
3. Start CLI and connect to remote Qwery Server
4. Start CLI and connect to generic JDBC Database
5. Start SQL script in standalone mode
Choice (1..5)> 

help

help 'create%'

set @x = 7
set @s = 'Hello World'
this

SELECT
   exchange AS market,
   SUM(lastSale) AS sumLastSale,
   MIN(lastSale) AS minLastSale,
   MAX(lastSale) AS maxLastSale,
   AVG(lastSale) AS avgLastSale,
   COUNT(*) AS total,
   COUNT(DISTINCT(symbol)) AS tickers
FROM `stocks.transaction_history`
GROUP BY exchange
ORDER BY market DESC

CREATE TABLE `stocks.transaction_history` (
  symbol STRING(8) NOT NULL,
  exchange STRING(8) NOT NULL,
  transactions TABLE (
    price DOUBLE,
    transactionTime DATE
  )[25]
)

INSERT INTO `stocks.transaction_history` (symbol, exchange, transactions)
VALUES ('AAPL', 'NASDAQ', '{"price":156.39, "transactionTime":"2021-08-05T19:23:11.000Z"}'),
       ('AMD', 'NASDAQ', '{"price":56.87, "transactionTime":"2021-08-05T19:23:11.000Z"}'),
       ('INTC','NYSE', '{"price":89.44, "transactionTime":"2021-08-05T19:23:11.000Z"}'),
       ('AMZN', 'NASDAQ', '{"price":988.12, "transactionTime":"2021-08-05T19:23:11.000Z"}'),
       ('SHMN', 'OTCBB', '[{"price":0.0010, "transactionTime":"2021-08-05T19:23:11.000Z"},{"price":0.0011, "transactionTime":"2021-08-05T19:23:12.000Z"}]')

SELECT symbol, exchange, TO_JSON(transactions) AS transactions
FROM `stocks.transaction_history`
ORDER BY symbol ASC

SELECT symbol, exchange, EXPLODE(transactions)
FROM `stocks.transaction_history`
WHERE symbol = 'SHMN'

VAR @name: STRING = 'Larry Jerry'
VAR @age: INT8 = 32
VAR @title: STRING = 'name: {{ @name }}, age: {{ @age }}'
SELECT @title

CREATE VIEW IF NOT EXISTS securities.stocks_view
COMMENT IS 'NYSE Stock symbols sorted by last sale'
AS
SELECT
   symbol AS ticker,
   exchange AS market,
   lastSale,
   ROUND(lastSale, 1) AS roundedLastSale,
   lastSaleTime
FROM `securities.stocks`
WHERE exchange = 'NYSE'
ORDER BY lastSale DESC
LIMIT 50

USE examples

-- create a table
DROP TABLE IF EXISTS Travelers
CREATE TABLE Travelers (id UUID, lastName VARCHAR(12), firstName VARCHAR(12), destAirportCode VARCHAR(3))
INSERT INTO Travelers (id, lastName, firstName, destAirportCode)
VALUES ('7bd0b461-4eb9-400a-9b63-713af85a43d0', 'JONES', 'GARRY', 'SNA'), (UUID(), 'JONES', 'DEBBIE', 'SNA'),
       (UUID(), 'JONES', 'TAMERA', 'SNA'), (UUID(), 'JONES', 'ERIC', 'SNA'),
       (UUID(), 'ADAMS', 'KAREN', 'DTW'), (UUID(), 'ADAMS', 'MIKE', 'DTW'),
       (UUID(), 'JONES', 'SAMANTHA', 'BUR'), (UUID(), 'SHARMA', 'PANKAJ', 'LAX')

-- create the webservice
DROP WEBSERVICE IF EXISTS findTraveler
CREATE WEBSERVICE findTraveler (firstName varchar(80), lastName varchar(80)) {
   SELECT * FROM Travelers WHERE firstName = @firstName AND lastName = @lastName
}

drop macro if exists `INSERT`
create macro `INSERT` via template('INSERT %e:total RANDOM STOCKS INTO %L:myQuotes') {
    -- @cnt is a local variable
    var @cnt: int32 = 0
    println 'Generating {{ @total }} random stock quotes...'
    do {
        set @cnt = @cnt + 1
        insert into @@myQuotes (lastSaleTime, lastSale, exchange, symbol)
        select NEW DATE() as lastSaleTime,
               ROUND(500 * RAND(0.99), 4) as lastSale,
               ['AMEX', 'NASDAQ', 'NYSE', 'OTCBB'][rand(4)] as exchange,
               Random_string(['A' TO 'Z'], 4) as symbol
    } while @cnt < @total
    select 'INSERTED' as action, @cnt as count
}

VAR @@stockQuotes TABLE(symbol VARCHAR(4), exchange VARCHAR(5), lastSale DOUBLE, lastSaleTime DATE)[1000]

~ INSERT 1000 RANDOM STOCKS INTO @@stockQuotes ~

select * from @@stockQuotes limit 5

+---------------------------------------------------------+
| symbol | exchange | lastSale | lastSaleTime             |
+---------------------------------------------------------+
| AMD    | NASDAQ   | 67.55    | 2022-06-17T19:02:01.917Z |
| AAPL   | NYSE     | 123.55   | 2022-06-17T19:02:01.917Z |
| GE     | NASDAQ   | 89.55    | 2022-06-17T19:02:01.917Z |
| PEREZ  | OTCBB    | 0.001    | 2022-06-17T19:02:01.917Z |
| AMZN   | NYSE     | 1234.55  | 2022-06-17T19:02:01.917Z |
+---------------------------------------------------------+