EXAM PREP Retake Exam - 10 Dec 2021

CREATE DATABASE Airport

GO

USE Airport

--Problem01
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 VARCHAR(30) UNIQUE NOT NULL,
    LastName VARCHAR(30) UNIQUE NOT NULL,
    Age TINYINT CHECK(Age BETWEEN 21 AND 62) NOT NULL,
    Rating FLOAT CHECK(Rating BETWEEN 0.0 AND 10.0),
)

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

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 CHAR(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) UNIQUE NOT NULL,
    Country VARCHAR(100) UNIQUE NOT NULL
)

CREATE TABLE FlightDestinations (
    Id INT PRIMARY KEY IDENTITY,
    AirportId INT FOREIGN KEY REFERENCES Airports(Id) NOT NULL,
    [Start] DATETIME 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 NOT NULL
)

--Problem02
INSERT INTO Passengers(FullName, Email)
(
     SELECT ([FirstName] + ' ' + [LastName]) AS [FullName],
            ([FirstName] + [LastName] + '@gmail.com') AS [Email]
      FROM [Pilots]
      WHERE [Id] BETWEEN  5 AND 15
)

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

--Problem04
DELETE FROM Passengers
      WHERE LEN([FullName]) <= 10

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

--Problem06
SELECT p.FirstName,
       p.LastName,
       a.Manufacturer,
       a.Model,
       a.FlightHours
     FROM PilotsAircraft AS pa
LEFT JOIN Pilots AS p ON pa.PilotId = p.Id
LEFT JOIN Aircraft AS a ON pa.AircraftId = a.Id
     WHERE a.FlightHours <= 304
ORDER BY a.FlightHours DESC, p.FirstName

--Problem07
SELECT TOP(20)
          fd.Id AS DestinationId,
          fd.[Start],
          p.FullName,
          a.AirportName,
          fd.TicketPrice
     FROM FlightDestinations AS fd
     JOIN Passengers AS p ON fd.PassengerId = p.Id
     JOIN Airports AS a ON fd.AirportId = a.Id
    WHERE DATEPART(DAY, fd.[Start]) % 2 = 0
 ORDER BY fd.TicketPrice DESC, a.AirportName

 --Problem08
 SELECT fd.AircraftId,
        a.Manufacturer,
        a.FlightHours,
        COUNT(*) AS FlightDestinationsCount,
        ROUND(AVG(fd.TicketPrice), 2) AS AvgPrice
   FROM FlightDestinations AS fd
   JOIN Aircraft AS a ON fd.AircraftId = a.Id
GROUP BY AircraftId, a.Manufacturer, a.FlightHours
  HAVING COUNT(*) >= 2
ORDER BY FlightDestinationsCount DESC, fd.AircraftId

--Problem09
SELECT p.FullName,
       COUNT(p.FullName) AS CountOfAircaft,
       SUM(fd.TicketPrice) AS TotalPayed
  FROM [Passengers] AS p
  JOIN FlightDestinations as fd ON fd.PassengerId = p.Id
 GROUP BY p.FullName
 HAVING COUNT(p.FullName) > 1 AND p.FullName LIKE '_a%'
 ORDER BY p.FullName

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

GO
--Problem11
CREATE FUNCTION udf_FlightDestinationsByEmail(@email VARCHAR(50))
RETURNS INT
AS
BEGIN
        DECLARE @Count INT = (SELECT COUNT(*)
                             FROM FlightDestinations AS fd
                             JOIN Passengers AS p ON fd.PassengerId = p.Id
                             WHERE p.Email = @email)
        RETURN @Count
END

GO

--TestFunction Problem 11
SELECT dbo.udf_FlightDestinationsByEmail ('PierretteDunmuir@gmail.com')

SELECT dbo.udf_FlightDestinationsByEmail('Montacute@gmail.com')

SELECT dbo.udf_FlightDestinationsByEmail('MerisShale@gmail.com')

GO
--Problem12
CREATE PROCEDURE usp_SearchByAirportName @airportName VARCHAR(70)
AS
BEGIN
            SELECT a.AirportName,
                   p.FullName,
                   CASE
                       WHEN fd.TicketPrice <= 400 THEN 'Low'
                       WHEN fd.TicketPrice BETWEEN 401 AND 1500 THEN 'Medium'
                       WHEN fd.TicketPrice >= 1501 THEN 'High'
                   END AS LevelOfTickerPrice,
                   ac.Manufacturer,
                   ac.Condition,
                   act.TypeName
              FROM Airports AS a
              JOIN FlightDestinations AS fd ON a.Id = fd.AirportId
              JOIN Passengers AS p ON fd.PassengerId = p.Id
              JOIN Aircraft AS ac ON fd.AircraftId = ac.Id
              JOIN AircraftTypes AS act ON ac.TypeId = act.Id
              WHERE a.AirportName = @airportName
              ORDER BY ac.Manufacturer, p.FullName
END

GO

--TestProcedure Problem12

EXEC usp_SearchByAirportName 'Sir Seretse Khama International Airport'

--TheEnd