The World of Computer Software/World_Of_Computer_Software-02-387-Vol-3of3.iso/d/dtu11b.zip/QSQLDEMO.EXE/QSQL.DOC

Text File | 1992-09-24 | 46KB | 1,546 lines

COPYRIGHT (C) 1991, 1992 Cook Database Design,
Inc.
   ╔═════════════════════════════════╗
   ║            QSQL(TM)             ║
   ║                                 ║
   ║          Version 2.0            ║
   ║                                 ║
   ║            01/15/92             ║
   ║                                 ║
   ║   Cook Database Design, Inc.    ║
   ║      619 Woodduck Lane          ║
   ║    Georgetown Kentucky 40324    ║
   ║                                 ║
   ║    Order Desk: [phone]   ║
   ║   Information: [phone]   ║
   ║           Fax: [phone]   ║
   ║ When sending a Fax, please      ║
   ║ start as soon as you hear the   ║
   ║ first ring.                     ║
   ╚═════════════════════════════════╝



This is a demo version and may be freely distributed.
The difference between this version and the real version
is that this will only display the first 5 records of
your query and updates are not available.   The rest of
the records are suppressed.

Have fun learning SQL and enjoy!

                    - 1 -
Introduction

QSQL is a standalone query program for the
Clarion database environment.  It provides a
powerful subset of the ANSI SQL language with
special enhancements which were made to
support the Clarion environment.

Highlights

QSQL is a powerful subset of ANSI SQL

QSQL can perform a join on any number of
tables only limited by memory

QSQL can use keys to speed up its operation

QSQL is a state of the art SQL implementation
using advanced relational database techniques
in order to provide an extremely small and
fast SQL implementation.

QSQL can be used to perform mass updates of
your databases by using the UPDATE command.

Installation

To install QSQL, copy the programs into a
directory on your fixed disk.  The Clarion
directory would be a good place.

       COPY A:*.* C:\CLARION

That's it!  Note that it is assumed that the
Clarion directory is in your path statement.

Included on the distribution diskette are the
sailors, boats, and res database which are
used in the SQL tutorial.  These databases are
a good set of databases with which to learn
SQL.                    - 2 -
Using QSQL

The simplest way to use QSQL is via the QSQL
interactive query program. To use the program,
get into the directory which contains your
application data files and type: QSQL

A data entry screen will appear in which you
can enter your SQL queries.  There are twelve
lines in which to enter your queries. From
this screen the following keys can be pressed:

CTRL-ENTER
Once you have entered your query, you can
execute it and see the results by pressing
CTRL-ENTER (press the Ctrl key and while
holding it down press the Enter key and then
release both of them).  Your query will be
processed and the results will be displayed on
the screen.

You can scroll your query result one line at a
time with the up and down arrows.  Using the
PgUp and PgDn keys you can scroll up and down
one page at a time.

If you requested enough fields to be displayed
so that the output is longer than 80
characters, you can scroll to the right or
left if required by pressing the left and
right arrows.

Note: QSQL can only scroll to see the first
255 characters of the record.  If your output
is longer, you will not be able to see the
results which are past column 255.

While viewing your results, you can print the
output on your printer by pressing the CTRL-L
key.                    - 3 -
CTRL-ESC
This will terminate QSQL and return you back
to DOS.

CTRL-V
This will re-display the results of your last
query.

CTRL-C
This will clear the screen so you can enter
your query from scratch.

CTRL-L
The will permit you to load a previously saved
query.  You will be given a list of saved
queries.  Move the cursor to the one you want
to load and press enter.

While viewing the list of queries, you can
press the DEL key to delete them.

CTRL-S
Will permit you to save your current query.
You will be prompted for the name of the
query.  If this is a previously loaded query,
then the name of the query will be already
filled in.  Change the name to what you want
to save the query as and press enter to save
it.

CTRL-F
Will list all the databases (.DAT) files in
your directory                    - 4 -
SQL TUTORIAL

This section will give you a brief tutorial on
how to use SQL.  This section is intended for
those who have had no previous experience in
SQL, or those who need a refresher.

SQL is a query language which was developed by
IBM during their Systems R research project in
the late 70's.  It now has wide spread
popularity and as such, is considered as the
standard query language for relational
databases.  The main power in SQL is that it
is a non-navigational query language.  That
is, you specify what data you want to display,
but not how to navigate the databases in order
to answer your query.

Before we get into any details about SQL
queries, lets examine a few simple ones to
give you the flavor of the query language.
These queries are on the sailors database.
Refer to Appendix A for a schema listing of
the sailors database. Appendix A also has a
listing of all the records in the databases.
The query number e.g. (Query 1) is used to
identify the query so you can easily load and
execute it in the example databases which
accompany QSQL.                    - 5 -
(Query 1)

SELECT * FROM sailors;

