SQL Examples home page

These examples present a variety of single- and multiple-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. All material is introductory. 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 W3 Schools, Key Data SQL, and finally an online list of resources at SQL.org.

Go to Access SQL Examples below
Say thanks for these SQL Examples.

Visit our e-book
project site.

Examples of MySQL Queries 

This section was added to the Access SQL because of the extreme popularity of MySQL. The topics here will include multiple tables and creating and loading tables directly from SQL. It is not necessary to enter any data to try out the examples. There are CREATE and INSERT statements below for you to copy and paste into MySQL Workbench or Sequel Pro that will build the data for you to use in the examples.

You would be well served to download a free database console and try out each example below on your desktop computer. Try MySQL Workbench (MySQL.com) for Windows and Mac, or Sequel Pro (SequelPro.com) for Mac and it is available for several spoken languages.

You may build databases on your desktop and on a remote host or both. Follow directions for the free database console you choose above.

Instructions
Copy a line below and paste it into a query field or section of your SQL Editor where you can run SQL queries. In the examples below a line always ends with ; but may wrap for easy reading. Wrapped lines will end with ;. After your line is pasted into the Editor find and click "execute (or run) SQL".

Build a MySQL table   

This is your first exercise. Start by building an example table of people employed in a company.

Block copy everything in the three semicolons below. Paste them into your SQL Editor and execute the entire block. 

DROP TABLE IF EXISTS `tblPeople`;

