IT360 Spring 2013 Lab 5 ER Model SOLUTIONS

Ex1: YP Squadron Entity-Relationship Model

Ex 2: Sample design for Online Store

Ex3: Driving violations

Ex 4 SQL

Given the following tables:

ITEM(ItemID, Description, PurchaseDate, Store, City, Quantity, LocalCurrencyAmt, ExchangeRate)

SHIPMENT(ShipmentID, ShipperName, ShipperInvoiceNumber, DepartureDate, ArrivalDate, InsuredValue)

SHIPMENT_ITEM(ShipmentID, ShipmentItemNb, ItemID, Value)

Write the SQL query to achieve the following: For each ShipperName, display the ShipperName and total Value (from the SHIPMENT_ITEM table) of the items shipped by that shipper.

SELECT ShipperName, SUM(Value)

FROM SHIPMENT s, SHIPMENT_ITEM si

WHERE s.ShipmentID = si.ShipmentID

GROUP BY ShipperName;