IT215 Assignment 3 PDF

Preview

Summary

assignment...

Description

IT 215 (Fall 2019) Assignment 3 Due: 6pm on Tuesday (10/1/2019) (50 points) This is an individual assignment. A Word document and the supporting Access database files are expected. Late submission will not be graded. Question 1 (30 points). Multiple Table Queries. Your word document should include a screenshot of your query design view and the result. In addition, provide the SQL code. Consider SupplierParts.accdb stored in the Blackboard folder. Create the queries returning the following information: a. For each order show the supplier name, part name, color, and quantity. Provide SQL code.

b. For each order, show the order id, supplier name, part name, color, quantity, and total weight of all parts in the order. Please identify the Order ID of the heaviest orders. Note: Weight for Each Parts in an Order = Parts.Weight * Orders.Quantity c. d. e. f.

Show the total weight ordered from each supplier. Provide SQL code. Show the total number of units from each supplier. Provide SQL code. Show the minimum, maximum, and average quantity of parts in each city. Provide SQL code. Show the total number of units ordered by supplier and part (you do not need to provide SQL code). This requires a crosstab query: i. Click the Query Wizard (on the Create Ribbon) and then choose Crosstab Query Wizard (2nd choice). ii. Click Orders and then Next. iii. Click Supplier ID (“>” button) and then Next. iv. Click Next again to select the Part ID. v. Select whatever function you would like performed on quantity. vi. Hit Finish

Question 2 (20 points). For the ConsultMaster database, please work on all the queries we discussed in our last lecture including Task 1 and Task2a, b. Tutorials are available on Blackboard....