Microsoft Access
Lesson 5: Structured Query Language (SQL)
Structured Query Language (pronounced “S.Q.L.” or “sequel”) is a standard computing language for retrieving information from and manipulating databases. Access actually is a front-end to SQL: when you write a query using Access’s GUI interface, the query is converted to a SQL statement before executing.
SQL is a powerful, but fairly easy to use, tool. Although SQL is a versatile tool that can be used to update, insert, and delete records from database tables, we will limit this lesson to querying a database.
SQL Keywords and Syntax
There are several variations of SQL, but the major keywords in the different versions usually are the same. The keywords that are most important for queries are listed below
SELECT Used to specify the fields you want to retrieve
FROM Used to specify the tables from which you will pull the data WHERE Used to specify the criteria that limit the data you retrieve ORDER BY Used to specify how the data will be sorted
INNER JOIN. . . ON. . . Used to relate two tables (the usual Access join)
A SQL query commonly is made up of four clauses: SELECT field names
FROM table name
WHERE specify criteria (if any) ORDER BY specify sort (if any) ;
SQL Operators and Wildcards
These should be familiar, because they are the same as we used in Access, which is not coincidental since Access is a SQL front
Operator
= Equal to
> Greater th
< Less than
>= Greater than or equal to
<= Less than or equal to
<> Unequal to
BETWEEN ... AND ... Between two numbers
LIKE Used with a wild card
Wildcard Symbol
* One or more characters
? One alphabetic character
# Any single numeric character
The comparison operators and wildcards are used in th
Note that an expression containing a wildcard must be surrounded by apostrophes: LIKE ‘1/*/2000’
LIKE ‘Arth*’ LIKE ‘10#’ LIKE ‘r?g’
Sorting Information Using SQL
The ORDER BY clause tells how to sort the informati
Ascending order: ORDER BY
Descending order: ORDER BY . . . DESC If you ORDER By more than one field,
the first field and then the second. For example, the SQL statement below sorts by ZipCode first and then Last Name.
SELECT FirstName, LastName, ZipCode
SQL Operators and Wildcards
These should be familiar, because they are the same as we used in Access, which is not coincidental since Access is a SQL front-end.
Meaning Example
Equal to = 100
Greater than > 100
Less than < 100
Greater than or equal to >= 100
Less than or equal to <= 100
Unequal to <>100
Between two numbers Between 100 and 200
Used with a wild card Like ’r*g’ (see below)
Meaning Example
One or more characters r*g will find rig, rag, ring, rung
One alphabetic character r?g will find rig and rag
Any single numeric character 10# will find 101 and 105.
The comparison operators and wildcards are used in the WHERE clause of the SQL statement. Note that an expression containing a wildcard must be surrounded by apostrophes:
Sorting Information Using SQL
The ORDER BY clause tells how to sort the information.
ORDER BY (the default)
ORDER BY . . . DESC
If you ORDER By more than one field, the information will be sorted by the first field and then the second. For example, the SQL statement below
irst and then Last Name. SELECT FirstName, LastName, ZipCode
These should be familiar, because they are the same as we used in Access, which is not
Between 100 and 200
(see below)
Example
rig, rag, ring, rung and running
e WHERE clause of the SQL statement. Note that an expression containing a wildcard must be surrounded by apostrophes:
Creating a SQL Query in Access
Open the ExampleDB database and in the Queries window, choose Create a query in Design View
Tables window without adding a table.
You will see a SQL View icon on the left of the toolbar where you have seen the Design and Datasheet Views icons earlier. Click on the SQL View
The Design View window will become blank window ready to enter a SQL query.
Let’s write a simple SQL query to find the
names of all employees who live in South Carolina, sorted by last name. Before we write the query, let’s think about what we need.
Fields: FirstName, LastName, and State Table: All of these fields are in tblEmployees Criterion: State = ‘SC’
Sort: Sort by LastName
We can easily turn these requirements into a SQL statement. Here it is:
SELECT FirstName, LastName, State FROM tblEmployees
WHERE State=’SC’ ORDER BY LastName;
The use of single rather than double quotation marks around ‘SC’ is required by SQL. Enter this query in the window.
Again, the statement must be followed by a semicolon. Click on the exclamation mark in the
query. Does it work?
Creating a SQL Query in Access
Open the ExampleDB database and in the Queries window,
Create a query in Design View. Close the Show
window without adding a table.
View icon on the left of the toolbar where you have seen the Design and Datasheet Views
SQL View icon.
The Design View window will become blank window
Let’s write a simple SQL query to find the first and last live in South Carolina, sorted Before we write the query, let’s think about
FirstName, LastName, and State of these fields are in tblEmployees Sort by LastName
We can easily turn these requirements into a SQL
FirstName, LastName, State
The use of single rather than double quotation marks around ‘SC’ is required by SQL.
Again, the statement must be followed by a semicolon. Click on the exclamation mark in the top toolbar to run the
If you choose Design View from the view menu on the top toolbar, you will see the Access Design View corresponding to this SQL statement.
You can return to the SQL view from the same menu.
Combining Criteria in a WHERE
In the previous lesson we wrote an Access query to find all last name, who live in Spartanburg
to do this search.
First, let’s figure out what we need Fields: FirstName, LastName,
Table: All of these fields are in tblEmployees Criteria: City = ‘Spartanburg
Sort: Sort by LastName
This is slightly more complex than the previou include both criteria with an AND
SELECT FirstName, LastName, City, HireDate
FROM tblEmployees
WHERE City=’Spartanburg’ AND HireDate from the view menu on the top toolbar, you will see the Access Design View corresponding to this SQL statement.
You can return to the SQL view from the same menu.
iteria in a WHERE Clause
In the previous lesson we wrote an Access query to find all the names of all employees, sorted by Spartanburg and who were hired before 1/1/2000. Let’s write a SQL query
re out what we need
FirstName, LastName, City, HireDate All of these fields are in tblEmployees
Spartanburg’ and HireDate earlier than 1/1/2000 Sort by LastName
slightly more complex than the previous statement because there are two criteria. We will include both criteria with an AND operator.
FirstName, LastName, City, HireDate
Spartanburg’ AND HireDate < #1/1/2000#
employees, sorted by . Let’s write a SQL query
As in the Access criteria, the pound signs indicate that a quantity is a date. Enter this statement into the SQL View window and try the query. Does it work?
Practice: Design a SQL query that lists first and last names of all employees who live in Spartanburg or Greer. Sort by City first and then by Last Name (Hint: Use OR instead of AND). Try it.
Practice: Design a SQL query that lists all addresses (Street, City, State, Zip Code) with Zip Codes beginning with ‘293”. Order by Zip Code (Hint: Think about Wildcard symbols. Use LIKE rather than an equal sign when you are using a wildcard.) Try it. Practice: Design a SQL query to list all employees who live in SC and who were hired in 2004. Order by Hire Date. (Hint: Think about the BETWEEN . . . AND . . .
comparison operator.) Try it.
Practice: Design a SQL query to list all employees who satisfy these two criteria:
• Department IDs either 100 or 102 • Hired before January 1, 2004
Sort the list by Department ID. Careful. This one is harder than the others. You will need to group the OR part of the statement within parentheses. Try it.
Using Multiple Tables in a SQL Statement
You can use more than one table in a SQL statement by designating the table name associated with each field, tblTableName.FieldName. The table name is first, followed by a period, and then the field name.
Let’s write a SQL query to find the names and salaries of all employees, sorted by salary. As usual, let’s figure out what we need.
Fields: FirstName, LastName, Salary
Table: FirstName and Last Name in tblEmployees, Salary in tblSalaries Criteria: None
Sort: Sort by Salary
We need to look at the two tables and take note of the common element that defines the relationship between the two tables. In this case, it is the primary key in both tables, EmployeeID.
The two tables are joined by the common element, name “EmployeeID” in both tables. We specify this with an INNER JOIN statement as is illustrated in the SQL query below: SELECT tblEmployees.FirstName, tblEmployees.LastName, tblSalaries.Salary FROM tblEmployees
INNER JOIN tblSalaries
ON tblEmployees.EmployeeID = tblSalaries.EmployeeID ORDER BY tblSalaries.Salary ;
Note that the FROM section contains one table in the join and the INNER JOIN section contains the other. The ON section specifies the elements that relate the two tables.
Try the SQL query above to see how it works.
Practice: Write a SQL query to list the names of all members of the personnel department, sorting the list by Last Names. Try it.
Practice: Write a SQL query to list the names of all exempt employees with a salary that is $40,000 or less. Sort the list by salaries in descending order.
Why Use SQL?
I am sure the question is on your lips, “Other than to torture poor defenseless CS 101 students, what good is SQL? Access is hard enough!”
Practice: We want to produce the following query using tblEmployees in EmployeesDB.
• List all employees with their Department ID • List them as First Name, Last Name, Dept ID
• Sort them by Last Name first and then by First Name
First do this with the Access Design View grid, as in Lesson 2. Is it easy to do?
Most database users will never need to use SQL statements, but there reasons to use SQL statements rather than Access’s Design View grid
1. SQL queries are faster than Access queries
If you are dealing with a large database, such as Banner, that contains thousands of tables and hundreds of thousands of records, and has many concurrent users, performance matters. Working with SQL statements can make a difference.
2. There are SQL-specific queries that
Access has SQL specific queries that cannot be
example, pass-through queries allow you to look for data in other non
pass-through query will be sent directly to Banner (an Oracle database) without the Access engine. This improves performance immensely.
3. SQL queries are more flexible than those designed using the Design View Grid.
The Practice problem you tried a moment ago is an example and Department IDs of employees. You would lik
before the last, like we are accustomed to seeing them. You also would like to sort them by last name first and then by first name.
It turns out that this is a bit of a problem using the Design View grid. If we put the first name before the last in the Design View grid, then the names will be sorted by first name first – not how we want them sorted.
If, on the other hand, we put the last name first in the Design View grid, then the sort will be done correctly, but they will not be displayed with the first name before the last.
1
Green, Martin. “Access and SQL, Part 1: Setting the SQL Scene.” <http://www.fontstuff.com/access/acctut14.htm>
Most database users will never need to use SQL statements, but there sometimes are good reasons to use SQL statements rather than Access’s Design View grid1.
SQL queries are faster than Access queries
If you are dealing with a large database, such as Banner, that contains thousands of tables and of records, and has many concurrent users, performance matters. Working with SQL statements can make a difference.
queries that can do things you cannot with the Design View grid
Access has SQL specific queries that cannot be duplicated easily with the design grid. For through queries allow you to look for data in other non-Access databases. A through query will be sent directly to Banner (an Oracle database) without needing to use
s improves performance immensely.
3. SQL queries are more flexible than those designed using the Design View Grid.
The Practice problem you tried a moment ago is an example1. Suppose you want to list names and Department IDs of employees. You would like the names to be displayed with the first name before the last, like we are accustomed to seeing them. You also would like to sort them by last name first and then by first name.
problem using the Design View grid. If we put the first name before the last in the Design View grid, then the names will be sorted by first name
not how we want them sorted.
If, on the other hand, we put the last name first in the Design View grid,
rrectly, but they will not be displayed with the
Green, Martin. “Access and SQL, Part 1: Setting the SQL Scene.” Access Tips. 2003. 27-July 2005 cctut14.htm>
sometimes are good
If you are dealing with a large database, such as Banner, that contains thousands of tables and of records, and has many concurrent users, performance matters. Working
can do things you cannot with the Design View grid.
duplicated easily with the design grid. For ccess databases. A SQL
needing to use
3. SQL queries are more flexible than those designed using the Design View Grid.
Suppose you want to list names e the names to be displayed with the first name before the last, like we are accustomed to seeing them. You also would like to sort them by last
There is a way to get around this, but it is a little doesn’t work well for this type of query.
A simple SQL query handles this ea The order of display is specified by the SELECT clause, whereas the sort order is specified by the ORDER BY clause.
There is a way to get around this, but it is a little cumbersome. Basically the Design View grid doesn’t work well for this type of query.
A simple SQL query handles this easily. The order of display is specified by the SELECT clause, whereas the sort order is specified by the ORDER BY clause.
