/---------------------\
| Notes for lecture13 |
| 30 November 1999    |
| CS 125-609          |
| Michael Goldwasser  |
\---------------------/

=========================================================================
     RELATIONAL DATABASES AND SQL
=========================================================================

Preface:   Consider this a "Cliff Notes" version of Chapter 5.4.

=========================================================================

Primary Keys

Foreign Keys

Rule of Referential Integrity

=========================================================================

SQL Requests

We can create a "virtual database" from an existing one by specifying
a subset of records with certain characteristics which we want
displayed and by specifying how we want our data to be displayed.

Specifically, we can define queries on an existing database to:

 * Show the records of a table in a specified order

 * Show just the records that meet certain criteria

 * Join multiple tables together, connected by a foreign key

 * Only show some of the fields

The Structured Query Lanuage (SQL) is a standard language for
describing such queries in relational databases.

-------------------------------------------------------------------------

When using a complete table, we set the DataControl.RecordSource to
equal the name of one of the tables of a database.  If we instead wish
to use SQL to define a "virtual table", we set the RecordSource equal
to a string which is a SQL query statement.


A generic SQL statment will look like this:

  SELECT www FROM xxx WHERE yyy ORDER BY zzz

The SELECT and FROM expressions are required, whereas the WHERE and
ORDER BY expressions are optional.

 www -  this specifies the fields which should be displayed. You may
        write "*" to indicate all fields, or you can give a
        comma-separated list of fields, in the order you wish them to
        appear. 




 xxx -  this specifies the table or join of tables which you wish to
        consider.  If just a single table, you give the name
        (e.g. Cities).  If you want to combine two tables, you write:

        t1 INNER JOIN t2 ON foreign key of t1 = primary key of t2
 (e.g. Cities INNER JOIN Countries ON Cities.country = Countries.country)

 yyy -  this is a list of criteria which records must meet to be
        included.  The format is similar to boolean conditions which
        are used in if statements. (e.g. pop2015 >= 20) In addition,
        SQL allows us to look for partial matches of string
        expressions by using wildcards and the "LIKE" keyword. 
        A "*" is a placeholder for any arbitrary string. 
        A "?" is a placeholder for any arbitrary single character.
        An expression "[letter1-letter2]" is a placeholder for any letter
        in the given range. Examples include:
          WHERE country = "China"
          WHERE city="Calcutta" or pop1995>15
          WHERE city Like 'S*'
          WHERE currency Like '?u*'
          WHERE country Like '[A-F]*'

 zzz -  this specifies the field or fields used to sort, along with
        whether the records should be in ascending (ASC) or descending
        (DESC) order.  If multiple fields are given, the second one is
        only used as a tiebreaker when the first fields match, the
        third is only used as a tiebreaker when both the first and
        second fields match, and so on. 
        (e.g.  ORDER BY lastname,firstname ASC )

-------------------------------------------------------------------------

Let's try some examples:

 * Show the records from Cities in alphabetical order based on the
   name of the city.

 * Show the records from Cities in alphabetical order based on the
   name of the country and, within each country group, the name of
   the city.

 * Show the records from Cities in descending order based on the
   projected 2015 population

 * Show the records for the Cities in China.

 * Show the records from Cities whose 2015 population is projected to
   be at least 20 million

 * Show the records from Cities whose name begin with the letter S.

 * Show the records from the joined table in descending order of
   the populations of their countries.

 * Show the records from the joined table whose currency has "u" as
   its second letter.

 * Show just the city and country fields of the table Cities.

 * Show just the city and currency fields of the joined table.


Let's try a few more...

=========================================================================