Use sqsh, not isql!

Sqsh is a sql shell and a far superior alternative to the isql program supplied by Sybase. It's main advantage is that it allows you to combine sql and unix shell commands! Here are a few reason why I love it:

1. Pipe data to other programs
You can create a pipeline to pass SQL results to an external (or unix) program like less, grep, head etc. Here are a few examples:

# pipe to less to browse data
1> select * from data; | less

# a more complex pipeline which gzips data containing a specific word
2> select * from data; | grep -i foo | gzip -c > /tmp/foo.gz

# this example shows the use of command substitution
3> sp_who; | grep `hostname`

2. Redirect output to file
Just like in a standard unix shell, you can redirect output of a sql command to file:

# write the output to file
1> sp_helptext my_proc; > /tmp/my_proc.txt

3. Functions and aliases
You can define aliases and functions in your ~/.sqshrc file for code that you run frequently. Some of mine are shown below. (Visit my GitHub dotfiles repository to see my full .sqshrc.)

\alias h='\history'

# shortcut for select * from
\func -x sf
    \if [ $# -eq 0 ]
        \echo 'usage: sf "[table [where ...]]"'
        \return 1
    \fi
    select * from $*; | less -F
\done

# count rows in a table
\func -x count
    \if [ $# -eq 0 ]
        \echo 'usage: count "[table [where ...]]"'
        \return 1
    \fi
    select count(*) from $*;
\done

You can invoke them like this:

# select * from data table
1> sf "data where date='20140306'"

# count the rows in the employee table
2> count employee

# list aliases
3> \alias

4. History and reverse search

You can rerun a previous command by using the \history command or by invoking reverse search with Ctrl+r:

1> \history
(1) sp_who
(2) select count(*) from data
(3) select top 10 * from data

# invoke the second command from history
2> !2

# invoke the previous command
3> !!

# reverse search
4> <Ctrl+r>
(reverse-i-search)`sp': sp_who
4> sp_who

5. Customisable prompt
The default prompt is ${lineno}> , but it can be customised to include your username and database, and it even supports colours. It would be nice if there was a way to change the colour based on which database you were connected to (for example, red for a production database), but I haven't been able to figure out if this is possible yet. Here is my prompt, set in my ~/.sqshrc:

\set prompt_color='{1;33}' # yellow
\set text_color='{0;37}'   # white
\set prompt='${prompt_color}[$histnum][$username@$DSQUERY.$database] $lineno >$text_color '

6. Different result display styles
sqsh supports a number of different output styles which you can easily switch to. The ones I frequently use are csv, html and vert (vertical). Here is an example:

1> select * from employee; -m csv
123,"Joe","Bloggs"

2>select * from employee; -m vert
id:        123
firstName: Joe
lastName:  Bloggs

7. For-loops
A for-loop allows you to iterate over a range of values and execute some code. For example, if you want to delete data, in batches, over a range of dates, you can use a for-loop like this:

\for i in 1 2 3 4 5
    \loop -e "delete from data where date = '2014020$i';"
    \echo "Deleted 2014020$i"
\done

8. Backgrounding long-running commands
If you have a long-running command, you can run it in the background by putting an & at the end of the command. You can then continue running other commands, whilst this one runs in the background. You will see a message when the background command completes and you can use \show to see the results. Here is an example:

# run a command in the background
1> select * from data; &
Job #1 running [6266]

Job #1 complete (output pending)

# show the results of the backgrounded command
3> \show 1

Further information:
You can download sqsh here and then read the man page for more information.
You can take a look at my .sqshrc in my GitHub dotfiles repository.

Finding the Maximum using Relational Algebra

We know that if you want to get the maximum value of a column in SQL, you can simply use the MAX function as shown below:

SELECT MAX(value) FROM T

You can also do it without the MAX function as follows:

SELECT T.* FROM T
MINUS
SELECT T.* FROM T, T as T2 WHERE T.value<T2.value

or:

SELECT T.* FROM T
LEFT JOIN T as T2 ON T.value<T2.value
WHERE T2.value IS NULL

Relational Algebra:
Using Relational Algebra (RA) syntax, this would be:

\project_{value}(T)
\diff
\project_{value} (
    \select_{value < value2}(
      \project_{value}(T)
      \cross
      \rename_{value2}(\project_{value}(T))
    )
)

where:

• \cross is the relational cross-product operator
• \diff is the relational diff operator
• \project_{attr_list} is the relational projection operator
• \rename_{new_attr_name_list} is the relational renaming operator
• \select_{cond} is the relational selection operator