SQLize Online / PHPize Online  /  SQLtest Online

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;