SQL Select Queries

Introduction. When a user has access to a database they are often interested in determining certain things based on information in the database. Let's say you have a database like a relatively typical customer/product/inventory database like the Northwind database and you want to determine things like the percentage of customers from each state in the northeast. Or maybe you want to determine the average monthly purchases for each customer. These sorts of things are done using queries of the database. While more complicated procedures on the database require a statistical analysis package, many questions can be answered by performing queries against the database.

Considering that it is desirable to be able to query a database there are currently two main approaches to implementing queries

QBE - Query By Example

SQL - Structured Query Language

Approaches to Query By Example are typically very dependent on the particular DBMS that is being used. On the other hand, if one learns SQL, one is able to more easily transport that knowledge into a variety of DBMSs. Knowledge of SQL, by itself, is a very desirable asset in the marketplace. SQL commands can be partitioned into data manipulation language statements (DML) and data definition language statements (DDL). In the present webpage we will focus on data manipulation language commands.

Considering the nature of the database operations we need to do for our e-commerce store we will be able to focus on just a few SQL commands and clauses. We will try to stay with the ANSI standard in SQL should be universally portable. For the convenience of making the SQL commands more obvious we will always write them in capital letters. The resulting records with particular fields determined by an SQL query is typically called a resultset.

We will focus on the SELECT _ FROM command in SQL and work through its implementation with a variety of clauses. The commands and clauses on which we will focus are

SQL Command	Short Description
SELECT _ FROM	Used to select particular fields from a table

SQL Clause	Short Description
ORDER BY	When selecting you need to use ORDER BY to let SQL know what field you want to use for ordering the records. This is done in either ASC or DESC order.
WHERE	This is usually used to limit/filter the contents of the results of a query.
DISTINCT	This is used to ensure that you do not get records with duplicate values in a particular field.
DISTINCTROW	This is used when you want to collect information on all rows that are distinct in any field.
TOP n	This limits the resultset to the TOP n records determined through either a DESCending or ASCending ORDER BY
TOP n PERCENT	This limits the resultset to the TOP n PERCENT of the records determined through either a DESCending or ASCending ORDER BY
GROUP BY _ HAVING	Allows you to group records by using particular HAVING criteria. It also allows you to use SQL Aggregate functions when creating resultsets that contain group subtotals.
INNER JOIN	This joins together two tables that have an exact match in both tables according to some field.
LEFT JOIN	This creates a resultset that has all the records in the first (LEFT) table in the join and any records on the right that have a matching column/field value.
RIGHT JOIN	This creates a resultset that has all the records in the second (RIGHT) table in the join and any records on the left that have a matching column/field value.

In order to summarize data, there are a variety of SQL Aggregate Functions.

SQL Aggregate Functions	Short Description
AVG( ) _ AS	This returns the average of all the values in a (column) placed in a new column with a name determined by what follows AS
COUNT( ) _ AS	This returns the count of the number of values in a (column) with a name determined by what follows AS
SUM( ) _ AS	This returns the sum of all the values in a (column) with a name determined by what follows AS
MIN( ) _ AS	This returns the minimum of all the values in a (column) with a name determined by what follows AS
MAX( ) _ AS	This returns the maximum of all the values in a (column) with a name determined by what follows AS

It is my opinion that these SQL commands and aggregate functions are best illustrated by a large variety of examples. We will make use of ASPs to execute our SQL commands while connecting to the Northwind database. We will display the results of these queries in HTML tables. You are likely to create just one ASP file and then modify the SQL command and possibly the table configuration within it for each example.

SELECT _ FROM Examples. The first SQL Query you will develop involves typing the following command in the SQL query window of MSQuery. Make sure you don't make any typos. This exercise will help you understand the added value of the Query Builder in the Utilities menu. The form of the SELECT _ FROM command is

SELECT ... fields ... FROM table

You should cut and paste the command or type it in the query window after clicking on the SQL button. First I will present the SQL command and then I will develop the code. At first, we will be working with the Products table within the Northwind database.

A typical SELECT _ FROM command might look like the following.

SELECT Products.ProductID, Products.ProductName, Products.QuantityPerUnit, Products.UnitPrice FROM Products

Notice we are trying to view the first and last name of each potential admittee along with their phone number. You should also notice that the SQL command joins each field after the table name using the period (.) operator using a tablename.fieldname syntax. This isn't necessary when you are selecting from only one table, but it is very good programming practice.

Now you need to create the following NorthwindSQL.asp file and upload this to the server.

' Develop the SQL command that will be used to develop the recordset
Dim sqlCommand
sqlCommand = "SELECT Products.ProductID, Products.ProductName," & _ "Products.QuantityPerUnit, Products.UnitPrice FROM Products"

' Instantiate a recordset object and retrieve the info from the Products table
Dim rsProducts
Set rsProducts = Server.CreateObject("ADODB.Recordset")
rsProducts.Open sqlCommand, connNorthwind

' Display the contents of the query

Response.Write "<center><table bgcolor=aaccff border=1>"
Response.Write"<tr><td>Product ID</td>" & _
"<td>Product Name</td>" & _
"<td>Quantity per Unit</td>" & _
"<td>Unit Price</td></tr>"

Do While Not rsProducts.EOF

Response.Write "<tr>"
Response.Write "<td>" & rsProducts("ProductID") & "</td>"
Response.Write "<td>" &rsProducts("ProductName") & "</td>"
Response.Write "<td>" &rsProducts("QuantityPerUnit") & "</td>"
Response.Write "<td>" &rsProducts("UnitPrice") & "</td>"
Response.Write "</tr>"
rsProducts.MoveNext

Loop

Response.Write "</table></center>"

' Clean up the ADO objects
rsProducts.Close
Set rsProducts = Nothing

connNorthwind.Close
Set connNorthwind = Nothing
%>

While this table has other fields such as SupplierID, UnitsInStock and ReorderLevel, we have selected the above fields and displayed them in a table that should look somewhat like the following.

The ORDER BY Clause. Now we will work with essentially the same query, but we will also order the resultset according to the ProductName field in ascending order. This requires typing in the following SQL command.
SELECT Products.ProductID, Products.ProductName, Products.QuantityPerUnit, Products.UnitPrice FROM Products ORDER BY Products.ProductName ASC

You are going to need to change only one statement in the previous ASP. You want to make sure you do it in a way that doesn't cause problems due to word wrap.

sqlCommand = "SELECT Products.ProductID, Products.ProductName," & _
"Products.QuantityPerUnit, Products.UnitPrice" & _
" FROM Products ORDER BY Products.ProductName ASC"

You can also use the ORDER BY clause to put all of the records with a common field value together in the resultset. Consider the following situation where you want to list all the products in groups according to their UnitPrice but still list out a variety of other fields.

SELECT Products.ProductID, Products.ProductName, Products.QuantityPerUnit, Products.UnitPrice FROM Products ORDER BY Products.UnitPrice ASC

The SQL command in the ASP is likely to look like the following

sqlCommand = "SELECT Products.ProductID, Products.ProductName," & _
"Products.QuantityPerUnit, Products.UnitPrice" & _
" FROM Products ORDER BY Products.UnitPrice ASC"

Notice the following image that shows some quite disparate products being grouped together due to their price.

If you want to make sure they are first grouped by UnitPrice and then they are alphabetized by ProductName the sqlCommand must be modified to the following.
sqlCommand = "SELECT Products.ProductID, Products.ProductName," & _
" Products.QuantityPerUnit, Products.UnitPrice" & _
" FROM Products ORDER BY Products.UnitPrice, Products.ProductName ASC"

So you can use prioritized criteria for the ordering.

As part of using the ORDER BY clause you can also select the TOP values based on the criteria specified in the clause. The two options we have available are the TOP n and TOP n PERCENT.

The WHERE Clause. You can control the content of the resultset by using a WHERE clause. Now consider the situation where we want to query the database to find out the products within a particular price range, let's say between 35 and 50. We will make use of what we've used before and order the products by UnitPrice and ProductName. The SQL command is

sqlCommand = "SELECT Products.ProductID, Products.ProductName," & _
"Products.QuantityPerUnit, Products.UnitPrice FROM Products" & _
" WHERE (Products.UnitPrice >= 35) AND (Products.UnitPrice <= 50)" & _
" ORDER BY Products.UnitPrice, Products.ProductName ASC"

