BIS245 Lab 4B

DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
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.
BIS245_W4b_iLab_Instructions.docx Page 1 of 20
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 lab 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 lab, 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.
• 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:
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
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.
BIS245_W4b_iLab_Instructions.docx Page 2 of 20
Preparation:
1. Get the Database from Doc Sharing:
a. Download the “Lab4_Start.accdb” Northwind database file from your course “Doc
Sharing” panel (Labs view) & 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 tab in Course Home.
b. You will have to upload the “Lab4_Start.accdb” file to your Citrix folder. Follow the
instructions located on the iLab Tab 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
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
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.
BIS245_W4b_iLab_Instructions.docx Page 3 of 20
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.
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
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.
BIS245_W4b_iLab_Instructions.docx Page 4 of 20
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.
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
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.
BIS245_W4b_iLab_Instructions.docx Page 5 of 20
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”.
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
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.
BIS245_W4b_iLab_Instructions.docx Page 6 of 20
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.)
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
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.
BIS245_W4b_iLab_Instructions.docx Page 7 of 20
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.
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
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.
BIS245_W4b_iLab_Instructions.docx Page 8 of 20
Click the “Run” icon to run the query.
The query result should appear as below.
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
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.
BIS245_W4b_iLab_Instructions.docx Page 9 of 20
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):
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
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.
BIS245_W4b_iLab_Instructions.docx Page 10 of 20
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
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
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.
BIS245_W4b_iLab_Instructions.docx Page 11 of 20
“Lab4_Query3”.
Tables to add:
• Customers
• Order Details
• Orders
• Products
• Suppliers
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
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.
BIS245_W4b_iLab_Instructions.docx Page 12 of 20
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.
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
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.
BIS245_W4b_iLab_Instructions.docx Page 13 of 20
When executed, your query should return 29 records.
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
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.
BIS245_W4b_iLab_Instructions.docx Page 14 of 20
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
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
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.
BIS245_W4b_iLab_Instructions.docx Page 15 of 20
Query 5: Find the customer details of all your customers in USA. 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 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.
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
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.
BIS245_W4b_iLab_Instructions.docx Page 16 of 20
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.
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
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.
BIS245_W4b_iLab_Instructions.docx Page 17 of 20
Enter the following query:
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
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.
BIS245_W4b_iLab_Instructions.docx Page 18 of 20
Run the query. The result should look like the following:
Save the query as “Lab4_Query5”. Save the database file.
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
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.
BIS245_W4b_iLab_Instructions.docx Page 19 of 20
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 lab, use the comment area of the Dropbox to explain what you accomplished in
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
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.
BIS245_W4b_iLab_Instructions.docx Page 20 of 20
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 Syllabus/”Due Dates for Assignments & Exams” for due dates.
End of Lab 4b