SQL TUTORIAL

Copyright © 2001 by Peter Gulutzan
All Rights Reserved.

Copyright violation is illegal and despicable.

CONTENTS

Download and install an SQL DBMS

Make sure you have all the following: a browser such as Internet Explorer or Netscape, a "Microsoft Windows" operating system (Windows 95/98/Me/NT), an internet connection, and 20MB free space on your disk drive. You can skip the download if you don't have Windows and disk space and a browser, but that would be a shame. It's much better to learn by doing. By way of reassurance: thousands of people have done this download before you, there will be no changes to your system settings, and I'll show you how to do a "100% uninstall" when you finish the tutorial.

>> Start up a second copy of your browser. That way you will be able to continue reading this page, while you download.

>> Load this internet web page (use cut and paste to copy the URL):

>> Read the instructions and notices on the page. Click on the words "Click here to download THE OCELOT SQL DBMS".
>> When you see a dialog box asking where to put the zip file, enter:
c:\ocelot
If your C: drive is not available then substitute another letter, but please, follow instructions exactly if you can. In everything that follows, I'll be assuming that there is a directory named OCELOT which is directly off the root directory.

>> Wait. The size of the file that you're downloading, "ocelot.exe", is about 4 megabytes. This is a tiny file considering it contains a complete DBMS, but it's on a slow server. While you're waiting, now is the time to soak up some background material. Memorize these words:

TABLE. For example, a database can contain an Employees table.

ROW. For example, an Employees table can have one row for each employee.

COLUMN. For example, an Employees table can have a column for employee names.

The major SQL vendors (Oracle Microsoft IBM and others) peddle their own dialects of the language, but think. When you took a foreign language in school, did you start by learning a regional dialect? No, you studied the standard speech. Fine, so when you learn SQL, start with standard SQL. Standard SQL comes from ISO internationally, or ANSI in the United States. Since the latest standard version came in 1999, correct possible terms are SQL:1999, SQL/99, SQL99, or the one I prefer, SQL-99. The older version (SQL-92) is no longer an official standard, but some current SQL implementations only support a subset of SQL-92. The DBMS you are downloading now is THE OCELOT SQL DBMS, which contains all of SQL-92, all the SQL-99 you'll need, and absolutely no deviations or extensions. I want to emphasize that! There is no such thing as an "Ocelot SQL" dialect. You are going to learn standard SQL.

Okay, by now the download should be done. If you took my advice and started a second instance of your browser, you can close it now. You won't be needing anything else off the internet while you use this tutorial.

>> Run (i.e. click Start+Run on your Windows taskbar):
c:\ocelot\ocelot.exe
You downloaded the "self-extracting zip". Now you can run it, or you can say "unzip c:\ocelot.exe" using a program like PKZIP. The unzip will put a few new files on the directory c:\ocelot, including one big one named setup.exe.

>> Run:
c:\ocelot\setup.exe
SETUP.EXE is the Install program. Your job at this stage is simple, you just have to click the "okay" box on each screen. In what follows, I present a low-resolution image of each setup.exe screen so you'll know you're following along correctly.

SETUP screen 1: OCELOT SQL
Click the "Install" button.

SETUP screen 2: LICENSE
Notice that this is an End-User License Agreement. Naturally you will read the terms of the agreement carefully before continuing.
Click the "I Agree" button.

SETUP screen 3: CHOOSE DIRECTORY
If you cannot use Drive C, but you can use drive X, change C:\OCELOT to X:\OCELOT.
Click the "Continue" button.

SETUP screen 4: ODBC DRIVER
Click the "Continue" button.

SETUP screen 5: NETWORKS
In a later exercise you'll run setup.exe again and choose a non-default option, but not now.
Click the "Continue" button.

SETUP screen 6: DEFAULTS
Click the "Continue" button.

SETUP screen 7: READY TO GO ...
Click the "Continue" button.

SETUP screen 8: OK!
Click the "Finish" button.

You have now finished the download and installation of an SQL DBMS.

Get Acquainted With A Database "Front End"

I recommend that you keep two separate windows open throughout this tutorial, one showing the text you're reading now, the other for the program that you're about to start and run.

