DEVRY BIS245 iLab 4 of 7: Database Design Based on Data Requirements and Business R

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 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:

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

DeVry University

Student Lab Activity

BIS245 Database Essentials for Business with Lab

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

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

DeVry University

Student Lab Activity

BIS245 Database Essentials for Business with Lab

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.

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

DeVry University

Student Lab Activity

BIS245 Database Essentials for Business with Lab

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

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

DeVry University

Student Lab Activity

BIS245 Database Essentials for Business with Lab

After clicking “Query Design”, the Tables window will open. Hold down the Ctrl key, and click to selectthree tables (Customers, Orders, andEmployees). Then, click “Add“.

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

DeVry University

Student Lab Activity

BIS245 Database Essentials for Business with Lab

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.)

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

DeVry University

Student Lab Activity

BIS245 Database Essentials for Business with Lab

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.

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

DeVry University

Student Lab Activity

BIS245 Database Essentials for Business with Lab

Click the “Run” icon to run the query.

The query result should appear as below.

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

DeVry University

Student Lab Activity

BIS245 Database Essentials for Business with Lab

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):

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

DeVry University

Student Lab Activity

BIS245 Database Essentials for Business with Lab

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

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

DeVry University

Student Lab Activity

BIS245 Database Essentials for Business with Lab

“Lab4_Query3”.

Tables to add:

• Customers

• Order Details

• Orders

• Products

• Suppliers

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

DeVry University

Student Lab Activity

BIS245 Database Essentials for Business with Lab

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.

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

DeVry University

Student Lab Activity

BIS245 Database Essentials for Business with Lab

When executed, your query should return 29 records.

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

DeVry University

Student Lab Activity

BIS245 Database Essentials for Business with Lab

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

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

DeVry University

Student Lab Activity

BIS245 Database Essentials for Business with Lab

Query 5:Find the customer details of all your customers in USA. Use the straight SQL approach (asopposed 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.

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

DeVry University

Student Lab Activity

BIS245 Database Essentials for Business with Lab

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 Viewas shown below.

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

DeVry University

Student Lab Activity

BIS245 Database Essentials for Business with Lab

Enter the following query:

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

DeVry University

Student Lab Activity

BIS245 Database Essentials for Business with Lab

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

Save the query as “Lab4_Query5”. Save the database file.

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

DeVry University

Student Lab Activity

BIS245 Database Essentials for Business with Lab

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

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

DeVry University

Student Lab Activity

BIS245 Database Essentials for Business with Lab

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

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.

Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.