james morgan owns and operates morgan importing, which purchases

Project Description:

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 (itemid, description, purchasedate, store, city, quantity, localcurrencyamount,exchangerate)shipment (shipmentid, shippername, shipperinvoicenumber, 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 2-40.the column characteristics for the tables are shown in figures 2-41, 2-42, and 2-43. the data for the tables are shown in figures 2-44, 2-45, and 2-46. the relationship between item and shipment_item should enforce referential integrity, and although it should cascade updates, it should not cascade deletions. the relationship between shipment and shipment_item should enforce referential integrity and cascade both updates and deletions.we recommend that you create a microsoft access 2010 database named mi-ch02.accdb using the database schema, column characteristics, and data shown above, and then use this database to test your solutions to the questions in this section. alter-natively, sql scripts for creating the mi-ch02 database in sql server, oracle database, and mysql are available on our web site at www.pearsonhighered.com/kroenke.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, 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 are in the format mm/dd/yy. list the shipmentid, shippername, 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.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 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 shippername in ascending order and then departuredate in descending order.n. show the shippername 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 and departuredate of all shipments that have an item that was purchased in singapore. use a subquery. present results sorted by shippername in ascending order and then departuredate in descending order.p. show the shippername and departuredate of all shipments that have an item that was purchased in singapore. use a join. present results sorted by shippername in ascending order and then departuredate in descending order.q. show the shippername, 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 shippername in ascending order and then departuredate in descending order.
Skills Required:
Project Stats:

Price Type: Negotiable

Expired
Total Proposals: 11
1 Current viewersl
38 Total views
Project posted by:

Proposals

Proposals Reputation Price offered
  • 4.8
    208 Jobs 139 Reviews
    $0 in 0 Day
  • 0.0
    0 Jobs 0 Reviews
    $0 in 0 Day
  • 4.6
    647 Jobs 468 Reviews
    $0 in 0 Day
  • 4.3
    7 Jobs 3 Reviews
    $50 in 0 Day
  • 2.5
    8 Jobs 6 Reviews
    $0 in 0 Day
  • 5.0
    5 Jobs 3 Reviews
    $0 in 0 Day
  • 3.7
    12 Jobs 3 Reviews
    $0 in 0 Day
  • 4.8
    60 Jobs 32 Reviews
    $0 in 0 Day
  • 3.8
    30 Jobs 19 Reviews
    $0 in 0 Day