SID        SNAME                RATING
────────── ──────────────────── ──────
         1 George Bush             100
         2 Ronald Regan             50
         3 Jimmy Carter            255
         4 Gerald Ford             230
         5 Richard Nixon           124
         6 Lyndon Johnson          250
         7 John Kennedy             75
         8 Dwight Eisenhower         5
         9 Harry Truman              4
        10 Franlin Roosevelt         8
        11 Herbert Hoover          124


This query will display all records and fields
from the sailors.dat database.  It will also
display all records.  The SELECT tells SQL
that a query is to follow.  The * is a
wildcard character and tells QSQL to display
all fields which are contained in the
database. FROM tells QSQL the database(s)
which need to be processed.  In the above
query, the database is sailors.

After the databases are listed in the FROM
clause, the WHERE clause can be used to
restrict which records from the database are
to be displayed. Since the WHERE clause was
not part of QUERY 1, this tells QSQL to
display all records in the database.  The next
query will demonstrate the use of the where
clause.
                    - 6 -
(Query 2)

SELECT * FROM sailors WHERE sid = 5;

SID        SNAME             RATING
────────── ───────────────── ──────
         5 Richard Nixon        124

This query will display all fields from the
sailors.dat database.  It will only display
the records in which the SID field has the
value of 5.  This query is similar to query 1
with the exception of the WHERE clause sid =
5.  The where clause restricts what is
displayed to only those records in the
database which matches the clause.

(Query 3)

SELECT sname, rating
  FROM sailors
 WHERE rating > 100;

     SNAME                RATING
     ──────────────────── ──────
     Jimmy Carter            255
     Gerald Ford             230
     Richard Nixon           124
     Lyndon Johnson          250
     Herbert Hoover          124


This query will display the sailors name and
their rating of all sailors in the SAILORS.DAT
database who have a rating greater than 100.

This completes the introduction to SQL. We
will now explicitly describe every aspect of
QSQL with examples.  Now would be a good time
to try QSQL on your own databases to practice
what you have learned.                    - 7 -
SQL SELECT SYNTAX

The syntax of SQL queries is as follows:

       SELECT [DISTINCT] fieldnames
         FROM filenames
       [WHERE clause1 AND clause2 AND etc...]
       [GROUP BY fieldnames]
       [ORDER BY fieldnames] ;

Clauses enclosed in brackets [  ] are
optional.

The SELECT clause describes the fields you
wish to display.

The DISTINCT keyword is used to insure that no
duplicate information is displayed.

The FROM clause describes the files which are
to be processed.

The WHERE clause describes which records from
the database(s) are to be displayed. It is
also used to specity how files are to be
joined together.

The GROUP BY clause is used to group
information together.

The ORDER BY clause is used to sort the
information being presented.

                    - 8 -
Fieldnames

Fieldnames are the name of the fields that are
to be displayed. The names are separated by
commas.  The names can be qualified with a
file alias (e.g. alias.fieldname) to
distinguish among same field names in several
files. Two special field names exist.  The *
field name specifies all fields in the first
file in the FROM clause.  The * can also be
qualified with an alias to specify the file if
more than one exists.  The # field name
corresponds to the physical record number. It
too can be qualified with a file alias.

When fields are displayed, they are displayed
according to there type (i.e. strings are
displayed as strings and numbers are displayed
as numbers).  There are two exceptions to this
rule.  If a field is of type LONG and has the
substring DATE contained in the field name, it
is converted to a date and displayed in the
form: mm/dd/yy.  If a field is of type long
and has the substring TIME contained in the
field name, it is converted to a time and
displayed in the form: HH:MM:SS.

You can override the output format by
suffixing the field name with an @ followed by
the format with which you would like the data
to be displayed.  Valid suffixes are:

@D - display the field as a date. Used when
       you have a date field which does not
       contain the substring DATE in the field.
@T - Display the field as a time. Used when
       you have a time field which does not
       contain the substring TIME in the field.
@N - Display the field as a number.  Used when
       you want to display a date or time as its
       numeric value.                    - 9 -
@Nw - Used to change the width of the field in
       which the number is displayed. w is the
       size.
@Nw.d - Used to specify the width as well as
       the number of decimal places to display.
       This only works on REAL or DECIMAL
       fields.

@Sw - Display the string field with a given
       width.

Some examples are:

DTFLD@D - Display the field DTFLD as a date.

MONEY@N10.2 - Display the money field as a
number with two decimal places.

Note: QSQL will always display the number.  If
the number is too large to  fit within the
field width, it will expand the width of the
field appropriately, but only for the line(s)
in which the number is too large. This will
cause the line to not be aligned with the
lines above or below it on the report. So, be
sure to specify a width in which all numbers
for the field can fit.  The width field used
with strings do truncate any characters which
cannot be displayed.

Here is a sample query:

SELECT sname@s5, rating@n15
  FROM sailors
 WHERE rating > 200;