>> Run:
c:\ocelot\demo32.exe
DEMO32.EXE isn't the DBMS itself; the actual DBMS is in a dynamically loadable library named OCELOT32.DLL. DEMO32.EXE's job is to accept your input and display results. So DEMO32.EXE is a "front end" and OCELOT32.DLL is a "back end". DEMO32.EXE conforms to Windows guidelines and to what generic front ends should look like.

DEMO32 will take up the full screen. The picture below shows what it would look like slightly reduced. Take a moment to look at it.

The "SQL Command:" box is where you'll type in SQL statements. TIP: you can enter SQL statements by cutting from this window that you're reading now, and pasting to the screen input for demo32.exe, but only one statement at a time please.

The "bottom of the screen" is where you'll see results. When you retrieve data, you'll see rows and columns in this area.

Entering SQL Statements

Now it is time to enter an SQL statement. An SQL statement looks like an English imperative sentence. The parts of a statement are:

• SQL KEYWORDS. A word that means something in SQL. I will always type keywords in upper case, example: CONNECT.
• LITERALS. A string with a value. A numeric literal is just a number, example: 123. A character string is zero or more characters enclosed in single quotes, example: 'OCELOT'.
• NAMES. A word that refers to an SQL object. I will always type names of tables with the first letter in upper case, example: Employees. I will always type names of columns entirely in lower case, example: employee_id.
• PUNCTUATION. Spaces should separate words and literals. In some statements you will see other punctuation such as commas or parentheses. They are mandatory. I will always use a semicolon to mark the end of a statement.

It's time for your first exercise. I will introduce all exercises with the words ">> Enter an SQL statement:" and you must type the statement that follows, as far as the semicolon, exactly as shown.

Make sure that your input is going in the box labelled "SQL Command:".

When you finish typing, click the Enter key or the Execute button or the menu shortcut Ctrl-E. You will see the word OK appear in the diagnostic box. The program also removes the words "CONNECT TO 'ocelot';" from the SQL Command: and puts them in the History box. The SQL Command: box will clear.

All these things happen when an SQL statement succeeds, so this means you have successfully connected to the sample database.

If you would prefer to enter statements by reading files rather than typing or pasting them -- something I don't recommend -- you can click here for a look at script files.

You won't be needing demo32.exe's advanced features, so this "get acquainted with the front end" section is at an end. In the next section, you can start getting acquainted with SQL itself.

Make And Manipulate A Small Table

You'll create a table with two columns, then insert two rows, then retrieve what you inserted, then update it, then destroy it. This is necessary. You must have some idea how databases are manipulated, before you can work on queries.

Create A Table With Two Columns

>> Enter an SQL statement:
CREATE TABLE Books (title CHAR(10), quantity INTEGER);
You just made a table named Books, and it has two columns. The first column is named title and it can contain up to 10 characters (that's what the 'CHAR(10)' means). The second column is named quantity and it can contain numbers which have no fractional quantities (that's what the 'INTEGER' means). There are no rows in this table yet.

Insert Two rows

>> Enter an SQL statement:
INSERT INTO Books VALUES ('SQL-92',5);
>> Enter an SQL statement:
INSERT INTO Books VALUES ('SQL-99',1);

You just put two rows in the Books table. You used a character literal for the first column because the title has data type = CHAR(10). You used a number literal for the second column because the quantity has data type = INTEGER.

Select and display

>> Enter an SQL statement:
SELECT title, quantity FROM Books;

The result area of the demo32 window fills up with a display of the table.

A SELECT's result is called a "result set". This result set has two rows, in two columns named title title and quantity. So you got out what you put in.

>> Click the Close Button on the bottom right of the display. (The Close Button will appear whenever there is a result set on display. You will have to click it before you can continue. I will not repeat this instruction every time I say "Enter an SQL Statement", I assume you will remember to click "Close" when you finish looking at results.)

Select certain rows

>> Enter an SQL statement:
SELECT title, quantity FROM Books WHERE quantity > 3;

Observe what the words "WHERE quantity > 3" have caused. The row in the display is the row where the equation (value in quantity column greater than 3) is TRUE. The symbol > means greater than. Other symbols are: = equals, >= greater than or equal, < less than, <= less than or equal, and <> not equal. Notice that last item: <> is the way to say not equal. If you're used to using != as in C, you must evolve.

Commit

>> Put your ear beside your computer's hard drive.

>> Enter an SQL statement:
COMMIT;

Hear that clunk? When you COMMIT, you are writing to disk. Before you COMMIT, all the changes you made to the database (the CREATE and INSERT statements) are tentative, you could have changed your mind. There is an optional switch to make commits happen automatically for every data change, but the switch is usually OFF in this DBMS.

Update

>> Enter an SQL statement:
UPDATE Books SET quantity = 1 WHERE title = 'SQL-92';

This is how you change a row that already exists. You are selecting a row, but the selection won't come back to you. The UPDATE statement takes the result set and changes the values. The diagnostic box will say "OK, 1 rows updated". Now let's SELECT again:

>> Enter an SQL statement:
SELECT title, quantity FROM Books WHERE quantity > 0;

You'll see that there are no 5s in the table now, only 1s.

Rollback

>> Enter an SQL statement:
ROLLBACK;

ROLLBACK is the reverse of COMMIT. Instead of making the recent change permanent, you are cancelling it.

>> Enter an SQL statement:
SELECT title, quantity FROM Books;

See the difference: the table is now back to where it was before the UPDATE. But the rows are still there. The ROLLBACK can only affect changes done since the last COMMIT.

Destroy

Now you'll destroy the rows in the table and the table itself. This cleanup will make it possible to re-run the exercises another time.

>> Enter an SQL statement:
DELETE FROM Books;

The DELETE statement eliminates all rows in the table. You could be selective by using a WHERE clause, but you want them all gone.

>> Enter an SQL statement:
DROP TABLE Books CASCADE;

DELETE only removes rows. DROP TABLE ... CASCADE destroys the table itself. That is, it reverses the CREATE statement.

>> Enter an SQL statement:
COMMIT;

You have to commit destructions just as you had to commit creations. Now the table that you created is gone irrevocably. In the next exercises you'll begin with a clean slate.

Query A Pre-Existing Large Table

SELECT From The Predefined Sample Tables

It's time to do some SELECT exercises with larger tables, but it would be tedious to do hundreds of CREATE and INSERT statements first. So you will use some already-existing sample tables. Ocelot created and filled three tables for you: EMPS (500 rows), DEPT (52 rows), and SDEPT (3 rows). You will start with some simple SELECTs just to see what the tables look like, and will progressively do more and more complex SELECTs.

The Emps table

>> Enter an SQL statement:
SELECT * FROM Emps;
"*" is shorthand for "all columns".

Get familiar with the Emps table by scrolling vertically (up and down the rows) or horizontally (forward and backward the columns), before you close the result set.

AND, OR, NOT

>> Enter an SQL statement:
SELECT surname, empnum, dept FROM Emps WHERE empnum < 15 AND (dept = 'B' or dept = 'C') AND NOT surname = 'JONES';

The idea here is that you can combine various search conditions using AND / OR, with parentheses. I don't like to say AND NOT SURNAME = 'JONES' I prefer to say AND SURNAME <> 'JONES'.

ORDER

The keywords ORDER BY, then one or more column names, cause sorting.

>> Enter an SQL statement:
SELECT surname, empnum FROM Emps ORDER BY surname, empnum DESC;

You sorted first by surname, then within that (where the surname values are equal) by empnum. The word DESC stands for "DESCENDING", that's why ABEL-189 precedes ABEL-184.

Numbers

SQL allows three "approximate numeric" data types (example: FLOAT), and four "exact numeric" data types (examples: INTEGER and DECIMAL). The deduction and rate columns were created with DECIMAL(6,2), that is, they are 6-digit numbers but they have two positions after the decimal point.

>> Enter an SQL statement:
SELECT 1 + 5 * 2, deduction, rate, (deduction - rate) / 2 FROM Emps;

To do arithmetic, combine column names, literals, parentheses if necessary, and the arithmetic operators + / - *.

Characters

SQL allows one fixed-length character data type (CHAR), one variable-length character data type (VARCHAR), and one long-variable-length character data type (CLOB). Alternate names are CHARACTER, CHARACTER VARYING, and CHARACTER LARGE OBJECT. Several Emps columns are either CHAR or VARCHAR.

>> Enter an SQL statement:
SELECT SUBSTRING(gname FROM 1 FOR 1) || '$' || LOWER(surname) || CAST(empnum AS VARCHAR(5)) FROM Emps;