Notice how the WHERE clause is in parentheses and is before the ORDER BY clause in the command line.

You should look at the resultset to validate that you've gotten the records you want. You may find it worthwhile to play around with developing some other criteria to see the results.

SQL Aggregate Functions. The aggregate functions are used to compute some basic representative measures of a column or field. For this first example I will only focus on some statistics relative to UnitPrice.
SELECT COUNT(ProductID) AS NumberOfProducts, AVG(UnitPrice) AS AverageUnitPrice, MIN(UnitPrice) AS MinimumUnitPrice, MAX(UnitPrice) AS MaximumUnitPrice FROM Products

Obviously this can become more elaborate and probably more useful when comparing results between groups. You are likely to want to save this in a new ASP called NorthwindSQLAggregate.asp.

<%@ Language=VBScript %>
<% Option Explicit %>


<%
' Open a connection to our SQL Server database
' We will use the DSN-less connection
Dim connNorthwind
Set connNorthwind = Server.CreateObject("ADODB.Connection")
connNorthwind.ConnectionString="Driver={SQL Server}; Server=cisdev.quinnipiac.edu;" & _
"Database=northwind; UID=cis; PWD=csatqu"

connNorthwind.Open

' Develop the SQL command that will be used to develop the recordset
Dim sqlCommand
sqlCommand = "SELECT COUNT(ProductID) AS NumberOfProducts, " & _
"AVG(UnitPrice) AS AverageUnitPrice, MIN(UnitPrice) AS MinimumUnitPrice, " & _
"MAX(UnitPrice) AS MaximumUnitPrice FROM Products"

' Instantiate a recordset object and retrieve the info from the Products table
Dim rsProducts
Set rsProducts = Server.CreateObject("ADODB.Recordset")
rsProducts.Open sqlCommand, connNorthwind

' Display the results of the query

Response.Write "<center><table bgcolor=aaccff border=1>"
Response.Write"<tr><td>Number of Products</td>" & _
"<td>Average Unit Price</td>" & _
"<td>Minimum Unit Price</td>" & _
"<td>Maximum Unit Price</td></tr>"

Do While Not rsProducts.EOF

Response.Write "<tr>"
Response.Write "<td>" & rsProducts("NumberOfProducts") & "</td>"
Response.Write "<td>" &rsProducts("AverageUnitPrice") & "</td>"
Response.Write "<td>" &rsProducts("MinimumUnitPrice") & "</td>"
Response.Write "<td>" &rsProducts("MaximumUnitPrice") & "</td>"
Response.Write "</tr>"
rsProducts.MoveNext

Loop

Response.Write "</table></center>"

' Clean up the ADO objects
rsProducts.Close
Set rsProducts = Nothing

connNorthwind.Close
Set connNorthwind = Nothing
%>

The results of the query should look like the following.

These results are not particularly informative. It would probably be more interesting to see some sort of comparative results.

The GROUP BY _ HAVING Clause. The GROUP BY clause is considerably different than using the ORDER BY clause to list out the desired fields where records are ordered into groups based on some field. The GROUP BY clause is used with aggregate functions to compare results across different groupings. The only field that can be used outside of an aggregate function is the field that is used for the grouping.

Now, what we want to do with our previous ASP is modify it slightly so that we are computing these same aggregate results based on what we purchase from each supplier. Thus the results will be grouped by SupplierID and the ASP needs to be modified to be the following NorthwindSQLGroupBy.asp.

<%@ Language=VBScript %>
<% Option Explicit %>


<%
' Open a connection to our SQL Server database
' We will use the DSN-less connection
Dim connNorthwind
Set connNorthwind = Server.CreateObject("ADODB.Connection")
connNorthwind.ConnectionString="Driver={SQL Server}; Server=cisdev.quinnipiac.edu;" & _
"Database=northwind; UID=cis; PWD=csatqu"

connNorthwind.Open

' Develop the SQL command that will be used to develop the recordset
Dim sqlCommand
sqlCommand = "SELECT SupplierID, " & _
"AVG(UnitPrice) AS AverageUnitPrice, MIN(UnitPrice) AS MinimumUnitPrice, " & _
"MAX(UnitPrice) AS MaximumUnitPrice FROM Products GROUP BY SupplierID"