SNAME RATING
───── ───────────────
Jimmy             255
Geral             230
Lyndo             250                                - 10 -
Filenames

filenames are the names of the files from
which the fields of the files are to be
selected. The files are separated by a comma.

Example:

filename1, filename2

You can assign an alias to a file by entering
the alias name after the filename seperating
the two by a space.  Example:

filename1 xxx, filename2 yyy

This assigns the alias of xxx to filename1 and
yyy to filename2.  When refering to fields,
you can prefix then with the alias name.  For
example, suppose field1 is contained in the
file filename1.  Then, you could refer to
field1 by xxx.field1.   Aliases are important
when the same field name is contained in
several files in order to uniquely identify
the field.  For Clarion files, the alias
defaults to the file 3 character prefix.

If your file is contained in another
directory, you can specify the directory
information.  Example:

\directory\filename

If your file is contained on another drive and
directory, you specify this as follows:

d:\directory\filename

where d: is the drive letter.                   - 11 -
Where clauses

clause1, clause2, etc... are the clauses with
which records are selected from the databases.
These clauses are used to specify which
records are to be printed and how two files
are to be joined.

Where clauses are of the form:

       field op value
       field op field
       clause or clause

The valid operators are:

       =     Equals (begins with)
       <>    Not Equals
       <     Less Than
       <=    Less Than Or Equal
       >     Greater Than
       >=    Greater Than Or Equal
       LIKE  pattern matching - strings only

Note:  The equals operator (=) acts like a
begins operator.  For example: name = 'j' will
match all records in which the name field
begins with the letter 'j'.  If you want only
the name field which has exactly J, pad the
string with blanks to the length of the field
to which it is compared against.

In addition, comparisons against strings are
case insensitive.  That is, corresponding
lower case and upper case letters are
considered equivalent. For example, if you
search for 'qsql' it will match 'qsql',
'QSQL', 'Qsql', 'QsQL', etc...
                   - 12 -
Like Operator

The above operators are pretty self
explanatory except for the  LIKE operator. The
LIKE operator is a powerful pattern matching
operator which can be used on string fields.
It can be use to find substrings, begins with
strings and ends with strings.  The syntax of
the LIKE operator is:

        field LIKE 'pattern'

where pattern can contain characters which are
to be matched or two special characters:

       % - The percent sign (%) is used to
             indicate any string of zero or more
             characters.

       _ - The underscore (_) is used to
             indicate any single character

The LIKE operator is a case insensitive
operator.  That is, when matching a lower case
character with an upper case character, the
LIKE operator will result in a match.

Here are some examples:

1) Names which begin with the letter J

       name LIKE 'j%'

Note that this performs the same function as
NAME = 'j'. You should use the name = value
syntax when you want to perform a begins with
as it is much faster than the LIKE operator.

2) Names which begin with J and end with S

       name LIKE 'j%s'                   - 13 -
3) Names which are three characters long and
end with IM

       name LIKE '_im'

4) Names which start with B, and have a LY
somewhere in it

       name LIKE 'B%LY%'

5) Names which contain the word LEE

       name LIKE '%LEE%'

6) Names which have the initials HH
       name LIKE 'H% H%'

Other interesting patterns include:

  '__'    Matches strings which are exactly 2
          characters long
  '____%' Matches strings which are at least 4
          characters long

                   - 14 -
Strings

