# 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