Question

SQL

1--> List slPax.PaxID, slPax.PaxFirstName, slPax.PaxLastName, and slCensus.PaxOrder for passengers with the lastname 'lozano'.
Order the list by slCensus.PaxOrder in ascending order.

2-->List slShip.Make, slMaintenance.MaintenanceDate, and slSupplier.SupplierName for maintenance items with cost between 50 and 75.
Order the list by slMaintenance.MaintenanceDate in ascending order.

3-->List slShip.Model, slTrip.TripStart, and slWayPoint.WayPoint for TripID = 118.
Order the list by slFloatPlan.FloatPlanID in descending order.

4-->List slTrip.TripID, slTrip.TripStart, slLaunch.Launch, Passenger, and slShip.Make for TripID 100. This query provides the list of passengers and the launch for a given trip.
Passenger is the concatenation of first and last name with a blank in between.
Order the list by slCensus.PaxOrder in ascending order.

5-->List slTrip.TripID, slTrip.TripEnd, slTrip.WaterTemp, slLaunch.Launch, slShip.Make, and CaptainName for trips with a water temperature greater than 88 and for which the trip ended in the afternoon.
CaptainName is the concatenation of first and last name with a blank in between.
Derive the hour from slTrip.TripEnd using DATEPART() and the HOUR interval. A trip that ends in the afternoon will have a DATEPART() value greater than or equal to 12.
Order the list by slTrip.TripID in ascending order.

Schema for help

slMaintenance sIRepair lGearPurchase Column Name Column Nme Data Type Column N.. Data Type RepairlD int RepairDate datetime RepairCost float Description varchan500) SupplierlD int Data Type MaintenanceDate datetime slShip slPax slGasPurchase Column Name Column N... ShiplD MfgYear int Data Type Data Type Column Name Data Type PurchaseDate detetime PaxLastName varchar 200) varchar(100) CostPerGallon float sITrip Column Name Data Type slLaunch Trip D slCensus slSupplier Column Name Data Type datetime Column Name Data Type SupplierlD int Column Name Data Type LaunchlD CensusID Launch Triplo PaxID PaxOrder TripEngineEnd float WaterTemp int slWayPoint Column Neme Data Type WayPointiD int slFloatPlan Column Name Data Type TFloatPlanlD

Answer 1

1-->List slPax.PaxID, slPax.PaxFirstName, slPax.PaxLastName, and slCensus.PaxOrder for passengers with the lastname 'lozano'.
Order the list by slCensus.PaxOrder in ascending order.

Query: select slPax.PaxID, slPax.PaxFirstName, slPax.PaxLastName, slCensus.PaxOrder from slPax, slCensus where slPax.PaxID = scCensor.PaxID AND slPax.PaxLastName = 'lozano' order by slCensor.PaxOrder;

2-->List slShip.Make, slMaintenance.MaintenanceDate, and slSupplier.SupplierName for maintenance items with cost between 50 and 75.
Order the list by slMaintenance.MaintenanceDate in ascending order.

Query: select slShip.Make, slMaintenance.MaintenanceDate, slSupplier.SupplierName from slShip, slMaintenance, slSupplier where slShip.ShipID = slMaintenance.ShipID and slMaintenance.SupplierID = slSupplier.SupplierID AND sl.MaintenanceCost BETWEEN 50 AND 75 order by slMaintenance.MaintenanceDate;

3-->List slShip.Model, slTrip.TripStart, and slWayPoint.WayPoint for TripID = 118.
Order the list by slFloatPlan.FloatPlanID in descending order.

Query: select slShip.Model, slTrip.TripStart, slWayPoint.WayPoint from slShip, slTrip, slWayPoint, slFloatPlan where slShip.ShipID = slTrip.ShipID and slTrip.TripID = slFloatPlan.TripID and slFloatPlan.WayPointID = slWayPoint.WayPointID where slTrip.TripID = 118 order by slFloatPlan.FloatPlanID desc;

4-->List slTrip.TripID, slTrip.TripStart, slLaunch.Launch, Passenger, and slShip.Make for TripID 100. This query provides the list of passengers and the launch for a given trip.
Passenger is the concatenation of first and last name with a blank in between.
Order the list by slCensus.PaxOrder in ascending order.

Query: slTrip.TripID, slTrip.TripStart, slLaunch.Launch, concat(slPax.PaxFirstName, ' ', slPax.PaxLastName) as Passenger, slShip.Make from slTrip, slLaunch, slPax, slCensus where slPax.PaxID = slCensus.PaxID and slCensus.TripID = slTrip.TripID and slTrip.LaunchID = slLaunch.LaunchID where slTrip.TripID = 100 order by slCensus.PaxOrder;

NOTE: As per HOMEWORKLIB POLICY, I am allowed to answer only 4 questions (including sub-parts) on a single post. Kindly post the remaining questions separately and I will try to answer them. Sorry for the inconvenience caused.