String constants are entered enclosed in
apostrophe (') symbols.  For example, to enter
the string YELLOW, you would code:

       'YELLOW'

Note: Null strings '' don't have any meaning
and can product un-predictable results.

Numbers

Numbers are entered as usual.  Commas are
permitted and ignored.  If you wish to start
your number with a $ to signify money, that is
acceptable as well.  Some numeric examples
are:
       1234
       $1,234
       1234.56
       $1,234.56
       -$1234.56

Dates and Times

When dealing with dates and times, you can
enter the values as: mm/dd/yy or hh:mm:ss.

For example, you can enter a query clause:

       DATEFIELD = 06/01/91

To find dates which match June 1st, 1991.

For times you can enter:

       TIMEFIELD = 13:30:00

To find times which match 1:30 P.M..                    - 15 -
Joins

One of the powerful features of QSQL is the
ability to combine (join) various databases
together in order to obtain related
information. In order to motivate the reasons
for doing a join, a demonstration of a query
which needs to do a join is in order. Suppose
you want to know the names of the sailors who
have reserved a boat on 06/01/91.  To solve
the query, you could enter a query as
follows:

(Query 4)
SELECT * FROM res WHERE date = 06/01/91;

BID        SID        DATE
────────── ────────── ────────
         4          5 06/01/91
         4          7 06/01/91
         9          1 06/01/91


This query would list out the sid, bid, and
date fields from the database.  However, the
query you want is to display the sailor's
names, not their sid numbers.  The names of
the sailors are in the sailors.dat database,
but their reservation information is not so we
cannot just do a simple query on the sailors
database.

In order to obtain both pieces of information,
we need to join the two tables together.  To
join the two tables, you need to specify two
pieces of information: the tables to be
joined, and the fields on which they are
joined.  In our example, the two databases
are: res and sailors.   The field on which
they are to be joined is the field which they
share, the sid field.                   - 16 -
To specify the shared (joined) field, you add
a statement to the where clause: alias1.field
= alias2.field where alias1 and alias2 are the
aliases of the two files which are to be
joined.  Note that usually the field names are
the same, but do not have to be.  Therefore to
answer our query, the SQL command would be:

(Query 5)

SELECT sai.sname
  FROM sailors sai, res res
 WHERE date = 06/01/91
   AND sai.sid = res.sid;

SAI.SNAME
────────────────────
Richard Nixon
John Kennedy
George Bush


You can join any number of tables together by
just listing them in the FROM clause and
adding N-1 WHERE clauses where N is the number
of databases to be joined.

Lets augment the above query to include not
only the sailor's name, but to also include
the color of the boat they reserved.  The boat
color is contained in the boats database.  The
res and boats both have the bid field for
which to perform the join on.  Here is the SQL
query to answer that question:
                   - 17 -
(Query 6)

SELECT sai.sname, boa.color
  FROM sailors, res, boats
 WHERE date = 06/01/91
   AND sai.sid = res.sid
   AND boa.bid = res.bid;

SAI:SNAME            BOA:COLOR
──────────────────── ──────────
Richard Nixon        GREEN
John Kennedy         GREEN
George Bush          BLUE


Some points to note:

- Order of the were clauses does not matter

  sai.sid = res.sid and boa.bid = res.bid

     is equivalent to:

  boa.bid = res.bid and sai.sid = res.sid

- Order of the join fields does not matter

  sai.sid = res.sid

     is equivalent to:

  res.sid = sai.sid
                   - 18 -
Rename and Joins

With the alias function, you can have multiple
instances of the same file. This is useful if
you would like to join a file with itself.
Having multiple instances of the same file is
know as a rename of the file.

Suppose you want to display the sailors who
have reserved a boat on 06/01/91 and 06/02/91.
In order to solve this type of query, the
sailors database needs to be joined against
the reservations database, twice.  Here is the
query:

(Query 7)

SELECT sai.sname
  FROM  sailors sai, res rs1, res rs2
 WHERE  rs1.date = 06/01/91
   AND rs2.date = 06/02/91
   AND rs1.sid = rs2.sid
   AND rs1.sid = sai.sid;

SNAME
────────────────────
George Bush


This query needs to rename the reservations
database so that it could have two instances
active at once.  The rs1 instance is for all
reservations for 06/01/91 and the rs2 instance
is for all reservations on 06/02/91.  The
clause rs1.sid = rs2.sid insures that the
sailor who reserved the boat on 06/01/91 is
the same one who reserved the boat on
06/02/91.  And finally the rs1.sid = sai.sid
is used to relate the sid found with the name
of the sailor.
                   - 19 -
You may be wondering if the query:

       SELECT sailors.sname
         FROM sailors sailors, res res
        WHERE res.date = 06/01/91
          AND res.date = 06/02/91
          AND res.sid = sailors.sid;

would solve the problem.  It does not.  This
is because there is only one instance of the
res database active in the query.  Therefore
there is only one date value active when the
comparison is made.  No records would be
selected as there is no date which is equal to
both 06/01/91 and 06/02/91.

Another example in which you would need to use
a rename is a follows:

Suppose you had a database, employee, with
three fields:

  name - the name of the employee
  salary - the salary of the employee
  mgrname - the name of the employee's manager

To answer the query, list all employees who
earn more than their manager, the following
query is used:

       SELECT emp.name
         FROM employee emp, employee mgr
        WHERE emp.mgrname = mgr.name
         AND emp.salary > mgr.salary;

                   - 20 -
SELECT DISTINCT

The distinct keyword is used to eliminate
duplicates from your result.  To motivate its
use, here is a query we want to answer

What types of boats does the club own?

The boat types are in the bname field of the
boats relation.  So, we could issue the
following simple query to get the list:

  SELECT bname FROM boats;

BNAME
────────────────────
Interlake
Interlake
Interlake
M20
M20
M20
Keel
Interlake
Guppy
Guppy

But as you can see, all boat records are
displayed an as such, you have several
INTERLAKE, M20, etc... displayed.  Just think
if this boat club had hundreds or thousands of
boats.                     - 21 -
To solve the problem in a more appropriate
fashion, we need to remove duplicate types
(bname's).  This can be accomplished with the
DISTINCT keyword. Here is a revised query:

SELECT DISTINCT bname FROM boats;

BNAME
────────────────────
Guppy
Interlake
Keel
M20

This removes the duplicates and gives you the
answer you want.  Note that duplicate
elimination is performed by sorting the data.
This slows down your query and as such you
should only specify DISTINCT when it is
required.                   - 22 -
ORDER BY

The order by clause is used to sort the output
in ascending order. The clause is used to
specify the fields in which the sort order is
to be performed.

For example, in QUERY 1 we displayed all the
sailors in the database via the query:

SELECT * FROM sailors;

The sailor information was printed in the
order it is in the database.

If we wanted them printed in order of their
rating, we could use the query:

SELECT * FROM sailors ORDER BY rating;

SID        SNAME                RATING
────────── ──────────────────── ──────
         9 Harry Truman           4
         8 Dwight Eisenhower      5
        10 Franlin Roosevelt      8
         2 Ronald Regan          50
         7 John Kennedy          75
         1 George Bush          100
        11 Herbert Hoover       124
         5 Richard Nixon        124
         4 Gerald Ford          230
         6 Lyndon Johnson       250
         3 Jimmy Carter         255


                   - 23 -
AGGREGATES

Aggregate functions (or sometimes known as
column functions) are used to perform
operations on a particular field.  There are 5
aggregate functions:

AVG - Computes the average value.

COUNT - Counts the number of records.

SUM - Computes the sum (total) of the field.

MIN - Finds the minimum value.

MAX - Finds the the maximum value.

All of the above aggregates takes a field name
as its argument. The COUNT aggregate is
special in that it does not require a field
because it is just counting occurances.  As
such, you can code the COUNT aggregate as
COUNT(*) which is a common practice.

For example, to find the average rating of all
sailors in the sailing club, you would use the
query:

SELECT AVG(rating) FROM sailors;

AVG(RATING)
───────────
111                            - 24 -
Suppose that you want to know the average
rating of all sailors who reserved an
interlake boat.  To get the list of sailors
who served an interlake boat, you could enter
the query:

       SELECT *
         FROM sailors sai, res res, boats boa
        WHERE res.bid = boa.bid
          AND res.sid = sai.sid
          AND boa.bname = 'interlake';

SID     SNAME                RATING
─────── ──────────────────── ──────
      1 George Bush          100
      3 Jimmy Carter         255
      2 Ronald Regan          50

To get the average value, you would enter the
query:

       SELECT AVG(rating)
         FROM sailors sai, res res, boats boa
        WHERE res.bid = boa.bid
          AND res.sid = sai.sid
          AND boa.bname = 'interlake';

AVG(RATING)
───────────
135                            - 25 -
GROUP BY

The GROUP BY clause is used in conjunction
with aggregate functions in order to produce
statistics by groups, instead of a single
number for the whole database.  For example,
suppose we wanted to find out how many boats
we have of each color.  We could display all
the boats with the

 SELECT color FROM boats;

query and count them.  Or, we could count all
the red boats by entering the query:

 SELECT count(*) FROM boats WHERE color='red';

COUNT(COLOR)
────────────
     3

Then repeat the above query for each color of
boat.

But, the best way is to use the GROUP BY
clause as demonstrated by the following query:

  SELECT color, COUNT(*)
    FROM boats
   GROUP BY color;

COLOR      COUNT(COLOR)
────────── ────────────
BLACK               2
BLUE                4
GREEN               1
RED                 3
                        - 26 -
Now suppose we want the number of boats of
each type which has the same color. (i.e. how
many red interlake boats are there?, green
interlakes? etc...)  The query is:

  SELECT bname, color, COUNT(*)
    FROM boats
   GROUP BY bname, color;

BNAME                COLOR      COUNT(*)
──────────────────── ────────── ──────────
Guppy                BLUE                2
Interlake            BLACK               1
Interlake            BLUE                1
Interlake            RED                 2
Keel                 BLUE                1
M20                  BLACK               1
M20                  GREEN               1
M20                  RED                 1

Now lets answer the question: What type of
boats are the most popular? By popular we mean
which ones get reserved most often.  To answer
this question, we want to know how often each
type of boat get reserved. This can be
accomplished by the following query:

SELECT bname, count(*)
  FROM boats boa, res res
 WHERE boa.bid = res.bid
GROUP BY bname;

BNAME                COUNT(*)
──────────────────── ──────────
Guppy                         1
Interlake                     3
M20                           9

As you can see, the M20 boats are the most
popular type of boats to be reserved.                        - 27 -
SQL UPDATE SYNTAX

The syntax of the update command is:

UPDATE filenames
   SET expressions
[WHERE where clauses];

Note: The update command in QSQL does not
update the key files.  If you update a field
which has a key on it, you will need to
rebuild the key in order for it to obtain the
new value.  In addition, you cannot update
decimal fields.  These restrictions will be
removed in a future version of QSQL.


Structure of Update command

The structure of the SQL update command
consists of three clauses: UPDATE, SET, WHERE.

The UPDATE clause describes the databases
which are to be used or updated in the
command.

The SET clause describes what fields are to be
changed and to what values.

The WHERE clause describes which records from
the database(s) are to be updated.

filenames

Specifies the file names which are used in the
update command.  Refer to the select command
for more information on the file names.                   - 28 -
expression

Specifies the fields which are to be updated
and to what value they are to be set. The
syntax of the expression is:

       field = value

where field is the field to be updated and
value is the value the field is to be changed
to.  Value can be a constant or another field.
Some examples are:

       name = 'jones'
       date = 01/01/91
       age = 14
       name = newname

where clauses

The where clauses are similar to those of the
select command. The clauses describes which
records are to be updated.  A simple where
clause would be:

       field = value

More complex where clauses can be used which
involves joins in order to perform complex
updates.

For more information on the where clause,
refer back to the SELECT command where clause
section of the manual for more information.
                   - 29 -
Here are some sample updates and a discussion
about what they do.

(Update 1)

       UPDATE sailors SET rating = 100;

This update changes every record in the
sailors database and sets the rating to 100.

(Update 2)

       UPDATE sailors
          SET rating = 200
        WHERE sname = 'Jimmy Carter";

This update changes Jimmy Carter's rating to
200.

Now for a more complex update which involves a
join. Suppose we want to set the rating to 200
of anyone who has reserved a boat on 06/01/91
and 06/02/91.  In (Query 7) we found the
sailors who reserved boats on those dates.
Using the same where clauses, we can perform
the update as follows:

(Update 3)

       UPDATE sailors sai, res rs1, res rs2
          SET rating = 200
        WHERE rs1.date = 06/01/91
          AND rs2.date = 06/02/91
          AND rs1.sid = rs2.sid
          AND rs1.sid = sai.sid;
                   - 30 -
Next, suppose we sold boat 4 which was a green
M20 and bought a new boat M30 which is yellow
to replace it.  The following update can be
used to change the database:

(Update 4)

       UPDATE boats
          SET bname = 'M30',
              color = 'YELLOW'
        WHERE BID = 4;

Please note that the comma between bname =
'M30' and color = 'YELLOW' is required to
separate the set expressions.

And a final reminder, if you update a field
which has a key on it, you need to rebuild the
key with the clarion BUILD command or the
Clarion utility filer (CFIL.EXE).                    - 31 -
The SQLTABLE Model File

The SQLTABLE model file is a model file which
can be used to execute SQL commands directly
from your designer application. The records
which match the query are then displayed in a
table.  The SQLTABLE.MDL file only contains
the model file for the SQL tables.  This model
is based on the STANDARD.MDL file which is
distributed by Clarion.  As such, it is not
intended for network applications.  Also, if
you are using a 3rd party model file, (e.g.
Mike Hanson's Super Model files), this model
may or may not work.  It is only intended to
work with the STANDARD.MDL.   If you would
like to use this capability with other 3rd
party model files, please contact the model
file vendor and request that they support QSQL
directly in their model files.

Installation

Before you can use the SQLTABLE.MDL file in
your application, you need to combine it with
the STANDARD.MDL file in order to create a
complete model file.  This can be accomplished
with the command:

       COPY SQLTABLE.MDL+STANDARD.MDL QSQL.MDL

This will create a new model, QSQL.MDL which
contains both the standard model and sqltable
models.

To install this into your designer
application, you need to select designer from
your main clarion menu and follow the steps
below:

1) On the initial designer screen, enter your
       application name.                   - 32 -
2) Change the model file name to the new
       QSQL.MDL file you created.

3) Once at the designer application display,
       you need to add an other procedure by
       moving the cursor to the procedure side
       of the screen and then press the INS key.
       Then add the following information:

        PROCEDURE NAME: BLDSQL
           MODULE NAME: XBLDSQL
                BINARY: NO
          RETURN VALUE: NO

