Liren Large Software Subsidy 5/05.iso/a/a520/3.ddi/WINTUTOR.WTS/WINTUTOR.WTS

Text File | 1990-10-28 | 72.5 KB | 2,198 lines

REMARK
\
The following tutorial is intended to illustrate the  capabilities  of the
SQL language as implemented in the  SQLBASE  database management  system.
Since SQLBASE is intended  primarily  as  a  database  engine  for  serious
application development on the IBM PC family of computers and  networks,  a
basic level of familiarity with database concepts is assumed.

Note also that the program you are currently running is called SQLTALK.  It
is an Interactive Data Manager that enables you  to  execute  SQL  commands
(either interactively from the keyboard or  by executing  a  batch  file).
SQLTALK also contains certain non-SQL commands.  For example,  PAUSE  is  a
SQLTALK command that causes a pause to occur in the output.  Press Carriage
Return to proceed after the pause.

NOTE: This tutorial assumes that you are connected to the DEMO database. If
      you are not, halt this tutorial, connect to DEMO and begin again.
/
PAUSE
/
REMARK
\
The following SQLTALK command will cause the time taken by the database for
performing each operation to be displayed after the  results. In  queries
involving screen output, the time reflects how long the database  takes  to
process the query rather than the elapsed time.  Thus, screen display  time
is not included in this measurement.
/
SET TIME ON
/
REMARK
\
Other SQLTALK commands include REMARK, RUN, STORE, EXECUTE etc.  that  make
this tutorial more easy to  read  or  are  intended  to  perform  functions
usually  available  only  through  the C  Application Program  Interface.
SQLTALK can also be used to format reports based  on  the  results  of SQL
queries.  These report writing facilities are illustrated towards  the end
of the tutorial.  In addition, SQLTALK has commands  that  are useful for
database administrators (such as CONNECT,  DISCONNECT, LOAD  and  UNLOAD).
All commands issued to SQLTALK (whether they are SQL statements or  SQLTALK
commands) must be terminated by a / on a new line or a ; at the end of the
last line of the command.  Throughout this session, we will  use  a  /  to
signify the end of a command.

During this session, simply press Carriage Return when you are ready to  go
to the next command.  In case a query results in more than one screenful of
data, press Carriage Return to see the next screen or <A> to abort  and  go
to the next command.  To exit from the SQLTALK demo, type in Control-C.

We start off by first creating a table called PRESIDENT.
/
CREATE TABLE PRESIDENT
(PRES_NAME VARCHAR(20) NOT NULL,BIRTH_DATE TIMESTAMP,YRS_SERV INTEGER,
DEATH_AGE INTEGER,
PARTY VARCHAR (20),STATE_BORN VARCHAR(20))
/
REMARK
\
In the above table, we defined six columns.  The data in those columns can
be of four types: a character field, a number field, a date field or a long
field. SQLBASE supports  the  following  SQL  data  type  keywords:  CHAR,
VARCHAR, SMALLINT, INTEGER, FLOAT,  DECIMAL,  and  LONG  VARCHAR.   In the
PRESIDENT table,  PRES_NAME,  PARTY  and  STATE_BORN  are  variable  length
character fields with a maximum length of 250. The PRES_NAME is  specified
to be a NOT NULL field, ie. it may not contain a blank.

