72 lines
1.2 KiB
SQL
72 lines
1.2 KiB
SQL
/* *** CS4-MI.A *** */
|
|
SELECT *
|
|
FROM ITEM
|
|
;
|
|
|
|
SELECT *
|
|
FROM SHIPMENT
|
|
;
|
|
|
|
SELECT *
|
|
FROM SHIPMENT_ITEM
|
|
;
|
|
|
|
/* *** CS4-MI.B *** */
|
|
SELECT ShipmentID, ShipperName, ShipperInvoiceNumber
|
|
FROM SHIPMENT
|
|
;
|
|
|
|
/* *** CS4-MI.C *** */
|
|
SELECT ShipmentID, ShipperName, ShipperInvoiceNumber
|
|
FROM SHIPMENT
|
|
WHERE InsuredValue > 10000
|
|
;
|
|
|
|
/* *** CS4-MI.D *** */
|
|
SELECT ShipmentID, ShipperName, ShipperInvoiceNumber
|
|
FROM SHIPMENT
|
|
WHERE ShipperName LIKE 'AB%'
|
|
;
|
|
|
|
/* *** CS4-MI.E *** */
|
|
SELECT ShipmentID, ShipperName, ShipperInvoiceNumber, ArrivalDate
|
|
FROM SHIPMENT
|
|
WHERE MONTH(DepartureDate) = 12
|
|
;
|
|
|
|
/* *** CS4-MI.F *** */
|
|
SELECT ShipmentID, ShipperName, ShipperInvoiceNumber, ArrivalDate
|
|
FROM SHIPMENT
|
|
WHERE DAY(DepartureDate) = 10
|
|
;
|
|
|
|
/* *** CS4-MI.G *** */
|
|
SELECT MAX(InsuredValue) AS MaxInsuredValue,
|
|
MIN(InsuredValue) AS MinInsuredValue
|
|
FROM SHIPMENT
|
|
;
|
|
|
|
/* *** CS4-MI.H *** */
|
|
SELECT AVG(InsuredValue) AS AvgInsuredValue
|
|
FROM SHIPMENT
|
|
;
|
|
|
|
/* *** CS4-MI.I *** */
|
|
SELECT COUNT(*) AS NumShipments
|
|
FROM SHIPMENT
|
|
;
|
|
|
|
/* *** CS4-MI.J *** */
|
|
SELECT ItemID, Description, Store,
|
|
LocalCurrencyAmount * ExchangeRate AS USCurrencyAmount
|
|
FROM ITEM
|
|
;
|
|
|
|
/* *** CS4-MI.K *** */
|
|
SELECT City, Store
|
|
FROM ITEM
|
|
GROUP BY City, Store
|
|
;
|
|
|
|
|