SQL Examples for Single Tables

Amazon Honor System Support classAnytime.com. Click Here to Pay Support classAnytime.com. Learn More
These examples present a variety of single-table SQL syntax which illustrate the power and flexibility of SQL in queries, as the RowSource for controls on forms, or in the opening of recordsets. The textbook used for MIS 325 is a good SQL reference: Running Access 2000 by John Viescas (MS Press, 1999). A very highly rated web site for basic SQL information (including very many examples and tutorials) is James Hoffman's Introduction to Structured Query Language. Other sources for SQL information and learning are SQL Course 1, SQL Course 2, W3 Schools, Key Data SQL, PL/SQL (Oracle) at UC Davis, and finally an online list of resources at SQL.org.


Example Data (tblPeople)   

pkPeople
ID  FirstName LastName Hire Review  Salary      Sex    IsSelected
1   Carla   Dumont  9/4/87  10/2/99 $60,249.82  F   Yes
2   Andrew  Frank   2/9/97  2/9/99  $55,081.10  M   Yes
3   Janet   Lydell  6/25/94 6/25/99 $49,875.00  F   No
4   Margo   Oniell  1/16/94 7/16/99 $77,629.58  F   Yes
5   Edward  Jones   1/17/98 9/17/99 $40,163.31  M   No
6   Harry   Jones   9/22/78 10/1/99 $103,500.00 M   Yes
7   Jane    Doe     8/9/78  10/3/99 $103,750.00 F   Yes
8   Hugh    Poynor  9/12/89 9/30/99 $30,601.00  M   No
9   Jane    Deaux   9/8/87  10/3/99 $79,368.71  F   Yes

How to use the example data in Access   

If you have this document open, highlight all 9 data rows and copy them to the clipboard. In Access create a new blank table and highlight 8 datasheet columns. Paste the example data into the table and rename the fields to the names above. Be sure and name the new table tblPeople. If you have the examples only in hard copy, you would have to re-type the data in an Access table.

How to use the example SQL statements in Access   

If you have this document open, highlight an SQL statement and copy it to the clipboard. In Access create a new Query and attach tblPeople to it, then open the SQL view of the query. If you have the SQL in the clipboard, paste it; but if you have this in hard copy you would just transcribe the SQL statement. Finally, select datasheet view to see the results.

Or, you might want to execute the SQL directly without using a Query. All you need is a VBA Standard Module with a Procedure for testing the SQL in a very simple program. You can then execute the SQL directly without using a Query. Confused about what to do? Start by using Queries. As you learn more about VBA programming learn how to execute SQL directly.

Conditionally selecting records by hire date and arranging them by hire date   

SELECT pkPeopleID, FirstName, LastName
FROM tblPeople
WHERE Hire < #01/01/90#
ORDER BY Hire;

Result: three fields showing people hired before the 90's in the order they were hired.

Creating a field with strings   

SELECT (FirstName & ' ' & LastName) AS Names
FROM tblPeople
WHERE Salary > 50000 AND Hire > #12/31/95#
ORDER BY LastName;

Result: one field in alphabetical order of recent hires making more than $50,000/year.

Creating a field with immediate If and strings   

SELECT IIF(Sex='F', 'Ms. ', 'Mr. ') & LastName AS Salutation
FROM tblPeople
ORDER BY Sex, Salary;

Result: one salutation field arranged from lowest to highest Salary for females first, then males. Related syntax.

Predicate TOP (also applies to TOP PERCENT, DISTINCT, DISTINCTROW)   

SELECT TOP 2 FirstName, LastName, Sex, Salary
FROM tblPeople
ORDER BY Salary;

Result: four fields for the two lowest paid employees.

Predicate DISTINCT   

SELECT DISTINCT FirstName FROM tblPeople;

Result: a list of people's first names with no names duplicated.

Aggregate function   

SELECT Avg([Salary]) AS AvgSalary FROM tblPeople;

Result: one field (and one record) holding average Salary. Other Aggregate Functions are Sum(), Count(), First(), Last(), Min(), Max(), StDev(), StDevP, and Var().

Aggregate function used with SubQuery   

SELECT FirstName, LastName, ([Salary]) AS [High Salary]
FROM tblPeople
WHERE (([Salary])) > (SELECT AVG([Salary]) FROM [tblPeople]);

Result: three fields for those employees with above-average salaries.

Changing data in a table or query (an Update Query)   

UPDATE tblPeople SET IsSelected = No;

Result: Set the IsSelected field of all records to No.

This is very useful when you have a form to let users check off the records they want to work with. First you must use table design to give the Boolean field IsSelected the properties Yes/No and format "Check Box", then it can be displayed on a form as a check box. Use the following VBA to activate the SQL above. 

Dim MySQL as String
MySQL = "UPDATE tblPeople SET IsSelected = No;"
CurrentProject.Connection.Execute MySQL ' IF ADO
CurrentDB.Execute MySQL                 ' IF DAO

Adding records to a table (an Append Query)   

INSERT INTO tblPeople (pkPeopleID,LastName,FirstName,Hire,Salary,Sex)
               VALUES (101,'Smith-Kline','Mary', #11/01/00#,50000,'F')

Result: A new record is added to tblPeople.

Union used to supplement a list and give users additional choices   

SELECT DISTINCTROW [CompanyID],[CompanyName] FROM [tblCompany]
UNION SELECT "0","< None >" FROM [tblCompany]
ORDER BY [CompanyName]

Result: two fields for use in a combo or listbox. The list would have company names to pick from, with < None > at the top of the list for users to signify that these were not picking a name. Another example illustrates using more than one Union to provide more user choices. 

SELECT DISTINCTROW [CompanyID],[CompanyName] FROM [tblCompany]
UNION SELECT "0","< No companies >" FROM [tblCompany]
UNION SELECT "-1", "< Non-American companies >" FROM [tblCompany]
UNION SELECT "-2", "< American companies >" FROM [tblCompany]
ORDER BY [CompanyName]

Aliasing of source table to make reading (and typing) SQL easier   

Without aliasing

SELECT tblRosterLink.SSN, tblRosterLink.ID, tblRosterLink.Name,
tblRosterLink.SEX, tblRosterLink.[Q3%], tblRosterLink.[Q4%]
FROM tblRosterLink;

With aliasing

SELECT L.SSN, L.ID, L.Name, L.SEX, L.[Q3%], L.[Q4%]
FROM tblRosterLink AS L;

Result: the same identical query. You can alias in SQL as shown, or in QBE use the Properties of the Table/Query source by right clicking the table. Useful only for multitable syntax. In the case of a single table, this syntax has little or no advantage, because the table name is optional then.

Using your own functions and VBA functions in place of fields   

SELECT PrettyName([StudentName]), Right([Section],2)
FROM [tblRoster] ORDER BY [StudentName]

Result: two fields. The function PrettyName would reformat the UT roster format for student names (example: POYNOR; HUGH W.) so that the query list would have names formatted to appear as Hugh W. Poynor. The built in VBA function Right() is used to advantage, too. Related syntax.

Summary statistics and formatting   

SELECT DISTINCTROW Party, Sum(Amount) AS [SumP]
FROM tblContribution GROUP BY Party
UNION SELECT 'Total=', Sum(Amount) AS [SumT]
FROM tblContribution;

Result: This SQL syntax summarizes contributions by political party. It is based on records of people's contributions in five political parties. The resulting datasheet would look like this.

Party SumP
Democrat$449.00
DK$99.00
Independent$493.00
Reform$100.00
Republican$226.00
Total$1,367.00

Programming SQL with Strings   

How do you program SQL statements so they use variables instead of constants? This issue arises when you want to re-use SQL, or program general-purpose selection forms for your users. For example, what would be necessary to change the hard-coded SQL below to an SQL statement that selects any salaries and dates?

Look at the following hard coded SQL to open a recordset.

MySQL = "SELECT pkPeopleID, Salary, Hire FROM tblPeople " & _
"WHERE (Salary > 20000 AND Salary < 100000) " & _
"AND (Hire > #12/31/95# AND Hire < #12/31/00#) " & _
"AND (Sex = 'F');"

The first step in moving away from hard coding is to provide five variables for the salary and date ranges, and sex. The variables could be given values by the user on a query-by-form interface. We will use txtLowSalary, txtHighSalary, txtLowDate, txtHighDate, and txtSex.

txtLowSalary = 20000
txtHighSalary = 100000
txtLowDate = #12/31/95#
txtHighDate = #12/31/00#
txtSex = "F"

Here is the rewritten SQL with the hard coded values replaced with variables. Because the contents of the variables were chosen to be identical to those in the hard coded example above, the resulting SQL string created below will be identical to the SQL string above.

MySQL = "SELECT pkPeopleID, Salary, Hire FROM tblPeople WHERE " & _
"(Salary > " & txtLowSalary & " AND Salary < " & txtHighSalary & ") AND " & _
"(Hire > #" & txtLowDate & "# " & "AND Hire < #" & txtHighDate & "#) AND " & _
"(Sex = " & "'" & txtSex & "'" & ");"

There are a number of ampersands, and single and double quotes in this large variable SQL statement. Use the following general rules for creating number variables, date variables and string variables. Each has different syntax requirements.

You might want to read about how to execute the SQL in a program. There is more information available about using SQL in recordsets and how to execute SQL directly without using recordsets.

Action queries and DDL (data definition language)   

Action
Append a record INSERT INTO tblEmployee (Name, Extension) VALUES ("Joe", "353")
Delete DELETE * from tblEmployee WHERE Name='Joe'
Make-table by copying SELECT * INTO tblEmployee FROM tblEmployeeBAK
Update specific fields UPDATE tblEmployee SET Extension='555' WHERE Name='Joe'


Public Sub CurrentProject_Execute()
Dim strSQL As String
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
strSQL = "UPDATE tblEmployee SET IsSelected=true WHERE IsSelected=false;"
cnn.BeginTrans
     cnn.Execute strSQL
cnn.CommitTrans
End Sub
Data-definition (SQL-specific)
Create a table: creates new tables and fields CREATE TABLE tblEmployee (Name TEXT(25), Extension TEXT(4))
Alter a table: adds, modifies or removes a column or index ALTER TABLE tblEmployee ADD COLUMN HireDate DATETIME
Delete a table: deletes an existing table DROP TABLE tblEmployee
Create an index: creates a new index in an existing table CREATE INDEX tblEmployee Extension
Delete an index: deletes an existing index DROP INDEX tblEmployee Extension 


Public Sub CurrentProject_Execute()
Dim strSQL As String
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
strSQL = "CREATE TABLE tblMerge (pkID COUNTER, IsSelected BYTE)"
cnn.BeginTrans
     cnn.Execute strSQL
cnn.CommitTrans
End Sub
Top of page