Make your own free website on Tripod.com
 
 
TBSC  -   The Blue Software Company
 Home Page   Order Information   Partners / Employmt. 
 Products / Services   Freeware / Shareware   More ... 
 Download Products   Resources / Links 
 
Practical software, services and solutions for the small business
and the individual computer user - world wide !



 
SQL (structured query language)  -  an introduction for our target group
[ short - and easy to understand ]



 

The following extracts are from a shareware package to learn SQL (which we have used to learn the basics more than a decade ago). SSQL VERSION 2.2  COPYRIGHT (C) 1990 BY STEVE SILVA from: SILVAWARE, 902 NORTH 87TH STREET,  SCOTTSDALE, AZ 85251

The full shareware package will also be available from our web site - at the moment we want to inquire if there have been further developments of SSQL. We keep you posted.

The text below will also help you to explore the possibilities WinSQL is offering. We have kept it as short as possible.

Extracts:
 
KEY WORDS NEEDED TO UNDERSTAND THE DOCUMENTATION
 
SQL - Structured Query Language. A standard method of  interacting with a database.  It is pronounced "SEQUEL"!

TABLE - A table is typically known as a FILE in other systems. You may ask why they don't just call a table a file.  It is because that although normally a table does refer to a specific file, a table can refer to something that spans two or more files.  This can be done by "creating a view". If you read a book on relational databases, they will probably refer to a table as a relation.

ROW - A row corresponds to a record or a portion of a record in a file.  In relational theory it is called a tuple.

COLUMN - A column is typically known as a field in other systems.  In relational theory it is called an attribute.

The above names were created to give relational databases a consistent and accurate view of data.

         EXAMPLE:

         You may have a TABLE named sales which contains COLUMNs called
         date, custnum, partnum and quantity.  Every time you made a
         sale, you would add a ROW of data to the TABLE.

                      COLUMNS
           -----------------------------
          |         |        |         |
         date    custnum  partnum  quantity
         ------  -------  -------  --------
         880201  8524     AD873         928       <-- ROW
         880203  7687     VF8709         87       <-- ROW

         ----------------------------------
                        ^
                        |
                      TABLE
 

WHAT IS SQL AND WHY IS IT SO IMPORTANT TO KNOW?

SQL stands for Structured Query Language.  It was developed as a standard method to query
(extract data from) a relational database and do other operations to maintain relational databases.

Relational databases look at files as if they were simply tables.  SQL was developed years ago at a theoretical level but because of its inherent inefficiencies and programming complexity, it has been very difficult to create workable programs.  It provides the most flexible approach to extracting data from a database.  It allows us to extract data in seconds that would take a knowledgeable programmer hours, days or weeks to extract, even if the programmer had the most advanced non-SQL languages available.
 

QUERIES - SINGLE TABLE

OVERVIEW

The select command is used to retrieve data from tables. Although the use of select on a single table is relatively easy, translating from English to SQL (or most other query languages) can be rather difficult. The approach of this chapter is not merely to explain the syntax of the command, it will give you a more critical approach to apply SQL.

         SYNTAX:

         SELECT [DISTINCT] column_name [,column_name ...]
         FROM table_name [,table_name ...]
         [WHERE search_expression]
         [GROUP BY column_name [,column_name ...]
         [HAVING criteria]]
         [ORDER BY column_name [,column_name ...]

         If you want all the columns in a table you can replace the list
         of column names with an asterisk (*).

         The select command produces a report in the form of a table.
 

THE BASICS OF SELECT

         Find all the data in the manu table.

         select *
         from manu;

         date     code mst defects qty
         -------- ---- --- ------- ---
         02/07/87 GC   ID       12  15
         02/01/87 GC   ID        0  55
         02/02/87 NM   CA       17  93
         02/02/87 DD   ID           25
         02/03/87 DD   WA       22  46
         02/02/87 NM   WA       15  25
         02/04/87 DD   AZ       12  25
         02/04/87 DD   CA       15  25
         02/06/87 GC   AZ        4  43

         9 rows selected
 

WHERE

The where clause of the select command is the most powerful and complex part of SQL.
 

search_expression

The simplest search expression relates a column name to a constant. A constant is an exact value that you enter. All column values are tested against the constant value. If the column name that you are testing is defined as a character, the constant must be enclosed in single quotes or double quotes.

The constant you are searching for may not contain a quote. If the column name you are testing for is defined as numeric, the constant is a number without any quotes.

You can relate the column name to the constant in the following ways:

         = equal
         <> not equal
         != not equal
         ~= not equal
         >  greater than
          less than
         >= greater than or equal
         <=  less than or equal
         IS NULL   column value is null
         IS NOT NULL   column value is not null
         LIKE  like a pattern
         NOT LIKE  not like a pattern

         Find out which customers are in Arizona.

         select *
         from cust
         where st = 'AZ';

         code name               st        rating
         ---- --------------- -- ------
         ZZ   Organomice      AZ     34
         DD   QuarkCo         AZ     10

         2 rows selected

         Spaces are optional on either side of the "=". You could have typed:  where st='AZ';

         Instead of single quotes you could have used double quotes:  where st="AZ";
 
 
 

ORDER BY

The order by clause sorts the output of the table based on the column name(s) listed.  The original table is not changed. As       with the group by clause, the order by clause can be used with the where clause.  For example, the following query will produce a list of manufacturing information sorted by defects for those items manufactured in Idaho.

         select date, code, defects
         from manu
         where mst = 'ID'
         order by defects;

         date     code defects
         -------- ---- -------
         02/02/87 DD
         02/01/87 GC         0
         02/07/87 GC        12
 

Now - you can start with your own data or with our test data.

It will take some time to get used to it. We also recommend to download the full shareware version of SSQL - there is no easier way to learn the basics and understand more about the background.

The full document is also included in the shareware package (around 140 pages).
 
 



 
TBSC  -   The Blue Software Company
 Home Page   Order Information   Partners / Employmt. 
 Products / Services   Freeware / Shareware   More ... 
 Download Products   Resources / Links 
 
Practical software, services and solutions for the small business
and the individual computer user - world wide !