4) Now you are ready to add your SQL table.
       Select the menu you want to have the SQL
       option on.

5) Add a menu item which calls a new
       procedure.  Any procedure name you like
       will be fine.  Save the menu by pressing
       ctrl-enter.

6) Next select the procedure you just added
       which should be labeled as TODO. Create
       the procedure type as a TABLE and then
       build the table just like you would if
       you were normally building a table to
       view/update the file with one exception,
       the model name should be SQLTABLE.

7) You are now done.  Press SHIFT-F7 to create
       and test your application.
                   - 33 -
SQLTABLE Usage

When you invoke the table from your
application, you will be first prompted to
enter the query.  This is the where clause
part of the SQL statement.  The SQLTABLE model
automatically creates:

SELECT # FROM filename WHERE

and tacks on what you enter on this form. Once
you have entered the query, press CTRL-ENTER
and the query will execute and only the fields
which match the query will be displayed on the
table.

If you like, after the where clauses, you can
enter the ORDER BY clause to specify the sort
order of your query.

Join Queries

If you need to perform joins to solve your
query, you will need to modify XBLDSQL.CLA to
suit your needs.  For joins, the form of the
query needs to be: SELECT # FROM filename1,
etc... where filename1 is the file to be
displayed on the table. I.E. The table file
needs to be specified first in the list of
files.                    - 34 -
Appendix A - Sailors/Boats/Res Schema and
             listing

