Week 4 iLab for evagat

DeVry University Student Lab Activity BIS245 Database Essentials for Business with Lab

A. Lab #: BSBA BIS245A-4B

B. Lab 4B of 7: Completing Queries

C. Lab Overview—Scenario / Summary

TCOs:

# 6: Given a physical database containing tables and relationships and business requirements, create the necessary queries.

Scenario/Summary

The iLab begins with a simple example of query development using Access; then, evolves to more complex queries which the student should perform after completing the first exercise. The student can create a query with the wizard, with query design view, or with SQL statements. The Northwind database will be used again in this lab.

Upon completing this iLab, you should be able to

• create a query by following lab instruction;

• create a query by using either query designer, or query wizard;

• create a query by using SQL statements; and

• interpret the results of queries.

D. Deliverables

Submit the MS Access Database file that contains the queries created in this lab.

Step	Deliverable	Points
1	Query #1—Step-by-step
2	Query #2—Compound Statements
3	Query #3—Suppliers—step-by-step
4	Query #4—Suppliers (more advanced)
5	Query #5—Customers—using SQL Statements
6	Query #6—Putting it all together

E. Lab Steps

Preparation

1. Download the database starter file, Lab4_Start.accdb (Northwind database), from the Week 4 iLab page and save the file to your local drive.

2. Using Citrix for MS Visio and/or MS Access

a. If you are using the Citrix remote lab, follow the login instructions located in the iLab area in Course Home.

b. You will have to upload the Lab4_Start.accdb file to your Citrix folder. Follow the instructions located in the iLab area in Course Home.

3. The E-R diagram for the database is represented below.

4. Start MS Access:

a. If you are using Citrix, click on Microsoft Office Applications folder.

b. If you are using Visio on a local computer, select Microsoft Office from your Program Menu.

Lab:

Step 1: Query #1 Using Step-by-Step Instructions

Open the Lab4_Start.accdb in Access by going through the File Menu, Open command.

Query 1: In the Create ribbon, use the Query Design function to find the list of employees who worked on orders placed by UK customers. The list should be presented in ascending order of the employee last names

After clicking Query Design, the Tables window will open. Hold down the Ctrl key, and click to select three tables (Customers, Orders, and Employees). Then, click Add.

The tables are added to the query design panel. Drag and drop (or double click on the field names) to add the FirstName and LastName fields from the Employees table onto the query design grid. Add the Country field from the Customers table.

In the Country field, Criteria Row, enter “UK” to filter so that only the UK customers will show in the query results. Also, set the Last Name field, Sort Row to Ascending.

The grid now looks like the following. (Note that the tables have been rearranged to better show the relationships. You may choose to do this also.)

Select the Property Sheet function from the Query Tools, Design ribbon. Set the Unique Value property to Yes. Notice that the properties shown are for the query rather than a particular field. If you are not seeing the appropriate properties, move your cursor to the upper part of the design grid displaying the tables.

Click the Run icon to run the query.

The query result should appear as below.

Save the query by clicking the Save button at the top left portion of the screen. For a query name enter Lab4_Query1.

Step 2: Query #2 Using Compound Statements

Using the same procedures described in Step 1, find the list of employees who worked on orders placed by Germany, UK, and USA customers. The list should be presented in ascending order of the employee last names. Make sure Unique Values is set to No.

Hint: In the criteria row, under Country, key in “UK” OR “Germany” OR “USA”

The results will look like the following (only partial table is displayed to save space. You should produce 300 records).

Save the query as Lab4_Query2.

Step 3: Query #3 Using Step-by-Step Instructions

Query 3: Make a list of suppliers, who supply products ordered by USA customers.

Note that for this query, only the major steps are demonstrated here. Refer to previous steps if you need further assistance.

Create a new query and add the following tables. Name the query as Lab4_Query3.

Tables to add:

· Customers

· Order Details

· Orders

· Products

· Suppliers

Here’s a snapshot of how your query should look like in the Design mode.

Note that you need to set the properties to display only the unique values (just like Query1). Also, notice that even though the CompanyName and Country fields from the Customers table are added to the grid, the Show check boxes are unchecked. These two fields will not appear in the query results.

When executed, your query should return 29 records.

Step 4: Query #4

Using the procedures described in Step 3, find the list of suppliers who supply products ordered by German customers.

When executed, your query should return 29 records, a part of which is displayed.

Save the query as Lab4_Query4.

Note that you need to set the properties to display only the unique values (just like Query1).

Step 5: Query #5

Query 5: Find the customer details of all your customers in the United States. Use the straight SQL approach (as opposed to Query wizard).

In this query, we won’t use the Query Designer or Query wizard. This is to demonstrate the SQL coding approach. SQL (Structured Query Language) is a very powerful language. It has a rich set of features to manipulate data in a number of ways.

Guidelines for SQL Query

-­‐ Select the fields for the query.

-­‐ Determine which table or tables contain those fields.

-­‐ Determine criteria.

-­‐ Determine Sort order.

-­‐ Determine grouping.

-­‐ Determine any update operations to be performed.

Basic SQL Commands

The basic form of SQL expression is quite simple.

SELECT—FROM—WHERE

The statement begins with SELECT clause, which consists of the word SELECT, followed by a list of those fields you want to include.

Next, there is a FROM clause, which consists of the word FROM, followed by a list of tables involved in the query

Finally, there is an OPTIONAL WHERE clause, which consists of the word WHERE, followed by any criteria that the data must satisfy.

The command ends with a Semicolon (;).

Simple criteria: The criterion following the word WHERE is called a Simple Criterion. A Simple Criterion has the form: Field name, Comparison Operator, then either another field name or a value.

Comparison Operators

= Equal to

< Less than

> Greater than

<= Less than or equal to

>= Greater than or equal to

<> or ! Not equal to

Create a new query by clicking Query Design. However, close the Show Table dialog box without selecting any tables. The Query is shown in Design View. Using the View option, change to SQL View as shown below.

Enter the following query.

Run the query. The result should look like the following.

Save the query as Lab4_Query5. Save the database file.

Step 6: Query #6

In this query, you will demonstrate your understanding of queries.

Using the steps described in Step 5, create a new query using SQL View. Enter the following query.

SELECT Customers.CompanyName, Customers.ContactName, Orders.EmployeeID, Orders.OrderDate, Orders.ShippedDate, Orders.ShipVia

FROM Employees INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID =

Orders.CustomerID) ON Employees.EmployeeID = Orders.EmployeeID;

The results will look like the following, with 830 records.

Save the query as Lab4_Query6. Save the database file.

When you upload your iLab, use the comment area of the Dropbox to explain what you accomplished in this query.

Step 7: Submit Deliverables

Save your MS Access Lab4_Start.accdb file as YourName_Lab4 _Finitial.accdb.

Submit the Access file created during this assignment to the weekly iLab Dropbox located on the silver tab at the top of this page.

Do not forget to provide your comments from Step 6 in the comments area of the Dropbox.

(See the Syllabus section "Due Dates for Assignments & Exams" for due date information.

End of Lab 4b

Copyright © 2010 by DeVry Educational Development Corporation. All rights reserved. No part of this work may be reproduced or used in any form or by any means – graphic, electronic, or mechanical, including photocopying, recording, Web distribution or information storage and retrieval systems – without the prior consent of DeVry Educational Development Corporation. Page 1