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 langauge 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


Key CombinationSyntaxComment
Data ManipulationSELECT 
 INSERT 
 UPDATEModifies data that is currently in a table
 DELETE 
 UNION 
 PARAMETER 
 TRANSFORM 
Data Retrieval -   
 INTO variable 
 FROM table_name 
 CREATE VIEW view_nameCreates a view based on a table
 AS SELECT table_column 
 FROM table_name 
 WHERE condition 
Data Definition - sets up, changes and removes data structures from tablesALTER TABLE table_nameRemoves a constraint from a table
 DROP CONSTRAINT constraint_name 
 ALTER TABLE table_nameAdds a new column to a table of the specified datatype
 ADD table_column datatype 
 ALTER TABLE table_nameChanges the datatype of a column to a new datatype
 MODIFY table_column 
 CREATE TABLE table_name(Creates a table with constraints
  Table_column1 datatype 
  Table_column2 datatype 
  CONSTRAINT constraint_name ...) 
 TRUNCATE TABLE table_nameDeletes a table and all its contents
 RENAME table_name1 TO table_name2Renames a table to a different name
  Deletes rows from a table matching the condition
Data ControlGRANT 
 REVOKE 
 ADD 
Data TransactionsCOMMITpermanently saved back to the database
 ROLLBACK 
 SAVEPOINT 


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

  DescriptionComment
0Requireddigit 0-9plus & minus signs not allowed
9Optionaldigit 0-9 or space plus & minus signs not allowed
#Optionaldigit 0-9 or spaceplus & minus signs allowed (any additional blanks are removed)
LRequiredletter A- Z 
?Optionalletter A-Z 
ARequiredletter or digit 
aOptionalletter or digit 
&Requiredany char or space 
COptionalany char or space 
[ . ][ , ][ : , ; - / ]  decimal placeholder, thousand, date and time
<  all chars to lowercase
>  all chars to uppercase
!  causes the input mask to be displayed from right to left rather than left to right
\  displays the following character to be displayed as a literal


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(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 (*) characetr 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.




© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

Top

Next