BIRTH_DATE is a date  (and  may  be  entered  in  one  of  three  formats).
YRS_SERV and DEATH_AGE are  numeric  data  types  and  may  be entered  as
integers.  No LONG VARCHAR data field is defined  (these  may  be  variable
length character fields of unlimited length and can contain text or  binary
data). The SQLBASE Language Reference Guide contains more information on
SQL data types.
/
PAUSE
/
REMARK
\
The following INSERT command adds one row of data to the  PRESIDENT  table.
Note all character data must be enclosed in single  quotes.   Numbers  have
been entered as integers and the date field  is  specified  in dd-mon-yyyy
format.
/
INSERT INTO PRESIDENT
VALUES ('Washington G','1732-02-22-00.00.00.000000',4,67,'Federalist','Virginia')
/
REMARK
\
In the above SQL statement, an entire row of data  was entered.   You may
also enter data selectively into some columns only, and  not  into  others.
To do this, you must specify the columns into which data is to be inserted.
The remaining columns will all contain the null character.   The  following
statement inserts data into all six columns of the  PRESIDENTS  table but
lists them individually.
/
INSERT INTO PRESIDENT (PRES_NAME, BIRTH_DATE, YRS_SERV,
DEATH_AGE, PARTY, STATE_BORN)
VALUES ('Jefferson T','1743-04-13-00.00.00.000000',8,83,'Demo-Rep','Virginia')
/
REMARK
\
Although the above example did not show this, note that SQLBASE will  allow
you to use the decimal or scientific notation to enter numbers.   Magnitude
of the numbers may range from E-99 to  E+99.   Decimal precision  upto  18
digits is maintained automatically.  Dates (as in BIRTH_DATE above) may  be
entered in mm-dd-yy,  mm/dd/yy or  dd-mon-yy  format.  The  'yy'  may  be
expanded to 'yyyy' if the year is not in the current century.
/
PAUSE
/
REMARK
\
The following is an example of the use of an INSERT statement to enter more
than one row into a table with a single command.  Here the :1,:2,:3, :4, :5
and :6 are called 'Bind Variables'.  The SQLBASE C Program Interface  Guide
contains more on Bind  Variables.   Note  however  that  in  the  following
example, the SQL command is followed by a \, then by the individual rows of
data in comma separated format, and then by a / to signify  termination  of
the SQL command.  This convention is specific to the SQLTALK  program.  If
you create your own command files for  use  with  SQLTALK,  make  sure you
follow this convention.
/
INSERT INTO PRESIDENT (PRES_NAME, BIRTH_DATE, YRS_SERV, DEATH_AGE, PARTY,
STATE_BORN) VALUES (:1, :2, :3, :4, :5, :6)
\
$datatypes character,datetime,numeric,numeric,character,character
Adams J,30-Oct-1735,4,90,Federalist,Massachusetts
Madison J,16-Mar-1751,8,85,Demo-Rep,Virginia
Monroe J,28-Apr-1758,8,73,Demo-Rep,Virginia
Adams J Q,11-Jul-1767,4,80,Demo-Rep,Massachusetts
Jackson A,15-Mar-1767,8,78,Democratic,South Carolina
Van Buren M,05-Dec-1782,4,79,Democratic,New York
Harrison W H,09-Feb-1773,0,68,Whig,Virginia
Tyler J,29-Mar-1790,3,71,Whig,Virginia
Polk J K,02-Nov-1795,4,53,Democratic,North Carolina
Taylor Z,24-Nov-1784,1,65,Whig,Virginia
Fillmore M,07-Jan-1800,2,74,Whig,New York
Pierce F,23-Nov-1804,4,64,Democratic,New Hampshire
Buchanan J,23-Apr-1791,4,77,Democratic ,Pennsylvania
Lincoln A,12-Feb-1809,4,56,Republican,Kentucky
Johnson A,29-Dec-1808,3,66,Democratic ,North Carolina
Grant U S,27-Apr-1822,8,63,Republican,Ohio
Hayes R B,04-Oct-1822,4,70,Republican,Ohio
Garfield J A,19-Nov-1831,0,49,Republican,Ohio
Arthur C A,05-Oct-1829,3,56,Republican,Vermont
Cleveland G,18-Mar-1837,8,71,Democratic,New Jersey
Harrison B,20-Aug-1833,4,67,Republican,Ohio
McKinley W,29-Jan-1843,4,58,Republican,Ohio
Roosevelt T,27-Oct-1858,7,60,Republican,New York
Taft W H,15-Sep-1857,4,72,Republican,Ohio
Wilson W,28-Dec-1856,8,67,Democratic,Virginia
Harding W G,02-Nov-1865,2,57,Republican,Ohio
Coolidge C,4-Jul-1872,5,60,Republican,Vermont
Hoover H C,10-Aug-1874,4,90,Republican,Iowa
Roosevelt F D,30-Jan-1882,12,63,Democratic,New York
Truman H S,08-May-1884,7,88,Democratic,Missouri
Eisenhower D D,14-Oct-1890,8,79,Republican,Texas
Kennedy J F,29-May-1917,2,46,Democratic,Massachusetts
Johnson L B,27-Aug-1908,5,65,Democratic,Texas
Nixon R M,09-Jan-1913,5,,Republican,California
Ford G R,14-Jul-1913,2,,Republican,Nebraska
Carter J E,01-Oct-1924,4,,Democratic,Georgia
Reagan R, 6-Feb-1911,4,,Republican,Illinois
/
REMARK
\
The following command (COMMIT) will be explained  later.   It  is  used  to
ensure that all changes made to a database since the  previous COMMIT (or
since the database was opened) are written to disk and will not be lost  in
case of system failure.
/
COMMIT
/
REMARK
\
The SELECT command is used to query the  database.   The  following  is  an
example of a very simple SELECT command that requests all  of  the  columns
and rows in the PRESIDENT table.
/
SELECT *
FROM   PRESIDENT
/
REMARK
\
In the following example, only the  PRES_NAME, BIRTH_DATE,  DEATH_AGE and
STATE_BORN columns are requested.
/
SELECT PRES_NAME, BIRTH_DATE, DEATH_AGE, STATE_BORN
FROM   PRESIDENT
/
REMARK
\
Here, in addition to limiting the request to the president's name  and his
birth state, only those rows are selected where the birth  state  is  Ohio.
Note that since Ohio is a  character  constant,  it  must  be  enclosed  in
quotes.
/
SELECT PRES_NAME, STATE_BORN
FROM PRESIDENT
WHERE STATE_BORN = 'Ohio'
/
REMARK
\
Here, only the presidents not born in Ohio are selected.
/
SELECT PRES_NAME, STATE_BORN
FROM PRESIDENT
WHERE STATE_BORN != 'Ohio'
/
REMARK
\
The query doesnt always need to be based on an exact match.   For  example,
in the following query, we want to see the  names  of  all  the  presidents
whose name contains the suffix 'son'.
/
SELECT PRES_NAME
FROM PRESIDENT
WHERE PRES_NAME LIKE '%son%'
/
REMARK
\
The % sign signifies a wild card character.  In the above query,  it  means
that any name that contains the character string 'son' ( regardless of what
comes before or after it ) will be retrieved.  The following query performs
a more exact match.  Only those names are requested where the string  'son'
is preceded by any four characters.
/
SELECT PRES_NAME
FROM PRESIDENT
WHERE PRES_NAME LIKE '____son%'
/
REMARK
\
The _ character is thus a more restrictive wild card character than the  %.
Both of them may be intermixed in all sorts of combinations.  The following
is an example of such intermixing.
/
SELECT PRES_NAME
FROM PRESIDENT
WHERE PRES_NAME LIKE 'J___son%'
/
REMARK
\
The above were examples where character strings were matched either exactly
or partially.  The following two queries perform numeric comparisons.  They
display the names and ages of all the presidents who died at age less  than
60 and at age greater than or equal to 60.
/
SELECT PRES_NAME, DEATH_AGE
FROM PRESIDENT
WHERE DEATH_AGE < 60
/
SELECT PRES_NAME, DEATH_AGE
FROM PRESIDENT
WHERE DEATH_AGE >= 60
/
REMARK
\
Note that the names of living presidents did not show up in the  above two
queries.  That's because in the database, their death_age is represented by
a null character.  A null implies that the value of a particular  field  is
'unknown'.  To retrieve a field on the basis of a null match, the  IS  NULL
function must be used.
/
SELECT PRES_NAME, DEATH_AGE
FROM PRESIDENT
WHERE DEATH_AGE IS NULL
/
REMARK
\
SQLBASE also allows comparisons  to  be  made  for  date  fields.   In the
following query, the names and birth dates of all the presidents  who  were
born after the year 1900 are requested.
/
SELECT PRES_NAME, BIRTH_DATE
FROM   PRESIDENT
WHERE  BIRTH_DATE > 1-Jan-1900
/
REMARK
\
Note that while date output is always in dd-mon-yyyy format,  SQLBASE  will
accept input in the mm-dd-yy and mm/dd/yy formats  too.   By  specifying  a
column as a DATE, you can make intelligent queries that dates follow  their
own special logic and arithmetic.  The following query results in an  error
because February did not have 29 days in the  year  1900  (centenary  years
have a leap year only once every four hundred years).
/
SELECT PRES_NAME, BIRTH_DATE
FROM   PRESIDENT
WHERE  BIRTH_DATE > 29-Feb-1900
/
REMARK
\
But this query works just fine.
/
SELECT PRES_NAME, BIRTH_DATE
FROM   PRESIDENT
WHERE  BIRTH_DATE > 29-Feb-1904
/
REMARK
\
The BETWEEN predicate is used to check if the data in given  field  belongs
to range of values.  The following query retrieves the names  and  ages  of
those presidents who were born between January 1,  1901  and  December 31,
1925.
/
SELECT PRES_NAME, BIRTH_DATE
FROM PRESIDENT
WHERE BIRTH_DATE BETWEEN 1/1/01 and 12/31/25
/
REMARK
\
The IN (list) predicate is used to retrieve rows based on data in  a  given
set of values. The following query returns the names of all the presidents
who were born in the states of New York, California or Texas.
/
SELECT PRES_NAME, STATE_BORN
FROM PRESIDENT
WHERE STATE_BORN IN  ('New York', 'California', 'Texas')
/
REMARK
\
The above queries (BETWEEN and IN) could  also have  been  constructed  by
ORing together two or more search conditions.  Queries that  contain  many
more complex conditions can be constructed by using a combination  of  ORs,
ANDs and parentheses.  In the following example, only the currently  living
presidents who either (a) belong to the democratic party or (b)  were  born
in the state of Illinois, are selected.
/
SELECT PRES_NAME, PARTY, STATE_BORN
FROM PRESIDENT
WHERE DEATH_AGE IS NULL
AND (PARTY = 'Democratic' OR STATE_BORN = 'Illinois')
/
REMARK
\
Here the entire search condition of the previous query is  negated  by the
NOT operator.
/
SELECT PRES_NAME, PARTY, STATE_BORN
FROM PRESIDENT
WHERE NOT (DEATH_AGE IS NULL
AND (PARTY = 'Democratic' OR STATE_BORN = 'Illinois'))
/
REMARK
\
Note also that even though DEATH_AGE is referenced in the WHERE clause,  it
does not appear in the list  of  columns  being  selected.   SQL  does not
require that the columns participating in a search condition also be in the
select list or that they appear in the same sequence as they  were  entered
into the table.  Infact, the select list may contain arithmetic expressions
that contain any combination of column names and constants.  The  following
query computes the number of terms a president has served (by dividing the
number of years served by 4).
/
SELECT PRES_NAME, YRS_SERV, YRS_SERV/4
FROM PRESIDENT
/
REMARK
\
Arithmetic expressions may also appear in the WHERE clause.
/
SELECT PRES_NAME, YRS_SERV, YRS_SERV/4
FROM PRESIDENT
WHERE YRS_SERV/4 > 2
/
REMARK
\
Arithmetic expressions can also be  used  for  DATE  data  types.   SQLBASE
understands that date arithmetic is not the same as ordinary arithmetic and
will perform the appropriate  calculations  automatically.   The  following
query selects the name and bithdate of each president, and then  calculates
the approximate date of his death (BIRTHDATE + 365*DEATH_AGE).
/
SELECT PRES_NAME, BIRTH_DATE, BIRTH_DATE + 365*DEATH_AGE
FROM PRESIDENT
/
REMARK
\
Date expressions of the above type can be included anywhere that arithmetic
expressions can exist (ie. in a SELECT list or in the WHERE Clause).   Date
expressions can also include certain built-in functions  such  as  @YEARNO,
@QUARTER, @MONTH and @WEEKDAY. These compute the year, quarter,  month  or
weekday of a given date.  The following query computes the  year  of  each
president's birth and death.
/
SELECT PRES_NAME, BIRTH_DATE, @YEARNO(BIRTH_DATE + 365*DEATH_AGE)
FROM PRESIDENT
/
REMARK
\
A SQL query may contain an ORDER BY clause.  This enables rows to be sorted
by a specified order.  The following query selects the names  of  all the
presidents in the PRESIDENT table and displays them in alphabetical order.
/
SELECT PRES_NAME
FROM PRESIDENT
ORDER BY PRES_NAME
/
REMARK
\
Sorting may be in  ascending  or  descending  order.   DESCending  must  be
specified if desired.  ASCending is the default.
/
SELECT PRES_NAME, DEATH_AGE
FROM PRESIDENT
ORDER BY DEATH_AGE DESC
/
REMARK
\
Note from the above example that  if  the  sort  field contains  any  null
values, then those  rows  containing  nulls  are  displayed  prior  to the
remaining rows.  One primary sort  and multiple  secondary  sorts  may  be
specified.  The following query sorts them first by their state  of  birth,
and then in descending order by their date of birth.
/
SELECT PRES_NAME, STATE_BORN, BIRTH_DATE
FROM PRESIDENT
ORDER BY STATE_BORN, BIRTH_DATE DESC
/
REMARK
\
Sorting may be done by a column that does not appear in  the  select  list.
It may also reference a computed column (ie. an expression)  by  using its
relative position number in the select list.  The following query  computes
the year of death of each president and orders the result by that field.
/
SELECT PRES_NAME, @YEARNO(BIRTH_DATE + 365*DEATH_AGE)
FROM PRESIDENT
ORDER BY 2
/
PAUSE
/
REMARK
\
Now let us demonstrate the ability of SQLBASE to  perform  aggregations  on
various columns of a table.  To illustrate this  effectively,  we  need  to
create another table called ELECTION.  This table will contain the names of
all the candidates in each presidential election  since  independence, and
data regarding electoral votes cast in his favour, and a one character code
indicating whether he won or lost  the election.   Note  the  use  of the
SMALLINT and VARCHAR datatypes (these are used to demonstrate compatibility
with DB 2 language syntax).
/
CREATE TABLE ELECTION (ELECTION_YEAR SMALLINT, CANDIDATE VARCHAR(20),
VOTES SMALLINT, WINNER_LOSER_INDIC CHAR(1))
/
INSERT INTO ELECTION (ELECTION_YEAR,CANDIDATE,VOTES,WINNER_LOSER_INDIC)
VALUES (:1, :2, :3, :4)
\
1789,Washington G,69,W
1789,Adams J,34,L
1789,Jay J,9,L
1789,Harrison R H,6,L
1789,Rutledge J,6,L
1789,Hancock J,4,L
1789,Clinton G,3,L
1789,Huntington S,2,L
1789,Milton J,2,L
1789,Armstrong J,1,L
1789,Lincoln B,1,L
1789,Telfair E,1,L
1792,Washington G,132,W
1792,Adams J,77,L
1792,Clinton G,50,L
1792,Jefferson T,4,L
1792,Burr A,1,L
1796,Adams J,71,W
1796,Jefferson T,68,L
1796,Pinckney T,59,L
1796,Burr A,30,L
1796,Adams S,15,L
1796,Ellsworth O,11,L
1796,Clinton G,7,L
1796,Jay J,5,L
1796,Iredell J,3,L
1796,Henry J,2,L
1796,Johnson S,2,L
1796,Washington G,2,L
1796,Pinckney C C,1,L
1800,Jefferson T,73,W
1800,Burr A,73,L
1800,Adams J,65,L
1800,Pinckney C C,64,L
1800,Jay J,1,L
1804,Jefferson T,162,W
1804,Pinckney C C,14,L
1808,Madison J,122,W
1808,Pinckney C C,47,L
1808,Clinton G,6,L
1812,Madison J,128,W
1812,Clinton G,89,L
1816,Monroe J,183,W
1816,King R,34,L
1820,Monroe J,231,W
1820,Adams J Q,1,L
1824,Adams J Q,84,W
1824,Jackson A,99,L
1824,Crawford W H,41,L
1824,Clay H,37,L
1828,Jackson A,178,W
1828,Adams J,83,L
1832,Jackson A,219,W
1832,Clay H,49,L
1832,Floyd J,11,L
1832,Wirt W,7,L
1836,Van Buren M,170,W
1836,Harrison W H,73,L
1836,White H L,26,L
1836,Webster D,14,L
1836,Mangum W P,11,L
1840,Harrison W H,234,W
1840,Van Buren M,60,L
1844,Polk J K,170,W
1844,Clay H,105,L
1848,Taylor Z,163,W
1848,Cass L,127,L
1852,Pierce F,254,W
1852,Scott W,42,L
1856,Buchanan J,174,W
1856,Fremont J C,114,L
1856,Fillmore M,8,L
1860,Lincoln A,180,W
1860,Breckinridge J,72,L
1860,Bell J,39,L
1860,Douglas S,12,L
1864,Lincoln A,212,W
1864,McClellan G B,21,L
1868,Grant U S,214,W
1868,Seymour,80,L
1872,Grant U S,286,W
1872,Hendricks T A,42,L
1872,Brown B G,18,L
1872,Jenkins C J,2,L
1872,Davis D,1,L
1876,Hayes R B,185,W
1876,Tilden S J,184,L
1880,Garfield J A,214,W
1880,Hancock W S,155,L
1884,Cleveland G,219,W
1884,Blaine J G,182,L
1888,Harrison B,233,W
1888,Cleveland G,168,L
1892,Cleveland G,277,W
1892,Harrison B,145,L
1892,Weaver J B,22,L
1896,McKinley W,271,W
1896,Bryan W J,176,L
1900,McKinley W,292,W
1900,Bryan W J,155,L
1904,Roosevelt T,336,W
1904,Parker A B,140,L
1908,Taft W H,321,W
1908,Bryan W J,162,L
1912,Wilson W,435,W
1912,Roosevelt T,88,L
1912,Taft W H,8,L
1916,Wilson W,277,W
1916,Hughes C E,254,L
1920,Harding W G,404,W
1920,Cox W W,127,L
1924,Coolidge C,382,W
1924,Davis J W,136,L
1924,La Follette R M,13,L
1928,Hoover H C,444,W
1928,Smith A E,87,L
1932,Roosevelt F D,472,W
1932,Hoover H C,59,L
1936,Roosevelt F D,523,W
1936,Landon A M,8,L
1940,Roosevelt F D,449,W
1940,Wilkie W L,82,L
1944,Roosevelt F D,432,W
1944,Dewey T E,99,L
1948,Truman H S,303,W
1948,Dewey T E,189,L
1948,Thurmond J S,39,L
1952,Eisenhower D D,442,W
1952,Stevenson A,89,L
1956,Eisenhower D D,457,W
1956,Stevenson A,73,L
1956,Jones W B,1,L
1960,Kennedy J F,303,W
1960,Nixon R M,219,L
1960,Byrd N,15,L
1964,Johnson L B,486,W
1964,Goldwater B,52,L
1968,Nixon R M,301,W
1968,Humphrey H H,191,L
1968,Wallace G C,46,L
1972,Nixon R M,520,W
1972,McGovern G S,17,L
1972,Hospers J,1,L
1976,Carter J E,297,W
1976,Ford G R,240,L
1980,Reagan R,489,W
1980,Carter J E,49,L
1984,Reagan R,528,W
1984,Mondale W F,10,L
/
REMARK
\
A user may compute the MIN, MAX, AVG, SUM  and COUNT  of  the data  in  a
specified column of a table.  These are known as  aggregate  functions and
may be applied to an entire table or to groups of rows within a table. The
following query returns the count of all the  candidates  in  the  ELECTION
table.
/
SELECT COUNT(*)
FROM ELECTION
/
REMARK
\
The * is interpreted to mean "all the rows".  It may only be  used  in the
COUNT  aggregate  function.   Strictly speaking,  the query  should  have
requested count(candidate).  That's because all rows were counted  even  if
some columns in a row were null values.  If an election ever  had  occurred
where no candidates ran, then count(candidate) would  have  ignored  those
rows and returned a correct result.   Since  no  such  election  has  taken
place, count(candidate) is the same as count(*).  The above  query  may  be
further restricted to count only those candidates who won.
/
SELECT COUNT(CANDIDATE)
FROM ELECTION
WHERE WINNER_LOSER_INDIC = 'W'
/
REMARK
\
Aggregate functions ignore null values in a column.  You may  also  specify
that duplicate values of the data in a column be ignored.  For example, the
following query counts the total  number  of  presidential  elections  ever
held.  It counts only the distinct values of an election_year.
/
SELECT COUNT (DISTINCT ELECTION_YEAR)
FROM ELECTION
/
REMARK
\
The * is only valid for the COUNT function but the keyword DISTINCT may  be
used with any aggregate function.  The following query computes the minimum
and maximum electoral votes ever cast for a candidate.
/
SELECT MIN(VOTES), MAX(VOTES)
FROM ELECTION
/
REMARK
\
Sometimes, you may want to compute an aggregate for a  specified  group  of
rows.  For example, suppose you would like to know the count  of  all the
candidates in each  presidential  election  ever  held,  and  the  minimum,
maximum, average and sum of all the electoral votes cast in that  election.
The following query will return the desired result.
/
SELECT ELECTION_YEAR, COUNT(*), MIN(VOTES), MAX(VOTES), AVG(VOTES), SUM(VOTES)
FROM ELECTION
GROUP BY ELECTION_YEAR
/
REMARK
\
You can even compute an aggregate of the above results.  For  example, the
following query returns the minimum and maximum number of  electoral  votes
ever cast in an election.
/
SELECT MIN(SUM(VOTES)), MAX (SUM(VOTES))
FROM ELECTION
GROUP BY ELECTION_YEAR
/
REMARK
\
Sometimes it is desirable to be able to group the results of a query by  an
expression.  This can be done by specifying  in  the  GROUP  BY  clause,  a
number denoting the relative position of the expression in the SELECT list.
This syntax is similar to the  one  used  for  the  ORDER  BY  clause  when
expressions are involved.  The following query computes the count  and the
sum of the years served by presidents, grouped by the year of the month  of
their birth.
/
SELECT @MONTH(BIRTH_DATE), COUNT(YRS_SERV), SUM(YRS_SERV)
FROM PRESIDENT
GROUP BY 1
/
REMARK
\
When a GROUP BY clause is used in conjunction with a WHERE clause,  SQLBASE
first selects the rows that meet the WHERE condition, and then computes the
requested aggregates for the specified group.  If however, you would  like
to restrict the output of the aggregate rows after they have been computed,
you may use a HAVING clause to accomplish this result. The following query
computes the minimum and maximum votes for each election, but restricts the
output to only those rows where the maximum was  at  least  ten  times the
minimum.
/
SELECT ELECTION_YEAR, MIN(VOTES), MAX(VOTES)
FROM ELECTION
GROUP BY ELECTION_YEAR
HAVING MAX(VOTES) / MIN(VOTES) >10
/
REMARK
\
The GROUP BY capability is a very powerful feature of SQLBASE  and  can  be
used to perform a  a  number  of  queries  that  otherwise  may  require  a
procedural language capability.  Any number of columns may participate in a
GROUP BY and any number of conditions may be present in  a  HAVING  clause.
The only restrictions on their use are that both the SELECT  list  and the
HAVING clause must contain only aggregate functions (MIN,  MAX,  AVG,  SUM,
COUNT) or column names (or expressions) that are present in  the  GROUP  BY
clause.  Thus the following queries are illegal.
/
SELECT ELECTION_YEAR, COUNT(*)
FROM ELECTION
/
SELECT ELECTION_YEAR, COUNT(*)
FROM ELECTION
GROUP BY ELECTION_YEAR
HAVING WINNER_LOSER_INDIC = 'W'
/
REMARK
\
In all of the above queries, SQLBASE scans the entire table looking  for  a
match based on the  search  criteria.  Sometimes,  especially  for  large
tables, it is useful to create an index for one or more columns.   Creating
an index has only one effect.  It improves query performance. It  has  no
effect on the results of the query.  Lets run the following  query  without
an index.  It will search the entire ELECTION table looking for those  rows
where the election year is 1984 and the candidate is 'Reagan R'.
/
SELECT ELECTION_YEAR, CANDIDATE
FROM ELECTION
WHERE ELECTION_YEAR = 1984
AND CANDIDATE = 'Reagan R'
/
REMARK
\
The following statement creates an index for the ELECTION_YEAR column.
/
CREATE INDEX X_ELECTION_YEAR ON ELECTION (ELECTION_YEAR)
/
REMARK
\
The previous query now runs faster than before.
/
SELECT ELECTION_YEAR, CANDIDATE
FROM ELECTION
WHERE ELECTION_YEAR = 1984
AND CANDIDATE = 'Reagan R'
/
REMARK
\
Indices can be created on  every  column  in  a  table.   Indices  can  be
specified to be in ASCending or DESCending  order  (ASC  is  the  default).
Indices can also be specified to be UNIQUE (to ensure unique data  in  each
row).  Indices may also be created for a  concatenation  of  two  or  more
columns.  The maximum  size  of  an  index  must  not  however exceed 250
characters.  The following SQL statement creates  a  unique  index  on the
concatenation of ELECTION_YEAR and CANDIDATE.
/
CREATE UNIQUE INDEX X_YEAR_CANDIDATE
ON ELECTION (ELECTION_YEAR, CANDIDATE)
/
REMARK
\
If the concatenation of ELECTION_YEAR and CANDIDATE were found to  be not
unique in the ELECTION table, the above statment  would  have  returned  an
error. Also, from now on, any insertion or updating of  the  data  in the
ELECTION table will only be allowed if it does not violate  the  uniqueness
condition imposed by the above CREATE INDEX command.

