 Structured Query
Language (SQL) has the advantage that is it is widely used across many packages and platforms; the only
disadvantages are space & time as behind the scenes it creates large temporary files.
Like so many computer products, it originated with IBM.
These structured notes
were originally developed as a hand-out to follow one-to-one teaching sessions; important new words are
italicised when they are first used.
1. STATEMENT OF USE
| •
You've probably already used it! |
MS Access, Q+E. |
| •
Basic unit of structure |
Statement
consisting of a number of Clauses.
|
|
•
Where found or where useful
|
Any extraction of data from
a Database; may be written "on the fly" from a command
line, but more usually contained in a Script file or
as part of the coding for a Report. |
|
•
Database is composite
|
One
or more Tables each of which consists of Records (Rows)
containing Fields (Columns). |
|
•
Conventions 1
|
KEYWORDS of SQL are best written in upper case,
user-defined Table and Column names are best written in lower case. |
|
•
Conventions 2
|
Indented
layout of Clauses makes Structure easy on the eye and kills complexity; but is not always shown
as such in the literature. |
|
•
The simplest (mandatory) Statement
|
SELECT
 column
FROM
table
|
| •
One of five kinds of Statement |
Data Manipulation Statement
(forget the others!). |
|
• Clauses are optional
|
Must
always have SELECT & FROM. |
|
• terminate a Clause
|
Often " ; " or END
|
2. BEING SPECIFICALLY USEFUL ...
| •
... Means being Conditional! |
SELECT
column
FROM
table
WHERE
condition |
| •
Simple format of Condition |
Column operator Value
with operator one of =, >, <, >=, <=, <> and Column
& Value are both of the same Data Type. |
| •
Data Types |
Usually text (character),
integer, numerical, date, and logical. |
|
• Dialects for platforms & packages
|
May
exclude or provide alternative operators and/or data types. |
|
• Expressions
|
Combinations of
Columns and Values(literals) separated by +,-,*, and /. Parentheses () may
be essential, or may be inserted in pairs to ensure clarity. |
| •
System Constants |
Expressions may also usually
use System Constants (eg TODAY, USER) but the availability
of these varies with the dialect. |
|
• Complex or more than one Condition
|
The logical connectives AND
OR NOT (either singly or combined) are used to link simple
or complex conditions. Parentheses () may be essential, or may be
inserted in pairs to ensure clarity. |
| •
More than one Column |
SELECT
column_list
FROM
table
The list is separated by commas,
preferably with each column name on a new line. In some
dialects it may also be possible to use Functions such
as COUNT() in column_list. |
3. KEEPING ORDER
|
• Last Clause in Statement
|
Usually
coded with most significant column first, eg column1,
column2 but to be described with the least significant first
(column2 within column1). |
| •
Must match GROUP Clause |
See below. |
4. GROUP THERAPY
| •
Mainly for Reporting |
Where do you want sub-totals? |
|
• GROUP Precedes ORDER
|
SELECT
column_list
FROM
table
WHERE
condition
GROUP BY
column3,
all_other_columns
ORDER BY
column3
|
|
• Need for ORDER Clause
|
See
above. |
5. MULTIPLE TABLES
| •
Method of specifying |
table_list
replaces table
in above example. |
| • Need for Join
Condition in WHERE
|
Corresponding fields in
different tables. |
| •
Types of join |
Expresses relationship
between records in Tables, eg 1-to-1, Many-to-1.
|
| •
Ways of referring to Tables
|
Aliases
convenient (can use to access another copy of same table !) thus
FROM
table1
alias1, table2 alias2, etc
which can also be written as
FROM
table1
AS alias1, table2 AS alias2, etc
|
| •
Ways of referring to Columns (Fields) |
Either
table1.column2,
table2.column2, etc
or
alias1.column2,
alias2.column2, etc
|
| •
Sometimes need for OUTER joins
|
Even
if no corresponding value in one of the tables; this is to be used
with care! |
6. MULTIPLE STATEMENTS
| •
Separation by semi-colon
|
SELECT
column1
FROM
table1
INTO TEMP tempname;
SELECT
column3
FROM
table2
|
| •
Usually only one ORDER Clause |
|
7. FOR THE CONNOISSEUR
| •
Construct |
The way of writing an SQL
Statement; limited only by your ingenuity. Once the principles
of SQL have been mastered it becomes an attitude or way of
life. |
| •Normalisation
|
A
process for ensuring that the Tables in the Database are in their
simplest and most effective form. |
| •
Use of package-specific documentation |
Check what Functions
are available.
|
| •
Stick to a convention
|
Use
of upper and lower cases and 3 spaces for indentation. Be prepared
to read other conventions - MSAccess uses aliases throughout
(with everything also enclosed in parentheses). |
|
•
Care with parentheses ()
|
Redundant
or missing parentheses are often a problem if they are nested; this
problem is most easily avoided by using an indented layout.
Coloured pencils may help! |
| •
Build and test slowly
|
Make
sure you are getting what you think, use abnormally restrictive
conditions for initial tests. |
|
•Database integrity
|
If you don't get what
you expect then it may not be your construct. How can you
test database integrity? By writing special SQL's of course!
|
| •
Editors for scripts
|
Any
ASCII editor will do, but it is worth while mastering vi
(used in UNIX environments) a course in which is available
in this series. |
|
•Further study
|
Mainly
practice, reading other people's scripts (including those produced
"automatically" by Q+E etc), keep "library" of good
or unusual constructs. |
| •
The worst that can happen?
|
You
might take up more than your fair share of the processor, or print a
boxful of paper (always print to screen first). |
8. VOCABULARY
| ; |
End of Statement |
| AND |
|
| FROM |
Clause name (Mandatory) |
| GROUP BY |
Clause name |
| HAVING |
Clause name |
| INTO TEMP |
|
| IS NULL |
|
| NOT |
|
| OR |
|
| ORDER BY |
Clause name |
| SELECT |
Clause name (Mandatory) |
| WHERE |
Clause name |
 
© 1996-2007 Systems Studio - all rights reserved
|
