Courses/Computer Science/CPSC 203/CPSC 203 Template/Labs Template/Week 2 - Lab 2: Multi-table queries, Aggregate Qureies

Notes

• Skills covered in this module:

1. Create Table Relationships
2. Create Multi-table Queries
3. Create Aggregate Queries

• A step by step simple example of how to create Table Relationships , Multi-table and Aggregate Queries.
• Currently, sample screenshots are provided. Once a copy of Office 2007 is obtained, sample databases will be made, along with their corresponding screenshots.

Files

Here are some example access files to work with:

Media:W08 BookExampleWQueries 20080228.mdb

Media:W08 DBAnalysisExample 20080311.mdb

Media:W08 TraceExamples BookExampleWQueries 20080304.mdb

Background Resources

• • Lab 10 of Fluency Computer Skills Workbook

Table Relationships

What are table relationships and what are they used for? Relationships are essentially links that associate a field in one table with a field in another table. For example, a school database contains two tables consisting of Students and Classes.

The Students table holds the students' names and addresses.

The Class table holds information about the Students, in particular students in a Math Class.

Both tables share a common field, the Student Name field. By linking the Student Name fields, you ensure that John in the Students table is the same John listed in the the Math Class table.

Creating these table relationships are important if we want to be able perform queries with multiple tables. In the next section a step by step approach to creating a relationship between tables is presented.

Creating Table Relationships

First we need examine the current table relationships, assuming you have tables already created click on the Database Tools tab at the top.

In the ribbon click on the Relationships button.

In the ribbon, click the Show Table button.

The Show Tables window should appear, select a table called Customers and then click on the add button.

Select the Order Details table and then click on the add button, you should see both tables (Customers and Order Details) appear in the Relationships field. Click on the Close button for the Show Tables window.

To add a relationship between Customers and Order Details, you first need to determine which fields both tables have that can allow you to associate the field in one table with a field in another table. For Customers that field is called "Customer ID" and for Order Details that field is called "Customer ID". Place the cursor on the Title bar at the top of the Customers table. Click and drag the Customers tab to move and position it around in the screen.

Click the "Customer ID" field in the Customers table, then drag it and rest it on top of the other "Customer ID" in the Order Details table.

You should release the mouse button, and then the Edit Relationships window should appear. Click on the Create button to establish a link between the two "Customer ID" fields in both tables.

The relationship field should look like this now, you have established a link between the "Customer ID" field in the Customers table to the "Customer ID" field in the Order Details table. The same number in both tables refers to the same same customer.

With the table relationships set up now, we can perform some multi-table queries to extract information from more that one table.

Create Multi-table Queries

So now that we have established relationships between tables, you will now learn how to create queries from multiple tables. The Query Trace Mechanism teaches a way of “thinking” about a query. Essentially beginning with the Cartesian Product, you can trace a query by deleting rows and columns to reach the final result set.

However, it is worthwhile to note a small set of basic query patterns. Fairly complex patterns of data analysis can be conducted by linking together sequential queries using the following query patterns.

You can follow along and use the access database provided named "W08_BookExampleWQueries_20080228.mdb".

In each case a query pattern is followed by an example of how it would look in the Access Query GUI:

***MAKE SURE THAT THE PATTERN IS SQL COMPLIANT

One: Give me ALL the data in a table.

This query retrieves all data from a single table and displays the information.

PATTERN:
  SELECT * 
  FROM TableName
  ;

EXAMPLE:

RESULT:

Two: Give me ALL the data across SEVERAL tables

This query retrieves all the data from multiple tables and displays all the information

PATTERN:

SELECT *
FROM TableName1, TableName2 ….. TableNameN
WHERE( Table1.Pkey = Table2.Fkey) …. And (TableNameN-1.Pkey = TableNameN.Fkey)
;

Note:
Relationships need to be defined.
Pkey – primary key of the “parent” table.
Fkey – Foreign key of the “child” table. 