' Instantiate a recordset object and retrieve the info from the Products table
Dim rsProducts
Set rsProducts = Server.CreateObject("ADODB.Recordset")
rsProducts.Open sqlCommand, connNorthwind

' Display the results of the query

Response.Write "<center><table bgcolor=aaccff border=1>"
Response.Write"<tr><td>Supplier ID</td>" & _
"<td>Average Unit Price</td>" & _
"<td>Minimum Unit Price</td>" & _
"<td>Maximum Unit Price</td></tr>"

Do While Not rsProducts.EOF

Response.Write "<tr>"
Response.Write "<td>" & rsProducts("SupplierID") & "</td>"
Response.Write "<td>" &rsProducts("AverageUnitPrice") & "</td>"
Response.Write "<td>" &rsProducts("MinimumUnitPrice") & "</td>"
Response.Write "<td>" &rsProducts("MaximumUnitPrice") & "</td>"
Response.Write "</tr>"
rsProducts.MoveNext

Loop

Response.Write "</table></center>"

' Clean up the ADO objects
rsProducts.Close
Set rsProducts = Nothing

connNorthwind.Close
Set connNorthwind = Nothing
%>

Now the results should look like the following.

SQL JOIN Clauses. In many situations the user needs to pull in information from two or more different tables. In these situations it is very likely that the user will need to use some variant of a JOIN clause. Maybe you want to find out more about which customers have ordered which products or how much money they have spent. This sort of investigation requires information from different tables within the same database. There are three main types of join an inner join, a left join and a right join. Each of these joins gives particular priority to a table listed in each join clause. At present, I will only give an example of an inner join.

Consider the situation in the Northwind database where we have all of our basic information about each product in the Products table. We don't give the suppler name, address and contact person each time we list a product for that supplier, we only list the SupplierID. In the Suppliers table we have each supplier and their contact information listed. Now we want to create a query that will ist the name of each supplier along with the name of each product. This requires using a SELECT command with a JOIN clause like the following.

SELECT Suppliers.SupplierID, Suppliers.CompanyName, Products.ProductName From Suppliers INNER JOIN Products ON Suppliers.SupplierID=Products.SupplierID

The INNER JOIN selects only those records that appear in both of the tables from which records are selected. Now you want to create the following NorthwindSQLJoin.asp.

<%@ Language=VBScript %>
<% Option Explicit %>


<%
' Open a connection to our SQL Server database
' We will use the DSN-less connection
Dim connNorthwind
Set connNorthwind = Server.CreateObject("ADODB.Connection")
connNorthwind.ConnectionString="Driver={SQL Server}; Server=cisdev.quinnipiac.edu;" & _
"Database=northwind; UID=cis; PWD=csatqu"

connNorthwind.Open

' Develop the SQL command that will be used to develop the recordset
Dim sqlCommand
sqlCommand = "SELECT Suppliers.SupplierID, Suppliers.CompanyName, Products.ProductName " & _
"From Suppliers INNER JOIN Products ON Suppliers.SupplierID=Products.SupplierID"

' Instantiate a recordset object and retrieve the info from the Products table
Dim rsProducts
Set rsProducts = Server.CreateObject("ADODB.Recordset")
rsProducts.Open sqlCommand, connNorthwind

' Display the results of the query

Response.Write "<center><table bgcolor=aaccff border=1>"
Response.Write"<tr><td>Supplier ID</td>" & _
"<td>Company Name</td>" & _
"<td>Product Name</td></tr>"

Do While Not rsProducts.EOF

Response.Write "<tr>"
Response.Write "<td>" & rsProducts("SupplierID") & "</td>"
Response.Write "<td>" &rsProducts("CompanyName") & "</td>"
Response.Write "<td>" &rsProducts("ProductName") & "</td>"
Response.Write "</tr>"
rsProducts.MoveNext

Loop

Response.Write "</table></center>"

' Clean up the ADO objects
rsProducts.Close
Set rsProducts = Nothing

connNorthwind.Close
Set connNorthwind = Nothing
%>

The following image represents what you should see when you execute the ASP.

While there are many more records, this should give you some sense of how a join clause works.

A LEFT JOIN will give priority to the records in the table that is listed on the left so that each of these records is only used once. The RIGHT JOIN works analogously.