Turbo Toolbox

home *** CD-ROM | disk | FTP | other *** search

/ Turbo Toolbox / Turbo_Toolbox.iso / 1990 / 03 / ssql / intro.shr < prev next >

Wrap

Text File | 1989-01-03 | 22.3 KB | 571 lines

SSQL VERSION 1.4b COPYRIGHT (C) 1988 BY STEVE SILVA SILVAWARE 3902 NORTH 87TH STREET SCOTTSDALE, AZ 85251 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-8 Changes from 1.2 to 1.3 . . . . . . . . . . . . . . . . . INTRO-8 Changes from 1.3 to 1.4 . . . . . . . . . . . . . . . . . INTRO-8 THE NEW SSQL ENVIRONMENT (1.4) . . . . . . . . . . . . . . ENV-1 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 SUBQUERIES . . . . . . . . . . . . . . . . . . . . . . . . SUB-1 JOINING . . . . . . . . . . . . . . . . . . . . . . . . . . JOIN-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, or have already come out with SQL versions. 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. 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 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 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 it is used in. Quantity discounts available. CHANGES FROM 1.2 TO 1.3 Most of the changes were bug fixes - most of them in the area of create and insert. The use of a macro symbol, "@", which will call a text file with a command in it such as: SSQL>@REPORT1 <ENTER> where there exists a text file on the disk called REPORT1 which contains a "select" statement. See the section on the new SSQL environment for more details. CHANGES FROM 1.3 TO 1.4 More bug fixes. This time the emphasis was on nested selects and large tables. Major changes in the user environment. You can call your favorite word processor from within SSQL to make changes to your current statement or any other script file you have created. See the section on the new SSQL environment for more details. Numeric edits in INSERT. INTRO-8