EXAMPLE:

PARTIAL RESULT:

Three: Give Me Selected fields from Several Tables

This query retrieves some specific fields from multiple tables and displays the specified information

PATTERN:

SELECT TableNamei.FieldNamej, TableNamep.FieldNameq ….
FROM TableNamei, TableNamep …
WHERE( TablNamei.Pkey = TableNamep.Fkey) …. AND (…) …
;

EXAMPLE:

RESULT:

Four: Give me Selected fields from Several Tables with Constraints

This query retrieves some specific fields from multiple tables, however with some constraints and displays the specified information. First we will show the query using the logical AND constraint and then we will perform the same query but using a logical OR constraint. You should notice that depending on which logical condition employed, you should get different results.

AND PATTERN:
 SELECT TableNamei.FieldNamej, TableNamep.FieldNameq ….
FROM TableNamei, Tablenamep …
WHERE ( Tablei.Pkey = Tablep.Fkey) …. 
AND TableNamei.FieldNamej = “VALUE1” AND … TableNamep.FieldNameq = “Value2”
;

Note – text values in “”, while number values do not need quotes. 

EXAMPLE:

RESULT:

OR Pattern:

SELECT TableNamei.FieldNamej, TableNamep.FieldNameq ….
FROM TableNamei, Tablenamep …
WHERE ( Tablei.Pkey = Tablep.Fkey) …. 
AND TableNamei.FieldNamej = “Value1” OR  TableNamep.FieldNameq = “Value2”
;

EXAMPLE:

RESULT:

Five: Give Me Selected Fields from Several Tables with Custom Calculations

This query retrieves all data from multiple tables and displays the information with some added calculations. For example, determining sales profits based on the information retrieved such as: book title sales, book price, book advance, and book royalty rate.

PATTERN:
SELECT TableNamei.FieldNamej, TableNamep.FieldNameq …. , 
CalcField =TableNamei.Fieldnamej <operator> TableNameP.Fieldnameq <operator>  ….
FROM TableNamei, Tablenamep …
WHERE ( TableNamei.Pkey = TableNamep.Fkey) …. 
;

EXAMPLE:

RESULT:

Six: Give me Aggregate queries using selected fields from several tables

This query retrieves some specific information as well as uses a field to perform a calculation. For example, this query will obtain all book publishers and performs an average calculation to determine what each book publisher's average is for giving an advance to book authors.

PATTERN:
SELECT TableNamei.FieldNamej, StatFunction(TableNamep.FieldNameq) As  StatFieldnameq ….
FROM TableNamei, Tablenamep …
WHERE( TableNamei.Pkey = TableNamep.Fkey) …. 
GROUPBY TableNamei.FieldNamej
;

EXAMPLE:

RESULT:

Seven: Give me Aggregate queries using selected fields from several tables with Constraints on the Groups Shown

This query retrieves the same information as the previous query (six) did, however with some constraints using logical AND as well as logical OR operations.

AND PATTERN:
SELECT TableNamei.FieldNamej, StatFunction(TableNamep.FieldNameq) As  StatFieldnameq ….
FROM TableNamei, Tablenamep …
WHERE( TableNamei.Pkey = TableNamep.Fkey) …. 
GROUPBY TableNamei.FieldNamej
HAVING TableNamei.FieldNamej = “Value1” AND …
TableNamep.FieldNameq = “Value2”;

EXAMPLE:

RESULT:

OR PATTERN:

SELECT TableNamei.FieldNamej, StatFunction(TableNamep.FieldNameq) As  StatFieldnameq ….
FROM TableNamei, Tablenamep …
WHERE( TableNamei.Pkey = TableNamep.Fkey) …. 
GROUPBY TableNamei.FieldNamej
HAVING TableNamei.FieldNamej = “Value1” OR …
TableNamep.FieldNameq = “Value2”;

EXAMPLE:

RESULT: