GBIC: SQL
The following is reprinted for archival purposes from Gary Beene's Information Center, with permission from Mr. Beene himself.
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.
Loading Comments ...
Comments
No comments have been added for this post.
You must be logged in to make a comment.