Thursday, 2 November 2006

SQL 101 1: Intro / Overview

This is the first in a series of posts designed to teach somebody the basics of SQL. It is adapted from a training course I originally wrote around 10 years ago, revamped again in 2000 and then again in 2006. The syntax has been tested against Oracle but in the vast majority of cases should work fine against any version of SQL. Have Fun !

Information is data organised in a way that is useful. What we give you within database software is Information, what we actually store is Data.

Obviously the data is not stored in a totally random fashion. The data is stored in a structured fashion, this structure is represented by Tables and Columns.

A Table is a collection of related data. For example the table F_CUSTOMER contains all the Customer data. The table F_SUPPLIER contains all the Supplier data. A column represents a particular portion of the data, for example a column within the customer table might be called CUS_NAME. This column will contain the customers name. You can liken this structure to a spreadsheet worksheet, where each row represents a single customer and each column represents a particular type of data stored. See this diagram for clarification.

For this tutorial we are going to be concentrating on using a SQL Query Tool to retrieve data from the database and present it to you on your screen.

‘SQL’ is an abbreviation for ‘Structured Query Language’. It provides a programming language for accessing selected records from a relational database. SQL often (and possibly incorrectly) pronounced ‘Sequel’ is actually pronounced ‘ess que ell’. This confusion stems from the fact that ‘SQL’ is the successor of a language called ‘Sequel’ developed by IBM in the late 1960’s.
For even the most simple of database inquiries there are three elements that you must communicate to the system to perform the inquiry:
You must specify the Table or Tables that you wish to search.
You must specify the Columns of data you wish to retrieve.
You must specify the search conditions for the data you require.
In SQL this is achieved using the SELECT ..  FROM .. WHERE ..  clauses.
The SELECT clause is to tell the system which Columns you want to select for the inquiry. The FROM clause is used to tell the system which Table contains the records which are to be selected. The WHERE clause is used to specify the conditions that must be met for a record to be selected.
These three clauses are all that is a necessary to perform a simple query from the SQLQuery Engine. The system reads the specified Tables(s) and checks each row against the condition, if the condition is met the selected records are returned into the Results Table.
Another feature of SQL is the ability to request not the contents of the selected fields, but a function of those contents, such as the average value, sum total, maximum or minimum value, or just a count of the number of records that meet the selection criteria.

"Aim at the sun, and you may not reach it; but your arrow will fly far higher than if aimed at an object on a level with yourself." - Joel Hawes

No comments: