Question

1 Approved Answer

Posted on Sep 23, 2024

Based on the SHIPMENT table, list the ShipmentID, ShipperName, and ShipperInvoiceNumber of all shippers whose name starts with AB. Based on the ITEM table, show

image text in transcribed

Based on the SHIPMENT table, list the ShipmentID, ShipperName, and ShipperInvoiceNumber of all shippers whose name starts with AB.
Based on the ITEM table, show ItemID, Description, Store, and a calculated column named StdCurrencyAmount that is equal to LocalCurrencyAmt times the ExchangeRate for all the items purchased from Singapore,.
Based on the SHIPMENT table, calculate and report the total amount of InsuredValue for each Shipper. Present results sorted by the total amount of InsuredValue in ascending order.
Show the ShipmentID, ShipperName, and DepartureDate of all shipments that have an item with a value of 5,000 or more. Use a subquery. Present results sorted by ShipperName in ascending order and then DepartureDate in descending order.

*/ 4 /* Morgan Importing Database --- Create Tables 5 6 7 8 /********************** DROP DATABASE IF EXISTS MSIS618_ASSIGNMENT2; CREATE DATABASE IF NOT EXISTS MSIS628_ASSIGNMENT2; USE MSIS618_ASSIGNMENT2; 9 1e 11 12 13 14 15 16 17 18 CREATE TABLE ITEM ( ItemID Int NOT NULL AUTO_INCREMENT, Description Varchar(25) NOT NULL, PurchaseDate Date NOT NULL, Store Char(50) NOT NULL, city Char(35) NOT NULL, Quantity Int NOT NULL, LocalCurrency Amount Numeric(18,2) NOT NULL, ExchangeRate Numeric(12,6) NOT NULL, CONSTRAINT Purchase_PK PRIMARY KEY (ItemID) ); 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 CREATE TABLE SHIPMENT ( ShipmentID Int NOT NULL AUTO_INCREMENT, Shipper Name Char(35) NOT NULL, ShipperInvoiceNumber Int NOT NULL, DepartureDate Date NULL, ArrivalDate Date NULL, InsuredValue Numeric(12,2) NOT NULL, CONSTRAINT Shipment_PK PRIMARY KEY (ShipmentID) ); 36 37 38 39 CREATE TABLE SHIPMENT_ITEM ( ShipmentID Int NOT NULL, ShipmentItemID Int NOT NULL, ItemID Int NOT NULL, Value Numeric(12,2) NOT NULL, CONSTRAINT ShipmentItem_PK PRIMARY KEY(ShipmentID, ShipmentItemID), CONSTRAINT Ship_Item_Ship_FK FOREIGN KEY(ShipmentID) REFERENCES SHIPMENT(ShipmentID) ON UPDATE NO ACTION 40 41 42 43 CAUsers jayp5\Downloads Morgan-Imorting-DB-Setup.sal 88.98 Type here to search 1 'g WhatsApp - Googl. FEESTRES Morgan-morting-DB-Setup.nl CREATE TABLE SHIPMENT_ITEM ( ShipmentID Int NOT NULL, Shipment ItemID Int NOT NULL, ItemID Int NOT NULL, Value Numeric(12,2) NOT NULL, CONSTRAINT ShipmentItem_PK PRIMARY KEY(ShipmentID, ShipmentItemID), CONSTRAINT Ship_Item_Ship_FK FOREIGN KEY(ShipmentID) 2. REFERENCES SHIPMENT(ShipmentID) ON UPDATE NO ACTION ON DELETE CASCADE, 5 CONSTRAINT Ship Item_Item_FK FOREIGN KEY(ItemID) 6 REFERENCES ITEM(ItemID) ON UPDATE NO ACTION ON DELETE CASCADE 19 ); 53 ITEM Data 55 56 INSERT INTO ITEM 58 (Description, PurchaseDate, Store, city, Quantity, LocalCurrency Amount, ExchangeRate) 59 VALUES 'QE dining set', '2011-04-97', 'Eastern Treasures', 'Manila', 2, 403405, 0.01774); 51 52 INSERT INTO ITEM 53 (Description, PurchaseDate, Store, City, Quantity, LocalCurrencyAmount, ExchangeRate) 54 VALUES 55 Willow serving dishes', '2011-07-15', 'Jade Antiques', 'Singapore', 75, 102, 0.5983); 66 67 INSERT INTO ITEM 58 (Description, PurchaseDate, Store, City, Quantity, LocalCurrencyAmount, ExchangeRate) 69 VALUES 7e "Large Bureau', '2011-07-17', 'Eastern Sales', 'Singapore', 8, 2000, 0.5903); 71 72 INSERT INTO ITEM (Description, PurchaseDate, Store, City, Quantity, LocalCurrencyAmount, ExchangeRate) 74 VALUES 75 Brass lamps', '2011-07-20', 'Jade Antiques', 'Singapore', 40, 50, 0.5903); 76 SHIPMENT Data Usersljayp5\Downloads Morgan-Imorting-DB-Setup sel 88.98 Type here to search EI WhatsApp - Googl... File Explorer Morgan-marting-08-letup 57 58 59 66 INSERT INTO ITEM (Description, PurchaseDate, Store, City, Quantity, LocalCurrencyAmount, ExchangeRate) VALUES "QE dining set', '2011-84-67', 'Eastern Treasures', 'Manila', 2, 483485, 0.01774); 62 INSERT INTO ITEM (Description, PurchaseDate, Store, City, Quantity, LocalCurrency Amount, ExchangeRate) VALUES Willow serving dishes', '2011-07-15', 'Jade Antiques', 'Singapore', 75, 102, 0,5983); 65 66 68 69 70 71 72 INSERT INTO ITEM (Description, PurchaseDate, Store, City, Quantity, LocalCurrency Amount, ExchangeRate) VALUES "Large Bureau', '2011-07-17', 'Eastern Sales', 'Singapore', 8, 2008, 0.5983); INSERT INTO ITEM (Description, PurchaseDate, Store, City, Quantity, LocalCurrency Amount, ExchangeRate) VALUES Brass lamps', '2011-07-20', 'Jade Antiques', 'Singapore', 49, 50, 0.5983); 74 75 76 SHIPMENT Data 77 78 79 INSERT INTO SHIPMENT (ShipperName, Shipper InvoiceNumber, DepartureDate, ArrivalDate, InsuredValue) VALUES('ABC Trans-Oceanic', 2088651, 2010-12-10', '2011-03-15', 15000.ee); 88 81 82 83 84 85 INSERT INTO SHIPMENT(ShipperName, Shipper InvoiceNumber, DepartureDate, ArrivalDate, InsuredValue) VALUES("ABC Trans-Oceanic", 2009012, 2011-01-18", 2011-03-20', 12800.ee); INSERT INTO SHIPMENT(ShipperName, Shipper InvoiceNumber, DepartureDate, ArrivalDate, InsuredValue) VALUES('Worldwide', 49100380, 2011-05-05', '2011-06-17", 2000e.ee); 86 87 88 INSERT INTO SHIPMENT(Shipper Name, Shipper InvoiceNumber, DepartureDate, ArrivalDate, InsuredValue) VALUES("International', 3994ee, 2011-06-02', '2011-87-17', 17500.00); INSERT INTO SHIPMENT(ShipperName, Shipper InvoiceNumber, DepartureDate, ArrivalDate, InsuredValue) VALUES("Worldwide', 84899440, '2011-07-10', '2011-07-28', 25098.ee); 91 92 93 94 95 INSERT INTO SHIPMENT(ShipperName, Shipper InvoiceNumber, DepartureDate, ArrivalDate, InsuredValue) VALUES("International', 488955, 2011-08-05', '2011-10-11', 18000.00); 96 97 SHIPMENT Item CAUserjaypad Morgan-marting-08-Setup sal 890 Type here to search WhatsApp - Google File Explorer Morgan berting:00 Setup INSERT INTO ITEM (Description, Purchasedate, Store, city, Quantity, localCurrencyAmount, Exchangekato) VALUES 'Large Bureau', '2011-07-17', 'Eastern Sales', 'Singapore', B, 2000, 0.5903), 70 23 32 74 75 INSERT INTO ITEM (Description, PurchaseDate, Store, city, Quantity, Localcurrency Amount, ExchangeRate) VALUES Brass lamps', '2011-07-20', 'Jade Antiques', 'Singapore', 10, 50, 6.5903); SHIPMENT Data 78 79 INSERT INTO SHIPMENT(ShipperName, Shipper InvoiceNumber, DepartureDate, ArrivalDate, InsuredValue) VALUES('ABC Trans-Oceanic', 2008651, 2010-12-10', '2011-03-15', 15000.00); INSERT INTO SHIPMENT(ShipperName, Shipper InvoiceNumber, DepartureDate, ArrivalDate, InsuredValue) 83 VALUES ('ABC Trans-Oceanic', 2009012, 2011-01-10', '2011-03-20', 12000.00); 34 85 INSERT INTO SHIPMENT(ShipperName, Shipper InvoiceNumber, DepartureDate, ArrivalDate, InsuredValue) 86 VALUES( 'Worldwide', 49100308, 2011-05-05', '2011-06-17', 20000.00); 87 88 INSERT INTO SHIPMENT(ShipperName, Shipper InvoiceNumber, DepartureDate, ArrivalDate, InsuredValue) 89 VALUES("International', 399400, 2011-06-02', '2011-07-17', 17500.00); 90 92 INSERT INTO SHIPMENT(ShipperName, ShipperInvoiceNumber, DepartureDate, ArrivalDate, InsuredValue) 92 VALUES ('Worldwide', 84899440, 2011-07-10', '2011-07-28', 25000.00); 93 INSERT INTO SHIPMENT(ShipperName, ShipperInvoiceNumber, DepartureDate, ArrivalDate, InsuredValue) 95 VALUES ('International', 488955, 2011-08-05', '2011-10-11', 1.8000.ee); 96 97 SHIPMENT Item 98 99 INSERT INTO SHIPMENT_ITEM VALUES (3, 1, 1, 15000); 100 101 INSERT INTO SHIPMENT_ITEM VALUES(4, 1, 4, 1200); 102 INSERT INTO SHIPMENT_ITEM VALUES(4, 2, 3, 9500); 204 1e5 INSERT INTO SHIPMENT_ITEM VALUES (4, 3, 2, 4500); 106 107 103 109 CAUnaps\Downloads Morgan-morting--Setup 1890 type here to search WhatsApp Googl. R& File Explore 103