SQL - LECTURE NOTES

	A database is like an electronic filing cabinate.  It is used to store records.  The only difference is that, with a database, the records are stored electronically.

	A database management system (DBMS) is used to help use manipulate the data we wish to store in the database.  The DBMS is the interface between the user and the data that is stored.  The user can ask the DBMS to perform certain operations -- "Bring me this file," "Update this file," "Average this field."  

	We have been looking at relational databases in class.  Specifically, we have been creating databases and writting programs that work with dBASE IV.  In a relational system, data is stored and represented in tables.  This adds flexibility and convenience in manipulating data.  dBASE offers the control center, the dBASE command language, and SQL for flexibility in the retrieval of information.  We have learned to use the control center and  some of the dBASE command language to help us retrieve information from dBASE IV.  Now we are going to explore SQL - "Structure Query Language."  SQL is pronounced both "sequel" and "s-q-l."  It is a language for managing a relational database system.  You will notice that you can perform some of the same tasks with the dBASE command language that you can perform with SQL.  SQL is very popular and has become somewhat of a standard interface to relational database systems.  It consists of a certain set of statements that let us perform various operations.

Why use SQL?

1.	If you are already familiar with SQL, you can apply this knowledge to dBASE IV databases by using dBASE IV SQL.

2.	SQL offers an alternative method of carrying out complicated queries.  SQL is considered very strong in the area of dBASE IV's multiple database operations, where query operations are the most complex.

	dBASE IV is process-oriented.  In order to obtain a specific set of information you need to know what operations to execute and in what order.  Only after performing the correct sequence of commands can the desired data be obtained.

	SQL, on the other hand, is object-oriented.  The set of data you want to obtain is the object of the query.  The query is a description of the database object, not the operations needed to obtain it.  The actual mechanism, techniques, and sequence of operations needed to obtain information are hidden from the user.

	SQL has several advantages.  The command set for SQL is smaller than dBASE IV's because dBASE IV's commands refer to procedures that are all different, and SQL command refer to database objects that all have the same basic structure.  This should make SQL faster to learn and simpler to use.  Since the actual procedures to obtain the information are hidden from the user, SQL can be adapted to use all types of applications as the basis of the query.  This means that same SQL commands could be used to carry out database retrieval operations across a range of micro, mini, and mainframe computers.

The Future of SQL (the SQL server)

	Microsoft and Ashton-Tate are collaborating to produce a new network oriented application called the SQL server, which will allow a file server to process SQL queries issued by such programs as dBASE IV, Lotus 1-2-3, or Microsoft Excel.  The SQL server will run under the O/S 2 LAN manager software being developed by Microsoft.

	Let's take a look at this new catagory of database software:  the SQL server.  The SQL server divide the traditional database program into two parts:  front-end and back-end.  The back-end can be placed anywhere:  on a dedicated "database server," which can be a high performance 386 on a LAN, a minicomputer (like a DEC/VAX), or a remote mainframe (like an IBM 4381).

	Multiuser databases for PC's are unreliable, slow, difficult to write, and much more difficult to maintain.  On the PC data security, rollback, and restore capabillities are crudely implemented or nonexistant on PC's.  Central data on today's PC Lans resides on a file server.  Each response to a request for information sends the entire file to every workstation that needs even a small portion of the data.  This redundant traffic clogs the Lan and slows the response.

	With a true database server, queries are processed on the server and only the extracted information sent to the PC.  This frees the user's PC to analyze the data.  The performance improvements are drastic because networked PCs are allowed to supplant minicomputers and mainframes formerly used in the processing of huge databases for many department-level applications.

	Front-end applications such as spreadsheets, graphics programs, forms creators, query tools, or custom programs will most likely be used in the future to mask the SQL commands from the user.  SQL is a miserable failure at meeting its original purpose as a standard query language for users.  The syntax is far too complex for the average user.  The next generation of SQL products will allow the user to take advantage of SQL without knowing the actual commands.  The user will only learn the SQL if they are so inclined.  This means that SQL is not the vehicle for users to talk to DBMSs, it is the language that various database tools use to communicate with each other.

	Once the conceptual leap has been made to separate software into front-ends and back-ends, the imagination runs to which services will be shared by what software.  Common data on a remote server will not only be accessed by database front-ends, byt spreadsheets, project management programs, graphics software, word processors, and desktop publishing programs.