Throughout this document, the sailors, boats,
res databases are used for examples. Here is a
list of the fields and their descriptions in
the databases. The three databases are used to
model a sailing club who has sailors, boats
and reservations.

Sailors: Describes Sailors in the sailing
club.

   SID    - LONG   - A Sailor ID Number
   SNAME  - STRING - The name of the sailor
   RATING - LONG   - The sailor's rating

SID        SNAME                RATING
────────── ──────────────────── ──────
         1 George Bush          100
         2 Ronald Regan          50
         3 Jimmy Carter         255
         4 Gerald Ford          230
         5 Richard Nixon        124
         6 Lyndon Johnson       250
         7 John Kennedy          75
         8 Dwight Eisenhower      5
         9 Harry Truman           4
        10 Franlin Roosevelt      8
        11 Herbert Hoover       124
                   - 35 -
Boats: Describes Boats in sailing club.

   BID    - LONG   - A boat ID number
   BNAME  - STRING - The type of the boat
   COLOR  - STRING - The color of the boat

BID        BNAME                COLOR
────────── ──────────────────── ──────────
         1 Interlake            RED
         2 Interlake            RED
         3 Interlake            BLUE
         4 M20                  GREEN
         5 M20                  BLACK
         6 M20                  RED
         7 Keel                 BLUE
         8 Interlake            BLACK
         9 Guppy                BLUE
        10 Guppy                BLUE
                   - 36 -
