Pastein: CREATE DATABASE Airport; GO USE Airport; GO CREATE TABLE Cities ( Id INT IDENTITY(1,1) PRIMARY KEY, -- автоматический номер Name NVARCHAR(50) NOT NULL

Загрузка данных
CREATE DATABASE Airport;
GO

USE Airport;
GO

CREATE TABLE Cities
(
    Id   INT IDENTITY(1,1) PRIMARY KEY,   -- автоматический номер
    Name NVARCHAR(50) NOT NULL            -- название города
);

-- Рейсы самолетов, из нашенского
CREATE TABLE Flights
(
    Id             INT IDENTITY(1,1) PRIMARY KEY,
    FlightNumber   NVARCHAR(10) NOT NULL,  -- номер рейса
    CityId         INT NOT NULL,           -- куда летит 
    DepartureTime  DATETIME NOT NULL,      -- время вылета
    ArrivalTime    DATETIME NOT NULL,      -- время прилета
    BusinessSeats  INT NOT NULL,           -- всего мест в бизнес классе
    EconomySeats   INT NOT NULL,           -- всего мест в эконом классе
    FOREIGN KEY (CityId) REFERENCES Cities(Id)
);

-- Пассажиры
CREATE TABLE Passengers
(
    Id         INT IDENTITY(1,1) PRIMARY KEY,
    FirstName  NVARCHAR(50) NOT NULL,
    LastName   NVARCHAR(50) NOT NULL,
    Passport   NVARCHAR(20) NOT NULL       -- номер паспорта
);
CREATE TABLE Tickets
(
    Id          INT IDENTITY(1,1) PRIMARY KEY,
    FlightId    INT NOT NULL,              -- на какой рейс билет
    PassengerId INT NOT NULL,              -- кто купил
    Class       NVARCHAR(10) NOT NULL,     -- 'Business' или 'Economy'
    Price       MONEY NOT NULL,            -- цена билета
    SaleDate    DATE NOT NULL,             -- дата продажи
    FOREIGN KEY (FlightId)    REFERENCES Flights(Id),
    FOREIGN KEY (PassengerId) REFERENCES Passengers(Id),
    CHECK (Class IN ('Business', 'Economy'))  -- других классов быть не может
);
GO


-- Города (у Мадрида рейсов не будет)
INSERT INTO Cities (Name) VALUES
(N'Лондон'),   -- Id = 1
(N'Париж'),    -- Id = 2
(N'Берлин'),   -- Id = 3
(N'Рим'),      -- Id = 4
(N'Мадрид');   -- Id = 5

-- Берем сегодняшнюю дату, чтобы запросы на сегодня всегда что-то показывали
DECLARE @today DATETIME = CAST(CAST(GETDATE() AS DATE) AS DATETIME);

-- Рейсы: (номер, город, вылет, прилет, бизнес-мест, эконом-мест)
INSERT INTO Flights (FlightNumber, CityId, DepartureTime, ArrivalTime, BusinessSeats, EconomySeats) VALUES
('SU101', 1, DATEADD(HOUR, 9,  @today),      DATEADD(MINUTE, 9*60+150, @today),      2, 5),  -- сегодня, Лондон, 2.5 часа
('SU102', 2, DATEADD(HOUR, 14, @today),      DATEADD(MINUTE, 14*60+110, @today),     2, 5),  -- сегодня, Париж, 1ч 50м
('SU103', 1, DATEADD(HOUR, 32, @today),      DATEADD(MINUTE, 32*60+150, @today),     2, 5),  -- завтра, Лондон
('SU104', 3, DATEADD(HOUR, 36, @today),      DATEADD(MINUTE, 36*60+90, @today),      2, 5),  -- завтра, Берлин, 1.5 часа
('SU105', 4, DATEADD(HOUR, 55, @today),      DATEADD(MINUTE, 55*60+180, @today),     2, 5),  -- послезавтра, Рим, 3 часа
('SU106', 2, DATEADD(HOUR, 66, @today),      DATEADD(MINUTE, 66*60+110, @today),     2, 5),  -- послезавтра, Париж
('SU107', 1, DATEADD(HOUR, 82, @today),      DATEADD(MINUTE, 82*60+150, @today),     2, 5),  -- через 3 дня, Лондон
('SU108', 3, DATEADD(HOUR, 88, @today),      DATEADD(MINUTE, 88*60+90, @today),      2, 5);  -- через 3 дня, Берлин

-- Пассажиры
INSERT INTO Passengers (FirstName, LastName, Passport) VALUES
(N'Иван',   N'Петров',   'AB100001'),
(N'Анна',   N'Смирнова', 'AB100002'),
(N'Олег',   N'Кузнецов', 'AB100003'),
(N'Мария',  N'Попова',   'AB100004'),
(N'Сергей', N'Иванов',   'AB100005'),
(N'Елена',  N'Волкова',  'AB100006');

-- Билеты: (рейс, пассажир, класс, цена, дата продажи)
DECLARE @d DATE = CAST(GETDATE() AS DATE);   -- сегодня как дата (без времени)

INSERT INTO Tickets (FlightId, PassengerId, Class, Price, SaleDate) VALUES
(1, 1, 'Business', 500, @d),                    -- SU101: оба бизнес-места проданы
(1, 2, 'Business', 500, @d),
(1, 3, 'Economy',  200, DATEADD(DAY, -1, @d)),
(2, 4, 'Business', 450, @d),                    -- SU102: 1 из 2 бизнес-мест занято
(2, 5, 'Economy',  180, DATEADD(DAY, -1, @d)),
(3, 6, 'Economy',  210, @d),
(3, 1, 'Business', 520, DATEADD(DAY, -1, @d)),
(4, 2, 'Economy',  150, @d),
(5, 3, 'Business', 600, @d),
(5, 4, 'Economy',  300, DATEADD(DAY, -2, @d)),
(6, 5, 'Economy',  190, @d),
(7, 6, 'Economy',  220, DATEADD(DAY, -1, @d));
GO


-- Все рейсы в определенный город, дате, по времени
DECLARE @city NVARCHAR(50) = N'Лондон';
DECLARE @date DATE = CAST(GETDATE() AS DATE);   -- можно подставить любую дату

SELECT F.FlightNumber, C.Name AS City, F.DepartureTime
FROM Flights AS F
JOIN Cities AS C ON C.Id = F.CityId             -- присоединяем название города
WHERE C.Name = @city
  AND CAST(F.DepartureTime AS DATE) = @date     -- отрезаем время, оставляем дату
ORDER BY F.DepartureTime;                       -- сортировка по времени вылета
GO

-- Рейс с наибольшей длительностью полета
-- Используем ALL из урока: длительность >= длительности ВСЕХ рейсов
SELECT FlightNumber, DepartureTime, ArrivalTime,
       DATEDIFF(MINUTE, DepartureTime, ArrivalTime) AS DurationMinutes
FROM Flights
WHERE DATEDIFF(MINUTE, DepartureTime, ArrivalTime) >= ALL
      (SELECT DATEDIFF(MINUTE, DepartureTime, ArrivalTime)
       FROM Flights);
GO

-- Все рейсы, где полет длится больше двух часов
SELECT FlightNumber, DepartureTime, ArrivalTime,
       DATEDIFF(MINUTE, DepartureTime, ArrivalTime) AS DurationMinutes
FROM Flights
WHERE DATEDIFF(MINUTE, DepartureTime, ArrivalTime) > 120;
GO

-- Количество рейсов в каждый город
SELECT C.Name AS City, COUNT(F.Id) AS FlightsCount
FROM Cities AS C
LEFT JOIN Flights AS F ON F.CityId = C.Id       -- LEFT, чтобы города без рейсов тоже были (будет 0)
GROUP BY C.Name;
GO

-- Город, в который летают чаще всего
-- если несколько городов с одинаковым максимумом, покажет все
SELECT TOP 1 WITH TIES C.Name AS City, COUNT(*) AS FlightsCount
FROM Flights AS F
JOIN Cities AS C ON C.Id = F.CityId
GROUP BY C.Name
ORDER BY COUNT(*) DESC;                         -- самый частый - первый
GO

-- Рейсы по городам + общее количество за месяц
DECLARE @month INT = MONTH(GETDATE());          -- нужный месяц
DECLARE @year  INT = YEAR(GETDATE());

SELECT C.Name AS City, COUNT(*) AS FlightsCount
FROM Flights AS F
JOIN Cities AS C ON C.Id = F.CityId
WHERE MONTH(F.DepartureTime) = @month AND YEAR(F.DepartureTime) = @year
GROUP BY C.Name
UNION ALL                                       -- добавляем итоговую строку
SELECT N'ВСЕГО', COUNT(*)
FROM Flights
WHERE MONTH(DepartureTime) = @month AND YEAR(DepartureTime) = @year;
GO

-- Рейсы, вылетающие сегодня, где еще есть свободные места в бизнес классе
SELECT F.FlightNumber, F.DepartureTime,
       F.BusinessSeats - COUNT(T.Id) AS FreeBusinessSeats   -- всего мест минус проданные
FROM Flights AS F
LEFT JOIN Tickets AS T
       ON T.FlightId = F.Id AND T.Class = 'Business'        -- считаем только бизнес-билеты
WHERE CAST(F.DepartureTime AS DATE) = CAST(GETDATE() AS DATE)
GROUP BY F.FlightNumber, F.DepartureTime, F.BusinessSeats
HAVING COUNT(T.Id) < F.BusinessSeats;                       -- проданных меньше, чем мест
GO

-- Количемитво проданных билетов и общая сумма за указанный день
DECLARE @saleDay DATE = CAST(GETDATE() AS DATE);

SELECT COUNT(*) AS TicketsSold,
       SUM(Price) AS TotalSum
FROM Tickets
WHERE SaleDate = @saleDay;
GO

-- предварительная продажа: все рейсы на дату и сколько на них продано билетов
DECLARE @flightDay DATE = DATEADD(DAY, 1, CAST(GETDATE() AS DATE));  -- например, завтра

SELECT F.FlightNumber, F.DepartureTime, COUNT(T.Id) AS TicketsSold
FROM Flights AS F
LEFT JOIN Tickets AS T ON T.FlightId = F.Id     -- чтобы рейсы без билетов тоже были
WHERE CAST(F.DepartureTime AS DATE) = @flightDay
GROUP BY F.FlightNumber, F.DepartureTime;
GO

-- номера всех рейсов и названия всех городов
-- города без рейсов (Мадрид) покажутся с нул в номере рейса
SELECT F.FlightNumber, C.Name AS City
FROM Cities AS C
LEFT JOIN Flights AS F ON F.CityId = C.Id
ORDER BY C.Name;
GO
Больше возможностей при регистрации:
