BIS 345 Lab help

DeVry University

Student Lab Activity

BIS 345 Data Analysis for Decision Making

Lab 4A Student Answer Sheet

Student Name: Bonny Tiley

Using Lab 4 provide copies of your SQL Statements and/or results for each Section listed below

Part A Supplier and product list (10 points)

a. Paste your SQL Statement here:

b. SELECT S.CompanyName, COUNT (P.ProductID) as "No. of Products"

c. FROM Suppliers S INNER JOIN Products P

d. ON S.SupplierID = P.SupplierID

e. GROUP By S.CompanyName

f. Screen print your results and paste here:

Part B: Order information (10 points)

a. Paste your SQL Statement here:

b. SELECT O.OrderID, CONVERT (varchar, O.OrderDate, 101) as OrderDate,

c. ROUND (Sum(OD.UnitPrice * OD.Quantity * (1 - OD.Discount)),2) as Total

d. FROM Orders O INNER JOIN [Order Details] OD

e. ON O.OrderID = OD.OrderID

f. Group BY O.OrderID, O.OrderDate;

g. Screen print your results and paste here:

Part C: Customer and order (10 points)

a. Paste your SQL Statement here:

b. SELECT C.CompanyName, O.OrderID,

c. Convert (varchar (10), O.OrderDate, 101) as OrderDate,

d. ROUND(Sum(OD.UnitPrice * OD.Quantity * (1 - OD.Discount)),2) as Total

e. FROM Customers C INNER JOIN Orders O

f. ON C.CustomerID = O.CustomerID

g. INNER JOIN [Order Details] OD ON

h. O.OrderID = OD.OrderID

i. GROUP BY C.CompanyName, O.OrderID, O.OrderDate

j. ORDER BY C.CompanyName;

k. Screen print your results and paste here:

Part D: Employee and territory list (10 points)

l. Paste your SQL Statement here:

m. Screen print your results and paste here:

Part E: Order and product (10 points)

a. Paste your SQL Statement here:

b. Screen print your results and paste here:

Part F: Best customer list (10 points)

a. Paste your SQL Statement here:

b. Screen print your results and paste here:

Part G: Join Shipper list (10 points)

a. Paste your SQL Statement here:

b. Screen print your results and paste here:

Part H: Account payable (10 points)

a. Paste your SQL Statement here:

b. Screen print your results and paste here

Part I: Create a dataset for reporting (10 points)

c. Paste your SQL Statement here:

d. Screen print your results and paste here:

Part J: Report (10 points)

a. Paste a screen print of the report view:

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.

.

Lab4_Questions.docx Page 4 of 4