SQL Examples

The following examples show how SQL syntax can be used to produce a variety of database analysis. The syntax has been tested with VBA for Access2000, and should work well for any related database formats. Copy the SQL by holding down and dragging the mouse over the statement you want to use, then type Ctrl-C. You also may want to copy the sample data table located at the bottom of this page.


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 TOP combined with another query via UNION   

SELECT TOP 1 FirstName, LastName, Sex, Salary
FROM tblPeople
WHERE Sex="F"
ORDER BY Salary DESC
UNION SELECT TOP 1 FirstName, LastName, Sex, Salary
FROM tblPeople
WHERE Sex="M"
ORDER BY Salary DESC;

Result: four fields for the two highest paid employees; one female and one male.


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.


Aggregate function used with SubQuery and Union   

SELECT FirstName, LastName, Sex, Salary AS [High Salary]
FROM tblPeople
WHERE ((Salary) > (SELECT AVG(Salary) FROM tblPeople
                  WHERE Sex = 'F'))
UNION SELECT FirstName, LastName, Sex, Salary AS [High Salary]
FROM tblPeople
WHERE ((Salary) > (SELECT AVG(Salary) FROM tblPeople
                  WHERE Sex = 'M'))
ORDER BY Sex, [High Salary] DESC;

Result: four fields for females with above average salaries (among all females) and males with above average salaries (among all males). Arranged from highest- to lowest-salaries for females, then for males.


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

UPDATE tblContribution 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 VB to activate the SQL above. 

Dim MySQL as String
MySQL = "UPDATE tblPeople SET IsSelected = No;"
CurrentDB.Execute MySQL


Adding records to a table (an Action Query)   

INSERT INTO tblPeople (LastName,FirstName,Hire,Review,Salary,Sex,IsSelected)
               VALUES ('Smith-Kline','Mary', #11/01/2002#,#11/01/2003#,50000,'F','T');


... or use this alternate format:

INSERT INTO tblPeople
               VALUES ('Smith-Kline','Mary', #11/01/2002#,#11/01/2003#,50000,'F','T');

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 they are 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 multi-table 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 VB 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 VB 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 a 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 in your program's Help system about using SQL in recordsets and how to use it without recordsets as the data source of controls. SQL is also very useful for Web authors using ASP.


Sample data used in these examples (tblPeople)   

pkPeople
ID  FirstName LastName   Hire       Review  Salary     Sex IsSelected

1   James   Carlile 2/2/2002    10/13/2001  $23,200.00   M   Yes
2   Andrew  Frank   2/9/1997    2/9/1999    $46,276.92  M   Yes
3   Janet   Lydell  6/25/1994   6/25/1999   $68,674.72  F   No
4   Margo   ONiell  11/16/1994  11/16/1999  $56,834.25  F   No
5   Edward  Jones   11/17/1998  11/17/1999  $62,088.20  M   Yes
6   Harry   Jones   9/22/1978   10/1/1999   $43,920.23  M   Yes
7   Jimmy   Smith   2/24/2000   2/24/2001   $55,703.70  M   No
8   Hugh    Poynor  9/12/1989   9/30/1999   $28,923.08  M   Yes
9   Edward  Smith   3/6/2000    2/24/2001   $25,000.00  M   Yes

' this alternate format can be a flat text file

1,James,Carlile,2/2/2002,10/13/2001,23200.00,M,1
2,Andrew,Frank,2/9/1997,2/9/1999,46276.92,M,1
3,Janet,Lydell,6/25/1994,6/25/1999,68674.72,F,0
4,Margo,ONiell,11/16/1994,11/16/1999,56834.25,F,0
5,Edward,Jones,11/17/1998,11/17/1999,62088.20,M,1
6,Harry,Jones,9/22/1978,10/1/1999,43920.23,M,1
7,Jimmy,Smith,2/24/2000,2/24/2001,55703.70,M,0
8,Hugh,Poynor,9/12/1989,9/30/1999,28923.08,M,1
9,Edward,Smith,3/6/2000,2/24/2001,25000.00,M,1