An index may be removed with the DROP INDEX command.
/
DROP INDEX X_YEAR_CANDIDATE
/
PAUSE
/
REMARK
\
Now let us demonstrate SQLBASE's capability to JOIN two or more  tables  of
data.  Prior to doing that, the following table called PRES_MARRIAGE  needs
to be created. This table will contain information about the  spouses and
children of each president.
/
CREATE TABLE PRES_MARRIAGE
(PRES_NAME VARCHAR(20), SPOUSE_NAME VARCHAR(20),
PR_AGE INTEGER, SP_AGE INTEGER, NR_CHILDREN INTEGER, MAR_YEAR INTEGER)
/
INSERT INTO PRES_MARRIAGE (PRES_NAME, SPOUSE_NAME, PR_AGE, SP_AGE,
NR_CHILDREN, MAR_YEAR) VALUES (:1, :2, :3, :4, :5, :6)
\
Washington G,Custis M D,26,27,0,1759
Adams J,Smith A,28,19,5,1764
Jefferson T,Skelton M W,28,23,6,1772
Madison J,Todd D D P,43,26,0,1794
Monroe J,Kortright E,27,17,3,1786
Adams J Q,Johnson L C,30,22,4,1797
Jackson A,Robards R D,26,26,0,1794
Van Buren M,Hoes H,24,23,4,1807
Harrison W H,Symmes A T,22,20,10,1795
Tyler J,Christian L,23,22,8,1813
Tyler J,Gardiner J,54,24,7,1844
Polk J K,Childress S,28,20,0,1824
Taylor Z,Smith M M,25,21,6,1810
Fillmore M,Powers A,26,27,2,1826
Fillmore M,McIntosh C C,58,44,0,1858
Pierce F,Appleton J M,29,28,3,1834
Buchanan J,,,,0,,
Lincoln A,Todd M,33,23,4,1842
Johnson A,McCardle E,18,16,5,1827
Grant U S,Dent J B,26,22,4,1848
Hayes R B,Webb L W,30,21,8,1852
Garfield J A,Rudolph L,26,26,7,1858
Arthur C A,Herndon E L,29,22,3,1859
Cleveland G,Folson F,49,21,5,1886
Harrison B,Scott C L,20,21,2,1853
Harrison B,Dimmick M S L,62,37,1,1896
McKinley W,Saxton I,27,23,2,1871
Roosevelt T,Lee A H,22,19,1,1880
Roosevelt T,Carow E K,28,25,5,1886
Taft W H,Herron H,28,25,3,1886
Wilson W,Axson E L,28,25,3,1885
Wilson W,Galt E B,58,43,0,1915
Harding W G,De Wolfe F K,25,30,0,1891
Coolidge C,Goodhue G A,33,26,2,1905
Hoover H C,Henry L,24,23,2,1899
Roosevelt F D,Roosevelt A E,23,20,6,1905
Truman H S,Wallace E V,35,34,1,1919
Eisenhower D D,Doud G,25,19,2,1916
Kennedy J F,Bouvier J L,36,24,3,1953
Johnson L B,Taylor C A,26,21,2,1934
Nixon R M,Ryan T C,27,28,2,1940
Ford G R,Warren E B,35,30,4,1948
Carter J E,Smith R,21,18,4,1946
Reagan R,Wyman J,28,25,2,1940
Reagan R,Davis N,41,28,2,1952
/
REMARK
\
Let us create indices on the PRES_NAME column of the  PRESIDENT  and  PRES_
MARRIAGE tables  and  the  MAR_YEAR  column  of  the  PRES_MARRIAGE  table.
Indices are not essential for  doing  a  join.  But  as  in  single  table
queries, if an index is present, performance of a  join  query (especially
involving a large number of rows) is improved.
/
CREATE INDEX X_PRES_NAME ON PRESIDENT(PRES_NAME)
/
CREATE INDEX Y_PRES_NAME ON PRES_MARRIAGE(PRES_NAME)
/
CREATE INDEX X_MAR_YEAR ON PRES_MARRIAGE(MAR_YEAR)
/
REMARK
\
Note that the data contained  in  the  PRESIDENT  table  and  PRES_MARRIAGE
tables is different from each other.  The only common column in them is the
PRES_NAME.  If we need to find out the date of birth of each president and
the names(s) of his spouse(s), we will need to look up the PRESIDENT  table
for the birth date and the PRES_MARRIAGE table for the spouse  name(s) and
relate these two pieces of information via their common element -- ie. the
president's name.  The following query will accomplish this objective. The
operation is known as a JOIN.
/
SELECT PRESIDENT.PRES_NAME, BIRTH_DATE, SPOUSE_NAME
FROM   PRESIDENT, PRES_MARRIAGE
WHERE  PRESIDENT.PRES_NAME = PRES_MARRIAGE.PRES_NAME
/
REMARK
\
In the above example, the PRES_NAME column had to be prefixed with a  table
name to ensure that the query was unambiguous (a column that is unique to a
table does not need to be prefixed by its table name).  The  WHERE  clause
specified that only those rows should be returned where  the  PRES_NAME  in
the PRESIDENT table is the same  as  the  PRES_NAME  in  the  PRES_MARRIAGE
table. If this condition (known as the JOIN condition) were not  supplied,
the results would consist of all possible combinations of the rows  of the
two tables (ie. a total of 39 times 44 rows). This  combination  is  also
known as the cartesian product of two tables.
/
PAUSE
/
REMARK
\
A JOIN condition does not have to be based on an equality.  Any of the SQL
comparison operators (=,<,>,<=,>=) may be used.  The joining columns do not
have to have the same name either.  More than two tables may participate in
a join (the maximum number is 250), and as in a  single  table query, the
WHERE clause can contain any number of conditions.   The  following  query
finds (from the PRESIDENT, ELECTION and PRES_MARRIAGE tables) the  name  of
each president (and his spouse) who was married after he took office.
/
SELECT PRESIDENT.PRES_NAME, SPOUSE_NAME, ELECTION_YEAR, MAR_YEAR
FROM   PRESIDENT, ELECTION, PRES_MARRIAGE
WHERE  PRESIDENT.PRES_NAME = ELECTION.CANDIDATE
AND    PRESIDENT.PRES_NAME = PRES_MARRIAGE.PRES_NAME
AND    PRES_MARRIAGE.MAR_YEAR > ELECTION.ELECTION_YEAR
AND    WINNER_LOSER_INDIC = 'W'
/
REMARK
\
Joins may also be performed in conjunction with groupings  and aggregates.
The results may be displayed in any order.  The  following  query  displays
the name of each president, the number of times he  was  married,  and the
total number of children he had.
/
SELECT PRESIDENT.PRES_NAME, COUNT(SPOUSE_NAME), SUM(NR_CHILDREN)
FROM   PRESIDENT, PRES_MARRIAGE
WHERE  PRESIDENT.PRES_NAME = PRES_MARRIAGE.PRES_NAME
GROUP BY PRESIDENT.PRES_NAME
ORDER BY PRESIDENT.PRES_NAME
/
REMARK
\
A table may be joined to itself.  This is useful for queries involving the
"nesting" of conditions within the same table. In the following query,  we
request the names of the presidents (and their birth states) who were  born
in the same state as General Grant.  To accomplish this type of  query,  we
give the PRESIDENT table two temporary names (called labels)-- A & B.  Then
tables A & B are joined to each other based on the criteria contained below
in the query. type of join is known as a self-join.
/
SELECT B.PRES_NAME, B.STATE_BORN
FROM PRESIDENT A, PRESIDENT B
WHERE A.STATE_BORN = B.STATE_BORN
AND A.PRES_NAME = 'Grant U S'
/
REMARK
\
The above result could also have been accomplished  by executing  what  is
known as a Nested Query.  In this type of statement,  the  results  of one
query (called a Subselect) are used to drive another  query.   The  example
below illustrates a nested query to find all the presidents who  were  born
in the same state as General Grant.
/
SELECT PRES_NAME, STATE_BORN
FROM PRESIDENT
WHERE STATE_BORN =
   (SELECT STATE_BORN
    FROM PRESIDENT
    WHERE PRES_NAME = 'Grant U S')
