Courses/Computer Science/CPSC 203/CPSC 203 Template/Fall 2009 Teaching Assistants/F09 Thomas Burt/Lab 2.4

Lab 2.4

First, save this database to the desktop and open it.

We will be following off of the wiki today.

Examples Queries With Expressions

You will only need to know IF and nested IF. Please understand both the SQL syntax as well as the 'Expression' syntax that is used in design view.

IF function

We can use an IF statement to create a new result column in the query that categorizes the data based upon province:

SQL Mode

1. Create a new, blank query
2. Switch to SQL view
3. Paste the examples into the text area

SELECT 
Customers.[First Name], 
Customers.[Last Name], 
Cities.[City Name], 
Provinces.[Province Name],
IIf(([Provinces].[Province Name]="Alberta" Or [Provinces].[Province Name]="British Columbia"),"Western","Non-Western") AS Western

FROM Provinces INNER JOIN (Cities INNER JOIN (Customers INNER JOIN Orders ON Customers.ID = Orders.Customer) ON Cities.ID = Customers.[City Name]) ON Provinces.ID = Cities.[Province Name];

Design Mode

1. Right click in the Field column
2. Slect 'Build'
3. Place the following into the text box at the top

Customer Loyalty: IIf([Total Spent on Orders]>500,"Great",IIf([Total Spent on Orders]>250,"So-So","Not So Good"))

Nested IF function

Lets categorize how loyal the customers have been.

SQL Mode

Use the following expression

IIf([Total Spent on Orders] > 500, "Great",IIf([Total Spent on Orders] > 250, "So-So", "Not So Good")) AS [Customer Loyalty]

The entire SQL statement needs to include a [Total Spent on Orders] field, as shown here:

SELECT 

Customers.[First Name], 
Customers.[Last Name], 
Cities.[City Name], 
Provinces.[Province Name], 
Customers.[Email Address], 
Count(Products.Price) AS [Orders Placed], 
Sum(Products.Price) AS [Total Spent on Orders],

IIf([Total Spent on Orders] > 500, "Great",IIf([Total Spent on Orders] > 250, "So-So", "Not So Good")) AS [Customer Loyalty]

FROM Provinces INNER JOIN 
  (Cities INNER JOIN (
    Products INNER JOIN (
      Customers INNER JOIN Orders ON Customers.ID = Orders.Customer) 
    ON Products.ID = Orders.Product) 
   ON Cities.ID = Customers.[City Name]) 
  ON Provinces.ID = Cities.[Province Name]

GROUP BY Customers.[First Name], Customers.[Last Name], Cities.[City Name], Provinces.[Province Name], Customers.[Email Address];

There are other aggregate functions available. I found a listing here. Below, I included Avg([Products].[Price]).

SELECT 

Customers.[First Name], 
Customers.[Last Name], 
Cities.[City Name], 
Provinces.[Province Name], 
Customers.[Email Address], 
Count(Products.Price) AS [Orders Placed], 
Sum(Products.Price) AS [Total Spent on Orders],
Avg(Products.Price) AS [Average Spent On Each Order],

IIf([Total Spent on Orders] > 500, "Great",IIf([Total Spent on Orders] > 250, "So-So", "Not So Good")) AS [Customer Loyalty]

FROM Provinces INNER JOIN 
  (Cities INNER JOIN (
    Products INNER JOIN (
      Customers INNER JOIN Orders ON Customers.ID = Orders.Customer) 
    ON Products.ID = Orders.Product) 
   ON Cities.ID = Customers.[City Name]) 
  ON Provinces.ID = Cities.[Province Name]

GROUP BY Customers.[First Name], Customers.[Last Name], Cities.[City Name], Provinces.[Province Name], Customers.[Email Address];

Design Mode

Switch to design mode from SQL and examine the last two fields. 'Total Spent on Orders' has been renamed from 'Price', and this is the column the IF statement checks to make comparisons.