"Take a substring of gname starting at position 1 for length 1 (that is, take the first character of gname). Concatenate -- double bar || is the symbol for concatenate -- a character literal '$'. Concate surname after converting it to lower case. Convert empnum, which is an INTEGER, into a VARCHAR, and concatenate that too."

>> Enter an SQL statement:
SELECT address FROM Emps WHERE address SIMILAR TO '[:DIGIT:]_M%';

You can use SIMILAR TO for patterns with wild cards. The wild cards in the above example are [:DIGIT:] ("any digit"), _ ("any single character"), and % ("any series of zero more characters").

Dates

>> Enter an SQL statement:
SELECT CURRENT_DATE + INTERVAL '10' DAY, DATE '2001-02-18' FROM Emps WHERE empnum = 333;

You will see a different value for the first expression because you are reading this on a later "current date". The cumbersome-looking format for a date literal -- DATE 'yyyy-mm-dd' -- is mandatory. DATE and INTERVAL are separate data types.

Times

>> Enter an SQL statement:
SELECT CURRENT_TIME + INTERVAL '2' HOUR, TIME '13:14:15' FROM Emps WHERE empnum = 444;

A time literal has the form TIME 'hh:mm:ss' and a time INTERVAL literal has the form INTERVAL 'n' HOUR or INTERVAL 'n' MINUTE or INTERVAL 'n' SECOND. Most arithmetic is a matter of adding or subtracting intervals from times.

Set Functions

>> Enter an SQL statement:
SELECT COUNT(empnum), MAX(surname), MIN(gname), SUM(taxcode), AVG(rate) FROM Emps;

GROUP BY

>> Enter an SQL statement:
SELECT prov, COUNT(empnum) FROM Emps GROUP BY prov;

Subqueries

>> Enter an SQL statement:
SELECT manager FROM Dept WHERE dept IN (SELECT dept FROM Emps WHERE surname = 'WALSH');

Here I have a SELECT within a SELECT. In concept, the inner SELECT happens first and returns some dept values in the Emps table. Then the outer SELECT finds those values in the Dept table which have a value that matches any of the Emps results. The question could be phrased as: "who is the manager of the department that WALSH is in?".

Join

>> Enter an SQL statement in the classic style:
SELECT Dept.dept, manager, duty, empnum, surname FROM Dept, Emps WHERE Dept.dept = Emps.dept AND surname = 'WALSH';

This is a join of two tables. Notice that the row that results has columns from both Dept and Emps. The WHERE clause is establishing this criterion: "combine the rows where the department, identified by dept, is the same the department that WALSH is in". The result is the same as in the previous exercise, but now you can get columns from both tables (with a subquery you can only get columns from the outermost SELECT). On the other hand, it looks more complex than the subquery example. Also, you had to use a new way to identify the dept column: since both tables have a column with this same name, you had to resolve the ambiguity by putting the table name first, then a period, then the column name, as in "Dept.dept" or "Emps.dept". This is called qualification.

That's the classic join syntax.

>> Enter an SQL statement in the new style:
SELECT Dept.dept, manager, duty, empnum, surname FROM Dept JOIN Emps USING (dept) WHERE surname = 'WALSH';

That's the new join syntax. It gives the same results as the classic syntax.

Set Theory

Tables are sets. In set theory, sets can interfere with each other in three classic ways:

• INTERSECT. Includes all rows which are in both sets. >> Enter this SQL statement:
  SELECT surname FROM Emps WHERE surname > 'AO' INTERSECT SELECT surname FROM Emps WHERE surname < 'BA';

  SURNAME

  ARMSTRONG

  ARMBRUSER

  ASTAIRE

• EXCEPT. Includes all rows which are in the first set but not the second. >> Enter this SQL statement:
  SELECT surname FROM Emps WHERE surname < 'AC' EXCEPT SELECT surname FROM Emps WHERE surname = 'AARON';

  SURNAME

  ABBEY

  ABERNATHY

  ABEL

• UNION. Includes all rows which are in either set. >> Enter this SQL statement:
  SELECT surname FROM Emps WHERE surname < 'AB' UNION SELECT surname FROM Emps WHERE surname > 'ZY';

  SURNAME

  AARON

  ZYTARUK

Access information_schema

>> Enter an SQL statement:
SELECT * FROM information_schema.tables;

You'll see a whole bunch of rows come out. Where did they come from? Answer: the DBMS always maintains a "table of the tables" or "meta-table". There are quite a few tables in the list, you can SELECT from any of them.

It's a hallmark of a real DBMS that it contains a database of itself. With standard SQL, such metadata is always in information_schema. (A schema is a grouping of objects such as tables and columns.)

Constraints

I don't want to deal in this tutorial with "database administration" features that might not affect you directly.

I will, though, touch on the subject of constraints. You might not make these yourself, but they affect you, and they're an important part of the SQL idea, so let's see what's up with constraints.

The following SQL statements define the sample OCELOT database:
CREATE TABLE SDEPT ( DEPT VARCHAR ( 04) , MANAGER VARCHAR ( 12) , CODE DECIMAL ( 02) ) ;
CREATE TABLE DEPT ( DEPT VARCHAR ( 04) , MANAGER VARCHAR ( 12) , DUTY VARCHAR ( 12) , BUDGET DECIMAL ( 10,02) ) ;
CREATE TABLE EMPS ( EMPNUM DECIMAL ( 03) , DEPT VARCHAR ( 04) , SURNAME VARCHAR ( 12) , GNAME VARCHAR ( 09) , ADDRESS VARCHAR ( 18) , CITY VARCHAR ( 10) , PROV CHAR ( 02) , PC VARCHAR ( 06) , PHONE CHAR ( 08) , RATE DECIMAL ( 06,02) , TAXCODE DECIMAL ( 02) , DEDUCTION DECIMAL ( 06,02) ) ;
ALTER TABLE DEPT ADD CONSTRAINT DEPT_PKEY PRIMARY KEY ( DEPT ) ;
ALTER TABLE EMPS ADD CONSTRAINT EMPS_PKEY PRIMARY KEY ( EMPNUM ) ;
ALTER TABLE EMPS ADD CONSTRAINT EMPS_FKEY_DEPT FOREIGN KEY ( DEPT ) REFERENCES DEPT ;
(These structural definitions are also in the "metadata" and can be found in Ocelot's main database file, c:\ocelot\cluster.) I will concentrate on the final lines, the definitions of primary keys and foreign keys.

>> Enter an SQL statement:
INSERT INTO Dept VALUES ('A','KLEIN R','GOVERNMENT',10000000.00);
The statement fails! Look at the diagnostics box, and you'll see the message: [OCELOT][OCELOT]Integrity constraint violation: Unique / Primary Key constraint <DEPT_PKEY>, on base table <DEPT> -- attempted violation by an attempted key duplication

The DEPT_PKEY constraint says that the dept column is a primary key. One of the two big features of a primary key is: it can't be duplicated. And there is a dept which has dept = 'A'. So there.

>> Enter an SQL statement:
UPDATE Emps SET dept = 'VM';
The statement fails! Look at the diagnostics box, and you'll see the message: [OCELOT][OCELOT]Integrity contraint violation: Foreign constraint <EMPS_FKEY_DEPT> attempted violation -- there are <0> rows in the primary-key table which match the new foreign-key value What this means is: if you want to assign an employee to a department, you must first ensure that the department exists. The EMPS_FKEY_DEPT constraint says that Emps.dept is a foreign key, which references the primary key of Dept.dept. (That's the second big feature of a primary key -- it can be referenced by a foreign key.) In other words, you would have to insert some data for department 'VM' first, before trying such an update. Or maybe you just spelled something wrong.

You should appreciate these constraints. They ensure that your database will be harder to ruin with bad or inconsistent data.

Program

Traditionally, people have written programs in another language (like C or Pascal or Java) and called SQL routines from there. I just want to illustrate what such programs look like, without having to explain every detail of the SQL Call Level Interface (SQL/CLI and its variant, ODBC). So I'll just dump a sample C program here. If you understand some of it, fine. If not, skip to the next section.

/* SAMPLE2.C

One of a series of short SQL example programs in the OCELOT package.

This is an ODBC-SQL program which uses static SQL.

*/

#include <stdio.h>
#include <stdlib.h>
#include <mem.h>
#include <string.h>

#include "sqlcli.h"
#include "windows.h"

extern unsigned _stklen = 32500;

void main ()
{
HENV henv; /* We fill this in with SQLAllocEnv */
HDBC hdbc; /* We fill this in with SQLAllocConnect */
HSTMT hstmt; /* We fill this in with SQLAllocStmt */
SDWORD s1_in,s1_out; /* This has a value for column T.S1 */
UCHAR s2_in[6],s2_out[6]; /* This has a value for column T.S2 */
SDWORD s2_out_length;
UCHAR create[]="CREATE TABLE T (S1 INT,S2 VARCHAR(5))";
UCHAR insert[]="INSERT INTO T VALUES (?, ?)";
UCHAR select[]="SELECT S1,S2 FROM T";
SDWORD sqlcode;
char sqlcode_after_ltoa[7];
char sqlmess_[255];
char sqlstate[]="xxxxx";
char message_box_text[255];
SQLRETURN sqlreturn;

/* Allocate environment and connection handles; then connect to data source.
These statements are the typical initialization of any ODBC program. */
if (SQLAllocEnv(&henv)<0) goto error;
if (SQLAllocConnect(henv,&hdbc)<0) goto error;
if (SQLConnect(hdbc,"OCELOT",SQL_NTS,"OCELOT",SQL_NTS,"",SQL_NTS)<0) {
goto error; }

/* Allocate statement handle. In the body, we always will work with hstmt. */
if (SQLAllocStmt(hdbc,&hstmt)<0) goto error;

/* Create a table. The create string has CREATE TABLE T (S1 int,S2 CHAR(5) */
if (SQLExecDirect(hstmt,create,SQL_NTS)<0) goto error;

/* Prepare insert. The insert string has INSERT INTO T VALUES (?,?) */
if (SQLPrepare(hstmt,insert,SQL_NTS)<0) goto error;

/* Set parameter #1. First we show an old way (commented out) using the dated
function SQLSetParam. SQL_C_LONG corresponds to S1 datatype=INT. The
current SQL/CLI way, following, uses SQLBindParameter.
We employ a convention here: 00 instead of 0 for "don't care". */
/* if (SQLSetParam(hstmt,1,SQL_C_LONG,SQL_INTEGER,0,0,&s1_in,0)<0)
goto error; */
if (SQLBindParameter(hstmt,1,SQL_PARAM_MODE_IN,SQL_C_LONG,SQL_INTEGER,
00,00,&s1_in,00,0)<0) goto error;
s1_in=15;

/* Set parameter #2: SQL_C_CHAR corresponds to S1 datatype=CHAR. This time we
show in a one-parameter-per-line style; perhaps this makes it clearer. */
sqlreturn = SQLBindParameter(
hstmt, /* SQLHSTMT hstmt */
2, /* SQLSMALLINT ParameterNumber */
SQL_PARAM_MODE_IN, /* SQLSMALLINT InputOutputMode */
SQL_C_CHAR, /* SQLSMALLINT ValueType */
SQL_CHAR, /* SQLSMALLINT ParameterType */
sizeof(s2_in)-1, /* SQLINTEGER ColumnSize */
00, /* SQLSMALLINT DecimalDigits */
s2_in, /* SQLPOINTER ParameterValue */
00, /* SQLINTEGER BufferLength */
0); /* SQLINTEGER *StrLen_Or_Ind */
if (sqlreturn<0) goto error;
strcpy(s2_in,"JIM");

/* Execute the prepared insert. This inserts (15,'JIM') into table T. */
if (SQLExecute(hstmt)<0) goto error;

/* Do a select. The select string has SELECT S1,S2 FROM T. */
if (SQLExecDirect(hstmt,select,SQL_NTS)<0) goto error;

/* Bind column #1. SQL_C_LONG corresponds to S1 datatype=INT. */
if (SQLBindCol(hstmt,1,SQL_C_LONG,&s1_out,0,0)<0) goto error;

/* Bind column #2. SQL_C_CHAR corresponds to S2 datatype=CHAR. */
if (SQLBindCol(hstmt,2,SQL_C_CHAR,s2_out,sizeof(s2_out),&s2_out_length)<0) {
goto error; }

/* Fetch. This gets the "next" in the result set, which is what we put in. */
if (SQLFetch(hstmt)<0) goto error;

/* Check that what came out is the same as what went in. */
if (s1_in!=s1_out ||strcmp(s2_in,s2_out)!=0) {
MessageBox(0,"Input <> output.","Ocelot Sample #2",MB_OK); }

/* End the transaction which began when we inserted. */
if (SQLEndTran(SQL_HANDLE_DBC,hdbc,SQL_ROLLBACK)<0) goto error;

/* Free the statement handle. */
if (SQLFreeStmt(hstmt,SQL_DROP)<0) goto error;

/* Disconnect, free connection, free env. This reverses the initialization
steps at the start of the program, in reverse order. */
if (SQLDisconnect(hdbc)<0) goto error;
if (SQLFreeConnect(hdbc)<0) goto error;
if (SQLFreeEnv(henv)<0) goto error;

MessageBox(0,"Program completed with no errors.","Ocelot Sample #2",MB_OK);

exit(0);

error:
strcpy(message_box_text,"Program aborting with an error.");
SQLError(henv,hdbc,hstmt,sqlstate,&sqlcode,sqlmess_,sizeof(sqlmess_),0);
strcat(message_box_text," sqlstate=");
strcat(message_box_text,sqlstate);
strcat(message_box_text," sqlmess_=");
strcat(message_box_text,sqlmess_);
strcat(message_box_text," sqlcode=");
ltoa(sqlcode,sqlcode_after_ltoa,10);
strcat(message_box_text,sqlcode_after_ltoa);
MessageBox(0,message_box_text,"Ocelot Sample #2",MB_OK);

exit(1);
}