/
REMARK
\
Nested queries can be used with the IN predicate also. That is,  they may
be used to compare a value to a collection  of values returned  from the
inner query.  In the following example, the names of those  presidents are
requested who were born in any of the states in which either General  Grant
or President Lincoln were born.
/
SELECT PRES_NAME, STATE_BORN
FROM PRESIDENT
WHERE STATE_BORN IN
   (SELECT STATE_BORN
    FROM PRESIDENT
    WHERE PRES_NAME IN ('Grant U S', 'Lincoln A'))
/
REMARK
\
Nested Queries can  be  used  to  construct  even  more  complex  database
requests.  For example, the following query requests  the  names  of  those
candidates in each election year who  obtained greater  than  the  average
number of electoral college votes cast per candidate.  This type  of  query
is called a correlated subquery because the inner query  is  executed  once
for each row of the outer query (ie. for each candidate  of  each  election
year). This is a fairly complex query and may take as much as a minute  on
a PC/XT.  Note the use of the correlation-name X and its similarity to the
temporary table name given to tables in a self-join.
/
SELECT ELECTION_YEAR, CANDIDATE, VOTES
FROM ELECTION X
WHERE VOTES >
  (SELECT AVG(VOTES)
   FROM ELECTION
   WHERE ELECTION_YEAR = X.ELECTION_YEAR)
