use mysql

database management system

Morgan Importing purchases antiques and home furnishings in Asia and ships those items to a warehouse facility in Los Angeles. Mr. Morgan uses a database to keep a list of items purchased, shipments, and shipment items. His database includes the following tables: SHIPMENT (ShipmentID ShipperName, ShipperinvoiceNumber, DepartureDate, ArrivalDate, InsuredValue). SHIPMENT_ITEM (SkiprnentID ShipmentitemID. ItemID, Value). ITEM_PURCHASE (ItemID Description, Purehase Date, Store, City, Quantity LocalCtirrencyAmt, Exchange Rate). In the database schema 'above, the primary keys are underlined and the foreign keys are shown in italics. In your home directory you will find a file named 'DatabaseLabs.odb'. This file contains the database and the data of it's' tables. Write SQL statements and shown the results based on the MDC data for each of the following: A. Show all data in each of the tables. B. List the ShipmentID, Shipper, and ShipperinvoiceNumber of all shipments. C. List the shipmentID. Shipper, and ShipperTnvoiceNumber for all shipments that have an insured value greater than $10,000.00 D. List the shipmentID. Shipper, and ShipperInvoiceNumber of all shippers whose name starts with 'AB'. E. Assume DepartureDate and ArrivalDate are in the format MM/DD/YY. List the Shipment.TD, Shipper, ShipperinvuicuNulber, and ArrivalDate of all shipments that departed in December Database Management Systems CS 361 Fall 2014_15 F. Assume DepartureDate and ArrivalDate are in the format MM/DD/YY. List the ShipmentID, Shipper, ShipperinvoiceNumber, 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. 1. Count the number of shipments. J. Show ItemID. Description. Store and a calculated column named USCurrencyAmount that is equal to LocalCurrencyAmt 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 Shipper and DepartureDate of all shipments that have an item with a value of $1,000.00 or more. Use a subquery. Present results sorted by Shipper in ascending order and then DepartureDate in descending order. N. Show the Shipper 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 Shipper in ascending order and then DepartureDate in descending order. 0. Show the Shipper and DepartureDate of all shipments that have an item that was purchased in Singapore. Use a subquery. Present results sorted by Shipper in ascending order and then DepartureDate in descending order. P. Show the Shipper and DepartureDate of all shipments that have an item that was purchased in Singapore. Use a join. Present results sorted by Shipper in ascending order and then DepartureDate in descending order. Q. Show the Shipper, DepartureDate of shipment, and Value for items that were purchased in Singapore. Use a combination of a join and a subquery. Present results sorted by Shipper in ascending order and then DepartureDate in descending order