Assignment Instruction

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:

An ITEM table that has columns of ItemID, Description, PurchaseDate, Store, City, Quantity,LocalCurrencyAmt, and ExchangeRate. This table contains information about all the items purchased by Morgan Importing.

An SHIPMENT table that has columns of ShipmentID, ShipperName, ShipperInvoiceNumber,DepartureDate, ArrivalDate, and InsuredValue This table contains information about the shipments that transport Morgan Importings purchased items from Asia to Los Angeles.

An SHIPMENT_ITEM table that has columns of ShipmentID, ShipmentItemID, ItemID, and Value . This table contains detailed information about which item was shipped in which shipment.

[IMPORTANT]SQL scripts for creating the Morgan Importing database in MySQL are available on our Blackboard class website. You need to download the .sql file there and execute it to create this database and insert data before working further on the assignment.

Questions

Unless specified, you can choose to use either subquery or join to complete some of the multi-table queries below.

1. Show the ShipmentID, ShipperName, and DepartureDate of all shipments that have an item with a value of 5,000 or more. Use a join. Present results sorted by ShipperName in ascending order and then DepartureDate in descending order

2. Show the ShipmentID, ShipperName, and DepartureDate of all shipments that have an item with a value of 5,000 or more as well as the Description and PurchaseDate of this item. (You need to decide whether a subquery or a join is suitable)

3. Show the ShipperName and DepartureDate of all shipments that have an item that was purchased in Singapore. Present results sorted by ShipperName in ascending order and then DepartureDate in descending order.

4. List the ShipmentID, ShipperName, and DepartureDate of ALL shipments as well as the ItemID and Value of each item in those shipments as long as the ItemID and Value are known in the SHIPMENT_ITEM table. Otherwise show NULL in the places of ItemID and Value. Present results sorted by ShipmentID and then ItemID, both in ascending order

5. Write INSERT statements to insert the following three rows into the ITEM table.

ItemID	Description	PurchaseDate	Store	City	Quantity	LocalCurrencyAmount	ExchangeRate
5	Antique Chair	2011-08-02	Tang Antiques	New York City	4	178367	0.8
6	Wine Bottles & Cups	2011-08-06	Tang Antiques	New York City	10	3847	0.6
7	Ming Coins	2011-08-09	Ming Collection	New York City	20	100	0.13

6. Write an UPDATE statement to change values of City from New York City to NYC in the ITEM table.

7. Write a DELETE statement to delete all the Items that were purchased from NYC in the ITEM table.