---

CREATE DATABASE Airport;

USE Airport;


CREATE TABLE Passengers 
(
    PassengerID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,           
    LastName NVARCHAR(50) NOT NULL,            
    PassportNumber VARCHAR(20) NOT NULL UNIQUE,
    Email VARCHAR(100),                        
    Phone VARCHAR(20)                          
);


CREATE TABLE Flights 
(
    FlightID INT IDENTITY(1,1) PRIMARY KEY,    
    FlightNumber VARCHAR(10) NOT NULL UNIQUE,  
    DepartureCity NVARCHAR(50) NOT NULL,       
    ArrivalCity NVARCHAR(50) NOT NULL,         
    DepartureTime DATETIME NOT NULL,           
    ArrivalTime DATETIME NOT NULL              
);

CREATE TABLE Tickets 
(
    TicketID INT IDENTITY(1,1) PRIMARY KEY,    
    FlightID INT NOT NULL,                     
    PassengerID INT NOT NULL,                  
    SeatNumber VARCHAR(5) NOT NULL,            
    Class VARCHAR(10) NOT NULL,                
    Price DECIMAL(10, 2) NOT NULL,             
    PurchaseDate DATETIME DEFAULT GETDATE(),  

    CONSTRAINT FK_Tickets_Flights FOREIGN KEY (FlightID) 
        REFERENCES Flights(FlightID) ON DELETE CASCADE,
    CONSTRAINT FK_Tickets_Passengers FOREIGN KEY (PassengerID) 
        REFERENCES Passengers(PassengerID) ON DELETE CASCADE,
        
 

    CONSTRAINT CHK_Ticket_Class CHECK (Class IN ('Бизнес', 'Эконом'))
);


INSERT INTO Passengers (FirstName, LastName)
VALUES ('Иван', 'Иванов');

INSERT INTO Flights (FlightNumber, DepartureCity, ArrivalCity, DepartureTime, ArrivalTime)
VALUES ('SU123', 'Москва', 'Санкт-Петербург', '2023-11-01 10:00:00', '2023-11-01 11:30:00');

INSERT INTO Tickets (FlightID, PassengerID, SeatNumber, Class, Price)
VALUES (1, 1, '1A', 'Бизнес', 15000.00);

—-2---

SELECT * 
FROM Flights 
WHERE ArrivalCity = 'Санкт-Петербург' 
  AND DepartureTime >= '2023-11-01' 
  AND DepartureTime < '2023-11-02' 
ORDER BY DepartureTime ASC;


SELECT TOP 1 *, 
       DATEDIFF(MINUTE, DepartureTime, ArrivalTime) AS DurationMinutes
FROM Flights 
ORDER BY DurationMinutes DESC;


SELECT * 
FROM Flights 
WHERE DATEDIFF(MINUTE, DepartureTime, ArrivalTime) > 120;


SELECT ArrivalCity, SUM(1) AS FlightCount 
FROM Flights 
GROUP BY ArrivalCity;


SELECT TOP 1 ArrivalCity, SUM(1) AS FlightCount 
FROM Flights 
GROUP BY ArrivalCity 
ORDER BY FlightCount DESC;


SELECT 
    ISNULL(ArrivalCity, 'ИТОГО ЗА МЕСЯЦ') AS City, 
    SUM(1) AS FlightCount 
FROM Flights 
WHERE MONTH(DepartureTime) = 11 AND YEAR(DepartureTime) = 2023 
GROUP BY ROLLUP(ArrivalCity);

SELECT F.FlightNumber, F.DepartureTime, F.ArrivalCity
FROM Flights F
LEFT JOIN Tickets T ON F.FlightID = T.FlightID AND T.Class = 'Бизнес'
WHERE DATEDIFF(day, F.DepartureTime, GETDATE()) = 0
GROUP BY F.FlightID, F.FlightNumber, F.DepartureTime, F.ArrivalCity
HAVING SUM(CASE WHEN T.TicketID IS NOT NULL THEN 1 ELSE 0 END) < 10;


SELECT 
    SUM(1) AS TotalTicketsSold, 
    SUM(Price) AS TotalRevenue
FROM Tickets 
WHERE PurchaseDate >= '2023-11-01' 
  AND PurchaseDate < '2023-11-02';


SELECT 
    F.FlightNumber, 
    F.ArrivalCity,
    SUM(CASE WHEN T.TicketID IS NOT NULL THEN 1 ELSE 0 END) AS TicketsSold
FROM Flights F
LEFT JOIN Tickets T ON F.FlightID = T.FlightID 
    AND T.PurchaseDate >= '2023-11-01' 
    AND T.PurchaseDate < '2023-11-02'
GROUP BY F.FlightNumber, F.ArrivalCity;


SELECT DISTINCT FlightNumber, ArrivalCity 
FROM Flights 
WHERE DepartureCity = 'Москва';