/
REMARK
\
Nested queries can also be used to select rows from one  table and  insert
them into another.  The  following  statement  creates a  temporary  table
called WINNER that has three columns: the president's NAME, the YEAR he was
elected in and the VOTES he  attained  from  the  electoral  college. The
INSERT statement following the table creation causes the names of  all the
candidates from the election table who have a winner_loser_indic of W to be
inserted into the WINNER table.
/
CREATE TABLE WINNER (NAME CHAR(20), YEAR_ELECTED SMALLINT, VOTES SMALLINT)
/
INSERT INTO WINNER
SELECT CANDIDATE, ELECTION_YEAR, VOTES
FROM ELECTION
WHERE WINNER_LOSER_INDIC = 'W'
/
REMARK
\
You may have noticed that the above table doesnt  necessarily  contain all
U.S. presidents.  For example, Gerald Ford was not elected  to the  office
but attained it as a result of the rules of  presidential  succession.  In
order to find the presidents who were not  elected  to office,  we  use  a
correlated subquery but this time with an EXISTS predicate.  The latter may
only be used in a subquery.  It does not return  any  values. Instead  it
returns to the outer query a binary YES/NO response for the inner query.
/
SELECT PRES_NAME
FROM PRESIDENT X
WHERE NOT EXISTS
(SELECT *
 FROM WINNER
 WHERE NAME = X.PRES_NAME)
/
REMARK
\
Sometimes it is useful to query two tables and merge  the  rows  that are
returned into one set of results.  This merging is called  a  UNION  and  a
special operator exists in SQL to accomplish it.  In the  following  query,
we request to see the names of all presidents who were elected to office by
popular vote and who were born in the state of Nebraska or Illinois.   This
involves a UNION of two queries, one on the WINNER table, and the other  on
the PRESIDENT table.
/
SELECT NAME FROM WINNER
UNION
SELECT PRES_NAME FROM PRESIDENT
WHERE STATE_BORN IN ('Nebraska','Illinois')
/
REMARK
\
The above query will display all of the winners and the name of Gerald Ford
who was born  in  Nebraska.   Notice  also  that  all  duplicate  rows are
automatically suppressed.  UNION also requires that both queries  have the
same number of columns in the select list and that  they  be  identical  in
data type.
/
REMARK
\
You may have noticed that the results of all  SQL  queries,  regardless  of
complexity, are always presented as a table  of  data.  Sometimes,  it  is
desirable to define the results of a query to be a VIEW.  By giving a  name
to the result table of a query, this subsequent VIEW can then be treated as
if it were a table.  You can  retrieve data  from  it without  having  to
specify all the search criteria that went into the  original  query. The
following statement creates a view called WINNER_VIEW.
/
CREATE VIEW WINNER_VIEW (NAME, YEAR_ELECTED, VOTES) AS
SELECT CANDIDATE, ELECTION_YEAR, VOTES
FROM ELECTION
WHERE WINNER_LOSER_INDIC = 'W'
/
REMARK
\
Note that the above view contains the same results  as the  WINNER  table.
However, the only difference between them  is  that  while  the  latter  is
defined as  physical  table  in  the  database,  and  contains real  data,
WINNER_VIEW does not actually exist  in  the  database except as  a  name
assigned to a query.  When the view is invoked in a subsequent query, the
underlying query that comprises WINNER_VIEW is executed, and the  resultant
table is used to retrieve the desired data.  In the following  example,  we
select the name of all the presidents who were not  elected. Instead  of
using the WINNER table, we use the the WINNER_VIEW view.
/
SELECT PRES_NAME
FROM PRESIDENT X
WHERE NOT EXISTS
(SELECT *
 FROM WINNER_VIEW
 WHERE NAME = X.PRES_NAME)
/
REMARK
\
VIEWS can be used to perform queries that simply cannot be  done  with one
SQL statement alone.  For example, suppose we wish to know the name of each
winner in a presidential election, the number of  votes  he  got,  and the
percentage of total votes obtained by him in the election.   The  following
two SQL statements will provide the results desired.
/
CREATE VIEW VOTES_TOTAL (YEAR_ELECTED, TOTAL) AS
SELECT ELECTION_YEAR, SUM(VOTES)
FROM ELECTION
GROUP BY ELECTION_YEAR
/
REMARK
\
The above view contains the total votes cast in each election. We now join
it to the ELECTION table.
/
SELECT CANDIDATE, VOTES, TOTAL
FROM ELECTION, VOTES_TOTAL
WHERE ELECTION.ELECTION_YEAR = VOTES_TOTAL.YEAR_ELECTED
/
REMARK
\
VIEWS can also be updated, deleted from or inserted into, as long  as  they
contain the name of only one table in the FROM clause, and do  not  contain
any  derived  columns  (based  on  group   by functions   or  arithmetic
expressions).  The following view simply contains all the rows and  columns
in the PRESIDENT table and is updatable.
/
CREATE VIEW PRES_VIEW AS
SELECT * FROM PRESIDENT
/
REMARK
\
PRES_VIEW could also have been specified as updatable with a CHECK  option.
This means that if an UPDATE, DELETE or INSERT operation  is  performed  on
the view, the system will check to see that  the  view definition  is not
violated as a result of this operation.  The USE of  the  CHECK  option  on
Views has significant ramifications for  implementing  what  are  known  in
database  terminology  as  Integrity  Constraints.  These   can   be   best
illustrated by means of an example.

Let us first create a table called STATES and insert into it, the names  of
all the 50 states in the union.  We will then drop PRES_VIEW  and  redefine
it to include an integrity constraint specifying that no presidents in that
view can  be  from  non-existent  states,  ie. the  STATE_BORN  column  in
PRES_VIEW must exist in the STATES table.  PRES_VIEW will be defined with a
CHECK option.
/
CREATE TABLE STATES (STATE CHAR(20))
/
INSERT INTO STATES (STATE)
VALUES (:1)
\
Alabama
Alaska
Arizona
Arkansas
California
Colorado
Connecticut
Delaware
D.C.
Florida
Georgia
Hawaii
Idaho
Illinois
Indiana
Iowa
Kansa
Kentucky
Louisiana
Maryland
Massachusetts
Michigan
Minnesota
Mississippi
Missouri
Montana
Nebraska
Nevada
New Hampshire
New Jersey
New Mexico
New York
North Carolina
North Dakota
Ohio
Oklahoma
Oregon
Pennsylvania
Rhode Island
South Carolina
South Dakota
Tennessee
Texas
Utah
Vermont
Virginia
Washington
West Virginia
Wisconsin
Wyoming
/
DROP VIEW PRES_VIEW
/
CREATE VIEW PRES_VIEW AS
SELECT * FROM PRESIDENT
WHERE STATE_BORN IN
(SELECT STATE FROM STATES)
WITH CHECK OPTION
/
REMARK
\
In the following  SQL  statement,  since  PRES_VIEW  is  identical  to the
PRESIDENT table in terms of its contents, inserting a record into PRES_VIEW
is the same as inserting it into PRES_VIEW.  Since PRES_VIEW  is  identical
to the PRESIDENT table in terms of its contents, inserting  a  record  into
PRES_VIEW is the same as inserting it into PRESIDENT about with  one  added
benefit.  The integrity constraint that ensures the validity of  STATE_BORN
is enforced during any data manipulation operation.  This type of integrity
checking is known as referential integrity.
/
INSERT INTO PRES_VIEW (PRES_NAME, STATE_BORN)
VALUES ('Ferdinand Marcos','Philippines')
/
REMARK
\
As you can see, VIEWS can be a powerful  means  to  perform  queries  that
otherwise might require significant amounts of programming.   The  benefit
they have over creating temporary tables is that they are always  uptodate.
They do not  require  storage  of  redundant  data  and  are  automatically
maintained current by SQLBASE. BY using the CHECK option on updatable views,
they can be used  to  enforce  a  wide variety  of  integrity constraints
including referential integrity.  Later in this tutorial, you will also see
how views can be used to implement field and row level security  within  a
table by assigning access privileges on views.
/
PAUSE
/
REMARK
\
Now let us demonstrate the use of the LONG VARCHAR datatype.  In  order  to
do so, we must first create a table called BIOGRAPHY.  The first column  in
the table is called PRES_NAME and the second is the TEXT column.  TEXT is a
VARCHAR LONG column.
/
CREATE TABLE BIOGRAPHY
(PRES_NAME VARCHAR(20),
TEXT LONG VARCHAR)
/
REMARK
\
To insert data into a VARCHAR LONG column, you must  always  use  the  bind
variable approach.  The following command inserts the name 'Reagan R'  into
the PRES_NAME column, and then a short biography  into  the  TEXT  column.
Note that the special keyword $long that must precede entry  of  a  VARCHAR
LONG field.  If you examine the DEMO command file, you will notice  that  a
// terminates entry of each VARCHAR  LONG  field.   These  conventions for
entering long data are specific to SQLTALK only.  For more  information  on
the calls required to read and write VARCHAR LONG data from  a C  program,
consult the SQLBASE C Program Interface Guide.
/
INSERT INTO BIOGRAPHY (PRES_NAME, TEXT)
VALUES (:1,:2)
\
Reagan R, $long
Ronald Wilson Reagan, 40th president, Republican, was born Feb. 6, 1911, in
Tapico, Ill., the son of John Edward  Reagan  and  Nellie  Wilson.   Reagan
graduated from Eureka (Ill) Cllege in 1932.  Following his  graduation,  he
worked for 5 years as a sports announce in Des Moines, Iowa.

Reagan began a successful career as a film actor in 1937,  and starred  in
many movies, and later television, until the 1960s.  He was  a captain  in
the Army Air Force during World War II.  He  served  as  President  of the
Screen Actor's guild from 1947 to 1952, and in 1959.

Once a liberal democrat, Reagan became active in Republican politics during
the 1964  presidential camapaign  of  Barry  Goldwater.   He  was  elected
governor of California in 1966,  and  reelected  in  1970.   Following his
retirement as  governor,  Reagan  became  the  leading spokesman  for the
conservative wing of the Republican Party, and made a strong  bid  for the
party's 1976 presidential nomination.

In 1980 he gained the Republican nomination and  won  a  landslide  victory
over Jimmy Carter.  As president,  he successfully  forged  a  bipartisan
coalition in Congress which led to enactment of an economic  program  which
included the largest budget and tax cuts in  U.S.  history,  and  a  Social
Security reform bill designed  to  ensure  the longterm  solvency  of the
system.

