SQL

SQL (Standard Query Language) is the standard data access language for relational databases
SQL statements are not case sensitive
SQL statements can be on more than one line
When using an & to input variables it must be in single quotes
Clauses are usually placed on separate lines and Tabs and Indents are normally used. Keywords cannot be abbreviated or split across lines and are in uppercase.
SQL statements can be split up into 3 components
The SQL language is made up of a series of keywords, statements and clauses.
These can all be combined to create queries that extract meaningful data from a database.

What is ANSI Standard ?

This stands for the American National Standards Institute

After all DDL statements an explicit commit is submitted (ie an auto COMMIT statement). This commit cannot be rolled back

Access - http://msdn.microsoft.com/en-us/library/aa164825(office.10).aspx
Access - http://support.microsoft.com/kb/252908
Access - http://support.microsoft.com/kb/225048
Access - introduction - http://www.reading.ac.uk/web/FILES/www_File_Library/Microsoft_Access_2003_beginners.pdf
http://support.microsoft.com/kb/q198503/

Relational Databases

A database is a collection of records and fields that are organised for a particular purpose.

Nearly all database management systems store and handle information using the relational database management model.
The term relational stems from the fact that each record in the database contains information related to a single subject and only that subject.
The term relation applies to a set of rows about a single subject.
In a relational database the information all the data is stored in tables.

You can join information on related values from multiple tables or queries.

Relation - Information about a single subject. A relation is usually stored as a table in a relational database.
Attribute - A specific piece of information about a subject. An attribute is usually stored as column or field in a table
Join - The process of linking tables and queries on tables via related data values.

What is Normalisation ?

Normalisation is a three-step technique used to ensure that all tables are logically linked together and that all fields in a table directly relate to the primary key.
This is the term given to efficiently organising the tables in a database by removing duplicate data and ensuring the correct data dependencies.
By normalising your database you not only make it more efficient but also help to reduce its size.
The database community has developed a series of guidelines for ensuring that databases are normalized.
These are referred to as normal forms and are numbered from one 1NF to five 5NF.

Phase 1
You must identify repeating groups of information and create primary keys.
To complete the first form of normalisation eliminate the Cashier ID and Cashier Name columns from the Products table. since they represent a separate group of information and would be better suited in another table.
Assign a primary key to the Products table.

Phase 2
You need to take another look at your column names to make sure that all columns are dependent on the primary key.
This involves eliminating columns that may be partically in the same group, but not totally dependent of the primary key.
The columns Order ID and Order Date should be removed and placed in a different table called Orders.

Phase 3
You need to reexamine the column and make sure each column is dependend on the primary key.
Consider creating additional tables to eliminate non-dependent primary key columns if necessary.

The Products table contains columns that are all dependent upon the primary key so nothing further can be done.

If you have created / generated an input form and want to change the input masks you must do it on the form itself
In VB Forms!Customers!Telephone.InputMask = "---"

Special Characters

Note: Setting it to "password" creates a password-entry control. Any character is stored as a literal but asterisks (*) are displayed
Note: When displaying data, the Format of the control takes precedence over how the data is stored (ie inputmask)

Examples - Inputmasks
>L<???????? = Maria, Brendan
(000)AAA-AAAA = (206)555-TELE

A validation rule checks the value entered and ensures the value doesn't violate this rule. In general a field validation expression consists of an operator and a comparison value.
The validation text is the message displayed if the data entered doesn't pass the validation rule
Note: If you do not include an operator then "equals" is assumed
You can specify multiple comparisons separated by the boolean operators OR and AND

Defining Relationships