CREATE TABLE `tblPeople` (
	`ID` int(5) unsigned NOT NULL AUTO_INCREMENT,
	`FirstName` varchar(20) NOT NULL,
	`LastName` varchar(20) NOT NULL,
	`Hire` date NOT NULL,
	`Review` date NOT NULL,
	`Salary` decimal(12,2) NOT NULL,
	`Sex` varchar(1) NOT NULL,
	`updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP 
	     ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DESCRIBE tblPeople;



	

		ID int(5) unsigned NO PRI NULL auto_increment
	

	

		FirstName varchar(20) NO  NULL 
	

	

		LastName varchar(20) NO  NULL 
	

	

		Hire date NO  NULL 
	

	

		Review date NO  NULL 
	

	

		Salary decimal(12,2) NO  NULL 
	

	

		Sex varchar(1) NO  NULL 
	

	

		updated timestamp NO  CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP

Insert MySQL table data   

Next, block copy the SQL below which will insert data into the example table. Paste them into your SQL Editor and execute the entire block. 

LOCK TABLES `tblPeople` WRITE;

INSERT INTO `tblPeople` (`FirstName`, `LastName`, `Hire`,`Review`,
    `Salary`,`Sex`)
VALUES
('Carla','Dumont','1987-09-04','1999-10-02',60249.82, 'F'),
('Andrew','Frank','1997-02-09','1999-02-09',55081.10, 'M'),
('Janet','Oniell','1994-06-25','1999-06-25',49875.00, 'F'),
('Margo','Dumont','1994-01-16','1999-07-16',77629.58, 'F'),
('Edward','Jones','1998-01-17','1999-09-17',40163.31, 'M'),
('Harry','Jones', '1978-09-22','1999-10-01',103500.00,'M'),
('Jane','Doe',    '1978-08-09','1999-10-03',103750.00,'F'),
('Hugh','Poynor', '1989-09-12','1999-09-30',30601.00, 'M'),
('Jane','Deaux',  '1987-09-08','1999-10-03',79368.71, 'F');

UNLOCK TABLES;


# The next SQL query is a basic, bare bones way to see table data 


SELECT * FROM tblPeople;



	

		ID FirstName LastName Hire Review Salary Sex  updated
	

	

		1 Carla Dumont 1987-09-04 1999-10-02 60249.82 F  2017-03-01 12:27:59
	

	

		2 Andrew Frank 1997-02-09 1999-02-09 55081.10 M 2017-03-01 12:27:59
	

	

		3 Janet Oniell 1994-06-25 1999-06-25 49875.00 F 2017-03-01 12:27:59
	

	

		4 Margo Dumont 1994-01-16 1999-07-16 77629.58 F 2017-03-01 12:27:59
	

	

		5 Edward Jones 1998-01-17 1999-09-17 40163.31 M 2017-03-01 12:27:59
	

	

		6 Harry Jones 1978-09-22 1999-10-01 103500.00 M  2017-03-01 12:27:59
	

	

		7 Jane Doe 1978-08-09 1999-10-03 103750.00 F 2017-03-01 12:27:59
	

	

		8 Hugh Poynor 1989-09-12 1999-09-30 30601.00 M  2017-03-01 12:27:59
	

	

		9 Jane Deaux 1987-09-08 1999-10-03 79368.71 F  2017-03-01 12:27:59

Conditional SQL queries   

Here you select people who were hired before a particular date. Next you change displayed column names and change the order of the records. 

# the SQL query below is limited to four fields from the table
# unlike  SELECT * FROM tblPeople shown above
# and it has WHERE conditions


SELECT ID, FirstName, LastName, Hire
FROM tblPeople
WHERE Hire < '1990-01-01';



	

		IDFirstName LastName Hire
	

		

		7 Jane Doe 1978-08-09
	

		

		6 Harry Jones 1978-09-22
	

		

		1 Carla Dumont 1987-09-04
	

	

		9 Jane Deaux 1987-09-08
	


	

		8 Hugh Poynor 1989-09-12
	





# the SQL query below changes FirstName, LastName and Hire 
# headings for the result 
# but does not (cannot) use Hired in the SQL WHERE logic


SELECT ID, FirstName as First, LastName as Last, Hire as Hired
FROM tblPeople
WHERE Hire < '1990-01-01'
ORDER BY Hire DESC;




	

		IDFirst Last Hired
	

	

		8 Hugh Poynor 1989-09-12
	

	

		9 Jane Deaux 1987-09-08
	

	

		1 Carla Dumont 1987-09-04
	

	

		6 Harry Jones 1978-09-22
	

	

		7 Jane Doe 1978-08-09
	







Next tryout typing the SQL yourself and use it on our database.
You do not have to set up a MySQL database account for yourself.
Practice SQL syntax on our database.

Format SQL results   

Here you select and format the names of people who were hired after a particular date and who now have a particular annual salary level. Next you format the salary to be more readable. 



# first and last names are formatted together and placed in one column 
# but salary is left unformatted


SELECT Salary, CONCAT(FirstName , ' ' , LastName) AS Names
FROM tblPeople
WHERE Salary > 50000.00 AND Hire > '1995-12-31'
ORDER BY LastName;



	

		SalaryNames
	

	

		55081.10 Andrew Frank
	





# salary is formatted in the usual way


SELECT CONCAT( '$',FORMAT(Salary,2) ), CONCAT(FirstName , ' ' , LastName) 
    AS Names
FROM tblPeople
WHERE Salary > 50000.00 AND Hire > '1995-12-31'
ORDER BY LastName;



	

		SalaryNames
	

	

		$55,081.10 Andrew Frank
	




#  See the blue button before this section where you can type in your practice SQL

Sequence SQL results

Here you select and further format the names of people and show them in order by sex with salaries shown low to high for females then males. Next you select the two highest earning people. 

# note the two fields Sex, Salary mentioned in ORDER BY
# the effect is to sequence salary low to high for females 
# then do the same for males


SELECT CONCAT(IF(Sex='F', 'Ms.', 'Mr.'), ' ',FirstName,' ', LastName) 
    AS Salutation, 
CONCAT( '$',FORMAT(Salary,2) ) as "Annual Salary"
FROM tblPeople
ORDER BY Sex, Salary;



	

		SalutationSalary
	

	

		Ms. Janet Oniell $49,875.00
	

	

		Ms. Carla Dumont $60,249.82
	

	

		Ms. Margo Dumont $77,629.58
	

	

		Ms. Jane Deaux $79,368.71
	

	

		Ms. Jane Doe $103,750.00
	

	

		Mr. Hugh Poynor $30,601.00
	

	

		Mr. Edward Jones $40,163.31
	

	

		Mr. Andrew Frank $55,081.10
	

	

		Mr. Harry Jones $103,500.00
	




# query the two highest paid people


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



	

		FirstNameLastNameSexSalary
	

	

		Jane Doe F 103750.00
	

	

		Harry Jones M 103500.00
	




#  See the blue button before this section where you can type in your practice SQL

Statistical SQL queries, subqueries and insert   

Select a list of first names with no duplications in the list. Ask "what is the average salary?" Execute a subquery to list only people with above average salaries. Update (change) everyones IsSelected to true. Add an employee (note that only partial data is included). 

SELECT DISTINCT FirstName FROM tblPeople;

# Note there is only one Jane FirstName shown below



	

		FirstName
	

	

		Carla
	

	

		Andrew
	

	

		Janet
	

	

		Margo
	

	

		Edward
	

	

		Harry
	

	

		Jane
	

	

		Hugh
	




SELECT AVG(Salary) AS AvgSalary FROM tblPeople;



	

		AvgSalary
	

	

		66690.946667
	





SELECT FirstName, LastName, Salary AS HighSalary
FROM tblPeople
WHERE Salary > (SELECT AVG(Salary) FROM tblPeople);

# these people earn above-average salaries



	

		FirstNameLastNameHighSalary
	

	

		Margo Dumont 77629.58
	

	

		Harry Jones 103500.00
	

	

		Jane Doe 103750.00
	

	

		Jane Deaux 79368.71
	




# next add a new person -- notice not all fields are included
# with the result that missing fields will have blank data or contain defaults

INSERT INTO tblPeople (LastName,FirstName,Hire,Salary,Sex)
               VALUES ('Smith-Kline','Mary', '2000-11-01' ,50000,'F');
               

#  See the blue button before this section where you can type in your practice SQL

Summarizing categories or grouping queries   

Add to the above examples by summarizing "Mens and Womens lowest/highest salary?" This is usually done to arrange or aggreagte identical data into groups. 

SELECT Sex, MIN(Salary) as "Lowest Annual Salary"
FROM tblPeople
GROUP BY Sex ASC;

# Notice the GROUP BY operator. 
It will group the sexes together, then the function MIN() will be applied.



	

		SexLowest Annual Salary
	

	

		F49875.00
	

	

		M30601.00
	




SELECT Sex, CONCAT( '$',FORMAT(MAX(Salary),2) ) as "Highest Annual Salary"
FROM tblPeople
GROUP BY Sex ASC;

# Here the MAX() is embedded in a formatting syntax.



	

		SexHighest Annual Salary
	

	

		F$103,750.00
	

	

		M$103,500.00
	



               

#  See the blue button before this section where you can type in your practice SQL

Combine several queries in one result   

The UNION syntax lets to combine queries for an organized result. The first example below illustrates statistical operations (AVG and SUM), and a summary function (GROUP BY) that results in a neat statement of average salaries for the sexes and the company as a whole. 

# the query below shows a stat report
# with formatted results, stat functions, and two queries in one result


SELECT DISTINCTROW Sex, CONCAT( '$',FORMAT(AVG(Salary),2) ) AS "Average Salary"
FROM tblPeople GROUP BY Sex
UNION SELECT 'Overall=', CONCAT( '$',FORMAT(AVG(Salary),2) ) AS Total
FROM tblPeople;



	

		Sex Average Salary
	

	

		F $74,174.62
	

	

		M $57,336.35
	

	

		Overall= $66,690.95
	




# the query below shows four queries in one result prepared 
# for HTML OPTION tags that will fit into a SELECT tag 
# with help from a PHP script you write


SELECT DISTINCTROW ID as Value,LastName FROM tblPeople
UNION SELECT "-1","< All People >" FROM tblPeople
UNION SELECT "-2", "< Women Only >" FROM tblPeople
UNION SELECT "-3", "< Men Only >" FROM tblPeople
ORDER BY LastName;



	

		Value Names
	

	

		-1 < All People >
	

	

		-3 < Men Only >
	

	

		-2 < Women Only >
	

	

		9 Deaux
	

	

		7 Doe
	

	

		1 Dumont
	

	

		4 Dumont
	

	

		2 Frank
	

	

		5 Jones
	

	

		6 Jones
	

	

		3 Oniell
	

	

		8 Poynor
	





#  See the blue button before this section where you can type in your practice SQL

Database guidelines   

To use SQL as it is intended, and for the most power with it, your data will be structured into fields (columns), and the fields structured into records (rows), and the records into tables. "Normalization" is a convention or guideline to help you get the most from sql data. Think of your tables as "entities" such as people or things, and then think of characteristics of the entity to help you decide what fields of information you need.

A primary guideline is to never include "repeating fields" in tables. A repeating field is any particular data, such as hours worked per week, that you might put in tblPeople. This error would be to include fields in tblPeople for Monday, another field for Tuesday, an so on. Another mistake would be to include up to three email addresses for each employee in the people table. Fields like these belong in a separate table, say tblHours and tblEmail. As long as the hours and the employees, and email addresses and employees can be linked up using SQL then queries are MUCH easier to write and are faster.

Create a table for Email addresses   

People usually have more than one email address, for example, one for business another for personal matters. If tblPeople were to include fields for email addresses then it would be making the "repeating fields" error. Instead, create a table for email addresses and make it work with tblPeople.

The link between tblPeople and tblEmail below will involve a second ID field known as a "Foreign Key". For making this key in tblEmail clearer to you we will name it "tblPeopleID". It will hold the value from the primary key field in tblPeople (named ID) -- and guide the SQL to the right prople-email combination.

Notice below tblEmail has a primary key (ID) and a foreign key (tblPeopleID). 

DROP TABLE IF EXISTS `tblEmail`;

CREATE TABLE `tblEmail` (
	`ID` int(5) unsigned NOT NULL AUTO_INCREMENT,
	`tblPeopleID` int(5) unsigned NOT NULL,
	`Principle` BOOLEAN NOT NULL DEFAULT 0,
	`Email` varchar(40) NOT NULL,
	`updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP 
	    ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

LOCK TABLES `tblEmail` WRITE;

INSERT INTO `tblEmail` (`tblPeopleID`,`Principle`, `Email`)
VALUES
(1,1,'CarlaDumont@companyname.com'),
(2,1,'AndrewFrank@companyname.com'),
(3,1,'JanetOniell@companyname.com'),
(4,1,'MargoDumont@companyname.com'),
(5,1,'EdwardJones@companyname.com'),
(6,1,'HarryJones@companyname.com'),
(7,1,'JaneDoe@companyname.com'),
(8,1,'HughPoynor@companyname.com'),
(8,0,'HughPoynor@yahoo.com'),
(9,1,'JaneDeaux@companyname.com'),
(9,0,'JaneDeaux@gmail.com');

UNLOCK TABLES;

SELECT * FROM tblEmail;



	

		ID  tblPeopleIDPrinciple  EmailUpdated   
	

	

		1 1 1 CarlaDumont@companyname.com 2017-03-01 16:30:30
	

	

		2 2 1 AndrewFrank@companyname.com 2017-03-01 16:30:30
	

	

		3 3 1 JanetOniell@companyname.com 2017-03-01 16:30:30
	

	

		4 4 1 MargoDumont@companyname.com 2017-03-01 16:30:30
	

	

		5 5 1 EdwardJones@companyname.com 2017-03-01 16:30:30
	

	

		6 6 1 HarryJones@companyname.com 2017-03-01 16:30:30
	

	

		7 7 1 JaneDoe@companyname.com 2017-03-01 16:30:30
	

	

		8 8 1 HughPoynor@companyname.com 2017-03-01 16:30:30
	

	

		9 8 0 HughPoynor@yahoo.com 2017-03-01 16:30:30
	

	

		10 9 1 JaneDeaux@companyname.com 2017-03-01 16:30:30
	

	

		11 9 0 JaneDeaux@gmail.com 2017-03-01 16:30:30

Link two tables with keys   

Create a query that lists people and email addresses. Remember that tblEmail has two keys: a primary key (ID) and a foreign key (tblPeopleID). 

# this sql uses prefix letters to distinguish the tables primary keys 
# which have the same names (ID and ID)


SELECT CONCAT(p.FirstName , ' ' , p.LastName) AS Names, e.Email
FROM tblPeople as p, tblEmail as e
WHERE p.ID = e.tblPeopleID 
ORDER BY LastName;



	

		Names  Email
	

	

		Jane Deaux JaneDeaux@gmail.com
	

	

		Jane Deaux JaneDeaux@companyname.com
	

	

		Jane Doe JaneDoe@companyname.com
	

	

		Carla Dumont CarlaDumont@companyname.com
	

	

		Margo Dumont MargoDumont@companyname.com
	

	

		Andrew Frank AndrewFrank@companyname.com
	

	

		Harry Jones HarryJones@companyname.com
	

	

		Edward Jones EdwardJones@companyname.com
	

	

		Janet Oniell JanetOniell@companyname.com
	

	

		Hugh Poynor HughPoynor@companyname.com
	

	

		Hugh Poynor HughPoynor@yahoo.com
	





# the query above lists all addresses for all people 
# and its results look disorganized
# the query below only lists people's principle email addresses


SELECT CONCAT(p.FirstName , ' ' , p.LastName) AS Names, e.Email
FROM tblPeople as p, tblEmail as e
WHERE p.ID = e.tblPeopleID 
AND e.Principle
ORDER BY LastName;



	

		Names  Email
	

	

		Jane Deaux JaneDeaux@companyname.com
	

	

		Jane Doe JaneDoe@companyname.com
	

	

		Carla Dumont CarlaDumont@companyname.com
	

	

		Margo Dumont MargoDumont@companyname.com
	

	

		Andrew Frank AndrewFrank@companyname.com
	

	

		Harry Jones HarryJones@companyname.com
	

	

		Edward Jones EdwardJones@companyname.com
	

	

		Janet Oniell JanetOniell@companyname.com
	

	

		Hugh Poynor HughPoynor@companyname.com
	




# how would you find just one persons email address? 
# Look it up by their ID. See below.  


SELECT Email FROM tblEmail WHERE Principle AND tblPeopleID = 7;

#  See the blue button before this section where you can type in your practice SQL

Find orphan records in linked tables   

Orphan records happen when a foreign key points to a non-existing record in another table. This could occur, for example, if a person quits a job and his or her record is deleted from tblPeople, but his or her Email record is left in tblEmail. In other words a foreign key points to a record that does not exist. The unmatched records that have the foreign key are called orphans. 

# for this example to work you have to add the orphan record 
# to tblEmail then run the query that looks for 
# unmatched tblEmail-tblPeople records

# the first query adds a record to tblEmail


INSERT INTO tblEmail (tblPeopleID,Email) 
VALUES( 45,"orhpanRecord@gmail.com");

# the next query looks through tblEmail for all records 
# that have a bad foreign key


SELECT *
FROM tblEmail as e
WHERE NOT EXISTS 
    (SELECT *
    FROM tblPeople as p
    WHERE e.tblPeopleID = p.ID
    );
    


	

		ID  tblPeopleIdPrinciple  Email updated
	
    

    
12 450 orhpanRecord@gmail.com2017-03-05 16:14:45
	




#  See the blue button before this section where you can type in your practice SQL

Link three tables with keys   

Create a query that lists peoples names, passwords and email addresses

It is an industry practice to not store peoples names, passwords and userIDs in the same table because a security breech of the one table would expose sensitive data that could be used maliciously. Our example below puts passwords in a separate table and will be used to show how three tables can be joined in a query. 

# assume that tblPassword has a primary key and a foreign key (ID and tblPeopleID) 
# like tblEmail above


SELECT CONCAT(p.FirstName , ' ' , p.LastName) AS Names, s.Password, e.Email
FROM tblPeople as p, tblPassword as s, tblEmail as e
WHERE p.ID = e.tblPeopleID 
AND   p.ID = s.tblPeopleID 
AND   e.Principle
ORDER BY LastName;



	

		Names Password Email
	

	

		Andrew Frank BetterLate AndrewFrank@companyname.com
	

	

		Carla Dumont OHiseeU CarlaDumont@companyname.com
	

	

		Edward Jones Password192 EdwardJones@companyname.com
	

	

		Harry Jones CourseW0rk HarryJones@companyname.com
	

	

		Hugh Poynor HPrivate410 HughPoynor@companyname.com
	

	

		Jane Deaux Tie0neOn JaneDeaux@companyname.com
	

	

		Jane Doe DeadEnd JaneDoe@companyname.com
	

	

		Janet Oniell GuessWhat JanetOniell@companyname.com
	

	

		Margo Dumont Conga45 MargoDumont@companyname.com
	




#  See the blue button before this section where you can type in your practice SQL

MySQL Schema queries   

After you have accomplished basic set up of your Editor copy a line below and run it. None of the schema queries will run on the tryout SQL form above this section. 

SHOW DATABASES;

SHOW TABLES;

SHOW COLUMNS FROM tablename;

DESCRIBE tablename;

Examples of Single Access Tables 


Example Data (tblPeople)   

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

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  

Visitor count since April 1, 2017

Flag Counter