He was reelected  by  an  overwhelming margin in  the  1984  presidential
elections.  His opponent, Walter Mondale managed to  carry  only  his  home
state of Minnesota while Reagan won all the other 49 states.
//
/
REMARK
\
The following query displays the contents of the BIOGRAPHY table.
/
SELECT PRES_NAME, TEXT
FROM  BIOGRAPHY
/
REMARK
\
SQLTALK also allows you to enter VARCHAR LONG  data  directly  from  a DOS
file.  The command below inserts one row into  the  BIOGRAPHY  table. The
first column contains an entry for 'Carter  J' and  into  the second,  we
insert a  file called bio.txt  which  contains  a  brief  biography  of
President Jimmy Carter.
/
INSERT INTO BIOGRAPHY(PRES_NAME, TEXT)
VALUES (:1,:2)
\
Carter J E, $long bio.txt
/
REMARK
\
The following query displays only the biography for Jimmy Carter.
/
SELECT PRES_NAME, TEXT
FROM BIOGRAPHY
WHERE PRES_NAME = 'Carter J E'
/
PAUSE
/
REMARK
\
Upto now, we have demonstrated the creation of tables and indices (CREATE),
data entry (INSERT) and data query (SELECT).  Now let us look  at  examples
of data modification (UPDATE and DELETE).

The following command updates the BIOGRAPHY  table  and  changes  the  name
'Carter J E' to 'Jimmy Carter'
/
UPDATE BIOGRAPHY
SET PRES_NAME = 'Jimmy Carter'
WHERE PRES_NAME = 'Carter J E'
/
REMARK
\
Note the use of the WHERE clause in an UPDATE statment.  Any  WHERE  clause
that can be constructed for a single table SELECT statement can be used  in
an UPDATE statement.  If no WHERE clause is specified, then all the rows of
the table that contain data in the specified column are updated.  An UPDATE
can only be applied to a  single  table.   The SET  clause  in  an  UPDATE
statement may contain constants, column names  or  arithmetic  expressions.
More than one column may be updated in a single SET clause.
/
SELECT PRES_NAME
FROM BIOGRAPHY
WHERE PRES_NAME LIKE '%Carter%'
/
REMARK
\
To delete an entire row from a table,  use  the  DELETE  command.   In the
following example, the entry for 'Carter Jimmy' in the BIOGRAPHY  table  is
deleted.
/
DELETE
FROM BIOGRAPHY
WHERE PRES_NAME = 'Jimmy Carter'
/
REMARK
\
If no WHERE clause is specified in a DELETE or UPDATE statment, all rows in
table are deleted or updated.  The following  statement  deletes  all  rows
from the BIOGRAPHY table.
/
DELETE
FROM BIOGRAPHY
/
SELECT *
FROM BIOGRAPHY
/
REMARK
\
To completely remove a table from the database, you must use the DROP TABLE
command.  Even though we have deleted all the rows in the BIOGRAPHY  table,
the database still contains an entry for this table.  The following command
removes the entire table from the database.
/
DROP TABLE BIOGRAPHY
/
REMARK
\
SQLBASE keeps track of information regarding all the tables in the database
and their associated columns and indices.  This information comprises  what
is known as the data dictionary.
/
PAUSE
/
REMARK
\
The SQLBASE data dictionary includes the  following  tables:  (a)  a  table
called SYSTABLES containing a list of  all  the  tables  and  their  column
count, (b) a table called SYSCOLUMNS containing a list of all  the  columns
in the database and their associated tables, column numbers and  datatypes,
(c) a table called SYSINDEXES containing a  list  of  all  indexes  in the
database and their  associated tables and  (d)  a  table  called  SYSKEYS
containing a list of all indexes and their associated  columns and  (e)  a
table called SYSVIEWS containing the text of all the views in the database.
These tables may be queried using SQL commands just like any  user  defined
table in the database. Other  dictionary  tables  exist  in  SQLBASE  that
contain information about users and their security  privileges.   They are
described later in this tutorial.
/
SELECT NAME, COLCOUNT, REMARKS
FROM SYSTABLES
/
SELECT NAME, TBNAME, COLNO, COLTYPE, LENGTH, NULLS, REMARKS
FROM SYSCOLUMNS
/
SELECT NAME, TBNAME, UNIQUERULE, COLCOUNT
FROM SYSINDEXES
/
SELECT IXNAME, COLNAME, COLNO, COLSEQ, ORDERING
FROM SYSKEYS
/
REMARK
\
The views that exist in a database are listed in the SYSTABLES
table and designated as such. They are also contained in the SYSVIEWS table.
/
SELECT * FROM SYSTABLES
WHERE TYPE = 'V'
/
SELECT * FROM SYSVIEWS
/
REMARK
\
Note that the REMARKS column of the SYSTABLES  and  SYSCOLUMNS tables was
truncated because of not enough screen width.  This column may be  updated
via the COMMENT ON command.
/
COMMENT ON TABLE PRESIDENT
IS 'Names of U.S. Presidents'
/
COMMENT ON COLUMN PRESIDENT.DEATH_AGE
IS 'Age at which president died'
/
SELECT NAME, REMARKS
FROM SYSTABLES
WHERE NAME = 'PRESIDENT'
/
SELECT TBNAME, NAME, REMARKS
FROM SYSCOLUMNS
WHERE TBNAME = 'PRESIDENT'
AND NAME = 'DEATH_AGE'
/
PAUSE
/
REMARK
\
Data in the dictionary tables (other than the REMARKS columns) is  updated
by SQLBASE whenever appropriate.  The following sequence  of  SQL  commands
demonstrates this automatic dictionary maintenance  facility  of  SQLBASE.
Let us drop the index X_PRES_NAME on PRES_NAME in the PRESIDENT table.
/
DROP INDEX X_PRES_NAME
/
REMARK
\
Now, if we query the SYSINDEXES table, we will see that  no  indices  exist
for the PRESIDENT table.
/
SELECT *
FROM SYSINDEXES
WHERE TBNAME = 'PRESIDENT'
/
PAUSE
/
REMARK
\
You can alter the structure of a table by adding or dropping columns. The
following command adds a column called BIO_DATA  to  the  PRESIDENT  table.
Perhaps this column could be used to contain the text matter we  previously
stored in the BIOGRAPHY table.
/
ALTER TABLE PRESIDENT
ADD BIO_DATA LONG VARCHAR
/
REMARK
\
Note that the data dictionary will automatically  reflect  this  additional
column.
/
SELECT TBNAME, NAME
FROM SYSCOLUMNS
WHERE TBNAME = 'PRESIDENT'
/
REMARK
\
More than one column can be added with a single ALTER TABLE statement.
/
ALTER TABLE PRESIDENT
ADD NEWCOL1 VARCHAR(30),
    NEWCOL2 INTEGER
/
REMARK
\
Columns can be added to any of the dictionary tables too.   Thus  if  you'd
like to store information in the data dictionary that is not maintained  by
SQLBASE, you may do so by adding the appropriate columns  to  the  relevant
dictionary table.  The following command adds the 'RECORD_COUNT' column  to
the SYSTABLES table.  It could be used to store the total record  count  of
the specified table.
/
ALTER TABLE SYSTABLES
ADD RECORD_COUNT INTEGER
/
REMARK
\
Columns can be dropped as easily.  Note however, that when  this  is  done,
the column numbering of the remaining columns is not changed.  This ensures
that application programs that utilize the column numbering feature of the
data dictionary are not suddenly rendered incompatible.  The maximum column
numbers that can be utilized for a table is 250.
/
ALTER TABLE PRESIDENT
DROP NEWCOL1, NEWCOL2
/
REMARK
\
You can drop user defined columns from a dictionary table but  not  any  of
the system defined columns.
/
ALTER TABLE SYSTABLES
DROP RECORD_COUNT
/
ALTER TABLE SYSTABLES
DROP NAME
/
REMARK
\
The ALTER TABLE command can also be used to rename a  table,  to  rename  a
column or to increase the length of a character column.  The following are
examples of these capabilities.
/
ALTER TABLE PRES_MARRIAGE
RENAME TABLE MARRIAGE
/
REMARK
\
The above query renamed the PRES_MARRIAGE table to the MARRIAGE table. The
following statement renames the NR_CHILDREN column to NUM_CHILDREN
/
ALTER TABLE MARRIAGE
RENAME NR_CHILDREN NUM_CHILDREN
/
REMARK
\
The following statement increases the length of the PRES_NAME column to 40.
Note that the length may be increased but not decreased.
/
ALTER TABLE MARRIAGE
MODIFY PRES_NAME CHAR(40)
/
REMARK
\
Now let's take a look at the SYSCOLUMNS table  to  verify  that  the  above
changes have actually taken place.
/
SELECT *
FROM SYSCOLUMNS
WHERE TBNAME = 'MARRIAGE'
/
REMARK
\
Let's go back to the old names.
/
ALTER TABLE MARRIAGE
RENAME TABLE PRES_MARRIAGE
/
ALTER TABLE PRES_MARRIAGE
RENAME NUM_CHILDREN NR_CHILDREN
/
PAUSE
/
REMARK
\
Now let us demonstrate a very key feature of SQLBASE---its  capability for
transaction level rollback and recovery.  A transaction is any logical unit
of work.  In SQLBASE terms, it may consist of one or more SQL commands.   A
transaction starts either when you connect to a database or when you  issue
the SQL command called COMMIT. Let us issue a COMMIT.
/
COMMIT
/
REMARK
\
At this point, all changes to the database made since we  issued  the  last
COMMIT have been committed to disk.  In the event the computer goes down or
you shut it off, SQLBASE guarantees you that the data you have entered  or
modified since the start of the  session  will not  be  affected  by  this
shutdown.  Now, let us perform the following transaction.
/
UPDATE PRES_MARRIAGE
  SET SPOUSE_NAME = 'Davis N'
  WHERE PRES_NAME = 'Carter J E'
/
UPDATE PRES_MARRIAGE
  SET SPOUSE_NAME = 'Smith R'
  WHERE PRES_NAME = 'Reagan R'