Return to the Database Window, closing all table windows. Tools > Relationships [click all tables and select add if it's the first relationship in this database]
Typically a relationship will be "one to many", that means for one record in the first table, there are many related in the second table, but for one record in the second table there is exactly one matching matching record in the first table

Enforce Referential Integrity

Cascade Deleted related Records - If this is checked then Access deletes child rows ( the related rows in the "many" table of a one to many relationship) when you delete a parent row ( the related row in the one table)
Cascade Update Related Fields - If this is checked Access will automatically update any foreign key values in "child" tables (the "many" table in the one to many) if you change a primary key in a parent table
Note: Sometimes you might want to define relationships between tables and queries or between queries so that Access knows how to join them properly
A| lin eis drawn between to indicate a relationship
Place any column tables (with spaces) in square brackets

Access Datatypes / Field Sizes ????

TEXT
Byte
Integer
Long Integer
Single
Double
Replication ID
Decimal

SQL Differences

Concatenation is & and not ||
SQL Wildcards - % _

SQL Extensions
TRANSFORM - allows you to build crosstab queries
IN - allows you to specify a remote database connection or to specify column names in a cross tab query. This clause applies to all tables references. In the FROM clause and any subqueries in your query.
DISTINCTROW - to limit the number of rows returned from the tables to only rows that have different primary key values in the selected columns. Only rows in which the concatenation of the primary keys from all tables supplying output columns is unique.

SELECT       COUNT(*) 
FROM         ORDERS 
WHERE        ShipName = 'Hanari Carnes' 

SELECT       * 
FROM         ORDERS AS O 
INNER JOIN   CUSTOMERS AS C ON O.CustomerID = C.CustomerID 
WHERE        C.CompanyName = 'Island Trading' 

=--this works in both SQL Server and Access

SELECT       * 
FROM         PRODUCTS 
WHERE        ProductName IN ('Chocolade','Outback Lager') 

=--this works in both SQL Server and Access

SELECT       * 
FROM         PRODUCTS 
WHERE        UnitPrice = (SELECT MAX(UnitPrice) FROM PRODUCTS) 

=--this works in both SQL Server and Access

SELECT       * 
FROM         PRODUCTS 
WHERE        LEFT(ProductName,1) IN ('A','B')  

=--this works in both SQL Server and Access

SELECT       * 
FROM         PRODUCTS 
WHERE        RIGHT(ProductName,1) IN ('r')  

=--this only works in SQL Server becuase there is no SUBSTRING function in Access

SELECT       * 
FROM         PRODUCTS 
WHERE        SUBSTRING(ProductName,1,2) IN ('ch')  
WHERE        SUBSTRING(ProductName,1,2) IN ('CH')  

=-- not case sensitive

=-----------------------------------------------TERRITORIES table
=--this table does not exist in Access

SELECT       * 
FROM         TERRITORIES 
WHERE        ABS(RegionID) = 4 

--RAND() returns a random number between 0 and 1

SELECT       RAND(), * 
FROM         TERRITORIES 
WHERE        RegionID = RAND(4) 

--The following all work in SQL Server

SELECT       Employees.* 
FROM         EMPLOYEES 
WHERE        ReportsTo IS NULL 

SELECT       * 
FROM         EMPLOYEES 
WHERE        ReportsTo IS NOT NULL 

SELECT       * 
FROM         [EMPLOYEES] 

SELECT       "Address" 
FROM         EMPLOYEES 

SELECT       [EMPLOYEES].[Address] 
FROM         EMPLOYEES 

SELECT       [Address] 
FROM         EMPLOYEES 

SELECT       DISTINCT FirstName 
FROM         EMPLOYEES 

SELECT       Address 
FROM         EMPLOYEES 
WHERE        NOT Address is NULL 

--This only works in SQL Server as there is no (nolock) feature in Access

SELECT       * 
FROM         INVOICES (nolock) 

When using the "Group By" clause, whatever columns are not mentioned in the "Group By" portion of the query must have a group function on them
The "Having" clause allows you to search a search condition for a group of rows. The traditional where search condition works with an individual row, not a group of rows.
A correlation name is an alias name you assign to a table / view or query name in the FROM clause
If a table name or a query name is also a SQL reserved word (for example Order) then you must enclose it in brackets
When you list more than one table without a join criteria, the result is the cartesian product of all the tables
The difference between a HAVING clause and a WHERE clause is that WHERE is applied to single rows before they are grouped, while HAVING applies to groups of rows
Comparing two empty strings or an empty string with a special asterisk (*) character evaluates to TRUE
You must supply an alias name when you embed a SELECT statement in a FROM clause
If you are joining a table or a query to itself, you must use alias table names to clarify which copy of the table you are referring to
If you use a SQL total function in a SELECT statement then any other columns must be derived using a total function or the column name must appear in a GROUP BY clause
If the same column name exists in multiple tables then you must prefix the column name with the table name
If you give a table or view an alias name (or correlation name) then you must use this one when prefixing column names
RDMS stands for Relational Database Management Systems
The difference between a Join and a Union is that a Join selects columns from 2 or more tables where a Union selects rows
Normalisation is a design procedure for representing data in a tabular format. The five normal forms are progressive rules to represent the data with minimal redundancy
Foreign keys are attributes of one table that have matching values in a primary key in another table, allowing for relationships between tables
The difference between GROUP BY and ORDER BY is that GROUP BY controls the presentation of the rows, where as ORDER BY controls the presentation of the columns for the results of a SELECT statement
The EXPLAIN statement provides information about the optimizers choice of access path of the SQL
Referential Integrity refers to the consistency that must be maintained between primary and foreign keys, ie every foreign key value must have a corresponding primary key value
When inserting foreign keys they must match their corresponding primary key values in their related tables. Any updates of primary key values may require changes in foreign key values to maintain referential integrity
Static SQL is hard-coded in a program when the programmer knows the statements to be executed. Dynamic SQL has to allocate memory to receive the query results
Any subselect can be re-written as a join (but not vica versa). Joins are usually more efficient as join rows can be returned immediately. Subselects require a temporary work area for inner SELECT while processing the outer SELECT
When displaying the result of a SELECT statement the name of the selected column is used as the column heading. If this heading is not descriptive you can change the heading by using a column alias.