CREATE TABLE client(
client_id INT PRIMARY KEY AUTO_INCREMENT,
surname VARCHAR(50),
client_name VARCHAR(50),
patronymic VARCHAR(50),
age INT,
passport VARCHAR(10),
phone VARCHAR(30)
);
CREATE TABLE tour (
tour_id INT PRIMARY KEY AUTO_INCREMENT,
cost INT,
departure_date DATE,
goal VARCHAR(20),
client_id INT,
trip_id INT
);
CREATE TABLE trip (
trip_id INT PRIMARY KEY AUTO_INCREMENT,
trip_name VARCHAR(50),
distance INT,
trip_time DECIMAL(10,1),
city_id INT,
vehicle_id INT
);
CREATE TABLE vehicle (
vehicle_id INT PRIMARY KEY AUTO_INCREMENT,
vehicle_name VARCHAR(50)
);
CREATE TABLE country (
country_id INT PRIMARY KEY AUTO_INCREMENT,
country_name VARCHAR(50),
need_visa BOOLEAN
);
CREATE TABLE city (
city_id INT PRIMARY KEY AUTO_INCREMENT,
city_name VARCHAR(50),
country_id INT
);
CREATE TABLE options (
options_id INT PRIMARY KEY AUTO_INCREMENT,
need_food BOOLEAN,
need_excursion BOOLEAN,
living VARCHAR(50),
visa_service BOOLEAN,
tour_id INT
);
INSERT INTO client(surname, client_name, patronymic, age, passport, phone)
VALUES ('Ермакова', 'Кира', 'Игнатьевна', 33, 4725350780, 6646246),
('Хмельнов', 'Константин', 'Макарович', 46, 4843303702, 0689314),
('Рудников', 'Яков', 'Никитович', 21, 4093685060, 5663911),
('Абакумова', 'Юлия', 'Николаевна', 24, 4536799639, 3433361),
('Полыгалова', 'Ксения', 'Власовна', 38, 4734651811, 3655017),
('Килин', 'Федор', 'Валерьевич', 19, 4026663500, 2771751),
('Винтухов', 'Антон', 'Семенович', 53, 4834708849, 7501654);
INSERT INTO country(country_name, need_visa)
VALUES ('Россия', 0),
('США', 1),
('Япония', 1),
('Франция', 1),
('Абхазия', 0);
INSERT INTO city(city_name, country_id)
VALUES ('Сочи', 1),
('Геленджик', 1),
('Лос-Анджелес', 2),
('Майами', 2),
('Токио', 3),
('Киото', 3),
('Париж', 4),
('Сухум', 5);
INSERT INTO vehicle(vehicle_name)
VALUES ('Самолет'),
('Поезд'),
('Автобус'),
('Яхта');
INSERT INTO options(need_food, need_excursion, living, visa_service, tour_id)
VALUES (1, 0, 'не требуется', 1, 1),
(1, 1, 'двухместное', 0, 2),
(0, 0, 'одноместное', 0, 3),
(1, 1, 'трехместное', 0, 4),
(1, 0, 'одноместное', 1, 5),
(1, 1, 'одноместное', 0, 6),
(0, 0, 'одноместное', 1, 7),
(1, 1, 'одноместное', 1, 8),
(1, 0, 'двухместное', 1, 9),
(1, 0, 'одноместное', 1, 10);
INSERT INTO trip(trip_name, distance, trip_time, city_id, vehicle_id)
VALUES ('Москва - Сочи', 1361, 1.2, 1, 1),
('Ростов - Геленджик', 322, 7, 2, 3),
('Москва - Майами', 9225, 11.4, 4, 1),
('Сочи - Лос-Анджелес', 11101, 18.5, 3, 1),
('Рыбинск - Сочи', 705, 69.5, 1, 2),
('Москва - Токио', 7484, 9.2, 5, 1),
('Владивосток - Киото', 948, 12, 6, 4),
('Москва - Париж', 2487, 1.5, 7, 1),
('Ростов - Сухум', 481, 18, 8, 2);
INSERT INTO tour(cost, departure_date, goal, client_id, trip_id)
VALUES (53000, "2022-06-14", 'Работа', 1, 4),
(33000, "2021-09-23", 'Отдых', 5, 1),
(5300, "2022-05-08", 'Работа', 1, 5),
(13000, "2020-12-25", 'Отдых', 7, 2),
(115000, "2020-08-20", 'Обучение', 3, 6),
(9000, "2021-10-17", 'Экскурсия', 6, 9),
(120000, "2020-08-18", 'Обучение', 5, 6),
(98000, "2022-08-25", 'Обучение', 4, 7),
(13000, "2020-06-14", 'Лечение', 2, 3),
(35000, "2021-09-05", 'Шоппинг', 4, 8);
SELECT surname AS Фамилия, client_name AS Имя, patronymic AS Отчество, country_name AS Страна
FROM
client
JOIN tour USING(client_id)
JOIN trip USING(trip_id)
JOIN city USING(city_id)
JOIN country USING(country_id)
WHERE country.country_id IN
(
SELECT quest1.country_id
FROM
(
SELECT country_id, COUNT(client_id) AS Количество_людей
FROM tour
JOIN trip USING(trip_id)
JOIN city USING(city_id)
JOIN country USING(country_id)
GROUP BY country_id
)quest1
JOIN
(
SELECT country_id, COUNT(client_id) AS Количество_людей
FROM tour
JOIN trip USING(trip_id)
JOIN city USING(city_id)
JOIN country USING(country_id)
GROUP BY country_id
ORDER BY Количество_людей DESC
LIMIT 1
) quest2
ON quest1.Количество_людей= quest2.Количество_людей
);
SELECT surname AS Фамилия, client_name AS Имя, patronymic AS Отчество, goal AS Цель
FROM
client
JOIN tour USING(client_id)
WHERE goal IN
(
SELECT quest1.goal
FROM
(
SELECT goal, COUNT(client_id) AS count_people
FROM tour
GROUP BY goal
)quest1
JOIN
(
SELECT goal, COUNT(client_id) AS count_people
FROM tour
GROUP BY goal
ORDER BY count_people DESC
LIMIT 1
) quest2
ON quest1.count_people= quest2.count_people
);
SELECT country_name AS Страна, COUNT(client_id) AS Количество_людей
FROM tour
JOIN trip USING(trip_id)
JOIN city USING(city_id)
JOIN country USING(country_id)
GROUP BY country_name
ORDER BY Количество_людей DESC;
SELECT surname AS Фамилия, client_name AS Имя, patronymic AS Отчество, age AS Возраст, country_name AS Страна
FROM client
JOIN tour USING(client_id)
JOIN trip USING(trip_id)
JOIN city USING(city_id)
JOIN country USING(country_id)
WHERE (goal = "Обучение") AND (country_name = "Япония") AND age < 25;
SELECT MONTH(departure_date) AS Месяц, SUM(cost) AS Сумма, MAX(cost) AS Макс_цена_покупки
FROM tour
GROUP BY MONTH(departure_date)
ORDER BY SUM(cost) DESC, MAX(COST) DESC;
SELECT surname, client_name, patronymic, YEAR(departure_date) AS Год
FROM client
JOIN tour USING(client_id)
JOIN trip USING(trip_id)
JOIN city USING(city_id)
JOIN country USING(country_id)
WHERE country_name = 'США' AND YEAR(departure_date) =
(
SELECT YEAR(departure_date)
FROM tour
JOIN trip USING(trip_id)
JOIN city USING(city_id)
JOIN country USING(country_id)
WHERE country_name = 'Россия' AND client.client_id = tour.client_id
)
ORDER BY departure_date;
Telegram: DB developers channel