# Step 2: Executing commands and scripts

> # Executing basic xSQL commands and scripts

> As mentioned in Step 1, xSQL can be started in server or client mode. For the next group of exercises we will start xSQL with the command file `xsql.bat` directory `/samples/xsql/scripts/financial` with the default parameters to enter interactive SQL statements. xSQL displays the following lines indicating it is ready to accept interactive SQL statements:

>         Simple interactive configuration is started
>         To run as a server, type, for example: 'xsql -size 100m -p 5000'
>         Then to connect as a client, use 'xsql @127.0.0.1:5000'
>         Type 'xsql -h' for more details

>         xsql started
>         Runtime configuration
>          Transaction manager  : MURSIW
>          Storage (transient)  : Conventional memory, 100M
>          Storage (persistent) : Not supported
>          Runtime              : Release
>         XSQL>

> ## Executing SQL statements

> To execute SQL statements interactively simply type the statement followed by a semicolon. For example, the following `select` statement queries the system table `Metatable` to show that there are as yet no tables in the database:

>         XSQL>select TableName from Metatable;
>         TableName
>         ------------------------------------------------------------------------------

>         Selected records: 0
>         XSQL>

> So to create and initialize a table we might enter statements like the following:

>         XSQL>create table t(id int);
>         XSQL>insert into t values( [1,2,3] );
>         XSQL>select * from t;
>         id
>         ------------------------------------------------------------------------------
>         1
>         2
>         3

>         Selected records: 3
>         XSQL>

> ## Executing script files

> But rather than type SQL statements interactively, it is often more convenient to read SQL script files into xSQL to preform SQL operations. To facilitate this exercise, several scripts and additional files are provided in the directory `/samples/xsql/scripts/financial`.

> For example, the scripts in files `quote.sql`, `insert.sql` and `count.sql` create the table Quote, then populate it with some randomized trade data and perform a query. The `quote.sql` script creates the table with a time series consisting of the ordered sequence `day` associated with five sequences of values `high`, `low`, `open`, `close` and `volume`:

>         create table Quote( symbol char(21) primary key, day sequence(unsigned(4) asc),
>             low sequence(float), high sequence(float), open sequence(float),
>             close sequence(float), volume sequence(unsigned(4)) );

> The `insert.sql` script inserts a number of values into the sequences with lines like the following:

>         insert into Quote (symbol,day,low,high,open,close,volume) values ('SYM0','{20130101,20130104,...},{...}, ..., {...});

> (Note the use of the '{ `value list` }' to insert a series of values into the sequence fields.)

> Then script `count.sql` simply sets the format for output (more about this later) and selects the count of records inserted:

>         format CSV
>         select count(symbol) as "Quote records inserted" from Quote;

> These three scripts can be executed in "batch" mode by starting xSQL with the following command:

>         xsql -size 100m -b -f quote.sql -f insert.sql -f count.sql
>         Quote records inserted
>         10

> Note that xSQL terminates after executing the scripts in "batch" mode. To use xSQL interactively we use the `-i` command line option. For example we can run the first two scripts to initialize the Quote table and enter interactive mode with the following command:

>         xsql -size 100m -i -f quote.sql -f insert.sql

> Now we can type an SQL select statement like the following to display the top performing stocks from this set of test data:

>         XSQL>select symbol, seq_top_pos_max(close, 1) as top_index,
>             close@top_index  as "top_Close",
>             volume@top_index as "volume" from Quote;

>         symbol  top_index      top_Close       volume
>         ------------------------------------------------------------------------------
>         SYM0    {34}    {97.2399978637695}      {732}
>         SYM1    {57}    {96.5299987792969}      {406}
>         SYM2    {2}      {94.870002746582}        {765}
>         SYM3    {65}    {98.7399978637695}      {953}
>         SYM4    {62}    {93.25}                               {312}
>         SYM5    {76}    {92.5199966430664}      {829}
>         SYM6    {59}    {94.8300018310547}      {761}
>         SYM7    {98}    {99.3199996948242}      {583}
>         SYM8    {37}    {97.5899963378906}      {593}
>         SYM9    {81}    {93.7799987792969}      {257}

>         Selected records: 10

> Note that this example makes use of some eXtremeSQL extensions to standard SQL that operate on the Quote sequence fields `close` and `volume`. These SQL extensions and the powerful library of statistical functions provided for analytics are explained and demonstrated in the SQL analytics functions examples.

> ## The script command

> Again, rather than type complex SQL statements interactively, especially when they may be repeated, it is more convenient to store the statement(s) in a script file. In fact the above `select` statement is provided in file `top_close.sql`. We can run this script to obtain the same results from interactive mode using the `script` command:

>         XSQL>script top_close.sql

> The file `top_close.sql` looks like this:

>         select symbol, seq_top_pos_max(close, 1) as top_index,
>             close@top_index  as "top_Close", volume@top_index as "volume" from Quote;

> Note that the last line issues command `input console` to return input to `STDIN` (the keyboard). Without this line the file will be executed in batch mode and xSQL will close. Alternatively the `script` command can be used to remain in xSQL interactive mode, or to run one script from another script file. For example suppose we have the following two script files:

> `inner.sql`:

>         XSQL>select TableName, FieldName, FieldTypeName  from Metatable;

> and `outer.sql`:

>         select 1;
>         script inner.sql
>         select 2;

> Now we could execute the `script` command in interactive mode to produce the following output:

>         XSQL>script outer.sql
>         #1
>         ------------------------------------------------------------------------------
>         1

>         Selected records: 1
>         TableName       FieldName       FieldTypeName
>         ------------------------------------------------------------------------------
>         Quote   symbol  String
>         Quote   day     Sequence
>         Quote   low     Sequence
>         Quote   high    Sequence
>         Quote   open    Sequence
>         Quote   close   Sequence
>         Quote   volume  Sequence

>         Selected records: 7
>         #1
>         ------------------------------------------------------------------------------
>         2

>         Selected records: 1
>         XSQL>

> Or the script `outer.sql` can be run from the xSQL command line to produce the same result and remain in interactive mode:

>         xsql -f outer.sql

> It is often convenient to display output from select statements in a different format. For example the output from the `top_close.sql` script above displays the `close` price in full precision, i.e. with 13 digits to the right of the decimal point, eg. `97.2399978637695`. It would be more readable with 2 or 3 digits of precision. The next step in this tutorial will demonstrate different display options.