CREATE DATABASE AA;

USE AA;

CREATE TABLE Company(
id INT IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(200) NOT NULL
);

CREATE TABLE Passenger(
id_Product INT IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(200)
);

CREATE TABLE Trip(
id INT IDENTITY(1,1) PRIMARY KEY,
company INT NOT NULL FOREIGN KEY REFERENCES Company(id) ON UPDATE CASCADE ON DELETE CASCADE,
plane VARCHAR(300) NOT NULL,
town_from VARCHAR(300) NOT NULL,
town_to VARCHAR(300) NOT NULL,
time_out DATETIME NOT NULL,
time_in DATETIME NOT NULL
);

CREATE TABLE Pass_in_trip(
id INT IDENTITY(1,1) PRIMARY KEY,
trip INT FOREIGN KEY REFERENCES Trip(id) ON UPDATE CASCADE ON DELETE CASCADE,
passenger INT FOREIGN KEY REFERENCES Passenger(id_Product) ON UPDATE CASCADE ON DELETE CASCADE,
place VARCHAR(100) NOT NULL
);


USE AA;

INSERT INTO Company (name)
VALUES
('Qatar Airways'),
('Eurowings'),
('LOT Polish Airlines')

INSERT INTO Passenger (name)
VALUES
('Christopher Nolan'),
('Tobey Maguire'),
('Arman Bale')

INSERT INTO Trip (company,plane,town_from,town_to,time_out,time_in)
VALUES
(1, 'TU-154', 'Samara', 'Turkey', '15.03.2024 08:15:13', '15.03.2024 14:15:13'),
(2, 'Boeing', 'Armavir', 'Moscow', '16.03.2024 02:25:21', '16.03.2024 10:25:21'),
(3, 'TU-134', 'Moscow', 'SPB', '11.03.2024 01:01:23', '11.03.2024 03:01:23')

INSERT INTO Pass_in_trip (trip,passenger,place)
VALUES
(1, 1, '1A'),
(2, 2, '2B'),
(3, 3, '3C')

#1)Вывести имена всех людей, которые есть в базе данных авиакомпаний
USE AA
SELECT name FROM Passenger

#2)Вывести названия всеx авиакомпаний
USE AA
SELECT name
FROM Company

#3)Вывести все рейсы, совершенные из Москвы
USE AA
SELECT id
FROM Trip
WHERE Town_from = 'Moscow'

#4)Вывести имена людей, которые заканчиваются на "man"
USE AA
SELECT * FROM Passenger
WHERE name LIKE '%man'

#5)Вывести количество рейсов, совершенных на TU-134
USE AA
SELECT COUNT(*) AS COUNT
FROM Trip
WHERE Plane = 'TU-134'

#6)Какие компании совершали перелеты на Boeing
USE AA
SELECT DISTINCT name
FROM Company
JOIN Trip
ON company.id=Trip.company
WHERE plane = 'Boeing'

#7)Вывести все названия самолётов, на которых можно улететь в Москву (Moscow)
USE AA
SELECT plane
FROM Trip
WHERE town_to = 'Moscow'

#8)Какие компании организуют перелеты из Владивостока (Vladivostok)?
USE AA
SELECT DISTINCT name
FROM Company
JOIN Trip
ON company.id=Trip.company
WHERE town_from = 'Vladivostok'

#9)В какие города летал Bruce Willis
USE AA
SELECT DISTINCT town_to
FROM Trip
JOIN Pass_in_trip
ON Trip.id=Pass_in_trip.trip
JOIN Passenger
ON Pass_in_trip.passenger=Passenger.id_Product
WHERE name = 'Bruce Willis'

#10)Вывести id и количество пассажиров для всех прошедших полётов
SELECT trip, COUNT(passenger) AS COUNT
FROM Pass_in_trip
GROUP BY trip

#11)Выведите дату и время прилёта пассажира Стив Мартин (Steve Martin) в Лондон (London)
USE AA
SELECT time_in
FROM Trip AS t
JOIN Pass_in_trip AS pit
ON t.id = trip
JOIN Passenger AS p
ON p.id_product = passenger
WHERE name = 'Steve Martin' AND town_to = 'London'