Using Data Definition Language - Creating and deleting tables (including NOT NULL) - working with indexes (DISALLOW NULL, IGNORE NULL, UNIQUE, DROP INDEX) - Defining Relationships Between Tables (One-to-one, one-to-many, many-to-many)
Using Data Manipulation Language - Retrieving Records (SELECT statements), restricting the result set (WHERE clause), sorting the result set (ORDER BY clause), using aggregate functions to work with values (COUNT, AVG, SUM, MIN, MAX, FIRST, LAST), group records records in a result set (GROUP BY, HAVING), inserting records into a table (INSERT INTO), updating records in a table (UPDATE), deleting records from a table (DELETE FROM)
Using SQL in Access - Building queries, specifying a data source, using SQL statements inline,
Differences Between Fundamental, Intermediate, and Advanced SQL Topics
How Can Intermediate SQL Be Beneficial?
SQL Enhancements - Jet 4.0 data engine enhanced to more closely conform to ANSI-92 standard, adding default value
Using ADO vs. DAO
SQL Coding Conventions
Intermediate Data Definition Language
Altering Tables - ADD COLUMN, ALTER COLUMN, (renaming column is not directly supported)
Constraints - named CONSTRAINT when altering a field. multi-field constraints declared at the table level (ADD CONSTRAINT). CHECK to validate field value. (CHECK constain only works through Jet OLE DB and ADO, can not be used with Access SQL View.)
More details: Note The check constraint statement can only be executed through the Jet OLE DB provider and ADO; it will return an error message if used though the Access SQL View user interface. Also note that to drop a check constraint, you must issue the DROP CONSTRAINT statement through the Jet OLE DB provider and ADO. Also, if you do define a check constraint: (1) it won't show as a validation rule in the Access user interface (UI), (2) you can't define the ValidationText?Create property so that a generic error message will display in the Access UI, and (3) you won't be able to delete the table through the Access UI or from code until you drop the constraint by using a DROP CONSTRAINT statement from ADO.
The expression that defines the check constraint can be no more than 64 characters long.
Referential integrity with ON UPDATE CASCADE, ON DELETE CASCADE
Fast foreign keys (foreign keys without an index) - Useful when there are few values to check against (for example 10 product types). Not suitable for a foreign key like a customer ID.
Data Types
TEXT data types - Up to 255 chars. - MEMO - 65535 character, unless no binary data, then limited to 2.14GB. Both use 2 bytes per character (Unicode) unless compressed.
Numeric data types
TINYINT - INTEGER1, BYTE 1 byte
SMALLINT - SHORT, INTEGER2 2 bytes
INTEGER - LONG, INT, INTEGER4 4 bytes
REAL - SINGLE, FLOAT4, IEEESINGLE 4 bytes
FLOAT - DOUBLE, FLOAT8, IEEEDOUBLE, NUMBER 8 bytes
DECIMAL - NUMERIC, DEC 17 bytes
CURRENCY - MONEY - 15 digits followed by four after decimal point - 8 bytes
OLEOBJECT - Synonyms: IMAGE, LONGBINARY, GENERAL, OLEOBJECT
DATETIME - DATE, TIME, DATETIME, TIMESTAMP
COUNTER - (autoincrement when new records are inserted) - Synonyms: COUNTER, AUTOINCREMENT, IDENTITY
@@IDENTITY - the value of @@IDENTITY is only accurate immediately after adding a record from code.
Intermediate Data Manipulation Language
Predicates - ALL, DISTINCT, TOP (return a certain number of rows that fall at the top or bottom of a range that is specified by an ORDER BY clause)
SQL Expressions - IN (determine is expression is equal to any of several specified values), BETWEEN, LIKE (patterns), IS NULL
The SELECT INTO Statement - Make table query
Subqueries - IN, (ANY, ALL, SOME), EXISTS
Joins
INNER JOINS - This join is used to retrieve rows from two or more tables by matching a field value that is common between the tables -
The OUTER JOIN is used to retrieve records from multiple tables while preserving records from one of the tables, even if there is no matching record in the other table.
A Cartesian product is defined as "all possible combinations of all rows in all tables." This happens when joining two tables without qualification or join type. You should avoid creating Cartesian products by always qualifying your joins.
Transactions - A transaction is a logical grouping of work, or a collection of SQL statements, that must be completed successfully as a group or not at all.
BEGIN, COMMIT, ROLLBACK TRANSACTION, COMMIT WORK, ROLLBACK WORK, BEGIN WORK
With OwnerAccess?Create Statement - WITH OWNERACCESS OPTION
![[Home]](/wiki.gif)
Microsoft Access SQL