Select Queries
The most common type of query is the Select Query. Its purpose is to return a dataset that is a dynamic view into your database. In fact, in other SQL dialects, like T-SQL (SQL Server) or SQL*Plus (Oracle) a saved Select Query is called a View.
The Select Query is very flexible and allows you to see your data in a variety of ways.
- You can specify which columns you want to see.
- You can also restrict the rows returned based on one or more criteria.
- You can join multiple tables together on a common field(s).
- You can return unique records, the top or bottom number (or percentage), sort your data on one or more columns.
- And you can make your query interactive by adding parameters.
A Select Query can be created in the Query Builder view or directly in the SQL view. go to the Create Tab on the Ribbon and select Query Design.
The Query Designer (aka QBE) looks like Figure 1. The top pane shows which table or tables on which the query is based. The bottom pane shows which fields will be used in the query and the criteria used to restrict the rows.
Figure 1:
Switching to SQL View will result in the above query to look like this:
SELECT Products.ProductName, Products.Cost, Products.Price
FROM Products
WHERE (((Products.Cost)>=5))
ORDER BY Products.ProductName;
It is fairly easy to learn SQL by creating a query in the Query Builder and switching to SQL View.
Sections of a SELECT Query
In general, a Select Query will look like this:
SELECT [optional Predicate] [Field List]
FROM [Table/Query/Join]
WHERE [criteria to restrict rows]
ORDER BY [Field List] ASC/DESC
SELECT Clause
The Select Clause identifies which fields will be displayed in the result set of the query. The Query Builder always qualifies the field name with the table name, which is not strictly necessary unless the field name exists in multiple tables. This will be important when we talk about Joins, but for now, the Select Clause could also look like this:
SELECT ProductName, Cost, Price
You can also create calculated columns in the Select Clause. For instance, suppose I wanted to know the difference between the cost and the price of a product. I could add a calculation to a new column. In the Query Builder, it would look like this:
Figure 2:
Which would look like this in SQL:
SELECT ProductName, Cost, Price, [Price]-[Cost] AS Margin
You have to give the calculated column a name also called an 'alias', which I decided to call "Margin". In the Query Builder, it goes in front of the calculation. In SQL View, it follows the calculation with the AS keyword. The square brackets ([]) are not strictly necessary here, but there are times that they are. For instance, if you had a space in your field name (Product Name), you would be required to put square brackets around it ([Product Name]).
A word about naming: While Access SQL will allow you to use spaces, odd characters, and reserved words as field names by surrounding them in brackets, there are times when this will get you into trouble. Therefore it is best practice to avoid field and table names which will require brackets. You won't go wrong if you name your objects with letters, numbers, and the underscore only and avoid words like Date, Month, Table and other reserved words. You can find a list of reserved words here:
htps://msdn.microsoft.com/en-us/library/bb208875(v=office.12).aspx
You can also select ALL columns with the asterisk:
SELECT *
The asterisk is not the most efficient way of specifying columns, especially in a large table with many columns. If performance is an issue, you might want to restrict your query to just the columns you need. On the other hand, if your base table structure changes frequently, the asterisk can be useful because you don't have to change your query when you add or remove a column.
FROM Clause
The From Clause specifies the table or tables that form the basis of the query. Ultimately, of course, all the data comes tables, but you are not limited to specifying table names. You can also specify saved queries just as if they were tables. This is useful for creating "stacked" queries, that is, queries that build upon a previous query or queries.
In the Query Builder, the From clause is represented by "tables" in the upper pane (see Figure 1). To add another table or query, right-click in the upper pane and select Add Table in the pop-up box.
In SQL View, the from clause looks like this:
FROM Products (Products being a table in our database)
Or
FROM qryProductsSold (assuming qryProductsSold is a saved query)
Adding the From clause to the query above yields:
SELECT ProductName, Cost, Price, [Price]-[Cost] AS Margin
FROM Products
Another very important function of the From clause is to create Joins, that is, merge the records of two or more tables on common fields. For instance, if I wanted to see all of my Orders and their corresponding Order Details, I can join the Order table and the Order Details table. Figure 3 shows the Query Builder View.
Figure 3
The SQL View looks like this:
FROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID;
In this case, the fully qualified field name (that is with the table name in front of the field name) is required because the OrderID field exists in both tables.
There's a lot more to be said about Joins, and I'll do that in a later post.
Lastly, it is also possible to use an entire SQL statement in a From Clause by simply putting it in parentheses followed by an alias. In the SQL View, this would be perfectly legal:
SELECT FullName, Address, City, State, Zip
FROM (SELECT [FirstName] & " " & [Lastname] AS FullName, Address, City, State, Zip FROM Customer) AS FullCustomer;
This example is not particularly useful, but it does demonstrate how a SQL Statement can be used as a result set in the From clause. You have to be careful, however, because Access doesn't always handle this well. I'll also talk about that in a later post.
Next time, I'll continue with the Select Query by looking at how to restrict the rows returned (WHERE clause) in Select Queries Part 2: Restricting Rows - the Where Clause
No comments:
Post a Comment