HW_02SQLandThroughput 2012(1) - ORIE 3120: Industrial Data...

Info iconThis preview shows pages 1–2. Sign up to view the full content.

View Full Document Right Arrow Icon
ORIE 3120: Industrial Data and Systems Analysis Spring 2012 Homework #2 Due Date: 2/8/12 (Wednesday) noon Rhodes Hall Course Dropbox 1. Structured Query Language: Joins and Aggregations (12 points) The RawNorthwind database used in Recitation 1 has the following schema. As an inventory manager for the Northwind company, you are interested in knowing what fraction of your inventory in each category is obsolete (discontinued). If you see a category with a high fraction of obsolete inventory, then you would want to have a meeting with the purchasing manager to discuss the problem. Let’s create a set of queries that will compute this for you. The Products table has most of the data you need.Your total inventory position in a product should be considered the sum of UnitsInStock and UnitsOnOrder (the units on order will arrive and become part of your inventory). It does not make sense to add units of different products together (literally “apples” and “oranges”), so you should multiply by UnitPrice first. That is, InventoryPositionValue equals (UnitsInStock+UnitsOnOrder)*UnitPrice. You could then use an if statement (IIF, actually) based on the Discontinued field to determine the value of your discontinued product. That is, DiscontinuedInventoryPositionValue is equal to InventoryPositionValue if Discontinued is true, 0 if false. You would then want to compute totals of these two numbers (InventoryPositionValue and DiscontinuedInventoryPositionValue) grouped by CategoryID. From these two
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 2
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 3

HW_02SQLandThroughput 2012(1) - ORIE 3120: Industrial Data...

This preview shows document pages 1 - 2. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online