ToC path:

xSQL Scripts

As explained in the samples/xsql/scripts/README.txt file, the SQL sample files *.sql in this directory are designed to demonstrate eXtremeSQL engine capabilities. They can be run by starting xSQL with the following command from directory eXtremeDB/samples/xsql/scripts:
 
    ..\..\..\target\bin\xsql -i
     
Or, to facilitate running the sample scripts, the command file sqltest.bat is provided. For example, using this command file from directory eXtremeDB/samples/xsql/scripts (in a Windows console window), the following is a minimal command to run xSQL as a server and execute script array.sql:
 
    xsqltest array
     
(Note that eXtremeDB for HPC packages provide an additional subdirectory financial which includes command file xsql.bat that is preferable for launching samples demonstrating analytics features.)

The following table lists the sample scripts available in directory eXtremeDB/samples/xsql/scripts:

alter Demonstrates the alter table statement

array Demonstrates inserting values into an array field (column) using two different methods: with “{}”and “[]”syntax

as Demonstrates the use of the as qualifier to convert the result of a query column

asof_join Demonstrates the use of the asof qualifier to perform joins with specified conditions

between Demonstrates the use of the between operator

bulk Demonstrates the use of the array syntax “[]” to insert multiple values into scalar fields (columns) in a single insert operation

compound Demonstrates the use of a compound index. Note also the use of aliases a1 and a2 to form a self-join and use of the explain operator to display the execution plan

compound_nullable Demonstrates the use of a compound index and a null (nullable) field (column). Note also the use of aliases a1 and a2 to form a self-join and use of the explain operator to display the execution plans

create_index Demonstrates the create index statement. Note the use of the trace command to display performance statistics for the queries

decimal Demonstrates the use of the decimal field (column) type

delete Demonstrates the delete statement

disgroup Demonstrates the use of the distinct qualifier. Note the use of the echo command to display each SQL statement as it is executed and explain to display the execution plan

distinct Demonstrates the use of the distinct qualifier

distinctsum Demonstrates the use of the distinct qualifier with the sum() function. Note the use of the explain command to display the execution plan

drop Demonstrates the use of the drop index statement. Note the use of the explain command to display the execution plan

exists Demonstrates the use of the exists operator

fulltext Demonstrates the use of the contains operator to perform a text search. Note the use of the trace command to display performance statistics for the queries

functions Demonstrates the use of several built-in functions including: sum(), substr(), length(), upper(), lower(), format() and count(). Note the use of the trace command to display performance statistics for the queries

group_by Demonstrates the use of the group by operator

group_by_having Demonstrates the use of the group by operator with a having clause

group_by_substr Demonstrates the use of the group by operator and the substr() function

group_order Demonstrates the use of the group by operator and the order by operator

hashjoin Demonstrates how to perform a hash join operation. Note the use of explain to display the execution plan

in Demonstrates the use of the in operator

indexjoin Demonstrates the use of an index to optimize a lookup operation. Note how the explain command displays different execution plans depending on the order of conjunctions in the where clause

insert Demonstrates different forms of the insert operation

join Demonstrates a join operation joining two tables by date field (column) values

join_compound Demonstrates a join operation joining two tables by date field (column) values using a compound index

like Demonstrates the use of the like operator

nullable Demonstrates the use of the null declaration to define a field (column) as nullable. Note the use of explain to display the execution plan

numeric Demonstrates the use of the numeric field (column) type

order_by Demonstrates the use of the order by operator

round Demonstrates the use of the round() function

s_p Demonstrates the use of the various union and join operations

s_p_j_sp Demonstrates the use of the various exists, any and in operations with relatively complex queries

search Demonstrates a variety of lookup operations with different orders of comparison operators and order by clauses. Note the difference in the execution plans displayed by the explain command and the performance statistics displayed using the trace command

select_s Demonstrates a variety of lookup operations on table S with relatively complex queries

select_sp Demonstrates a lookup operation on table SP with “>= AND <” operators

sp Demonstrates a variety of lookup operations on table SP with relatively complex queries

stringagg Demonstrates the use of the string_agg() function. Note the difference in the execution plans displayed by the explain command

system Demonstrates the contents of system tables Metatable and Statistic

trigram Demonstrates the use of a trigram index

union Demonstrates some queries with the union operator

unionall Demonstrates the use of the union all operator

upsert Demonstrates the use of the insert or update operation to insert “horizontal” data into a sequence data type, which stores the data “vertically”

with Demonstrates the use of the with operator

alter	Demonstrates the `alter table` statement
array	Demonstrates inserting values into an array field (column) using two different methods: with “{}”and “[]”syntax
as	Demonstrates the use of the `as` qualifier to convert the result of a query column
asof_join	Demonstrates the use of the `asof` qualifier to perform joins with specified conditions
between	Demonstrates the use of the `between` operator
bulk	Demonstrates the use of the array syntax “[]” to insert multiple values into scalar fields (columns) in a single insert operation
compound	Demonstrates the use of a compound index. Note also the use of aliases `a1` and `a2` to form a self-join and use of the `explain` operator to display the execution plan
compound_nullable	Demonstrates the use of a compound index and a null (nullable) field (column). Note also the use of aliases `a1` and `a2` to form a self-join and use of the `explain` operator to display the execution plans
create_index	Demonstrates the `create index` statement. Note the use of the `trace` command to display performance statistics for the queries
decimal	Demonstrates the use of the `decimal` field (column) type
delete	Demonstrates the `delete` statement
disgroup	Demonstrates the use of the `distinct` qualifier. Note the use of the `echo` command to display each SQL statement as it is executed and `explain` to display the execution plan
distinct	Demonstrates the use of the `distinct` qualifier
distinctsum	Demonstrates the use of the `distinct` qualifier with the `sum()` function. Note the use of the `explain` command to display the execution plan
drop	Demonstrates the use of the `drop index` statement. Note the use of the `explain` command to display the execution plan
exists	Demonstrates the use of the `exists` operator
fulltext	Demonstrates the use of the `contains` operator to perform a text search. Note the use of the trace command to display performance statistics for the queries
functions	Demonstrates the use of several built-in functions including: `sum()`, `substr()`, `length()`, `upper()`, `lower()`, `format()` and `count()`. Note the use of the trace command to display performance statistics for the queries
group_by	Demonstrates the use of the `group by` operator
group_by_having	Demonstrates the use of the `group by` operator with a `having` clause
group_by_substr	Demonstrates the use of the `group by` operator and the `substr()` function
group_order	Demonstrates the use of the `group by` operator and the `order by` operator
hashjoin	Demonstrates how to perform a hash join operation. Note the use of `explain` to display the execution plan
in	Demonstrates the use of the `in` operator
indexjoin	Demonstrates the use of an `index` to optimize a lookup operation. Note how the `explain` command displays different execution plans depending on the order of conjunctions in the `where` clause
insert	Demonstrates different forms of the `insert` operation
join	Demonstrates a `join` operation joining two tables by date field (column) values
join_compound	Demonstrates a `join` operation joining two tables by date field (column) values using a compound index
like	Demonstrates the use of the `like` operator
nullable	Demonstrates the use of the `null` declaration to define a field (column) as nullable. Note the use of `explain` to display the execution plan
numeric	Demonstrates the use of the `numeric` field (column) type
order_by	Demonstrates the use of the `order by` operator
round	Demonstrates the use of the `round()` function
s_p	Demonstrates the use of the various `union` and `join` operations
s_p_j_sp	Demonstrates the use of the various `exists`, `any` and `in` operations with relatively complex queries
search	Demonstrates a variety of lookup operations with different orders of comparison operators and order by clauses. Note the difference in the execution plans displayed by the `explain` command and the performance statistics displayed using the `trace` command
select_s	Demonstrates a variety of lookup operations on table `S` with relatively complex queries
select_sp	Demonstrates a lookup operation on table `SP` with “`>= AND <`” operators
sp	Demonstrates a variety of lookup operations on table `SP` with relatively complex queries
stringagg	Demonstrates the use of the `string_agg()` function. Note the difference in the execution plans displayed by the `explain` command
system	Demonstrates the contents of system tables `Metatable` and `Statistic`
trigram	Demonstrates the use of a `trigram` index
union	Demonstrates some queries with the `union` operator
unionall	Demonstrates the use of the `union all` operator
upsert	Demonstrates the use of the `insert` or `update` operation to insert “horizontal” data into a sequence data type, which stores the data “vertically”
with	Demonstrates the use of the `with` operator