129 CHAPTER 2 Introduction to Structured Query Language James Morgan owns and op
ID: 3739169 • Letter: 1
Question
129 CHAPTER 2 Introduction to Structured Query Language 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 subse- quent 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 data- base includes the following tables: ting ITEM (atemID, Description, Purchase Date, Store, City, Quantity LocalCurrencyAmount, ExchangeRate) SHIPMENT (ShipmentID, ShipperName, ShipperInvoiceNumber Departure Date, ArrivalDate, InsuredValue) SHIPMENT ITEM (ShipmentID, ShipmentltemID, 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-62 The column characteristics for the tables are shown in Figures 2-63, 2-64, and 2-65. The data for the tables are shown in Figures 2.66.2-67,and 2-68. The relationship between ITEM RE 2-62 l Database The ITEM table ITEM HIPMENT hippertine The SHIPMENTQuantit table DepartureCate Date The SHIPMENT ITEM table EschangeRate HPMENT ITEMExplanation / Answer
--a
SELECT * FROM ITEM;
SELECT * FROM SHIPMENT;
SELECT * FROM SHIPMENT_ITEM;
--b
SELECT ShipmentID,ShipperName,ShipperInvoiceNumber FROM SHIPMENT;
--c
SELECT ShipmentID,ShipperName,ShipperInvoiceNumber FROM SHIPMENT
WHERE InsuredValue>'10000.00';
--d
SELECT ShipmentID,ShipperName,ShipperInvoiceNumber FROM SHIPMENT
WHERE ShipperName LIKE 'AB%';
--e
SELECT ShipmentID,ShipperName,ShipperInvoiceNumber,ArrivalDate FROM SHIPMENT
WHERE DepartureDate BETWEEN '12/01/18' AND '12/31/18';--please provide required year
--f
SELECT ShipmentID,ShipperName,ShipperInvoiceNumber,ArrivalDate FROM SHIPMENT
WHERE DATEPART(d,DepartureDate) ='10';
--g
SELECT MIN(InsuredValue) AS MinimumInsuredValue,
MAX(InsuredValue) AS MaximumInsuredValue
FROM SHIPMENT;
--h
SELECT AVG(InsuredValue) AS AverageInsuredValue FROM SHIPMENT;
--i
SELECT COUNT(ShipmentID) AS NumberOfShipments FROM SHIPMENT;
--j
SELECT itemID,Description,Store,
(LocalCurrencyAmount*ExchangeRate) AS USCurrencyAmount
FROM ITEM;
--k
SELECT * FROM ITEM GROUP BY City,Store;
--l
SELECT City,Store,Count(ShipmentID) AS CombinationCount FROM ITEM GROUP BY City,Store;
--m
SELECT ShipmentID,ShipperName,DepartureDate FROM SHIPMENT
WHERE ShipmentID IN (SELECT ShipmentID FROM SHIPMENT_ITEM WHERE Value >=1000)
ORDER BY ShipperName ASC,DepartureDate DESC;
--n
SELECT s.ShipmentID,s.ShipperName,s.DepartureDate FROM SHIPMENT s
INNER JOIN SHIPMENT_ITEM i ON s.ShipmentID=i.ShipmentID
WHERE i.Value>=1000
ORDER BY s.ShipperName ASC,s.DepartureDate DESC;
--p
SELECT ShipmentID,ShipperName,DepartureDate FROM SHIPMENT
WHERE ShipmentID IN
(SELECT ShipmentID FROM SHIPMENT_ITEM
WHERE ItemID IN
(SELECT itemID FROM ITEM WHERE city='Singapore'))
ORDER BY ShipperName ASC,DepartureDate DESC;
--q
SELECT s.ShipmentID,s.ShipperName,s.DepartureDate FROM SHIPMENT s
INNER JOIN SHIPMENT_ITEM i ON s.ShipmentID=i.ShipmentID
INNER JOIN ITEM it ON i.ItemID=it.itemID
WHERE it.city='Singapore'
ORDER BY s.ShipperName ASC,s.DepartureDate DESC;
--r
SELECT s.ShipmentID,s.ShipperName,s.DepartureDate,i.Value FROM SHIPMENT s
INNER JOIN SHIPMENT_ITEM i ON s.ShipmentID=i.ShipmentID
WHERE i.ItemID IN (SELECT itemID FROM ITEM WHERE city='Singapore')
ORDER BY ShipperName ASC,DepartureDate DESC;
--s
SELECT s.ShipmentID,s.ShipperName,s.DepartureDate,i.Value, FROM SHIPMENT s
INNER JOIN SHIPMENT_ITEM i ON s.ShipmentID=i.ShipmentID
INNER JOIN ITEM it ON i.ItemID=it.itemID
WHERE it.city IN ('Singapore')
ORDER BY ShipperName ASC,DepartureDate DESC
UNION
SELECT s.ShipmentID,s.ShipperName,s.DepartureDate,i.Value, FROM SHIPMENT s
INNER JOIN SHIPMENT_ITEM i ON s.ShipmentID=i.ShipmentID
INNER JOIN ITEM it ON i.ItemID=it.itemID
WHERE it.city NOT IN ('Singapore')
ORDER BY ShipperName ASC,DepartureDate DESC;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.