---

--1
SELECT A.aircraft_code, A.model, S.seat_no, S.fare_conditions 
FROM Aircrafts A JOIN Seats S
ON A.aircraft_code = S.aircraft_code
WHERE A.model = 'Аэробус A319-100'
ORDER BY S.seat_no;

--2
SELECT T.passenger_name, F.departure_airport, F.status, TF.fare_conditions, TF.amount
FROM Tickets T JOIN Ticket_flights TF
ON T.ticket_no = TF.ticket_no
JOIN Flights F
ON TF.flight_id = F.flight_id
WHERE T.passenger_id LIKE ('5657%');

--3
SELECT T.passenger_name, BP.seat_no, TF.fare_conditions, TF.amount
FROM Tickets T JOIN Ticket_flights TF
ON T.ticket_no = TF.ticket_no
JOIN Boarding_passes BP
ON (TF.ticket_no, TF.flight_id) = (BP.ticket_no, BP.flight_id)
WHERE T.passenger_name LIKE ('% Naumov') OR T.passenger_name LIKE ('Maksim %')
ORDER BY TF.amount DESC
LIMIT 500;

--4
SELECT TF.ticket_no  ' '  TF.flight_id AS ticket_And_Flight
FROM Ticket_flights TF JOIN Boarding_passes BP
ON (TF.ticket_no, TF.flight_id) = (BP.ticket_no, BP.flight_id);

--5
SELECT A.airport_code, COUNT(F.departure_airport)
FROM Airports A JOIN Flights F
ON A.airport_code = F.departure_airport
GROUP BY airport_code
ORDER BY COUNT(F.departure_airport) DESC;

--6
INSERT INTO Aircrafts_data
VALUES (111, '{"en": "Boeing 111", "ru": "Боинг 111"}', 5000),
(222, '{"en": "Boeing 222", "ru": "Боинг 222"}', 6000);

SELECT F.flight_id, F.status, A.aircraft_code, A.model
FROM Aircrafts_data A LEFT JOIN Flights F
ON A.aircraft_code = F.aircraft_code
ORDER BY A.aircraft_code;