/
REMARK
\
If you query the database, you will see that it reflects the  changes  made
above.
/
SELECT PRES_NAME, SPOUSE_NAME
FROM   PRES_MARRIAGE
WHERE  PRES_NAME = 'Reagan R'
OR     PRES_NAME = 'Carter J E'
/
REMARK
\
Note that the names of the spouses of Presidents  Reagan  and  Carter  were
intersposed.  However, the database at this  point  is  still capable  of
"rolling back" to the state it was in at the time of the  previous  COMMIT.
This can be demonstrated by issuing the command ROLLBACK.
/
ROLLBACK
/
REMARK
\
Upon querying the PRES_MARRIAGE table, we will see that  the  spouse  names
are listed correctly.
/
SELECT PRES_NAME, SPOUSE_NAME
FROM PRES_MARRIAGE
WHERE PRES_NAME = 'Carter J E'
OR    PRES_NAME = 'Reagan R'
/
REMARK
\
In  the  previous  sequence  of  statements,  by  issuing  a  ROLLBACK  we
effectively undid the SQL commands that updated the SPOUSE_NAME column and
restored the database to a state as if those  statements  had never  been
executed.  This occurs automatically in the event of  unanticipated  system
failure.  A ^C exit from SQLTALK will  also  cause  a  rollback  to  occur.
However, a normal exit (ie. not a ^C) will cause a COMMIT to be  issued  at
the end of the SQLTALK session.
/
REMARK
\
The default mode of SQLTALK is that SQL statments are not  committed  until
you explicitly issue  the  COMMIT  command.   However,  you  may  set the
AUTOCOMMIT option on in SQLTALK which will  automatically  cause  each SQL
statement to be committed after it is executed.
/
PAUSE
/
REMARK
\
Another important advanced feature of SQLBASE is  that it  allows  you  to
store and retrieve precompiled queries.  In the  following  example,  STORE
and EXECUTE are commands specific to SQLTALK.  The equivalent C programming
operations are described in the SQLBASE C Program Interface Guide.
/
STORE COMMAND1
UPDATE PRESIDENT
SET DEATH_AGE = 100
WHERE PRES_NAME = :1
/
PAUSE
/
REMARK
\
The above command was stored in the database  as  COMMAND1.It  may  now  be
executed for a specific value of PRES_NAME.
/
EXECUTE COMMAND1
\
Washington G
/
PAUSE
/
REMARK
\
Querying the database shows that it has been updated accordingly.
/
SELECT PRES_NAME, DEATH_AGE
FROM PRESIDENT
WHERE PRES_NAME = 'Washington G'
/
REMARK
\
The above feature means that SQL commands that are used often in a  program
may be compiled and stored in a denser representation than  in their  text
form, and executed with a different bind  variable  each  time.   Executing
stored commands is faster than compiling  and  executing  them each  time,
because parsing is done only once, ie. at precompilation time.
/
PAUSE
/
REMARK
\
We will now illustrate the use of multiple cursors within a database. First,
We'll GRANT CONNECT privleges to cursor #2 called TWO.
/
GRANT CONNECT TO TWO IDENTIFIED BY TWO
/
GRANT DBA TO TWO
/
CONNECT TO DEMO AS TWO/TWO
/
PAUSE
/
REMARK
\
The CONNECT command above (which is a SQLTALK command) created  Cursor  #2
for the database (Cursor # 1 was created  automatically  at  the  time you
signed on  to  SQLTALK).   The primary  use  of  multiple  cursors  is  in
interactive applications where the user needs  to  browse  within  multiple
tables.  An application built using multiple cursors can have a significant
performance  advantage over  single  cursor  applications.   For  more  on
cursors, consult the SQLBASE C Program Interface Guide.
/
PAUSE
/
USE DEMO SYSADM
/
REMARK
\
The USE command indicates to SQLBASE that the next SQL statement is to use
Cursor # n.  The default is Cursor # 1.
/
PAUSE
/
REMARK
\
In the following SQL statement associated with Cursor # 1, we will also use
the ROWID feature.  ROWID is  SQLBASE  keyword that  provides a  relative
address for a row in a table.  If the ROWID of a given row is  known,  then
that row can be modified (or queried) directly by using the  ROWID  in the
search condition, thus improving performance.
/
SELECT ROWID, PRES_NAME, DEATH_AGE
FROM PRESIDENT
WHERE DEATH_AGE  > 75
/
REMARK
\
Typically, in a multi-cursor application, the  results of  one  query are
browsed in some fashion to determine  if  they need  to  be  modified,  or
perhaps used as input into another query.  Here, we examine the results  of
the  previous  query,  and  determine  that  George  Washington's  age  is
incorrect.  So instead of creating an update statement that  searches the
database for 'Washington G', we directly perform the update  based  on the
row_id for his record. We use Cursor # 2 for this update.
/
PAUSE
/
USE DEMO TWO
/
UPDATE PRESIDENT
SET DEATH_AGE = 67
WHERE ROWID = :1
\
ALAAAAAAABAAAAAAICAFAAAAAAAAAAAAGFAA
/
REMARK
\
Now, the user may loop back into using Cursor #  1,  browse  through the
results of the previous query again, then use Cursor # 2  to  perform yet
another update, go back to Cursor # 1, and so on and so forth.
/
PAUSE
/
USE DEMO SYSADM
/
REMARK
\
Querying the database will  show  that George Washinton's  age  has  been
corrected.
/
SELECT PRES_NAME, DEATH_AGE
FROM PRESIDENT
WHERE PRES_NAME = 'Washington G'
/
REMARK
\
DISCONNECT removes the specified cursor  from  the  named  database.   Both
CONNECT and DISCONNECT are SQLTALK commands.  They should not  be  confused
with the CONNECT and DISCONNECT  operations  described in  the  C  Program
Interface Guide.

This entire demo is being  conducted  on  the  'demo'  database.   You can
actually initialize more than one database (for example, one  could  be  on
the hard disk and another could be on the floppy) and then connect  to all
those databases simultaneously from SQLBASE.   In  a  network  environment,
this feature is extremely important because it allows multiple application
nodes to connect to multiple database nodes  without  being  aware  of the
physical  location  of any  of  the  databases.   For this  single   user
demonstration, we have stayed with one database.
/
DISCONNECT DEMO TWO
/
PAUSE
/
REMARK
\
The following sequence of  commands  illustrates  the use  of  the  FETCH
BACKWARDS feature of the SQLBASE  C  Program  In  Interface.   Normally,  a
program will execute a query, and then fetch each row returned by the query
by issuing a FETCH operation.  The FETCH  BACKWARDS  operation allows the
programmer to fetch the row previous to the current row.   The combination
of FETCH and FETCH  BACKWARDS  commands,  when used  in  conjunction  with
multiple cursors and row id, can allow record at a time  operations  to  be
performed on the database efficiently and easily.

First, let's issue a SQL query.
/
PAUSE
/
SELECT PRES_NAME, BIRTH_DATE
FROM PRESIDENT
WHERE BIRTH_DATE > 1-Jan-1850
/
REMARK
\
Now, let us ask for the same query to be repeated followed by the  last  15
rows to be displayed in reverse order.
/
PAUSE
/
BACKWARD 15
/
REMARK
\
Note that the above command syntax (BACKWARD n) is syntax created specially
for SQLTALK.  For details on how to  use  the  FETCH  and  FETCH  BACKWARDS
operations in a C program, refer to the SQLBASE C Program Interface Guide.
/
PAUSE
/
REMARK
\
Features such  as  transaction level  rollback  and  recovery, referential
integrity, precompiled queries, multiple cursor capability, and  row  level
access are unique to SQLBASE  among  database  management  systems  on the
market today.  These are  mainframe features,  and coupled  with SQL
compatibility, superior performance, and an extraordinarily  compact  size,
they make SQLBASE an excellent DBMS engine  for  your  micro  or  micro  to
mainframe application needs.
/
PAUSE
/
REMARK
\
The entire tutorial so far has assumed the existence of a single user. The
name of this user was SYSADM (assigned when you created the  demo  database
via the CREATEDB utility).  We shall now review the  security  features  of
SQLBASE whereby the original creator of  a  database  may  create  multiple
users with different levels of access authority  to  the  database. The
original  creator  has what  is  called  SYSADM  authority  ie.   he has
unrestricted access to all the tables in the database and he may create new
tables or drop them as he pleases.

He may also create new users and grant them  various  levels  of  authority
over the database.  These users, if they have the authority to create new
tables, may selectively grant access privileges to users for a given table,
or in some cases, even to individual columns within a table.  All of  these
capabilities are implemented through a single  SQL  command  called  GRANT.
The privileges and authority levels may be selectively revoked through the
REVOKE command.
/
PAUSE
/
REMARK
\
Prior to creating any new users, a SYSADM should change  his  own  password
since his own original password is known to everyone (SYSADM). A  password
may be modified by specifying the new password in a GRANT CONNECT command.
/
GRANT CONNECT
TO SYSADM
IDENTIFIED BY TIGER
/
REMARK
\
We shall change this back to SYSADM now since the SQLTALK  program  expects
the password of this 'demo' database to always be SYSADM.
/
GRANT CONNECT
TO SYSADM
IDENTIFIED BY SYSADM
/
REMARK
\
The following commands creates a new user called USER1 identified  by the
password PWD1.
/
GRANT CONNECT
TO USER1
IDENTIFIED BY PWD1
/
REMARK
\
More than one user may be created in a single GRANT command.
/
GRANT CONNECT
TO USER2, USER3, USER4
IDENTIFIED BY PWD2, PWD3, PWD4
/
REMARK
\
The CONNECT authority simply means that the specified user is a valid  user
and may connect to the database (in this case DEMO) and access any  tables
to which he has been granted specific privileges.  Other  authority  levels
are RESOURCE (a user may create new tables) and DBA (a user  may  have all
the privileges a SYSADM enjoys except that he may not create new  users  or
change the authority level of existing users).

