AB aviafirma Microsoft SQL

--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
)

--Lisa values Company tabelisse
INSERT INTO Company(name)
Values ('SMARTLYNX');
SELECT * FROM Company

INSERT INTO Trip(ID_comp, plane, town_from, town_to, time_out, time_in)
VALUES (2, 'Boeing 454', 'London', 'Stocholm', '2023-12-10', '2023-12-10')
select * from Trip;

CREATE TABLE Passenger (
ID_psg int primary key identity(1,1),
name char(20)
);
SELECT * FROM Passenger

INSERT INTO Passenger (Name)
VALUES ('Dimitry Harosh')
SELECT * FROM Passenger

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),
);

INSERT INTO Pass_in_trip(trip_no, [date], ID_psg, place)
VALUES (2, '2023-10-15', 4, '25A')
Select * from Pass_in_trip

SELECT * FROM Passenger;
ALTER TABLE Passenger ADD Age int;
SELECT * FROM Passenger;

Update Passenger SET Age=25
WHERE ID_psg=1

ALTER TABLE Company ADD Country_of_company char(15);
SELECT * FROM Company

Update Company SET Country_of_company='Sweden'
WHERE ID_comp=5;
SELECT * FROM Company

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, ([time_in]-[time_out]) AS Kestvus
FROM trip 

--запрос на основании 2 таблиц
SELECT c.name, t.plane
FROM Company AS c, Trip AS t
WHERE c.ID_comp=t.ID_comp

--запрос на основании 2 таблиц
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 'Milan'

--Найти все города, в которые путешествует один произвольно выбранный пассажир. 
--Запрос на основе таблиц trip, pass_in_trip, passenger и выводящий поля name, place, town_to.
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 'Aleks Begun'

--Создание таблицы пилотов
CREATE TABLE Pilot(
trip_no int,
Experience int,
PRIMARY KEY (trip_no, Experience),
FOREIGN KEY (trip_no) references Trip(trip_no),
pilot_name char(20)
);
SELECT * FROM Pilot
--Добавление значений
INSERT INTO Pilot(trip_no, Experience, pilot_name)
VALUES (5, 20, 'Red Baron')
Select * from Pilot

--Запрос который ищет по имени пилота город в который летит, самолёт, время вылета и имя
SELECT p.pilot_name, t.town_to, t.time_out, t.plane
FROM Trip AS t, Pilot AS p
WHERE t.trip_no=p.trip_no AND p.pilot_name like 'Red Baron'