Question

Group item purchases by city and store. Show the shipper name, shipment ID, and departure date of all shipments that have an item with a value of $1,000.00 or more. Use a subquery. Present results sorted by shipper name in ascending order and then departure date in descending order.

a) Shipper Name, Shipment ID, Departure Date
b) Shipment ID, Shipper Name, Departure Date
c) Departure Date, Shipper Name, Shipment ID
d) Shipment ID, Departure Date, Shipper Name

Answer 1

The question involves writing an SQL query to retrieve shipment information where the item value is $1,000 or more, grouped by city and store if necessary. The required fields are Shipper Name, Shipment ID, and Departure Date, with sorting by Shipper Name ascending and Departure Date descending.

Step-by-step explanation:

The student's question pertains to the use of subqueries in SQL for the purpose of retrieving certain shipment information from a database. First, a subquery is needed to identify the shipment IDs that have items valued at $1,000.00 or more. Then, the main query will retrieve the required Shipper Name, Shipment ID, and Departure Date, filtered by these shipment IDs. Lastly, the results are sorted first by Shipper Name in ascending order and then by Departure Date in descending order.

• 
  
• 
  
• 
  

Here's an example of how such a SQL query might look:

SELECT ShipperName, ShipmentID, DepartureDate
FROM Shipments
WHERE ShipmentID IN (
SELECT ShipmentID
FROM Shipments
WHERE ItemValue >= 1000
)
ORDER BY ShipperName ASC, DepartureDate DESC;

Fulfilling the request for grouping by city and store would depend on the specific database schema, which is not provided. It is assumed that the 'Shipments' table has a column for 'ShipperName', 'ShipmentID', and 'DepartureDate', and that there is detail in another table where the 'ItemValue' can be checked.