News!
The site was at: http://cesspool.crseo.ucsb.edu:8679/TUT1/tut1.htm was out but is back though the
execute  query button no longer works! // Still there Feb19/2001

A Brief History of SQL   by Jean Anderson [ July 26,1995 ]

 minor revision May 28, 2001                                                                                                 edit by Peter Komisar

The Structured Query Language (SQL) is a language for accessing data in a relational
database. Originally created by IBM, many vendors developed dialects of SQL. Early in
the 1980's, The American National Standards Institute (ANSI) started developing a
relational database language standard. ANSI and the International Standards
Organization (ISO) published SQL standards in 1986 and 1987, respectively.

ANSI and ISO jointly worked on an extension to the standard called SQL2 or SQL-92 or
SQL/92, depending on who you read. A SQL3 effort is under way to enhance relational
capabilities and add support for object-oriented features. SQL has been through several
versions  and continues to evolve.

Version SQL/86 was mostly IBM's SQL dialect. SQL/89 added referential integrity.
Embedded SQL (C, ADA, etc.) was adopted as a separate standard in 1989. SQL2
includes embedded SQL, system catalogs and 'schemas', domains, more data types, and
conversions between types called 'casts'. SQL3 includes enhanced relational capabilities,
such as active rules or 'triggers', and support for object features, such as user-defined data
types and inheritance.

The SQL3 standard is important because, among a variety of new features, it allows defining
new data types. Historically, database vendors have invested the most product development
cycles towards meeting the needs of the business community. However, much science data
does not fit the "rows and columns" paradigm. And, in particular, the data do not fit the handful
of text and numeric types supported by relational databases. The SQL3 standard probably
won't be ratified for several years.

// SQL3 is here. Read the note below and also see end of note regarding support provided in JDBC 2.1
 
 

The Blob Editor note: In late 99, an example database company, Cloudscape, advertises  it is SQL/92  compliant Another authors notes that JDBC drivers must support ANSI SQL-2 Entry Level  Now JDBC 2.0 offers SQL3 support for data types like BLOBs (Binary Large Object)  and  CLOBs (Character Large Object) and other features An interesting coincidence, Jean Andersen's site references Illustra as supporting SQL3 features. Clicking on this site takes you to Cloudscape, the all Java database that is now included with  the enterprise edition of the JDK. .

SQL Parts of Speech                           fromJean  Andersen's tutorial

Data Manipulation Language
- the set of SQL commands affecting the contents of database objects

Data Definition Language
- the set of  commands affecting the structure of database objects

Data Manipulation Language (DML)
 

SELECT	Retrieves data from the database.
INSERT	Inserts rows into a table
UPDATE	Updates rows in a table.
DELETE	Deletes rows from a table.

Data Definition Language (DDL)
 

CREATE	Creates a new database object.
DROP	Drops an existing database object.
ALTER	Modifies the structure of a database object.

   // notice sql keywords are not case sensitive

SELECT

The SELECT statement retrieves data from the database and has six basic clauses:
 

Clause	Values	Description
SELECT	column(s)	Identifies which data to extract
FROM	table(s)	Identifies source for the data in the select list
WHERE	search criteria	Restricts the rows that are returned
GROUP BY	constraint(s)	Summarizes query results by groups
HAVING	column(s)	Restricts groups that are returned in group by clause
ORDER BY	column(s)	Sorts results by one or more columns in the SELECT clause.

Examples

SELECT  ...  FROM

-selects columns of data to return from which table

SELECT column_name1, column_name2  FROM  table_name

WHERE

The where clause contains search conditions, also called query predicates, that
restrict which rows are returned. Here is a small subset of the search predicates.
 

Predicate	Meaning	Predicate	Meaning
=	equal	<>	not equal
>	greater than	<	less than
>=	greater than or equal	<=	less than or equal
like	string pattern matching	between	match between two values

Example
This query counts all TOPEX grids generated from data earlier than midnight, January 1, 1994.

select count(*)                 // the count() function with a wildcard counts the number of rows in a column
from   Identification_Information
where  Native_Data_Set_Environment = 'oa grid'
and    Beginning < '1994-01-01 00:00:00';

GROUP BY   // group data to form subsets of the table's content

The group by clause groups rows by a column or columns.

Example
The next query counts the number of rows by Native_Data_Set_Environment:

select   Native_Data_Set_Environment, count(*)
from     Identification_Information
group by Native_Data_Set_Environment;

HAVING

The having clause restricts the groups that are returned.

Example
The next query counts the number of rows by Native_Data_Set_Environment,
but only returns those groups that have more than 25 rows:

select   Native_Data_Set_Environment, count(*)
from     Identification_Information
group by Native_Data_Set_Environment
having   count(*) > 25;

ORDER BY

The order by clause sorts by a column or columns, in two directions:

    asc: ascending (the default)
    desc: descending

Example
This query sorts the Contact_Information table first by organization, then by name:

select       Primary_Contact_Organization, Primary_Contact_Person
from         Contact_Information
order by   Primary_Contact_Organization, Primary_Contact_Person;

INSERT

This command inserts a single row of data into a table. The basic syntax is:

INSERT INTO table_name (column_name, column_name...)
VALUES (expression, expression...);

The number of values or expressions in the values clause must correspond to the number
and data types of columns in the insert clause. If no columns are listed, then values must
be provided for every column in the table.

UPDATE

This command updates data in a table. The basic syntax is:

UPDATE   table
SET           column_name = expression
WHERE    search condition;

example Ginger Ogle was a graduate student. When she graduated and became a staff
               programmer, her entry in the Contact_Information table was updated like this:

update Contact_Information                                                               // the table reference
set       Contact_Position = 'Staff Programmer'                      // set x, across to column
where  Primary_Contact_Person='Ginger Ogle';                              // set y, down to row

DELETE

- removes data from a table. The basic syntax is:

DELETE FROM table
WHERE search condition;

example The following statement would remove all AVHRR data from
               the Identification_Information table:

DELETE FROM   Identification_Information            //  table
WHERE   Data_Set_Id = 'AVHRR';

If there were no where clause, all data in the table would be deleted.

JOINS

All examples up to this point have queried a single table. But often information is
in multiple tables.A joins selects data from more than one table and combines the
results into a single result table based on some search criteria.

SELECT  A.books, B.isbn FROMbooks A, book_info B
WHERE  A.book_ID = B.book_ID

// example not from original reference
// outer joins include null finds in the returned result table

The following table gives you most of the story regarding how Java types translate to
their equivalent  JDBC SQL types. The full story is described in four tables describing
mapping Java Primitives to JDBC types (that is SQL types), a table showing the reverse
mapping then a table describing mapping Java Object types to SQL types and the reverse.
All four tables are needed as they are minor variations when the order is reversed. I include
the following supplemental link which gives you the full picture. It in turn has the link to a
definitive reference at the Sun site. SQLMapping.html

SQL data types which may be associated with Java types
 

Java type	JDBC
String	VARCHAR or LONGVARCHAR
java.math.BigDecimal	NUMERIC
boolean	BIT
byte	TINYINT
short	SMALLINT
int	INTEGER
long	BIGINT
float	REAL
double	DOUBLE
byte[]	VARBINARY or LONGVARBINARY
java.sql.Date	DATE
java.sql.TimeTIME	TIME
java.sql.Timestamp	TIMESTAMP

 

// Actually shows mapping of SQL to java types. Mapping java to SQL results in  a similar table
// but with some of the types left out  Overview of JDBC 2.1 Core API Changes