Databases MSSQL Server Retake Exam - 10 Dec 2021

------------------------------------------------------------
-- 01. DDL
------------------------------------------------------------
CREATE TABLE [Passengers] (
  [Id] INT PRIMARY KEY IDENTITY,
  [FullName] VARCHAR(100) UNIQUE NOT NULL,
  [Email] VARCHAR(50) UNIQUE NOT NULL
) CREATE TABLE [Pilots] (
  [Id] INT PRIMARY KEY IDENTITY,
  [FirstName] NVARCHAR(30) NOT NULL UNIQUE,
  [LastName] NVARCHAR(30) NOT NULL UNIQUE,
  [Age] TINYINT NOT NULL,
  [Rating] FLOAT  ,
  CHECK (
    [Age] >= 21
    AND [Age] <= 62
  ),
  CHECK (
    [Rating] >= 0.0
    AND [Rating] <= 10.0
  )
) CREATE TABLE [AircraftTypes] (
  [Id] INT PRIMARY KEY IDENTITY,
  [TypeName] VARCHAR(30) NOT NULL UNIQUE
) CREATE TABLE [Aircraft] (
  [Id] INT PRIMARY KEY IDENTITY,
  [Manufacturer] VARCHAR(25) NOT NULL,
  [Model] VARCHAR(30) NOT NULL,
  [Year] INT NOT NULL,
  [FlightHours] INT,
  [Condition] NCHAR(1) NOT NULL,
  [TypeId] INT FOREIGN KEY REFERENCES [AircraftTypes]([Id]) NOT NULL
) CREATE TABLE [PilotsAircraft] (
  [AircraftId] INT FOREIGN KEY REFERENCES [Aircraft]([Id]) NOT NULL,
  [PilotId] INT FOREIGN KEY REFERENCES [Pilots]([Id]) NOT NULL,
  PRIMARY KEY ([AircraftId], [PilotId])
  ) CREATE TABLE [Airports] (
  [Id] INT PRIMARY KEY IDENTITY,
  [AirportName] VARCHAR(70) NOT NULL UNIQUE,
  [Country] VARCHAR(100) NOT NULL UNIQUE
) CREATE TABLE [FlightDestinations] (
  [Id] INT PRIMARY KEY IDENTITY,
  [AirportId] INT FOREIGN KEY REFERENCES [Airports]([Id]) NOT NULL,
  [Start] DATETIME2 NOT NULL,
  [AircraftId] INT FOREIGN KEY REFERENCES [Aircraft]([Id]) NOT NULL,
  [PassengerId] INT FOREIGN KEY REFERENCES [Passengers]([Id]) NOT NULL,
  [TicketPrice] DECIMAL(18, 2) NOT NULL DEFAULT(15)
)
------------------------------------------------------------
-- 02. Insert
------------------------------------------------------------
 Insert into [Passengers] ([FullName], [Email])
 (
SELECT
 [FirstName] + ' ' + [LastName] AS [FullName],
  [FirstName] + [LastName]  + '@gmail.com' AS [Email]
   FROM [Pilots] as [p]
WHERE [p].[Id] >= 5 and [p].[Id] <= 15
)
------------------------------------------------------------
-- 03. Insert
------------------------------------------------------------
UPDATE [Aircraft]
SET [Condition] = 'A'
WHERE ([Condition] = 'C' OR [Condition] = 'B')
AND
([FlightHours] IS NULL OR [FlightHours] <= 100)
AND ( [Year]>= 2013)
------------------------------------------------------------
-- 04. Insert
------------------------------------------------------------
DELETE FROM [Passengers]
WHERE
LEN([FullName]) <= 10
------------------------------------------------------------
-- 05. Aircraft
------------------------------------------------------------
 SELECT [Manufacturer], [Model], [FlightHours], [Condition]
FROM [Aircraft]
ORDER BY [FlightHours] DESC
------------------------------------------------------------
-- 06. Aircraft
------------------------------------------------------------
SELECT
[p].[FirstName],
[p].[LastName],
[a].[Manufacturer],
[a].[Model],
[a].[FlightHours]
FROM [Pilots] AS [p]
JOIN [PilotsAircraft] AS [pa] ON [p].[Id] = [pa].[PilotId]
JOIN [Aircraft] AS [a] ON [pa].[AircraftId] = [a].[Id]
WHERE [a].[FlightHours] IS NOT NULL AND [a].[FlightHours] <= 304
ORDER BY [a].[FlightHours] DESC , [p].[FirstName]
------------------------------------------------------------
-- 07. Top 20 Flight Destinations
------------------------------------------------------------
SELECT
  TOP(20)
  [pa].[Id] AS [DestinationId],
  [pa].[Start],
  [p].[FullName],
  [a].[AirportName],
  [TicketPrice]
FROM
  [Passengers] as [p]
  JOIN [FlightDestinations] AS [pa] ON [p].[Id] = [pa].[PassengerId]
   JOIN [Airports] as [a] ON [a].Id = [pa].[AirportId]
WHERE
  DAY([Start]) % 2 = 0
ORDER BY
  [TicketPrice] DESC,
  [AirportName]
------------------------------------------------------------
-- 08. Number of Flights for Each Aircraft
------------------------------------------------------------
SELECT
[f].[AircraftId]
, [a].[Manufacturer]
, [FlightHours]
, COUNT (*) AS [FlightDestinationsCount]
,ROUND(AVG([f].[TicketPrice]),2) AS [AvgPrice]
FROM [Aircraft] AS [a]
JOIN [FlightDestinations] [f] ON [F].[AircraftId]= [A].[Id]
GROUP BY [AircraftId], [a].[Manufacturer], [a].[FlightHours]
HAVING COUNT(*) >= 2
ORDER BY COUNT(*) DESC, [AircraftId]
------------------------------------------------------------
-- 09. Regular Passengers
------------------------------------------------------------


------------------------------------------------------------
--10. Full Info for Flight Destinations
------------------------------------------------------------

SELECT
[ar].[AirportName],
[f].[Start],
[f].[TicketPrice],
[p].[FullName],
[a].[Manufacturer],
[a].[Model]
FROM
[FlightDestinations] AS [f]
JOIN [Airports] AS [ar] ON [f].[AirportId] = [ar].[Id]
JOIN [Passengers] AS [p]  ON [p].[Id] = [f].[PassengerId]
JOIN [Aircraft] AS [a]  ON [a].[Id] = [f].[AircraftId]

WHERE
(CONVERT(time,[f].[Start]) >= CONVERT(time, '6:00') AND CONVERT(time, [f].[Start]) <=CONVERT(time, '20:00'))
AND
[f].[TicketPrice] > 2500
 ORDER BY [a].[Model]

------------------------------------------------------------
-- 11. Find all Destinations by Email Address
------------------------------------------------------------
CREATE FUNCTION udf_FlightDestinationsByEmail(@email VARCHAR(30))
RETURNS INT
AS
BEGIN
RETURN(
  SELECT COUNT(*) FROM [FlightDestinations] AS [f]
 JOIN [Passengers] AS [p] ON [p].[Id] = [f].[PassengerId]
  WHERE [p].[Email] = @email
)
END
------------------------------------------------------------
-- 12. Full Info for Airports
------------------------------------------------------------
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 'Medium'
 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