Case Study 4

Instructions

• Answer questions A through K of the Morgan Importing Project found on pages 210-211.
• Create and set up the M1_CH02 database with the given SQL files.
• Deliver a single sql file, along with a document that contains all the content from the sql file.
• Each query should start with a comment line that looks like:
  /* *** CS4-<MI.Q> *** */
  Example for Question A:
  /* *** CS4-MI.A *** */
• Do not include the result table unless specifically directed to.
• Include at least one line of white space between answers.

Questions MI.A - MI.K

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.) List the ShipmentID, ShipperName, ShipperInvoiceNumber, and ArrivalDate of all shipments that departed in December. HINT: For the DBMS you are using, research how to extract a month or day from date value so it can be compared to a number.

F.) List the ShipmentID, ShipperName, ShipperInvoiceNumber, and ArrivalDate of all shipments that departed on the tenth day of any month. HINT: For the DBMS you are using, research how to extract a month or day from a date value so it can be compared to a number.

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 LocalCurrencyAmount multiplied by the ExchangeRate for all rows of ITEM.

K.) Group item purchases by City and Store.