MS SQL Retake Exam – 10 Dec 2021

--Retake Exam – 10 Dec 2021

CREATE DATABASE Airport

GO

USE Airport

GO

CREATE TABLE Passengers(
Id INT PRIMARY KEY IDENTITY,
FullName NVARCHAR(100) UNIQUE NOT NULL,
Email NVARCHAR(50) UNIQUE NOT NULL)

CREATE TABLE Pilots(
Id INT PRIMARY KEY IDENTITY,
FirstName NVARCHAR(30) UNIQUE NOT NULL,
LastName NVARCHAR(30) UNIQUE NOT NULL,
Age TINYINT NOT NULL,
CHECK(Age BETWEEN 21 AND 62),
Rating FLOAT(8),
CHECK(Rating BETWEEN 0.0 AND 10.0))

CREATE TABLE AircraftTypes(
Id INT PRIMARY KEY IDENTITY,
TypeName NVARCHAR(30) UNIQUE NOT NULL)

CREATE TABLE Aircraft(
Id INT PRIMARY KEY IDENTITY,
Manufacturer NVARCHAR(25) NOT NULL,
Model NVARCHAR(30) NOT NULL,
[Year] INT NOT NULL,
FlightHours INT,
Condition CHAR 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 NVARCHAR(70) UNIQUE NOT NULL,
Country NVARCHAR(100) UNIQUE NOT NULL)

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) DEFAULT 15.00 NOT NULL)

--02. Insert

INSERT INTO Passengers(FullName, Email)
SELECT
        CONCAT(FirstName, ' ', LastName)
        ,CONCAT(FirstName, LastName, '@gmail.com')
   FROM Pilots
  WHERE Id BETWEEN 5 AND 15

--03. Update

UPDATE Aircraft
   SET Condition = 'A'
 WHERE Condition IN ('B', 'C')
   AND (FlightHours IS NULL OR FlightHours <= 100)
   AND [Year] >= 2013

 --04. Delete

DELETE FROM FlightDestinations
      WHERE PassengerId IN (SELECT
                                   Id
                              FROM Passengers
                             WHERE LEN(FullName) <= 10)

 DELETE FROM Passengers
       WHERE LEN(FullName) <= 10

--05. Aircraft

  SELECT
         Manufacturer
         ,Model
         ,FlightHours
         ,Condition
    FROM Aircraft
ORDER BY FlightHours DESC

--06. Pilots and 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
LEFT 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)
              f.Id AS DestinationId
              ,f.[Start]
              ,p.FullName
              ,a.AirportName
              ,f.TicketPrice
         FROM FlightDestinations AS f
    LEFT JOIN Passengers AS p ON f.PassengerId = p.Id
    LEFT JOIN Airports AS a ON f.AirportId = a.Id
        WHERE DATEPART(DAY, f.Start) % 2 = 0
     ORDER BY f.TicketPrice DESC, a.AirportName

--08. Number of Flights for Each Aircraft

   SELECT
          AircraftId
          ,Manufacturer
          ,FlightHours
          ,FlightDestinationsCount
          ,AvgPrice
     FROM (SELECT
                  a.Id AS AircraftId
                  ,a.Manufacturer AS Manufacturer
                  ,a.FlightHours AS FlightHours
                  ,COUNT(f.Id) AS FlightDestinationsCount
                  ,ROUND(AVG(f.TicketPrice), 2) AS AvgPrice
             FROM Aircraft AS a
        LEFT JOIN FlightDestinations AS f ON a.Id = f.AircraftId
         GROUP BY a.Id, a.Manufacturer, a.FlightHours
           ) AS CountingSubquery
     WHERE FlightDestinationsCount >= 2
  ORDER BY FlightDestinationsCount DESC, AircraftId

--09. Regular Passengers

  SELECT *
    FROM (SELECT
                 p.FullName AS FullName
                 ,COUNT(a.Id) AS CountOfAircraft
                 ,SUM(f.TicketPrice) AS TotalPayed
            FROM Passengers AS p
            JOIN FlightDestinations AS f ON p.Id = f.PassengerId
            JOIN Aircraft AS a ON f.AircraftId = a.Id
        GROUP BY p.FullName
          ) AS CountingSubquery
    WHERE CountOfAircraft >= 2
      AND SUBSTRING(FullName, 2, 1) = 'a'
 ORDER BY FullName

 GO

 --10. Full Info for Flight Destinations

  SELECT
         ap.AirportName
         ,f.[Start] AS DayTime
         ,f.TicketPrice
         ,p.FullName
         ,a.Manufacturer
         ,a.Model
    FROM Airports AS ap
    JOIN FlightDestinations AS f ON ap.Id = f.AirportId
    JOIN Passengers AS p ON f.PassengerId = p.Id
    JOIN Aircraft AS a ON f.AircraftId = a.Id
    WHERE DATEPART(HOUR, f.Start) BETWEEN 6 AND 20
     AND f.TicketPrice > 2500
ORDER BY a.Model

 --11. Find all Destinations by Email Address

 CREATE FUNCTION udf_FlightDestinationsByEmail(@email NVARCHAR(50))
 RETURNS INT
 AS
 BEGIN
    DECLARE @passengerId INT = 0
    SET @passengerId = (SELECT
                              Id
                         FROM Passengers
                        WHERE Email = @email)
    DECLARE @flightsCount INT = 0
    SET @flightsCount = (SELECT
                                COUNT(Id)
                           FROM FlightDestinations
                          WHERE PassengerId = @passengerId)
    RETURN @flightsCount
 END

SELECT dbo.udf_FlightDestinationsByEmail ('[email protected]')
SELECT dbo.udf_FlightDestinationsByEmail('[email protected]')
SELECT dbo.udf_FlightDestinationsByEmail('[email protected]')

--12. Full Info for Airports

CREATE PROC usp_SearchByAirportName @airportName NVARCHAR(70)
AS

SELECT
         ap.AirportName
         ,p.FullName
         ,CASE
            WHEN f.TicketPrice <= 400 THEN 'Low'
            WHEN f.TicketPrice BETWEEN 401 AND 1500 THEN 'Medium'
            ELSE 'High'
          END AS LevelOfTickerPrice
         ,a.Manufacturer
         ,a.Condition
         ,atp.TypeName
    FROM Airports AS ap
    JOIN FlightDestinations AS f ON ap.Id = f.AirportId
    JOIN Passengers AS p ON f.PassengerId = p.Id
    JOIN Aircraft AS a ON f.AircraftId = a.Id
    JOIN AircraftTypes AS atp ON atp.Id = a.TypeId
   WHERE ap.AirportName = @airportName
ORDER BY a.Manufacturer, p.FullName

GO

EXEC usp_SearchByAirportName 'Sir Seretse Khama International Airport'