- 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
- 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 |
- Write an UPDATE statement to change values of City from New York City to NYC in the ITEM table.
- Write a DELETE statement to delete all the Items that were purchased from NYC in the ITEM table.
*/ 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
