SQL server exercise GROUB BY II ( HAVING )
1) schema : TripPaxCnt2
Count the number of trips taken by each passenger in slCensus. slCensus documents the passenters on a trip.
List slCensus. PaxID and TripCnt for all passengers (PaxID) in slCensus for passengers having more than two (2) trips.
TripCnt is the count for each PaxID in slCensus.
Order the list by TripCnt in descending order.
2) schema : SUM20
List slMaintenance. SupplierID and SUMcost for all suppliers having a total cost greater than 20.
Produce the values for and group by SUMcost using the SUM() function for slMaintenance. MaintenanceCost.
Order the list by SUMcost in descending order.
3) schema: TempCnt75
List slTrip. WaterTemp and TempCnt for all trips where the WaterTemp is greater than 75 the count is greater than 3.
TempCnt is the count of the number of trips for a given water temperature.
Order the list by TempCnt in descending order.
4) AvgGearSupp50
List slGearPurchase. SupplierID and AVGamount for each supplier in slGearPurchase for SupplierIDs 1505, 1500, 1510, and 1507 that have an average purchase amount greater than 50.
AVGamount is the AVG() of slGearPurchase. PurchaseAmount for each supplier listed.
Use IN() to create an enumerated list of SupplierIDs.
Order the list by AVGamount descending.
5) schema :EngineHour100
List EngineYear and MaxEngineHour for all trips in slTrip for years having more than 100 engine hours. EngineYear is the year derived from slTrip. TripStart.
MaxEngineHour is the maximum value of slTrip. TripEngineEnd for a given year.
Order the list by EngineYear in ascending order.
6) schema :SUMcost50
List MainMonth and SUMcost for all maintenance items.
Only include maintenance items for SupplierID 1518, for the year 2010, and for slMaintenance. MaintenanceCost less than 60. Do not list a supplier unless SUMcost is greater than 50.
Derive the values and group by MainMonth using the MONTH() function for slMaintenance. MaintenanceDate.
SUMcost is the SUM() of slMaintenance. MaintenanceCost for a given SupplierID.
Order the list by MainMonth in ascending order.
7) schema: AvgGasYear4
List GasYear and AvgGas for all gas purchases having a value for AvgGas greater than 4.0.
Derive the values for and group by GasYear using the YEAR() function for slGasPurchase. PurchaseDate.
AvgGas is the AVG() of slGasPurchase. CostPerGallon for each year in the resulting list.
Order the list by GasYear in ascending order.