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|Search 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.
In general, user-level security is simpler to manage if you assign permissions only to groups, and then assign users to the appropriate group.
User and group accounts - CREATE GROUP, DROP GROUP, CREATE USER, ALTER USER, DROP USER, ADD USER
Database object permissions - GRANT, REVOKE
Privileges that can be granted:
Privilege - Applies To - Description
SELECT - Tables, Objects, Containers - Allows a user to read the data and read the design of a specified table, object, or container.
DELETE - Tables, Objects, Containers Allows a user to delete data from a specified table, object, or container.
INSERT - Tables, Objects, Containers Allows a user to insert data into a specified table, object, or container.
UPDATE - Tables, Objects, Containers Allows a user to update data in a specified table, object, or container.
DROP - Tables, Objects, Containers Allows a user to remove a specified table, object, or container.
SELECTSECURITY - Tables, Objects, Containers Allows a user to view the permissions for a specified table, object, or container.
UPDATESECURITY - Tables, Objects, Containers Allows a user to change the permissions for a specified table, object, or container.
UPDATEIDENTITY - Tables Allows a user to change the values in auto-increment columns.
CREATE - Tables, Objects, Containers Allows a user to create a new table, object, or container.
SELECTSCHEMA - Tables, Objects, Containers Allows a user to view the design of a specified table, object, or container.
SCHEMA - Tables, Objects, Containers Allows a user to modify the design of a specified table, object, or container.
UPDATEOWNER - Tables, Objects, Containers Allows a user to change the owner of a specified table, object, or container.
ALL PRIVILEGES - All Allows a user all permissions, including administrative, on a specified table, object, container, or database.
CREATEDB - Database Allows a user to create a new database.
EXCLUSIVECONNECT - Database Allows a user to open a database in exclusive mode.
CONNECT - Database Allows a user to open a database.
ADMINDB - Database Allows a user to administer a database.
Advanced Data Manipulation Language
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|Search Statement - WITH OWNERACCESS OPTION
![[Home]](/wiki.gif)
Microsoft Access SQL