Res: Reservations database - Describes which
boats are reserved, by which sailor, on which
date.

   SID  - LONG - The sailor who reserved boat
   BID  - LONG - The boat id of reserved boat
   DATE - LONG - The date the boat is reserved

Res Database
BID        SID        DATE
────────── ────────── ────────
         1          1 06/13/91
         2          2 06/13/91
         4          1 06/02/91
         4          5 06/01/91
         4          7 06/01/91
         1          3 06/10/91
         9          1 06/01/91
         5          9 06/02/91
         4          2 06/08/91
         6          3 06/02/91
         4          8 05/31/91
         6          1 05/28/91
         5          3 05/28/91
                   - 37 -
Appendix B - SQL.EXE

The SQL.EXE program is the main SQL processor.
It accepts SQL commands as a parameter:

       SQL "select * from sailors;"

From the keyboard

       SQL

the program will prompt you to enter a SQL
query

Or via re-direction

       SQL <query.in

where query.in is the file which contains a
SQL query.

The output is displayed on the terminal. If
you want the output to be sent to a file, you
can re-direct the output to a file via the dos
re-direction facility.  The following examples
send the output to a file called query.out.

       SQL <query.in >query.out

or

       SQL "select * from sailors;" >query.out
                   - 38 -
Appendix C - How to execute SQL from your
             Clarion application

If you would like to execute SQL from your
Clarion application and process the output,
you will need to code the interface by hand.
To run QSQL from your application you need to
use the RUN or RUNSMALL command.  RUNSMALL
executes much faster than the RUN command, but
may not work if there is not enough available
memory.   When you call SQL.EXE from your
application, you should code the following:

       RUNSMALL('SQL <q.in >q.out')
       IF RUNCODE() = -2 THEN
                          RUN('SQL <q.in >q.out').

This code will first try the RUNSMALL command
and if there is not enough memory, it will
then execute the RUN command to execute the
SQL query.  The above queries assume that the
query was loaded into a file called q.in and
the results of the sql query is to be placed
into the q.out file.

In most cases you will probably just want a
list of record numbers which match the query
to be placed into the q.out file.  Using the #
as the field name displays the record number
(e.g. "select # from ...).  If you have more
than one file the query is being processed
against, you can prefix the # with the prefix
of the file (e.g. PRE:#).

The -U option may be helpful in accessing the
data directly from Clarion.  Refer to the
XBLDSQL.CLA and SQLTABLE.MDL files for and
example on how to implement this as that is
exactly what they do.                     - 39 -
Appendix D - QSQL Version 2.0 Limitations

Here are a set of limitations which are
currently imposed.

1) Does not support BETWEEN  - Is equivalent
to: field >= low and field <= high

2) Does not support nested (correlated)
queries  - Most nested queries can be written
into non-nested queries with joins.

3) Does not support SQL HAVING statement.

4) Only the SELECT and UPDATE statements have
been implemented.  UPDATES do not update key
files. If you update a field on which there is
a key, you will need to perform a clarion
BUILD or run the filer utility (CFIL.EXE) on
the database in order to use the key with the
updated values. In addition, you cannot update
decimal fields.

