CREATE PROCEDURE  usp_SearchByAirportName( @airportName VARCHAR(70))
AS
BEGIN
SELECT
[ar].[AirportName],
[p].[FullName],
CASE
WHEN [f].[TicketPrice] < 400 THEN 'Low'
WHEN [f].[TicketPrice] > 400 AND [f].[TicketPrice] <=1500 THEN 'Mediumn'
 WHEN [f].[TicketPrice] > 1501 THEN 'High'
 END
AS
[LevelOfTickerPrice],
[a].[Manufacturer],
[a].[Condition],
[at].[TypeName]
FROM
[Airports]
AS [ar]
JOIN [FlightDestinations] aS [f] ON [f].[AirportId] = [ar].[Id]
JOIN [Passengers] as [p] ON [f].[PassengerId] = [p].[Id]
JOIN [Aircraft] as [a] ON [a].[Id] = [f].[AircraftId]
JOIN [AircraftTypes] as [at] ON [at].[Id] = [a].[TypeId]
WHERE  [ar].[AirportName] = @airportName
ORDER BY [a].[Manufacturer], [p].[Fullname]
END