create table Company (
ID_comp int primary key auto_increment,
name char(10)
);
create table  Trip (
trip_no  int primary key auto_increment, 
ID_comp int,
foreign key (ID_comp) references Company(ID_comp), 
plane char(10), 
town_from char(25), 
town_to char(25),
time_out datetime,
time_in datetime
);
create table Passenger (
ID_psg int primary key auto_increment,
name char(20)
);
create table Pass_in_trip (
trip_no  int primary key auto_increment,
foreign key (trip_no) references Trip(trip_no),
date_ datetime,
ID_psg int,
foreign key (ID_psg) references Passenger(ID_psg),
place char(10)
)

create table Pass_in_trip (
    trip_no int,
    FOREIGN KEY (trip_no) REFERENCES Trip(trip_no),
    date_ datetime,
    ID_psg int,
    FOREIGN KEY(ID_psg) REFERENCES Passenger(ID_psg),
    place char(10),
    PRIMARY KEY (trip_no, date_, ID_psg)
    )

INSERT INTO passenger(`ID_psg`,`name`,`age`)
VALUES(NULL,'Anna','34');

alter table company
add rating char(3);
update company set rating = 5 where ID_comp = 1;
update company set rating = 4.5 where ID_comp = 2;
update company set rating = 5 where ID_comp = 3;
update company set rating = 5 where ID_comp = 4;
update company set rating = 4.5 where ID_comp = 5;

select avg(age) as average_age from passenger

select count(ID_comp) as number_of_values from company

SELECT plane, COUNT(plane) AS Kogus
FROM Trip
GROUP by plane

SELECT town_from, town_to, time_out, time_in, (time_in - time_out) AS Kestvus
FROM trip

select name, town_from
from company as c, trip as t
where c.ID_comp = t.ID_comp and t.town_from like "Tallinn"

select p.name, pit.place, t.town_to
from passenger as p, pass_in_trip as pit, trip as t
where p.ID_psg = pit.ID_psg and pit.ID_psg = t.ID_comp and p.name like "Oleksander" 

create table exclusive_passenger (
ID_psg int primary key auto_increment,
name int,
foreign key (name) references Passenger(ID_psg),
is_exclusive boolean
);
insert into exclusive_passenger (name, is_exclusive) values (1, true);
insert into exclusive_passenger (name, is_exclusive) values (2, false);
insert into exclusive_passenger (name, is_exclusive) values (3, true);
insert into exclusive_passenger (name, is_exclusive) values (4, false);
insert into exclusive_passenger (name, is_exclusive) values (5, true);

select p.name, ep.is_exclusive
from passenger as p, exclusive_passenger as ep
where p.ID_psg = ep.name and ep.is_exclusive = 1;