SQLize | PHPize | SQLtest

-- Создаем таблицу аэропортов CREATE TABLE Airports ( AirportID INT PRIMARY KEY, Name VARCHAR(100), City VARCHAR(100), Country VARCHAR(100), IATA CHAR(3) ); -- Вставляем 5 тестовых записей INSERT INTO Airports (AirportID, Name, City, Country, IATA) VALUES (1, 'Sheremetyevo', 'Moscow', 'Russia', 'SVO'), (2, 'Domodedovo', 'Moscow', 'Russia', 'DME'), (3, 'Vnukovo', 'Moscow', 'Russia', 'VKO'), (4, 'Pulkovo', 'Saint Petersburg', 'Russia', 'LED'), (5, 'Koltsovo', 'Yekaterinburg', 'Russia', 'SVX'); -- Проверяем данные SELECT * FROM Airports; -- Таблица авиакомпаний CREATE TABLE Airlines ( AirlineID INT PRIMARY KEY, Name VARCHAR(100) ); -- Таблица рейсов CREATE TABLE Flights ( FlightID INT PRIMARY KEY, AirlineID INT, FlightNumber VARCHAR(20), DepartureAirportID INT, ArrivalAirportID INT, DepartureTime DATETIME, ArrivalTime DATETIME, FOREIGN KEY (AirlineID) REFERENCES Airlines(AirlineID) ); INSERT INTO Flights (FlightID, AirlineID, FlightNumber, DepartureAirportID, ArrivalAirportID, DepartureTime, ArrivalTime) VALUES (101, 1, 'SU100', 10, 20, '2024-06-01 08:00:00', '2024-06-01 10:30:00'), (102, 1, 'SU101', 10, 30, '2024-06-01 09:00:00', '2024-06-01 11:45:00'), (103, 2, 'S7100', 20, 10, '2024-06-01 12:00:00', '2024-06-01 14:30:00'), (104, 3, 'U6100', 30, 10, '2024-06-01 15:00:00', '2024-06-01 17:30:00'), (105, 1, 'SU102', 10, 40, '2024-06-01 16:00:00', '2024-06-01 18:30:00'); -- Таблица пассажиров на рейсах CREATE TABLE Passengers ( PassengerID INT PRIMARY KEY, FlightID INT, Name VARCHAR(100), FOREIGN KEY (FlightID) REFERENCES Flights(FlightID) ); SELECT f.* FROM Flights f JOIN Airlines a ON f.AirlineID = a.AirlineID WHERE a.Name = 'Pulkovo'; SELECT f.FlightID, f.FlightNumber, COUNT(p.PassengerID) AS PassengerCount FROM Flights f LEFT JOIN Passengers p ON f.FlightID = p.FlightID GROUP BY f.FlightID, f.FlightNumber;