Microsoft Access SQL

Featured: Featured Stories | Picture Gallery

Fashion | Electronics Resonance

Chat11.com Web Bible11.com MyBibleCenter.com

Microsoft Access SQL

Articles about Microsoft JET SQL for Access 2000:

• Fundamental Microsoft Jet SQL for Access 2000 - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acfundsql.asp
  
  • Introduction
  • SQL Defined
    • What is Structured Query Language? - SQL stands for Structured Query Language and is sometimes pronounced as "sequel."
    • Why and where would you use SQL?
    • Data definition language
    • Data manipulation language
    • ANSI and Access 2000
    • SQL Coding Conventions - Examples fo poorly formatted SQL code, and well-formatted SQL code
  • 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,
  • One Last Comment
• Intermediate Microsoft Jet SQL For Access 2000 - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp
  • Introduction
  • Intermediate SQL Defined
    • 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
        • BOOLEAN - BIT, Logical, Logical1, YESNO - -1 = true, 0 = false
        • BINARY - (510 bytes is maximum) - Synonyms: BINARY, VARBINARY, BINARY VARYING
        • 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.
      • UNION operator
      • TRANSFORM statement -
  • Using Intermediate SQL in Access
    • Sample Database
    • Queries
    • Inline Code
      • Intermediate DDL statements (data description language)
      • Intermediate DML statements (Data Manipulation Language)
  • One Last Comment
  • Additional Resources
• Advanced Microsoft Jet SQL For Access 2000 - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acadvsql.asp
  • Introduction
  • Advanced SQL Defined
    • The Multiuser Model
    • Jet vs. MSDE
    • SQL Coding Conventions
  • Advanced Data Definition Language
    • Views
    • Procedures
    • Managing Security
    • Share-Level Security - ALTER DATABASE PASSWORD
    • User-Level Security
      • 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 Statement - WITH OWNERACCESS OPTION
• Using Advanced SQL in Access
  • Sample Database
  • Setting the Database Password
  • Using a Procedure
• One Last Comment
• Additional Resources

Search for books about:

Bobsgear - Get A Free Enterrpise Wiki Space!
Review: The Bobsgear Project was started to develop a variety of Confluence plugins. This installation of the Confluence Enterprise wiki includes flexible attachments, many Confluence plugins, personal blogs, interesting articles, and more. Bobsgear already has spaces related to politics, art and photography wiki, technical issues wiki, ediscovery wiki, health, Christian theology and Sabbath School wiki, the bible, book reviews, and quotations. Bobsgear allows free signup, and invites anyone to create a free hosted Confluence wiki space.

NEW USERS CLICK HERE! for a quick introduction to Wiki.

Elapsed:0