Structured Query Language
an industry standard language
querying a database
the database. SQL
provide a plain
English method of accessing a database
An SQL query
which tells VB what
take against the
a recordset. Using SQL really does simplify the code you have
an application that utilizes databases.
You can even
SQL queries which will modify many records
operation. Once I understood the basics,
the use of SQL hit
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]
note about the examples:
denote all fields
Dates are enclosed by pound signs,
part names which include spaces are enclosed
brackets: [ ]
use SQL queries,
refresh the control
"SELECT * FROM Agency ORDER BY [City]"
Just make sure that any references
fields match those contained
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
an SQL statement which you should recognize:
Command Clauses Predicates Operators Aggregate Functions
Create From Distinct
Drop Where Top
Alter Group By
Having Between Max
Insert Order By
a little inspection you can pretty much guess what
of these pieces of an SQL statement
. However, here are a couple which you
'll not want to miss and which I use pretty regularly.
't miss the last one in my list, which is a very easy way to sort a recordset!
the most basic command which tells VB which fields
Simple, but very powerful means of deleting many records at one time.
Defines the Table from which the fields will be extracted.
Precedes the conditions by which records are selected from the database
Sorts the records by any combination of fields you chose.