SQL 101 7: SQL Background

This is the seventh and final post in my series designed to teach people the basics of SQL and it’s use in interrogating databases.

SQL Background Information
SQL is a declarative language, not a procedural language.  This effectively means that you tell the database what you want doing, not specifically how to actually do it. This is a similarity shared by several general fourth generation languages. Examples of procedural languages are Pascal, C++ and Basic, as well as old timers such as COBOL and FORTRAN.

The SQL Language consists of three parts:
                DDL:                Data Definition Language
                (Used to Design the tables and columns)
                DML:                Data Manipulation Language
                (Insert, Delete, Update & Query the database)
                DCL:                Data Control Language
                (Controls database security and permissions)

The DBA (Data Base Administrator) is concerned with the DDL & DCL, whereas the programmer or reasonably sophisticated end-user is responsible for the DML.

SQL differs from other procedural languages in that other languages handle data in FILES. A FILE is a container of data in which data is stored sequentially. There is a concept of last record and first record, this fits in well with the way files work in the real world with paper forms and filing cabinets and the like.

SQL however handles data in TABLES. A TABLE is a set, a set is a mathematical abstraction, sets are ordered and all of their members are of the same type. When you perform an operation on a set the operation happens all at once and to the entire membership. Tables can be permanent (base views) or virtual (views). An operation on an SQL table always returns a RESULTS TABLE.

Cardinality of a table
(The number of rows in a table, Zero or Greater)
Degree of a Table
(Number of columns in a table, One or Greater)

A foreign key is the column which links to another (primary) key in another table.

OR (When using an OR within the search string, the OR'd items must be bracketed for a successful search.)
NOT
AND
LIKE 'xxx%' %=wildcard of one or more characters, the like command is however CASE SENSITIVE.
IN
BETWEEN (= can be used for an exact match type search)

GROUP BY can be more than one field separated by a comma, both columns must also be in the actual columns displayed. DO NOT include a primary key in a GROUP BY command, as the primary key is unique. Fields other than the grouping fields cannot be in the display columns, as to do so would make zero sense.

NORMALISATION is the process where by a database designer attempts to minimise the amount of duplicate data stored within his database.

"If you have made mistakes, even serious ones, there is always another chance for you. What we call failure is not the falling down but the staying down." - Mary Pickford