|
  |
|
|
Introduction to SQL - Part 1 of 2
Structured Query Language (SQL) has the advantage that is it is widely used across many packages and platforms; the main disadvantages are space & time as behind the scenes it creates large temporary files. 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 ...
|
Contributor's Note
The rather condensed format of these notes is because they were originally developed to be a hand-out for one-to-one teaching sessions. In the second part of this Intel these principles will be extended to cover more than one table (Relational databases).
|
|
PLEASE VISIT THE CONTRIBUTOR'S WEBSITE
No reactions yet.
Please login or sign up to rate this intel.
Please login or sign up to add a comment.
The copyright for this content entitled "Introduction to SQL - Part 1 of 2" has been specified by the contributor as:
All Rights Reserved
This content may not be copied, distributed or adapted by anyone under any circumstances.
|
 |
|
This intel was contributed by Roy Law
Roy Law
|
May, 2012
2008
January, February, March, April, May, June, July, August, September, October, November, December
2009
January, February, March, April, May, June, July, August, September, October, November, December
2010
January, February, March, April, May, June, July, August, September, October, November, December
2011
January, February, March, April, May, June, July, August, September, October, November, December
2012
January, February, March, April, May
|
|
Not a member yet?
Qondio is a powerful network for making it online. If you have a website to
promote, we can help.
Sign up and get in on the action.
|
|
Welcome to Qondio! Discover the awesome power this network can deliver by going to our About page. Or you could skip straight to the Sign Up form.
|
|