At this point, USER1 has only been granted CONNECT  'authority'.   He  does
not have specific 'privileges' to any of the tables in the  database. Let
us connect to the database via the USER1 id and try to select data  from  a
table.
/
CONNECT TO DEMO AS USER1/PWD1
/
SELECT *
FROM PRESIDENT
/
REMARK
\
We got the response that the table does not exist because the user does not
have any privileges on the PRESIDENT table, and as far as he is  concerned,
there is no table by that name.  Let us connect back as SYSADM and grant to
USER1, the privilege to query the PRESIDENT table.
/
USE DEMO SYSADM
/
GRANT SELECT
ON PRESIDENT
TO USER1
/
REMARK
\
Now, let us connect back as USER1 and try  to  select  from  the  PRESIDENT
table.
/
USE DEMO USER1
/
SELECT *
FROM SYSADM.PRESIDENT
/
REMARK
\
Note the use of the prefix SYSADM for the  name  of  the  PRESIDENT  table.
This is because each table, when being accessed by anybody other  than the
creator of that table, must have the name of the creator as its  prefix  in
order to properly identify it. You can always create a synonym for a table
that enables you to avoid having to type in a prefix for  tables  that are
not your own.  The following command creates a synonym called MYPRES.
/
CREATE SYNONYM MYPRES
FOR SYSADM.PRESIDENT
/
REMARK
\
You can now refer to SYSADM.PRESIDENT  as  MYPRES.   Note  however  that  a
synonym is only valid for the person who created it.  No other user can use
it (though he could create an identically named synonym).  To find out all
the synonyms that exist in the database,  you  can  query  the SYSSYNONYMS
table.
/
SELECT NAME, CREATOR, TBNAME, TBCREATOR
FROM SYSADM.SYSSYNONYMS
/
REMARK
\
To drop a synonym, issue the DROP SYNONYM command.
/
DROP SYNONYM MYPRES
/
REMARK
\
Now let us connect back as SYSADM to grant further  privileges on  tables.
More than one privilege or user may be listed in  a  GRANT  command  for  a
table.
/
USE DEMO SYSADM
/
GRANT SELECT, INSERT, DELETE
ON PRESIDENT
TO USER1, USER2
/
REMARK
\
Note that  since  USER1  already  had  a  SELECT  privilege  on  PRESIDENT,
repeating the privilege in another GRANT command had no additional  effect.
Other privileges that may be granted to a user are UPDATE (an entire  table
or specified columns within a table), INDEX (create or drop  indices) and
ALTER (a table by adding, dropping, modifying or renaming its columns).

The following command grants the UPDATE privilege to USER1 and USER2 on the
DEATH_AGE and YRS_SERV columns of the PRESIDENT table.
/
GRANT UPDATE (DEATH_AGE, YRS_SERV)
ON PRESIDENT
TO USER1, USER2
/
REMARK
\
Rather than listing out all the privileges available for a given table, you
may use the keyword ALL to  signify  all  privileges. Also,  rather  than
listing individual users, the keyword PUBLIC may be  used  to  signify all
users.
/
GRANT ALL
ON ELECTION
TO PUBLIC
/
REMARK
\
Note that user privileges may include all SQL statements except CREATE and
DROP TABLE.  A user granted only the CONNECT authority to  a  database may
not create or drop tables.  A user must be granted  RESOURCE  authority  to
CREATE his own tables. Such a user may also DROP tables as  long  as  they
were created by him.  He automatically has all access  privileges  (SELECT,
INSERT, DELETE, UPDATE, INDEX and ALTER) on any tables that he creates.

The following examples illustrate how to grant RESOURCE  authority.   Note
that since USER3 has already been assigned a password (PWD3)  when  he was
granted CONNECT authority,  no password  needs  to  be  specified  in the
following command.
/
GRANT RESOURCE
TO USER3
/
REMARK
\
Sometimes, the original creator  of  a database  may  wish  to  grant the
authority to a user to be able to access, modify or drop not only  his own
tables, but also to be able to do so with all other tables in the database.
This is known as DBA authority and is very  similar  to  SYSADM  authority.
The only difference is that DBA authority does no allow a  user  to  create
new users or to change their existing authority levels.   The  only  person
who can do that is the original creator of the database  (ie.  with  SYSADM
authority).

The following command grants DBA authority to USER4 identified by PWD4.
/
GRANT DBA
TO USER4
/
REMARK
\
To verify that all of  the  above  authorities and  privileges  have  been
granted as specified, let us now look at the system  dictionary  tables  to
see if they accurately reflect the results of the above commands.   We are
already aware of the SYSTABLES, SYSCOLUMNS and SYSINDEXES tables.  Each  of
them also has a column called CREATOR that displays the name  of  the  user
who created a particular table.
/
SELECT NAME, CREATOR
FROM SYSTABLES
/
SELECT NAME, TBCREATOR, TBNAME
FROM SYSCOLUMNS
/
SELECT NAME, CREATOR, TBNAME
FROM SYSINDEXES
/
REMARK
\
In addition to the above three tables, the system dictionary contains three
other tables that contain  information about  users  and  their  authority
levels and  privileges.   These  are  as   SYSUSERAUTH,   SYSTABAUTH and
SYSCOLAUTH.

Let us take a look  at the  information  in  the  SYSUSERAUTH table.  It
contains the name of each user to whom a CONNECT (or higher) authority has
been granted, his password, and an indication whether he  has  RESOURCE  or
DBA authority.
/
SELECT *
FROM SYSUSERAUTH
/
REMARK
\
Now let us take a look at the SYSTABAUTH  and  SYSCOLAUTH  tables  for the
entries relating to the PRESIDENT and ELECTION tables.
/
SELECT *
FROM SYSTABAUTH
WHERE TTNAME IN ('PRESIDENT', 'ELECTION')
/
SELECT *
FROM SYSCOLAUTH
WHERE TNAME IN ('PRESIDENT', 'ELECTION')
/
REMARK
\
Note that in the SYSTABAUTH table, the UPDATECOLS column contains an * for
the entry relating to the PRESIDENT table.  This is because  only  some  of
the columns are specified as updateable by USER1 and USER2.   The  specific
columns that may be updated are contained in the SYSCOLAUTH table.
/
PAUSE
/
REMARK
\
So far, we have seen examples only of how to grant privileges and authority
levels to users.  For each GRANT command option, there is  a  corresponding
REVOKE option. Thus a SYSADM may revoke any of the authority granted to  a
user (including CONNECT authority), a DBA may revoke any privileges of any
user of the database (but not change authority levels), and the creator  of
a table (RESOURCE  authority)  may  revoke  any  privileges  he  previously
granted to a user.

The following are examples of revocation of privileges by a SYSADM.
/
REVOKE UPDATE
ON PRESIDENT
FROM USER2
/
REVOKE ALL
ON ELECTION
FROM PUBLIC
/
REVOKE ALL
ON PRESIDENT
FROM USER1
/
REVOKE RESOURCE
FROM USER3
/
REVOKE DBA
FROM USER4
/
COMMIT
/
REMARK
\
As you can see, SQLBASE has a security scheme that is comprehensive and
easy to administer. It is compatible with the scheme employed by SQL/DS
and DB 2 though there are some minor differences between SQLBASE and the
above mentioned mainframe products.
/
PAUSE
/
REMARK
\
Now let us see how you can backup and restore the  tables  in  a  database.
SQLTALK gives a user the capability to LOAD or UNLOAD tables (or an  entire
database) to and from a DOS file.  LOAD and UNLOAD also enable a  database
to be "reorganized"  in  the  event  of  excessive  fragmentation  (SQLBASE
employs extensive reclamation schemes to ensure optimum use of disk  space
but there  are always applications  where  periodic  reorganization  will
improve performance).
/
PAUSE
/
REMARK
\
The following is an example of the use of the  UNLOAD  command.   We  first
UNLOAD the ELECTION table to a (SQL format) file called file.1
/
UNLOAD SQL FILE.1 ELECTION
/
REMARK
\
The above statement created the file.1 file containing the  definition  of
the ELECTION table, its data, and all its indices.  If you  examine  file.1
you will find that it contains commands that can be executed by SQLTALK  to
recreate the table, its data and its indices.  To verify  this fact, let's
issue the following commands.
/
DROP TABLE ELECTION
/
LOAD SQL FILE.1
/
SELECT *
FROM ELECTION
/
SELECT *
FROM SYSINDEXES
WHERE TBNAME = 'ELECTION'
/
REMARK
\
Note that the ELECTION table is restored to the database by LOAD exactly as
it was prior to the UNLOAD operation.  You could also  have  UNLOADed  only
the data in the ELECTION table.  The following command will do that.
/
UNLOAD DATA SQL FILE.2 ELECTION
/
REMARK
\
The LOAD and UNLOAD commands will also work for more than one  table  at  a
time or for the entire database.  The following commands  UNLOAD  and  LOAD
the PRES_MARRIAGE and ELECTION tables. The use of the keyword ALL (in lieu
of the table name) would have unloaded the entire database.
/
UNLOAD SQL FILE.3 ELECTION PRES_MARRIAGE
/
REMARK
\
LOAD and UNLOAD can also be used to transfer data to and from DIF and ASCII
format files.  The following commands unload and load DIF  and ASCII  data
files for the ELECTION table.  The output files may subsequently be used to
transfer data to popular microcomputer software  products,  most  of  which
have utilities to accept either of those two file formats.
/
UNLOAD DATA DIF FILE.5 ELECTION
/
UNLOAD ASCII FILE.6 ELECTION
/
DELETE FROM ELECTION
/
LOAD DIF FILE.5 ELECTION
/
DELETE FROM ELECTION
/
LOAD ASCII FILE.6 ELECTION
/
REMARK
\
This concludes the SQLBASE tutorial.  We hope we have been able to  provide
you with an appreciation of the power and functionality of the SQL language
and its scope of implementation in SQLBASE.

For more information on SQLBASE, please contact:

        GUPTA TECHNOLOGIES, INC.
        1040 MARSH RD, SUITE 200
        MENLO PARK, CA 94025
        Tel: [phone]
/