Business 245 ILab Assignment part A and B

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 using the Query wizard 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