SQL Primer

Print-friendly Version

Training Index

[Table of Contents]

This section is meant to serve as a SQL refresher to help you along with the exercises. It is not meant to be a tell-all resource for SQL. It takes you through the basic commands necessary for CRUD operations.

• C - Create
• R - Read
• U - Update
• D - Delete

Creating Tables

Use the CREATE TABLE statement when you want to create a table. Because creating tables is such an important operation, it only requires minimum conformance. However, some datasources, such as Text ODBC sources, only support the simplest column elements, with little or no constraint support.

CREATE TABLE <table name>
  (<column element> [, <column element>]...)

A column element is of the form:

<column name> <data type>
   [DEFAULT <expression>]
   [<column constraint> [, <column constraint>]...]

A column constraint is of the form:

NOT NULL |
  UNIQUE |
  PRIMARY KEY

Example:

CREATE TABLE java (
   version_name varchar (30),
   major_version int,
   minor_version int,
   release_date date);

Use the DROP TABLE statement when you want to drop a table. Like CREATE TABLE, it only requires minimum conformance.

DROP TABLE <table name>

Accessing Columns

Use the SELECT statement when you want to retrieve a set of columns. The set may be from one or more tables, and you can specify the criteria to determine which rows to retrieve. Most of the available clauses are available with minimum conformance. Additional capabilities are available with the core grammar.

SELECT [ALL | DISTINCT] <select list>
   FROM <table reference list>
   WHERE <search condition list>
   [ORDER BY <column designator> [ASC | DESC]
          [, <column designator> [ASC | DESC]]...]

The select list usually contains a comma-separated list of columns or an '*' to select all of them.

SELECT version_name, release_date from java;

If your driver supports core compliance, you can also use the GROUP BY, HAVING, and UNION clauses.

Storing Information

Use the INSERT statement when you want to insert rows. It too can provide different capabilities depending upon the conformance level supported.

INSERT INTO <table name>
  [(<column name> [, <column name>]...)]
  VALUES (<expression> [, <expression>]...)

For example:

INSERT INTO java VALUES
  ('2.0Beta', 2, 0, 'Aug-1-1997');

If the core grammar is supported, you can use a SELECT clause to load multiple rows at a time.

Use the UPDATE statement when you want to update rows. It only requires the minimum grammar.

UPDATE <table name>
   SET <column name = {<expression> | NULL}
    [, <column name = {<expression> | NULL}]...
   WHERE <search condition>

Use the DELETE statement when you want to remove rows. It only requires the minimum grammar.

DELETE FROM <table name>
   WHERE <search condition>

Resources

Some web-based resources:

• Access FAQ
• dbANYWHERE FAQ
• Oracle FAQ

and books:

• Teach Yourself SQL in 14 Days by Bryan Morgan and Jeff Perkins (Sams Publishing ISBN 0-67230-855-X)
• Understanding the New SQL: A Complete Guide by Jim Melton and Alan Simon (Morgan Kaufman ISBN 1-55860-245-3)
• LAN Times Guide to SQL by James R. Groff & Paul N. Weinberg (McGraw-Hill ISBN 0-07882-026-X)

Copyright © 1996 MageLang Institute. All Rights Reserved.