I encourage you to look for all the files with a .c or .java or .pas extension on c:\ocelot. The Pascal example will especially awe you, since the code of demo32.exe -- the front end that you've been using -- is all there as Delphi source.

Configure A Client/Server Network

For the network exercises you need to run a SERVER and two CLIENTs. So that the setup is less tricky, you'll run them all on the same computer. First you'll exit from the current "standalone mode" demo32.exe. Then you'll run setup.exe again and this time specify that you want "client/server mode". Then you'll start up demo32.exe (the first client). Then you'll start up demo32.exe again (the second client). I will show how the clients can affect each other, but the server will resolve the problems. Since the Latin word for "run together" is concurrere, and you'll have two clients running together, I call these problems "concurrency problems". The server resolves concurrency problems using Isolation Modes.

>> Enter an SQL statement:
ROLLBACK;
This stops the current transaction.

>> Enter an SQL statement:
DISCONNECT 'OCELOT';
Remember the first thing you did was CONNECT. This reverses the connect. Your standalone session is now over.

Now it's time to get out of DEMO32.EXE. Do that by choosing File | Exit on the menu bar or by hitting the little "X" box on the top right of the window.

Run c:\ocelot\setup.exe. This should be easy: you ran setup.exe before. You will see the same screens as before, and will click the same buttons. But there will be two differences.

The first difference is: after you click "Continue" on the "Choose Directory:" screen, setup.exe will warn you that there is already a copy of the DBMS, you're overwriting it. That's okay, so click the OK button.

The second difference is: when you reach the "Networks" screen, you want to choose a non-default setting for client/server. In the area headed "Mode and Protocol", click the radio button for "Local". (The exercise might work if you choose "TCP/IP" but TCP/IP can be tricky.)

When you finish running setup.exe, start the server.
>> Run c:\ocelot\server32.exe
You'll see a window that looks approximately like this:

This is just a monitoring window.

>> Run c:\ocelot\demo32.exe

>> Run c:\ocelot\demo32.exe

Let's say the demo32.exe window on the left is "client 1" and the demo32.exe window on the right is "client 2". The essential thing in the following exercise is to execute each SQL command IN ORDER. Thus, after entering "CONNECT TO 'OCELOT';" on the client 1 window, use the mouse to switch to the client 2 window and enter "CONNECT TO 'OCELOT';" on the client 2 window, then use the mouse to switch back to client 1, and so on.

The SERIALIZABLE Exercise

STEP NUMBER	CLIENT 1	CLIENT 2
1	CONNECT TO 'OCELOT';
2		CONNECT TO 'OCELOT';
3	SELECT * FROM Emps;
4		SELECT * FROM Emps;
5	ROLLBACK;
6		ROLLBACK;

Do not be alarmed when client 2 hangs in step 4! Client 2 is waiting for client 1 to finish. When you switch to the client 1 window and enter "ROLLBACK;" you are ending the transaction. Transactions end with either COMMIT or ROLLBACK.

