home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Power-Programmierung
/
CD1.mdf
/
sql
/
intro.shr
< prev
next >
Wrap
Text File
|
1988-03-11
|
22KB
|
573 lines
SSQL
VERSION 1.1
COPYRIGHT (C) 1988 BY STEVE SILVA
SILVAWARE
3902 NORTH 87TH STREET
SCOTTSDALE, AZ 85251
Compuserve: 73177,2771
and Phx PC User's Group
Special thanks to the hard-working
students in my class on fourth generation
languages at the DeVry Institute of
Technology, Phoenix, Arizona.
TABLE OF CONTENTS
INTRODUCTION . . . . . . . . . . . . . . . . . . . . . . . INTRO-1
Key Words Needed to Understand the Documentation . . . . INTRO-1
What Is SQL And Why Is It So Important To Know? . . . . . INTRO-1
How Does This Implementation Of SQL Differ From Others? . INTRO-3
Differences In The Registered Version . . . . . . . . . . INTRO-4
How To Register . . . . . . . . . . . . . . . . . . . . . INTRO-5
Tutorial . . . . . . . . . . . . . . . . . . . . . . . . INTRO-6
Permission to copy . . . . . . . . . . . . . . . . . . . INTRO-7
EXTRACTING DATA FROM A SINGLE TABLE . . . . . . . . . . . . SELECT-1
Distinct . . . . . . . . . . . . . . . . . . . . . . . . SELECT-2
Where . . . . . . . . . . . . . . . . . . . . . . . . . . SELECT-4
search_expression . . . . . . . . . . . . . . . . . . . . SELECT-4
Special Search Expression - is null, is not null . . . . SELECT-5
Special Search Expression - like, not like . . . . . . . SELECT-7
And, Or, Not . . . . . . . . . . . . . . . . . . . . . . SELECT-8
Any . . . . . . . . . . . . . . . . . . . . . . . . . . . SELECT-12
In. . . . . . . . . . . . . . . . . . . . . . . . . . . . SELECT-13
All . . . . . . . . . . . . . . . . . . . . . . . . . . . SELECT-13
Mathmatical Functions Avg, Min, Max, Sum, Count . . . . . SELECT-14
Group by, Having . . . . . . . . . . . . . . . . . . . . SELECT-16
Order by . . . . . . . . . . . . . . . . . . . . . . . . SELECT-17
Into . . . . . . . . . . . . . . . . . . . . . . . . . . SELECT-18
JOINING TABLES . . . . . . . . . . . . . . . . . . . . . . JOIN-1
SUBQUERIES . . . . . . . . . . . . . . . . . . . . . . . . SUBQ-1
CREATE A TABLE . . . . . . . . . . . . . . . . . . . . . . CREATE-1
CREATE A VIEW . . . . . . . . . . . . . . . . . . . . . . . VIEW-1
INSERT DATA INTO A TABLE . . . . . . . . . . . . . . . . . INSERT-1
UPDATE DATA IN A TABLE . . . . . . . . . . . . . . . . . . UPDATE-1
DELETE DATA FROM A TABLE . . . . . . . . . . . . . . . . . DELETE-1
APPENDIX A - SAMPLE QUERIES . . . . . . . . . . . . . . . . APP-1
APPENDIX B - ANSWERS TO TUTORIAL . . . . . . . . . . . . . APP-3
APPENDIX C - ORDER FORM . . . . . . . . . . . . . . . . . . LAST PAGE
KEY WORDS NEEDED TO UNDERSTAND THE DOCUMENTATION
SQL - Structured Query Language. A standard method of
interacting with a database. It is pronounced "SEQUEL"!! NEVER
SAY THE LETTERS S-Q-L!! IT WILL BRING SEVEN YEARS OF BAD LUCK
AND SHOW PEOPLE THAT YOU ARE NEW TO SQL!!!
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" (see
documentation). 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. For a sampling of the types of queries that SQL can
handle, refer to Appendix A.
INTRO-1
It is also important to know that popular databases such as
Rbase and dBase, are coming out with SQL versions. The Rbase
version will cost from about $900 to $2495 for the network
version. Hopefully, in the years to come, the price will come
down.
The new operating system for PCs, OS/2, will have an extended
version (at a cost of about $800) which will include SQL. IBM
has stated that SQL is to become the standard interface for
databases.
INTRO-2
HOW DOES THIS IMPLEMENTATION DIFFER FROM THE OTHERS?
The following is a table from the January, 1988 issue of BYTE.
I have added SSQL to the end of the table for comparison:
SQL Command Informix Ingres Oracle SQLBase XDB XQL SSQL
2.0 5.0 5.1 3.2.2 II 1.0 1.1
($795) ($950) ($1295) ($995) ($395) ($795) ($30)
DML (Data Manipulation Language)
SELECT Yes Yes Yes Yes Yes Yes Yes
COLUMNS Yes Yes Yes Yes Yes Yes Yes
EXPRESSIONS Yes Yes Yes Yes Yes No No
DISTINCT Yes Yes Yes Yes Yes No Yes
FROM Yes Yes Yes Yes Yes Yes Yes
WHERE Yes Yes Yes Yes Yes Yes Yes
GROUP BY Yes Yes Yes Yes Yes Yes Yes
HAVING Yes Yes Yes Yes Yes Yes Yes
ORDER BY Yes Yes Yes Yes Yes Yes Yes
SUBQUERIES Yes Yes Yes Yes Yes Yes Yes
UPDATE SET Yes Yes Yes Yes Yes Yes 1
WHERE Yes Yes Yes Yes Yes Yes Yes
SUBQUERIES Yes Yes Yes Yes Yes No Yes
INSERT INTO Yes Yes Yes Yes Yes Yes 2
SUBQUERY Yes Yes Yes Yes Yes No No
DELETE FROM Yes Yes Yes Yes Yes Yes Yes
SUBQUERIES Yes Yes Yes Yes Yes Yes Yes
UNION Yes Yes Yes Yes Yes No No
CORRELATED -
SUBQUERIES Yes Yes Yes Yes Yes No No
DML Predicates
BETWEEN Yes Yes Yes Yes Yes Yes Yes
LIKE Yes Yes Yes Yes Yes No Yes
IS NULL Yes Yes Yes Yes Yes Yes Yes
EXISTS Yes Yes Yes Yes Yes No No
ALL Yes Yes Yes Yes Yes No Yes
ANY Yes Yes Yes Yes Yes No Yes
SOME No No No No No No No
[NOT] Yes Yes Yes Yes Yes Yes Yes
DML Functions
AVG Yes Yes Yes Yes Yes Yes Yes
COUNT(*) Yes Yes Yes Yes Yes No No
COUNT Yes Yes Yes Yes Yes Yes Yes
MAX Yes Yes Yes Yes Yes Yes Yes
MIN Yes Yes Yes Yes Yes Yes Yes
SUM Yes Yes Yes Yes Yes Yes Yes
DDL (Data Definition Language)
ALTER TABLE Yes Yes Yes Yes Yes Yes 3
CREATE TABLE Yes Yes Yes Yes Yes Yes Yes
NOT NULL Yes Yes Yes Yes Yes No No
CREATE INDEX Yes Yes Yes Yes Yes Yes No
CREATE UNIQUE
INDEX Yes Yes Yes Yes Yes No No
CREATE VIEW Yes Yes Yes Yes Yes Yes Yes
DROP TABLE Yes Yes Yes Yes Yes Yes Yes
DROP INDEX Yes Yes Yes Yes Yes Yes No
1. Although the syntax is a bit different (better), the insert
is available.
2. The update exists, but you must update one row (record) at a
time. You cannot use a calculation to update a set of rows.
3. Although the syntax is different, You can delete columns in a
table, change the names of the columns, change the size and data
type of a column, etc.
INTRO-3
STRENGTHS AND WEAKNESSES OF SSQL
My emphasis has been on the data manipulation language since
that is the most difficult to master and it is the most useful
to the end-user. Since the current version of SSQL cannot
create indexes, querying large tables tends to be slow in
comparison. When joining tables, SSQL on a PC with a RAM disk
can evaluate about 750-800 rows/minute. On a PC AT with a hard
disk it is about 3200 rows/minute.
I don't think SSQL can be touched on a price/performance basis
though. I use Oracle 5.1 - the stack of documentation is over
a foot high and it requires a PC AT with one megabyte of
extended memory. It is an excellent package but not everybody
needs the power of a $1,295 product.
SSQL documentation is oriented toward the end-user, not the
programmer.
DIFFERENCES IN THE REGISTERED VERSION
PROGRAM
There is NO difference in the programs except that registered
users are assured of getting the most current version.
DISK-BASED DOCUMENTATION (UNREGISTERED USERS)
The disk-based documentation only shows you enough to get you
started. However, it does show all the options so you
can see for yourself that the commands actually work.
FULL DOCUMENTATION (REGISTERED USERS)
The full documentation includes the disk-based documentation but
it is printed with a laser printer.
It also has:
1) A full explanation on how to simultaneously extract data out
of more than one table. This is called joining tables.
2) In-depth information on how to create subqueries. This
technique allows you to put select statements within select
statements. This is where the word Structured comes from in
the word Structured Query Language.
3) Answers and detailed explanations to the queries in the
appendix. This shows the amazing flexibility of SQL and why
it is becoming so popular.
4) The power of SQL can be TOTALLY lost if the user does not
understand the basics of data normalization. Data
normalization involves the rules for creating tables. If the
tables are not organized correctly, SQL cannot be used to its
INTRO-4
full potential. It is important to note that the topic is
discussed with the non-technical end-user in mind. Since
there have not been any widely available SQL program, all the
books on normalization tend to be very theoretical and
academically oriented.
Even if our objective is to just create tables in the third
normal form (there are more), going to the standard text
books is frustrating. For example, one of the best books on
databases is AN INTRODUCTION TO DATABASE SYSTEMS by C. J.
Date. His definitions for first, second and third normal
forms are:
First normal form -
A relation R is in first normal form (1NF) if and only if all
underlying domains contain atomic values only.
Second normal form -
A relation R is in second normal form if and only if it is in
1NF and every nonkey attribute is fully dependent on the
primary key.
Third normal form -
A relation R is in third normal form (3NF) if and only if it
is in 2NF and every nonkey attribute is nontransitively
dependent on the primary key.
Unfortunately, the above definitions are designed for
students of database theory, not the typical end-user who
wants to create some simple tables. I give plenty of examples
and try to avoid the jargon as much as possible.
5) Full information on utilities to delete columns from
tables, modify column names, change the width of columns, create
tables which are subsets of a current table, etc.
HOW TO REGISTER
Send $30 plus $2.50 shipping and handling to:
Silvaware
3902 North 87th St.
Scottsdale, AZ 85251
Make checks payable to STEVE SILVA. Sorry, no VISA or
MasterCharge. There is an Order Form on the last page.
You will receive full documentation and the latest version of
SSQL.
INTRO-5
TUTORIAL
Read the section on Getting Data From a Single Table. As you
read the documentation, it is better to have SSQL running. Type
the following at the prompt where the program and associated
files reside:
SSQL (press <ENTER>)
After a brief message, the cursor will stop at the following
prompt:
SSQL>
At this point, you can enter any SQL command. I suggest that
you type the select statements as you read about them. As you
understand the command, try your own variations. WHEN YOU WANT
TO EXIT SSQL AND RETURN TO THE DOS PROMPT TYPE "EXIT".
The table we want to create involves customer information. We
want to store the customer's code, name, state, and rating.
The following is the table we want to create:
code name st rating
---- --------------- -- ------
c1 Compugorp WA 20
c2 Techoharps OR
c3 Organomice AZ 34
Our first step is to create the table. Please refer to the
section on creating a table, then type:
SSQL> create table cust (
> code char 2
> name char 15
> st char 2
> rating num 2
> ) 20;
The above will create a table called "cust" with a column called
code which is two characters wide, a column called name which is
fifteen characters wide, a column called st which is two
characters wide and a column called rating which is a
number that can be two digits. The last line tells you how many
customers it can hold which is 20. It is important to note the
details - the "(" after cust, the ")" on the last line, and the
";" which ends the command.
Now we need to put some data into the table. Read the section
on Insert Data Into the Table. Type "insert cust", press
<ENTER>, and type data for each prompt. After you type all the
data and are prompted to enter a new code, press the ESC key to
exit the insert mode. It will respond with the number of rows
INTRO-6
inserted.
The screen should look like:
SSQL> insert cust;
ESC = EXIT, <ENTER> = NULL VALUE
code : c1
name : Compugorp______
st : WA
rating : 20
----
code : c2
name : Techoharps_____
st : OR
rating : __
----
code : c3
name : Organomice_____
st : AZ
rating : 34
----
code : <ESC> <-- Press ESC to exit
3 rows inserted
There is a special case for customer c2. Note that the rating
column is blank. This means that a rating is not appropriate
for this customer. This is not to say that the rating is zero.
This is explained further in the section on the "select"
statement. Just press <ENTER> when you come to the rating
prompt for c2.
Now refer to the section called EXTRACTING DATA FROM A SINGLE
TABLE in order to understand how to use the "select" statement
and the logic of the "where" clause which are helpful in
completing the tutorial.
Let's assume that you made a mistake entering the name for
customer c2. The name should be Technoharps instead of
Techoharps.
Refer to the section called UPDATE DATA IN A TABLE for the
solution and some other ways to update tables.
QUIZ TIME!!! (THE ANSWERS ARE IN APPENDIX C)
1. List all the data in the table.
2. List the customer name and rating for all customers in AZ.
3. List all the data for ratings from 10 to 35.
4. List the average rating.
5. How many customers have a rating of less than 25.
The above will give you a start. The real power of SQL would be
realized if you had other related tables as explained in
INTRO-7
Appendix A.
For deleting data, refer to the section called DELETING DATA
FROM A TABLE.
PERMISSION TO COPY
Please copy this disk and give it to a friend (or anybody else).
However, the following restrictions apply:
1. No changes can be made to the distribution disk, including the
documentation.
2. You cannot copy or reproduce the printed manual.
Any commercial, educational, governmental and other such
organizations are required to purchase a copy of SSQL for
every building/department it is used in.
Quantity discounts available.
INTRO-7