SQL IN dBASE IV --

	SQL tables consist of rows and columns like spreadsheets.  (An SQL table is the same as a standard dBASE IV database.)  Each column is assigned a name and a data type.  The types of data supported by SQL are CHAR, DATE, DECIMAL, FLOAT, INTEGER, LOGICAL, NUMERIC, and SMALLINT.  Memo type columns are not supported in SQL.  Table and Column names follow the same conventions a nd limitations as standard dBASE IV database file names (8 characters) and field names (10 characters).

ALTERING TABLE STRUCTURES IN dBASE IV SQL

	The table structure in dBASE IV can be altered with the Create Table and Alter Table.

Syntax:

CREATE TABLE table_name (column_list );

ALTER TABLE table_name  (column_list  );

Examples:

CREATE TABLE Guest
(Name CHAR(25), Sex CHAR(1),
  Build CHAR(3), Height INTEGER);

CREATE TABLE Roster
(Name CHAR(25), Room CHAR(3),
  Trainer CHAR(8), Arrival DATE, 
  Departure DATE, Discount DECIMAL(2,2));

ALTER TABLE Guest
ADD (Weight DECIMAL(5,2));

DATA ENTRY IN dBASE IV SQL

	When you have created a table, you will want to place data into that table.  Each set of data added to the table is called a row.  An SQL row is the same as a standard dBASE IV record.
	Entering data into SQL tables is very different than entering data into a dBASE IV database using the full-screen editing features.  There are three ways to enter information into a SQL table:  INSERT, UPDATE, and LOAD DATA.

INSERT

	The INSERT command allows you to add a new row of data to an existing table.  This is not a full screen editing feature.  The data items are entered as a list in the INSERT command.

SYNTAX:

INSERT INTO table_name  
(list of column names )
VALUES
(values_list  );

EXAMPLES:

INSERT INTO Guest
VALUES
('Bette Midriff','F','M',66,135);

INSERT INTO Roster
(Name, Room, Trainer, Arrival, Departure, Discount)
VALUES
('Jean-Paul Rotundo','3','Julio',{06-15-88},{06-18-88},.25);

INSERT INTO Roster
(Discount, Departure, Arrival, Trainer, Room, Name)
VALUES
(.25,{06-18-88},{06-15-88},'Julio','3','Jean-Paul Rotundo');

UPDATE

	The UPDATE command is used to add or replace existing data in a column of an existing row.  The UPDATE command performs a function similar to the REPLACE ALL command in standard dBASE IV.  It is difficult, though not impossible, to place a specific value in a specific record using SQL.  A logical expression must be entered in order to select a specific row on which a change is to be made.

SYNTAX:

UPDATE table name
SET column = value
WHERE logical expression ;

EXAMPLE:

UPDATE Roster
SET Trainer = 'Max'
WHERE Name = 'Jean-Paul Rotundo';

LOAD DATA

	The LOAD DATA command is usually the most practical SQL data entry command.  This command allows you to translate data already in files into SQL tables.  SQL assumes this will be the method used to enter data into SQL tables because using the INSERT and UPDATE commands is difficult and tedious.  The LOAD DATA command allows one to enter data from a variety of products like dBASE II, III, and IV DBF files, RapicFile RPD files, Framework II FW2 files (database and spreadsheet frames), Lotus 1-2-3 Ver 1A WKS files, Multiplan or Excel SYLK format files, Visicalc DIF files, and ASCII text files delimited with commas or blanks.





     Another file downloaded from:                     NIRVANAnet(tm)

     & the Temple of the Screaming Electron              510-935-5845

     Rat Head                                            510-524-3649
     Burn This Flag                                      408-363-9766
     realitycheck                                        415-474-2602
     Lies Unlimited                                      415-583-4102

   Specializing in conversations, obscure information, high explosives,
       arcane knowledge, political extremism, diversive sexuality,
       insane speculation, and wild rumours. ALL-TEXT BBS SYSTEMS.

  Full access for first-time callers.  We don't want to know who you are,
   where you live, or what your phone number is. We are not Big Brother.

                         "Raw Data for Raw Nerves"