--tabeli loomine
CREATE TABLE Company (
ID_comp int primary key identity(1,1),
name char(10)
);
Select * from Company;

CREATE TABLE  Trip (
trip_no  int primary key identity(1,1), 
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
);
Select * from Trip;

insert into Company(name)
Values('Lufthansa')
Select * from Company;

insert into Trip(ID_comp, plane, town_from, town_to, time_out, time_in)
Values 
(3,'Airbus','Tallinn','Milan','2023-12-10','2023-12-11')
Select * from Trip;

create table Pass_in_trip (
trip_no  int,
[date] datetime,
ID_psg int,
place char(10),
primary key (trip_no,[date], ID_psg),
foreign key (trip_no) references Trip(trip_no),
foreign key (ID_psg) references Passenger(ID_psg)
);

select * from Passenger;
alter table Passenger Add Age int;
select * from Passenger;

update Passenger set Age=17
where ID_psg=4;
select * from Passenger;

Select AVG(Age) AS keskmineVanus
from Passenger

select COUNT(*) as Kogus
from Company

--количество путешествий с группировкой по названию самолета
SELECT plane, COUNT(plane) AS Kogus
FROM Trip
GROUP by plane

--продолжительность путешествия
SELECT town_from, town_to, time_out, time_in,
Cast (([time_in]-[time_out]) as int)AS Kestvus
FROM trip 

select c.name,t.plane
from Company as c, Trip AS t
where c.ID_Comp=t.ID_Comp

select c.name, t.plane
from Company as c INNER JOIN Trip as t
on c.ID_Comp=t.ID_Comp
where t.town_from like 'Tallinn'

select pa.name, p.place, t.town_to
from  Trip as t, Pass_in_trip as p, Passenger as pa
where t.trip_no=p.trip_no and 
p.ID_psg=pa.ID_psg and
pa.name like 'Anna%'

insert into Kodakondus(ID_kod, rahvus, name, ID_psg)
values(4,'Eestlane','Anna',4)
select * from Kodakondus

CREATE TABLE Kodakondus (
ID_kod int, 
rahvus char(25),
name char(20),
ID_psg int,
foreign key (ID_psg) references Passenger(ID_psg)
);

select c.name,t.rahvus
from Company as c, Kodakondus AS t
where c.ID_Comp=t.ID_kod