Access SQL

How one looks at the Access SQL is a bit different because the form using to different terminologies in visually building a query.

Append: This means use a 'INSERT INTO'

I need to learn more about how the graphical build tools work in Access.

In specifying the query using the graphical interface you must specify at least one 'destination field.

From the Access Query From one can select either

• Select Query
• Update Query
• Append Query

In addtion on can select the following additional queries.

• Crosstab Query
• Make-table Query
• Delete Query

In creating a query on can use the ‘Design View’ or one of the following query wizards:

• Simple Query Wizard
• Crosstab Query Wizard
• Find Duplicates Query Wizard
• Find Unmatched Query Wizard

DELETE FROM

The DELETE statement is used to delete given row or rows or all rows in a table or view.

DELETE [*] FROM table_name [WHERE column_name = some_value];

UPDATE

The UPDATE statement is used to modify the data in a given row or all rows of a table. If are no row in the table nothing will be done.

Syntax:

UPDATE table_name SET column_value [WHERE column_name = some_value];

column_value :: column_name = new_value[, column_name = new_value ]

LEFT JOIN

The LEFT JOIN returns all the rows from the first table even if there is not a match in the second table. Let say Stan has ordered 3 things and Sally none. You will have 4 rows because you will get the 3 things Stan ordered and you will also get a column for Sally even though she is not in the orders table. However, you will not get anything that Ken ordered because he is not in the first table.

Example Code:
CREATE TABLE Person (IDIs int, NameIs varchar(20));
INSERT INTO Person VALUES (1,'stan');
INSERT INTO Person VALUES (2,'Sally');
--INSERT INTO Person VALUES (3, 'Ken');
CREATE TABLE Orders (OIDIs int, PIDIs int, WhatIs varchar(20));
INSERT INTO Orders VALUES (1, 1, 'Moon');
INSERT INTO Orders VALUES (2, 1, 'Sun');
INSERT INTO Orders VALUES (3, 1, 'Stars');
INSERT INTO Orders VALUES (4, 3, 'Dog');
INSERT INTO Orders VALUES (5, 3, 'Cat');
INSERT INTO Orders VALUES (6, 3, 'Car');
SELECT Person.NameIs, Orders.WhatIs FROM Person
LEFT JOIN Orders
ON Person.IDIs=Orders.PIDIs;

INNER JOIN

INNER JOIN is used to return a result table that contains all the rows from two tables where there is a match between the specified keys.

On the MS site the syntax is talk about in relation to the SQL 'FROM' command
SELECT [column_list] FROM first_table INNER JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield;

Everything the 'FROM' is building kind-of-a 'INNER JOIN' table. The 'FROM' command is just some table that the SQL is looking at.

The following example is done using the SQL 2000 Northwind database.

Example Code:

USE Northwind;
SELECT Employees.LastName, Orders.ShipName FROM Employees
INNER JOIN Orders ON Employees.EmployeeID=Orders.EmployeeID;

Using Inner Joins:

An 'INNER JOIN' returns all the columns from both tables. This can be seen by performing a 'SELECT * FROM' on a table. In Access the column that match will have the table name in from of them.

INSERT INTO

The INSERT INTO statement is a data manipulation language (DML) command. It inserts a new row into the table.

Syntax: INSERT INTO table_name [(column[, column])] VALUES (value[, value]);

See MS Doc: INSERT Transact-SQL

Example Code:

CREATE TABLE Person (LastName varchar(80), FirstName varchar(80));
INSERT INTO Person VALUES ('Hughes', 'Stan');
SELECT * FROM Person;

LIKE

The LIKE condition is used to specify a search pattern in a table column.

SELECT column FROM table WHERE column LIKE pattern

pattern :: '[% | _ | [] | [^] | character]'

The percent (%) is the wild card specifying zero or more characters of any values.

The case appears to be ignored.

The '[' is used as an escape character for special characters.

To find a string with a '[' in it you would used the following %[[]% pattern.



See LIKE (Transcat-SQL)

Column Data Types

The following system data types can be used in creating a table. This is also the data type precedence (see MS Doc):

• user-defined data types (highest)
  
• sql-variant
  
• xml
  
• datetime
• smalldatetime
  
• float
  
• real
  
• decimal
• money
  
• smallmoney
• bigint
• int
• smallint
• tinyint
• bit
• text
• image
• timestamp
• uniqueidentifier
• nvarchar
• nchar
• varchar
• char
• varbinary
• binary (lowest)
  

See MS documentation: Data Types (Transact-SQL)

char, varchar, binary, or varbinary can be combined into something with a length of 8,000 characters minus 1.

nchar, nvarchar expression will be smaller than 4,000.

See Precision, Scale, and Length (Transact-SQL)

Transactional-SQL

Here is the Microsoft documentation:

http://msdn2.microsoft.com/en-us/library/ms189826.aspx

Data Definition Lanaguage (DDL)

The data definition language (DDL) commands define the tables and their capabilities.

• CREATE TABLE
• ALTER TABLE
• DROP TABLE
• CREATE INDEX
• DROP INDEX

Data Manipulation Lanaguage (DML)

Data manipulation commands are commands that process the tables within a database.

• SELECT
• UPDATE
• DELETE
• INSERT INTO

UPDATE

UPDATE is one of the data manipulation language (DML) commands. It updates data in a database table.

SELECT

SELECT is one of the data manipulation language (DML) commands. It is used to extract data from a database table. The results of the selected are returned in a result table which can also be called the result-set.

Syntax: SELECT [DISTINCT] column_list FROM table [WHERE condition ][;]

The 'DISTINCT' command is used to return only distinct elements, or rows, in the result-set.

column_list :: * | column_name[, column_name]

column_name :: I am not sure at this point what defines a valid column name.

table :: this table can also be a result-set from some operation.

condition :: column_name operator_value [ conditional column_name operator ]
column ::

operator_value :: [= | <> | > | < | >= | <= | != ] value or [BETWEEN b_value | LIKE pattern| IN i_value ]

The '!=' may not be standard SQL.

b_value :: value AND value

pattern :: '[% | letter]+'

i_value ::
The semicolon is not required. Some SQL engines require it but SQL 2000 does not.

How truly portable is SQL code?

Purpose of Blog

I really need to know the structure query language (SQL) for working with SUSTAIN.

This analysis is based upon the SQL 2000 implementation of SQL.