James Morgan owns and operates Morgan Importing, which purchases antiques and home furnishings in Asia, ships those items to a warehouse facility in Los Angeles, and then sells these items in the United States. James tracks the Asian purchases and subsequent shipments of these items to Los Angeles by using a database to keep a list of items purchased, shipments of the purchased items, and the items in each shipment. His database includes the following tables:
ITEM (ItemlD. Description, PurchaseDate, Store, City, Quantity,
LocalCurrencyAmount, ExchangeRate)
SHIPMENT (ShipmentID, ShipperName, ShipperlnvoiceNumber,
DepartureDate, ArrivalDate, InsuredValue)
SHIPMENT ITEM (ShipmentID, ShipmentItemID, ItemID, Value)
In the database schema above, the primary keys are underlined and the foreign keys are shown in italics. The database that James has created is named MI, and the three tables in the MI database schema are shown in Figure 1.The column characteristics for the tables are shown in Figures 2, 3, and 4. The data for the tables are shown in Figures 5, 6, and 7. The relationship between ITEM
A. Show all data in each of the tables.
B. List the ShipmentID, ShipperName, and ShipperInvoiceNumber of all shipments.
C. List the ShipmentID, ShipperName, and ShipperInvoiceNumber for all shipments that have an insured value greater than $10,000.00.
D. List the ShipmentID, ShipperName, and ShipperInvoiceNumber of all shippers whose name starts with AB'.
E. Assume DepartureDate and ArrivalDate are in the format MM/DD/YY. List the ShipmentID, ShipperName, ShipperInvoiceNumber, and ArrivalDate of all shipments that departed in December.
F. Assume DepartureDate and ArrivalDate arc in the format MM/DD/YY List the ShipmentID, ShipperName, ShipperlnvoiceNumber, and ArrivalDate of all shipments that departed on the tenth day of any month.
G. Determine the maximum and minimum InsuredValue.
H. Determine the average InsuredValue.
I. Count the number of shipments.
J. Show ItemID, Description, Store, and a calculated column named USCurrencyAmount that is equal to LocalCurrencyAmountt multiplied by the ExchangeRate for all rows of ITEM.
K. Group item purchases by City and Store.
L. Count the number of purchases having each combination of City and Store.
M. Show the ShipperName, ShipmentID and DepartureDate of all shipments that have an item with a value of SI,000.00 or more. Use a subquery. Present results sorted by ShipperName in ascending order and then DepartureDate in descending order.
N. Show the ShipperName, ShipmentID, and DepartureDate of all shipments that have an item with a value of $1,000.00 or more. Use a join. Present results sorted by ShipperName in ascending order and then DepartureDate in descending order.
O. Show the ShipperName, ShipmentID, and DepartureDate of the shipments for items that were purchased in Singapore. Use a subquery. Present results sorted by ShipperName in ascending order and then DepartureDate in descending order.
P. Show the ShipperName, ShipmentID, and DepartureDate of all shipments that have an item that was purchased in Singapore. Use a join, but do not use JOIN ON syntax. Present results sorted by ShipperName in ascending order and then DepartureDate in descending order.
Q. Show the ShipperName, ShipmentID, and DepartureDate of all shipments that have an item that was purchased in Singapore. Use a join using JOIN ON syntax. Present results sorted by ShipperName in ascending order and then DepartureDate in descending order.
R. Show the ShipperName, ShipmentID, the DepartureDate of the shipment, and Value for items that were purchased in Singapore. Use a combination of a join and a subquery. Present results sorted by ShipperName in ascending order and then DepartureDate in descending order.
S. Show the ShipperName, ShipmentID, the DepartureDate of the shipment, and Value for items that were purchased in Singapore. Also show the ShipperName, ShipmentID, and DepartureDate for all other shipments. Present results sorted by Value in ascending order, then ShipperName in ascending order, and then DepartureDate in descending order.
We need at least 10 more requests to produce the solution.
0 / 10 have requested this problem solution
The more requests, the faster the answer.