CREATE TABLE Company (
ID_comp int primary key identity(1,1),
name char(10)
);

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('KebabLines');
Delete from Company
Where ID_comp=5;

Insert Into Trip(ID_comp,plane, town_from, town_to, time_out, time_in)
Values(1,'Airbus', 'Tokyo', 'Seul', '2023-12-10','2023-12-10' );
select*from Trip

CREATE TABLE Passanger (
ID_psg int primary key identity(1,1),
name char(20)
);
Select*from Passanger;

Insert into Passanger(name)
Values ('Laura')
Select * From Passanger

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 Passanger(Id_psg)
);

Insert Into Pass_in_Trip(
trip_no, [date], Id_psg, place)
Values
(3,'2022-10-12', 2, '25A');
Select * from Pass_in_Trip

select*from Passanger;
Alter Table Passanger ADD Age int;
select*from Passanger;

Update Passanger set Age =25
Where Id_psg=1;
select*from Passanger;

select*from Company;
Alter Table Company ADD Country varchar(20);
select*from Company;

Update Company set Country='Estonia'
where Id_comp =1;
select*from Company;

Select AVG (Age) As keskminevanus
from Passanger 

Select COUNT(*) as Kogus
from Company 

SELECT plane, COUNT(plane) AS Kogus
FROM Trip
GROUP by plane
--kolichectvo putishestviy s gruppirovkoy po nazavaniju

SELECT town_from, town_to, time_out, time_in,
Cast(([time_in]-[time_out])as int) AS Kestvus
FROM trip 
prodolzhitelnost putishestvija 

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

---айди все компании, вылетающие из одного произвольного города. Запрос на основе таблиц trip, company и выводящий поля company.name, town_from.
select c.name, t.town_from
from Company as c inner join Trip as t
on C.ID_comp=t.ID_comp
where t.town_from like 'Tallinn

Select p.name, pt.place, t.town_to
from Trip as t, Pass_in_trip as pt, Passanger  as p
Where t.trip_no = pt.trip_no and
p.Id_psg = pt.ID_psg and
p.name like 'Laura%';

CREATE TABLE Pilet (
pilet_id int primary key identity(1,1),
piletMaksa int,
ID_psg int,
Foreign key (ID_psg) references Passanger(ID_psg),
);
Select*from Pilet;

Insert into Pilet (piletihind, Id_psg)
Values(87,4);
Select*from Pilet;

Select p.name, pl.piletihind
from Passanger  as p, Pilet as pl
Where p.Id_psg = pl.Id_psg and
p.name like 'Maksim%';