create table city(
	CityID serial,
	Title  varchar(50) not null,
	constraint pkcity primary key (CityID)
);

create table users(
    UID    serial,
    Uname  varchar(50) not null,
    Mail   varchar(50) not null,
    Age    int not null,
    CONSTRAINT userpk PRIMARY KEY (UID)

);

create table airplane(
	AID    serial  PRIMARY KEY NOT NULL,
	fkCity int not null,
	Title  varchar(50) not null,
	CONSTRAINT fkCity FOREIGN KEY (fkCity) REFERENCES City(CityID)
	ON UPDATE CASCADE ON DELETE CASCADE
);

create table pilot(
	PID   serial PRIMARY KEY NOT NULL,
	Pname varchar(50) not null
);

create table staff(
	SID   serial PRIMARY KEY NOT NULL,
        Panem varchar(50) not null
);

create table hop(
    hopID     serial PRIMARY KEY NOT NULL,
    fkAir     int not null,
    fromCity  int not null,
    toCity    int not null,
    fkUser    int not null,
    fkPilot   int not null,
    fkStaff   int not null,
    CONSTRAINT fkAir FOREIGN KEY (fkAir) REFERENCES airplane(AID)
    ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fromCity FOREIGN KEY (fromCity) REFERENCES City(CityID)
    ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT toCity FOREIGN KEY (toCity) REFERENCES City(CityID)
    ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fkUser FOREIGN KEY (fkUser) REFERENCES Users(UID)
    ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fkPilot FOREIGN KEY (fkPilot) REFERENCES pilot(PID)
    ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fkStaff FOREIGN KEY (fkStaff) REFERENCES staff(SID)
    ON UPDATE CASCADE ON DELETE CASCADE
);

insert into City(Title) VALUES ('Almaty'),('Astana'),
                                ('Havana'),('Toronto'),
                                ('New York'),('Amsterdam'),
                                ('Samara'),('Sydney'),
                                ('Monaco'),('LA');

insert into users(Uname, Mail, Age) VALUES ('Daulet','d@gmail.com',19),('Zhanco','z@gmail.com',20),
							('Delilah','da@gmail.com',17),('Aray','ar@gmail.com',18),
							('Santiago','cuba@gmail.cu',25),('Dilara','DI@gmai.com',20),
						    ('Sonya','sonya@mail.ru',18),('Sydneyman','sydney@gmail.au',45),
						    ('Monacogirl','mongi@monaco.com',15),('Desmond','dasmond@la.com',25);

insert into airplane(fkCity, title) VALUES (1,'BOEING 727'),(2,'AIRBUS A320')
                                            ,(3,'GYROCOPTER 7.07'),(4,'FLYING COURIER'),
                                            (5,'Fly Emirates'),(6,'3Xtrim 452'),
                                            (7,'AERO BOERO 785'),(8,'BELL X2')
                                            ,(9,'Light ZX'),(10,'Sunrise 2334');

insert into pilot(pname) values('IAN'),('Dost'),
                                ('Clark'),('Kent'),
                                ('Yerassyl'),('Arnold'),
                                ('Gomez'),('Isco'),
                                ('Suarez'),('Remirez');

insert into staff(Panem) values('Sara'),('Aigerim'),
                                ('Samanta'),('Sam'),
                                ('Lisa'),('Merph'),
                                ('Karina'),('Milana'),
                                ('Anara'),('Elena');


insert into hop(fkAir, fromCity, toCity, fkUser, fkPilot, fkStaff) values(1,1,1,1,1,1),(2,2,2,2,2,2),
									(3,3,3,3,3,3),(4,4,4,4,4,4),
									(5,5,5,5,5,5),(6,6,6,6,6,6),
									(7,7,7,7,7,7),(8,8,8,8,8,8),
									(9,9,9,9,9,9),(10,10,10,10,10,10);
/*
  fkAir     int not null,
    fromCity  int not null,
    toCity    int not null,
    fkUser    int not null,
    fkPilot   int not null,
    fkStaff   int not null,
*/


select UName,Title from Users INNER JOIN hop ON Users.UID=hop.fkserwhere  City.CityID = 6;


select * from hop