SQL NEW

create table Flights
(
flightnumber varchar(20) primary key,
departure varchar(20),
destination varchar(20),
flightdate date ,
planemodel varchar(20),
price int,
);

create table Hotels
(
hotelsn varchar(20) primary key,
country varchar(20),
city varchar(20),
street varchar(20) ,
housenumber int,
hotelrank float,
price int,
);

create table Clients
(
id varchar(20) primary key,
hotelname varchar(20) ,
passportid varchar(20) ,
firstname varchar(20) ,
lastname varchar(20) ,
country varchar(20) ,
city varchar(20) ,
street varchar(20) ,
birthday date ,
telephone varchar(20) ,
email varchar (20) ,
);

create table orders
(
orderid varchar(20) primary key ,
dealtype varchar(20) ,
dealdate date ,
nights int ,
totalprice int ,
people int,
);

create table flightsorder
(
orderid varchar(20) foreign key references orders (orderid) ,
flightnumber varchar(20) foreign key references Flights (flightnumber) ,
);

create table  hotelsorder
(
orderid varchar(20) foreign key references orders (orderid) ,
hotelsnumber varchar(20) foreign key references Hotels (hotelsn) ,
);

create table clientsorder
(
id varchar(20) foreign key references Clients (id) ,
orderid varchar(20) foreign key references orders (orderid) ,
);

insert into Flights values('LY 082' , 'Ben-Gurion' , 'Bangkok - Survernabumi' , '2019/06/28' , 'Boeing 757' , '680$'),
                          ('LY 337' , 'Ben-Gurion' , 'Amsterdam - Schipol' , '2019/06/28' , 'boeing 737' , '380$'),
                          ('LY 006' , 'Ben-Gurion' , 'Los Angeles - LAX' , '2019/07/29' , 'Dreamliner' , '990$'),
                          ('TO 4970' , 'Ben-Gurion' , 'Lyon-Saint Exupéry' , '2019/04/17' , 'boeing 737 - Max' , '420$'),
                          ('LY 564' , 'Ben-Gurion' , 'Warsaw - Chopin' , '2019/05/01' , 'AirBus 320' , '380$')

insert into Hotels values ('052' ,'Bayshore hotel resort ', 'Thailand' , 'Kohphangan','Ban Tai' ,'141' , '5' , '850THB'),
                          ('671' ,'Radisson Blu Hotel ', 'Holland' , 'Amsterdam','Rusland' ,'17' , '4.4' , '221€'),
                          ('123' ,'The Beverly Hills Hotel', 'USA - California ' , 'Los Angeles','Sunset Boulevard' ,'9641' , '5' , '645$ '),
                          ('134' ,'Sofitel Lyon Bellecour', 'France' , 'lyon','20 Quai Gailleton' ,'2nd arr' , '5' , '145€'),
                          ('135' ,'Vienna House Mokotow', 'Poland' , 'Warsaw','Postepu' ,'4' , '4' , '60€')

insert into Clients values ('205725567' , '305725567' ,'Osher' , 'Revach' , 'Israel' , 'Nahariyya' , 'Hahalutz' , '1994/09/27' , '0509823235' , 'osher671@outlook.com'),
                           ('311555114' , '405725567' ,'Yanir' , 'Itzhak' , 'Israel' , 'Shlomi' , 'Yefe Nof' ,    '1993/09/21' ,    '0544498454' ,'yanir401@gmail.com'),
                           ('123456789' , '789456123' ,'Haim' , 'Schwetz' , 'Israel' , 'Tel Aviv' , 'Ben Yehuda' , '1974/01/21' , '0545556789' , 'HaimS@outlook.com'),
                           ('456789123' , '215725547' ,'Daniel' , 'Barak' , 'Israel' , 'Karmiel' , 'Nesher' , '1994/02/20' , '05098231235' , 'Daniel18@walla.com'),
                           ('205725567' , '305725567' ,'Dor' , 'Peled' , 'Israel' , 'Nahariyya' , 'Hanita' , '1994/09/10' , '0509823435' , 'Dorpe18@gmail.com')

insert into orders values ('123456789' , 'AllInclude' , '2019/06/28' ,'15', '1645$' , '4'),
                          ('135792468' , 'AllInclude' , '2019/06/28' ,'5' , '621€' , '2'),
                          ('111111111' , 'AllInclude' , '2019/06/28' ,'13' , '1990$' , '6'),
                          ('222222222' , 'AllInclude' , '2019/06/28' ,'4' , '721€' , '2'),
                          ('333333333' , 'AllInclude' , '2019/06/28' ,'8' , '921€' , '3')

insert into flightsorder values ('123456789' ,'LY 082'),
                                ('135792468' ,'LY 337'),
                                ('111111111' ,'LY 006'),
                                ('222222222' ,'TO 4970'),
                                ('333333333' ,'LY 564')

insert into hotelsorder values ('123456789' ,'052'),
                               ('135792468' ,'671'),
                               ('111111111' ,'123'),
                               ('222222222' ,'134'),
                               ('333333333' ,'135')