---

--1
CREATE TABLE Pilots
(
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  pilot_name TEXT NOT NULL,
  experience INT
);

--2
INSERT INTO Pilots (pilot_name, experience)
VALUES ('Иван', 5),
('Петр', 2),
('Павел', 7),
('Борис', 1);

--3
ALTER TABLE Pilots
ADD COLUMN phone VARCHAR(12) NOT NULL DEFAULT '';

UPDATE Pilots SET phone = '+70000000000' WHERE pilot_name = 'Иван';
UPDATE Pilots SET phone = '+70000000001' WHERE pilot_name = 'Петр';
UPDATE Pilots SET phone = '+70000000002' WHERE pilot_name = 'Павел';
UPDATE Pilots SET phone = '+70000000003' WHERE pilot_name = 'Борис';

ALTER TABLE Pilots
ALTER COLUMN phone DROP DEFAULT;

--4
CREATE VIEW Airport_name
AS
 SELECT airport_code, airport_name, city
 FROM Airports_data;

SELECT * FROM Airport_name;

--5
CREATE VIEW Siberian_airports
AS
 SELECT * FROM Airports_data
 WHERE city->>'ru' = 'Новосибирск' OR city->>'ru' = 'Кемерово';

SELECT * FROM Siberian_airports;

--6
CREATE VIEW Fl_from_moscow_or_st_petersburg
AS
 SELECT city FROM Airports_data
 WHERE airport_code IN (
  SELECT arrival_airport FROM Flights
  WHERE departure_airport IN (
   SELECT airport_code FROM Airports_data
   WHERE city->>'ru' IN ('Москва', 'Санкт-Петербург')
  )
 )
 ORDER BY city DESC;

SELECT * FROM Fl_from_moscow_or_st_petersburg;

--7
CREATE VIEW  flights_city
AS
 SELECT A.city, COUNT(F.departure_airport) AS count_flights
 FROM Airports_data A LEFT JOIN Flights F
 ON A.airport_code = F.departure_airport
 GROUP BY A.city;

SELECT * FROM flights_city
WHERE city->>'ru' LIKE ('А%');

--8
CREATE VIEW Pass_name_from_domodedovo
AS
 SELECT T.ticket_no, T.passenger_name
 FROM Ticket_flights TF JOIN Tickets T
 ON TF.ticket_no = T.ticket_no
 JOIN Flights F
 ON TF.flight_id = F.flight_id
 WHERE F.scheduled_departure BETWEEN '2017-09-05 11:15:00+05' AND '2017-09-05 17:15:00+05' AND departure_airport IN (
  SELECT airport_code FROM Airports_data
  WHERE city->>'ru' = 'Домодедово'
 );

SELECT * FROM Pass_name_from_domodedovo;