5) Does not use keys for fields of type REAL
or DECIMAL.  QSQL does not use keys for
strings which are defined to be case
sensitive.  Will use decimal keys for join
operations when both are the same size.

6) Does not support arithmetic operators (e.g.
+, -, *, /).

7) Does not support encrypted files.

8) On networks, updates does not support
record locking.                   - 40 -
Appendix E - Join Performance

Joins are a terribly slow process in
relational databases. While QSQL performs them
fast, as joins go, they are still slow.  To
speed up joins, insure that there is an index
on the join fields.  QSQL will use this index
to speed up the join processing.
                   - 41 -
Appendix F - Processing Options


You can alter the way SQL processes by passing
processing options to the SQL processor.  The
options are:


-C     Output should be in ascii, comma
       delimited format.  The first row of the
       output is the column headings.  The rest
       of the rows is the actual data. The -C
       option will probably be used in
       conjuction with the -O option.

-Offff       Send the output to the file ffff.

-Q     Don't display column headings.

-Rnnn        Where nnn is the number of sort
       runs.  nnn determines the largest file
       which can be sorted. The larges file size
       which can be sorted is 10,000 times the
       value on nnn.  The default value is 100,
       so a 1 megabyte file is the largest which
       can be sorted.

-S     Display some processing statistics

-U     Display the output as uninterpreted data.
       For example, a long is stored in the
       computer as a 4 byte binary number.  If
       you display a long field with the -U
       option turned on, the 4 byte number will
       be display in the internal format. -U
       should be used in conjunction with the -O
       operand in order to specify the output
       file.  The -U option is for exporting the
       results to another program.                    - 42 -
-Wx    Specifies that sort workfiles should be
       placed on drive x: (where x is the drive
       letter).  When sorting, two temorary
       files are required which is equal in size
       of the data to be displayed.  If you have
       a ram drive which is large enough to hold
       these workfiles, use it as that will
       greatly enhance the speed of the sorting
       process.  The default drive is the
       current drive.


Example usage:

You want to create a file called EXPORT.SQL
which contains the comma delimited output of
the query. In addition, you want to specify
the E drive as the temporary sort work drive.

The SQL options would be:

SQL -C -OEXPORT.SQL -WE "select * etc..."                   - 43 -
Appendix G - Revision History

07/01/91 V1.0 - Never released.  Beta testing
       went so well, we immediately went to
       version 1.1 as our initial release of the
       product.

07/19/91 V1.1 - Added UPDATE command

10/09/91 V2.0
       - Supports directory information for
             files
       - Supports DISTINCT keyword to eliminate
             duplicates
       - Supports ORDER BY to specify sort order
             (ascending only)
       - Supports GROUP BY to group output
       - Supports aggregate functions AVG,
             COUNT, MIN, MAX, SUM
       - Supports output formats for data
       - Supports output to a different file
       - Supports comma delimited output
       - Supports Uninterpreted data output
       - Add temp file specification for sorting
       - Add a SQLTABLE model file so that SQL
             can be incorporated into DESIGNER
             apps
       - Limited support for xbase files -- does
             not use an index on them.                   - 44 -
Appendix H - Creating Clarion Files

If you would like to create a Clarion file
from the output of your SQL query, you can
perform the following two steps:

1) Execute your SQL query with the options -C
       and -Ofilename to create an ascii, comma
       delimited file of the output.  Be sure to
       include the suffix .BAS for the file
       name. This will become apparent in the
       next step.

2) Execute the Clarion converter utility,
       CCVT.EXE to convert the file into a
       Clarion database.  The Clarion converter
       utility requires that the file have a
       suffix of .BAS .

These two steps can be consolidated into a
single .BAT file.                    - 45 -
Appendix I - License Agreement

Cook Database Design, Inc. hereby disclaims
all warranties relating to this software,
whether express or implied, including without
limitation any implied warranties of
merchantability or fitness for a particular
purpose. Cook Database Design, Inc. will not
be liable for any special, incidental,
consequential, indirect or similar damages due
to loss of data or any other reason, even if
Cook Database Design, Inc. or an agent of Cook
Database Design, Inc. has been advised of the
possibility of such damages.  In no event
shall Cook Database Design, Inc.'s liability
for any damages ever exceed the price paid for
the license to use software, regardless of the
form of the claim.  The person using the
software bears all risk as to the quality and
performance of the software.

This software is protected by both United
States copyright law and international treaty
provisions. You are licensed to use this
software under the provision that only one
copy will be in use at a time.  That is, the
software can reside in many places, (eg.
backup diskette, work computer, home computer)
as long as there is no possibility that it is
being used at one location while it is being
used at another.

If you require multiple copies of QSQL to be
executing simultaneously, then you need to
purchase addition copies of QSQL.

If you want to distribute SQL.EXE with your
applications, you need to purchase a SQL.EXE
runtime license, 1 for each SQL.EXE
distributed.