24 SQL

..SQL - Structured Query Language
SQL Is an industry standard language For querying a database - With the purpose To either retreive Data Or To modify
the Data In the database.  SQL Is intended To provide a plain - English method of accessing a database And To avoid
the need For custom programming.

An SQL query Is a text String which tells VB what To include In a recordset Or what actions To take against the Data
In a recordset.  Using SQL really does simplify the code you have To Write In an application that utilizes databases.
You can even Write SQL queries which will modify many records In a Single operation. Once I understood the basics,
the use of SQL hit Me Like a revelation. It 's easily one of the top 5 features of VB's database handling capabilities!

Sample SQL Queries

"Select * From Title Where [Year Published] < 1889"
"Delete From Titles Where [Year Published] < #1/1/1889#"
"Select Name, Picture From Authors Where Date_of_Birth = #2/1/1947#"
"Select * From Employees"
"Select [First Name], [Last Name] From Employees"
"Select Employees, Department, SupvName From Supervisors, Employees Where Employees.Department = Supervisorts.Department"
"Select Distinct [Last Name] From Employees"
"Select [Last Name], Salary From Employees Where Salary > 2100"
"Select * From Orders Where [Shipped Date] = #5/12/93#"
"Select [Product Name], Sum ([Units in Stock]) From Products Group By [Product Name]"
"Select * From Employees Order By [Last Name], Asc"
"Select [Last Name], [First Name] From Employees Order by 2 Asc"
"Select [Last Name], Salary From Employees Order By Salary, Desc, [Last Name]

Three things To note about the examples:

- "*" Is used To denote all fields
- Dates are enclosed by pound signs, Like this: "#2/1/1947#"
- Fields With multi - part names which include spaces are enclosed In brackets: [ ]

To use SQL queries, Set the RecordSource Property of a Data control To an SQL
statement such As those above And refresh the control Like this:

  Data3.RecordSource = "SELECT * FROM Agency ORDER BY [City]"
  Data3.Refresh

Just make sure that any references To fields match those contained In the actual
database. Doing so will create a recordset whose content will match the constraints
described by the SQL statement.

First of all, there are 5 parts To an SQL statement which you should recognize:

 Command  Clauses  Predicates Operators  Aggregate Functions
 Create  From  Distinct   And    Avg
 Drop  Where  Top     Or    Count
   Alter  Group By      Not    Sum
    Select   Having      Between  Max
   Insert  Order By      Like   Min
   Update          In
   Delete    

With a little inspection you can pretty much guess what Each of these pieces of an SQL statement
can Do . However, here are a couple which you 'll not want to miss and which I use pretty regularly.
Don 't miss the last one in my list, which is a very easy way to sort a recordset!

Select
     This Is the most basic command which tells VB which fields To show In the recordset.
Delete
     Simple, but very powerful means of deleting many records at one time.
From
     Defines the Table from which the fields will be extracted.
Where
     Precedes the conditions by which records are selected from the database
Order By
     Sorts the records by any combination of fields you chose.