SQL Exercise System

1-->Assume we want to analyze a pattern in gear purchases. Part of the pattern analysis is listing all purchases with purchase amounts that are a multiple of 10 where the amount is rounded to the nearest dollar amount. List slGearPurchase.GearPurchaseID, slGearPurchase.PurchaseAmount, AmountRounded, and slGearPurchase.Description where the rounded purchase amount is a multiple of 10. The attribute AmountRounded is produced using the ROUND() function. Write the WHERE clause using ROUND() IN() for rounded amounts that are equal to 10,20,30,...100. Order the list by slGearPurhcase.PurchaseAmount in ascending order.

2-->List slGearPurchase.GearPurchaseID, slGearPurchase.PurchaseAmount, slGearPurchase.Description for all gear purchases. Replace NULL values in Description with NA (Not Applicable). Order the list by slGearPurchase.GearPurchaseID in ascending order.

3-->For repairs with a rounded up repair cost greater than or equal to 10 and less than or equal to 15, list slRepair.RepairCost, CostRounded, and slRepair.SupplierID. Compute CostRounded using the ROUND() function. ROUND() must be used in the WHERE predicate to filter correctly. The previous query "cost10-15" did not include RepairCost for 9.89 and 9.99 which would round up to 10. See the hint for further information. The endpoints 10 and 15 should be included in the range. Order the list by RepairCost in ascending order

4-->Rewrite the query "Cost10-15R" using the BETWEEN operator. For repairs with a rounded up repair cost greater than or equal to 10 and less than or equal to 15, list slRepair.RepairCost, CostRounded, and slRepair.SupplierID. Compute CostRounded using the ROUND() function. ROUND() must be used in the WHERE predicate to filter correctly. Use the BETWEEN operator. Order the list by RepairCost in ascending order

5-->For repairs having supplierID 1501 OR 1517, list slRepair.RepairDate, slRepair.RepairCost, slRepair.SupplierID. Order the list first by SupplierID in ascending order and then by repair cost in descending order

SCHEMA

slMaintenance Column Name slRepair slGearPurchase Data Type Column N.. Data Type RepairlD int RepairDate datetime RepairCost float Description varchar(500) SupplielD int Column NaeData Type MaintenanceDate datetime slGasPurchase slShip sIPax datetime Column N... ShiplD MfgYear int Make Model Data Type Column Name Data Type GasPurchaseD int PurchaseDate datetime Column Name Data Type PaxlD PaxFirstName varchan200) SupplienD varchar 100) CostPerGallon fioat sITrip Column Nae Data Type TriplD slLaunch lCensus slSupplier Column N me Data Type Column Nme Data Type SupplierlD int Column Name Data Type 8 LaunchlD TripEnd CensusD Launch TripEngineEnd float WaterTempint slWayPoint Column Name Data Type g WayPointiD int lFloatPlan Column Name Deta Type FloatPlaniD WayPointlD slMaintenance Column Name slRepair slGearPurchase Data Type Column N.. Data Type RepairlD int RepairDate datetime RepairCost float Description varchar(500) SupplielD int Column NaeData Type MaintenanceDate datetime slGasPurchase slShip sIPax datetime Column N... ShiplD MfgYear int Make Model Data Type Column Name Data Type GasPurchaseD int PurchaseDate datetime Column Name Data Type PaxlD PaxFirstName varchan200) SupplienD varchar 100) CostPerGallon fioat sITrip Column Nae Data Type TriplD slLaunch lCensus slSupplier Column N me Data Type Column Nme Data Type SupplierlD int Column Name Data Type 8 LaunchlD TripEnd CensusD Launch TripEngineEnd float WaterTempint slWayPoint Column Name Data Type g WayPointiD int lFloatPlan Column Name Deta Type FloatPlaniD WayPointlD