You can get rid of that hang by reducing the isolation level. The SERIALIZABLE isolation level -- what you've just experienced -- is the default because it's absolutely secure. However, there are several optional isolation levels which allow more concurrency. That is, they reduce the hangs by increasing the security risk. You will experiment with just one of these lower isolation levels.

No doubt you observed the error message in step 5: [OCELOT][SERVER]CLI specific: Locked. This should only occur if (a) system is in multi-user mode (b) transaction type = READ COMMITTED or REPEATABLE READ (c) attempt to change what another job has read, or attempt to read what another job has changed . At the REPEATABLE READ isolation level, simultaneous SELECTs are okay but there is a prohibition on updating a row that another job is reading, or vice versa. At lower isolation levels, even that prohibition is lifted.

Now close both copies of demo32.exe, and close server32.exe (in all cases this is possibly by merely clicking the close button on the caption bar on the top right of the window.

Sample An ODBC Application

• On the "ODBC DRIVER" screen, click both the box that says "Allow use as ODBC driver" and the box that says "Allow non-standard ODBC quirks". When these boxes are checked, OCELOT will be in the Windows registry and some non-standard SQL syntax will be permitted.
• On the "Networks" screen, click "Standalone". You won't be needing client/server mode again.

ODBC 2:
Click on "Control Panel".

ODBC 3:
Double click the "ODBC Data Sources" icon.

ODBC 4:
The "ODBC Data Source Administrator" appears. Click the folder tab for "System DSN". Click the "Add" button.

ODBC 5:

ODBC 6:
The next dialog box says "Ocelot DBSETUP.DLL (ODBC Setup)" and "Fill in data source name, for example OCELOT". In the "Data Source Name:" edit box, enter: OCELOT. Click the OK button.

ODBC 7:
The "ODBC Data Source Administrator" appears again. Notice that OCELOT is in the list now. Click the OK button. The ODBC Administrator will finish.

>> Run:
c:\winnt\msapps\msquery\msqry32.exe
The exact location is uncertain; for example if you have Windows95 then the system directory is probably not "winnt". You might have to run the Find utility to find msqry32.exe. The reasons I picked msqry32.exe were: (a) it's a third-party program (b) you're almost certain to have it on your machine already, (c) it uses ODBC32. Although msqry32.exe is buggy, this seemed the best way to make an example.

ODBC 8:
On the tool bar below the main menu, click the leftmost button which is "New Query".

ODBC 9:
A "Select Data Source" dialog box will appear. Choose OCELOT. Click the Use button.

ODBC 10
An "Add Tables" dialog box will appear, giving you a choice of: DEPT, EMPS, SDEPT. Choose EMPS. Click the Add button. Click the Close button.

ODBC 11
Now, concentrating on the client window which is labelled "Query1", observe that there is a list of the columns in EMPS. Double-click on the top item in the list, which is a "*".

This has been a typical example of the use of an ODBC application (in this case Microsoft Query) to connect with an ODBC data source. The remarkable thing is that you told Microsoft Query nothing except the data source -- it figured out the table definition and contents by silently querying THE OCELOT SQL DBMS.

Exit from Microsoft Query by clicking File|Exit on the main menu. If a dialog box appears asking "Save changes to Query1?", click No.

Clean Up

And now to uninstall. There are two ways:

Way 1: From the taskbar Start, choose Settings. Choose Control Panel. Double-click Add/Remove Programs. Scroll down the list till you see the word "OCELOT". Select it.

Click the "Add/Remove" button. When the "OCELOT Uninstaller" dialog box appears, see below.

Way 2: From the Start on the taskbar, run:
c:\ocelot\uninst.exe
When the "OCELOT Uninstaller" dialog box appears, see below.

Either way "Way 1" or "Way 2" will lead to a dialog box titled "OCELOT Uninstaller". Click OK.

There is nothing left now. The programs and the sample databases are gone. If the install changed the registry, it has been restored.

Your hour is now up. The SQL tutorial is over.

I think you've accomplished what I promised at the start: you know what SQL can do now. Alas, there is much more to learn. I suggest that you buy a good book. That's not easy, because there are many bad books. To help you choose, Ocelot keeps a list of the current SQL books, with links to sample chapters and reviews, at the SQL Book Reviews page.

And please tell your friends to browse Ocelot's site or link to this tutorial.