Introduction to SQL - Part 1 of 2

Like so many computer products, it originated with IBM. Nowadays there are many easy-to-use products for the analysis of data; however they nearly all use SQL "behind the scenes", so familiarity with the basic concepts may be helpful.

Important new words are italicised when they are first used.

1. STATEMENT OF USE

• You may have already used it!

MS Access, Q+E were early examples.

• 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!).

• Other Clauses are optional

Must always have SELECT & FROM.

• To 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 of SQL.

• 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 are 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 an ORDER Clause

See above.

to be concluded ...