sql_academy_solutions

Решение задач по SQL. Тренажер sql-academy.org

Задание 1: Вывести имена всех когда-либо обслуживаемых пассажиров авиакомпаний

SELECT name from Passenger;

Задание 2: Вывести названия всеx авиакомпаний

SELECT name FROM Company;

Задание 3: Вывести все рейсы, совершенные из Москвы

SELECT * FROM Trip 
WHERE town_from = 'Moscow';

Задание 4: Вывести имена людей, которые заканчиваются на "man"

SELECT name FROM Passenger
WHERE name LIKE '%man';

Задание 5: Вывести количество рейсов, совершенных на TU-134

SELECT COUNT(plane) AS COUNT FROM Trip
WHERE plane = 'TU-134';

Задание 6: Какие компании совершали перелеты на Boeing

SELECT c.name
FROM Company AS c 
JOIN Trip AS t ON t.company = c.id
WHERE t.plane = 'Boeing'
GROUP BY c.id;

Задание 7: Вывести все названия самолётов, на которых можно улететь в Москву (Moscow)

SELECT plane FROM Trip
WHERE town_to = 'Moscow'
GROUP BY plane;

Задание 8: В какие города можно улететь из Парижа (Paris) и сколько времени это займёт?

SELECT town_to, TIMEDIFF(time_in, time_out) AS flight_time
FROM Trip
WHERE town_from = 'Paris';

Задание 9: Какие компании организуют перелеты из Владивостока (Vladivostok)?

SELECT c.name 
FROM Company AS c
JOIN Trip AS t ON t.company = c.id
WHERE town_from = 'Vladivostok';

Задание 10: Вывести вылеты, совершенные с 10 ч. по 14 ч. 1 января 1900 г.

SELECT * FROM Trip
WHERE time_out BETWEEN '1900-01-01T10:00' AND '1900-01-01T14:00';

Задание 11: Выведите пассажиров с самым длинным ФИО. Пробелы, дефисы и точки считаются частью имени.

SELECT name
FROM Passenger
WHERE LENGTH(name) = (
		SELECT MAX(LENGTH(name))
		FROM Passenger);

Задание 12: Вывести id и количество пассажиров для всех прошедших полётов

SELECT trip, COUNT(passenger) AS count
FROM Pass_in_trip
GROUP  BY trip;

Задание 13: Вывести имена людей, у которых есть полный тёзка среди пассажиров

SELECT name FROM Passenger
GROUP BY name
HAVING COUNT(name) > 1;

Задание 14: В какие города летал Bruce Willis

SELECT t.town_to 
FROM Trip AS t
JOIN Pass_in_trip AS pit ON pit.trip = t.id
JOIN Passenger AS p ON p.id = pit.passenger
WHERE name = 'Bruce Willis';

Задание 15: Выведите дату и время прилёта пассажира Стив Мартин (Steve Martin) в Лондон (London)

SELECT t.time_in
FROM Trip AS t
JOIN Pass_in_trip AS pit ON pit.trip = t.id
JOIN Passenger AS p ON p.id = pit.passenger
WHERE name = 'Steve Martin' AND town_to = 'London';

Задание 16: Вывести отсортированный по количеству перелетов (по убыванию) и имени (по возрастанию) список пассажиров, совершивших хотя бы 1 полет.

SELECT p.name, COUNT(pit.passenger) AS count
FROM Passenger AS p
JOIN Pass_in_trip AS pit ON pit.passenger = p.id
GROUP BY passenger
HAVING COUNT(trip) > 0
ORDER BY COUNT(trip) DESC, name;

Задание 17: Определить, сколько потратил в 2005 году каждый из членов семьи. В результирующей выборке не выводите тех членов семьи, которые ничего не потратили.

SELECT member_name, status, SUM(amount * unit_price) AS costs
FROM FamilyMembers AS fm
JOIN Payments AS p ON p.family_member = fm.member_id
WHERE YEAR(date) = 2005
GROUP BY member_name, status;

Задание 18: Выведите имя самого старшего человека. Если таких несколько, то выведите их всех.

SELECT member_name
FROM FamilyMembers
WHERE birthday = (
		SELECT MIN(birthday)
		FROM FamilyMembers);

Задание 19: Определить, кто из членов семьи покупал картошку (potato)

SELECT DISTINCT STATUS
FROM FamilyMembers AS fm
	JOIN Payments AS p ON p.family_member = fm.member_id
	JOIN Goods AS g ON g.good_id = p.good
WHERE good_name = 'potato';

Задание 20: Сколько и кто из семьи потратил на развлечения (entertainment). Вывести статус в семье, имя, сумму

SELECT status,
	member_name,
	SUM(amount * unit_price) AS costs
FROM FamilyMembers AS fm
	JOIN Payments AS p ON p.family_member = fm.member_id
	JOIN Goods AS g ON g.good_id = p.good
	JOIN GoodTypes AS gt ON gt.good_type_id = g.type
WHERE good_type_name = 'entertainment'
GROUP BY fm.status, fm.member_name;

Задание 21: Определить товары, которые покупали более 1 раза

SELECT good_name
FROM Goods AS g
	JOIN Payments AS p ON p.good = g.good_id
GROUP BY good
HAVING COUNT(*) > 1;

Задание 22: Найти имена всех матерей (mother)

SELECT member_name
FROM FamilyMembers
WHERE STATUS = 'mother';

Задание 23: Найдите самый дорогой деликатес (delicacies) и выведите его цену

SELECT good_name, unit_price
FROM Goods AS g
	JOIN Payments AS p ON p.good = g.good_id
	JOIN GoodTypes AS gt ON gt.good_type_id = g.type
WHERE good_type_name = 'delicacies'
ORDER BY unit_price DESC
LIMIT 1;

Задание 24: Определить кто и сколько потратил в июне 2005

SELECT member_name, SUM(amount * unit_price) AS costs
FROM FamilyMembers AS fm
    JOIN Payments AS p ON p.family_member = fm.member_id
WHERE YEAR(date) = '2005' AND MONTH(date) = 6
GROUP BY member_name;

Задание 25: Определить, какие товары не покупались в 2005 году

SELECT good_name
FROM Goods
WHERE good_id NOT IN (
		SELECT good
		FROM Payments
		WHERE YEAR(date) = '2005');

Задание 26: Определить группы товаров, которые не приобретались в 2005 году

SELECT good_type_name
FROM GoodTypes
WHERE good_type_id NOT IN(
		SELECT TYPE
		FROM Goods AS g
			JOIN Payments AS p ON p.good = g.good_id
		WHERE YEAR(date) = '2005');

Задание 27: Узнать, сколько потрачено на каждую из групп товаров в 2005 году. Вывести название группы и сумму.

SELECT good_type_name, SUM(amount * unit_price) AS costs
FROM GoodTypes AS gt
    JOIN Goods AS g ON g.type = gt.good_type_id
    JOIN Payments AS p ON p.good = g.good_id
WHERE YEAR(date) = '2005'
GROUP BY good_type_name;

Задание 28: Сколько рейсов совершили авиакомпании из Ростова (Rostov) в Москву (Moscow)?

SELECT COUNT(*) AS COUNT
FROM Trip
WHERE town_from = 'Rostov'AND town_to = 'Moscow'

Задание 29: Выведите имена пассажиров улетевших в Москву (Moscow) на самолете TU-134

SELECT DISTINCT name
FROM Trip AS t
	JOIN Pass_in_trip AS pit ON pit.trip = t.id
	JOIN Passenger AS p ON p.id = pit.passenger
WHERE town_to = 'Moscow' AND plane = 'TU-134';

Задание 30: Выведите нагруженность (число пассажиров) каждого рейса (trip). Результат вывести в отсортированном виде по убыванию нагруженности.

SELECT trip, COUNT(passenger) AS count
FROM Pass_in_trip
GROUP BY trip
ORDER BY count DESC;

Задание 31: Вывести всех членов семьи с фамилией Quincey.

SELECT *
FROM FamilyMembers
WHERE member_name LIKE '% Quincey';

Задание 32: Вывести средний возраст людей (в годах), хранящихся в базе данных. Результат округлите до целого в меньшую сторону.

SELECT FLOOR(AVG(TIMESTAMPDIFF(YEAR, birthday, CURDATE()))) AS age
FROM FamilyMembers

Задание 33: Найдите среднюю цену икры на основе данных, хранящихся в таблице Payments. В базе данных хранятся данные о покупках красной (red caviar) и черной икры (black caviar).

SELECT AVG(unit_price) AS cost
FROM Payments AS p
	JOIN Goods AS g ON g.good_id = p.good
WHERE good_name IN('red caviar', 'black caviar');

Задание 34: Сколько всего 10-ых классов

SELECT COUNT(name) AS count
FROM Class
WHERE name LIKE '10 %';

Задание 35: Сколько различных кабинетов школы использовались 2 сентября 2019 года для проведения занятий?

SELECT COUNT(classroom) AS count
FROM Schedule
WHERE YEAR(date) = '2019' AND MONTH(date) = '9' AND DAY(date) = '2';

Задание 36: Выведите информацию об обучающихся живущих на улице Пушкина (ul. Pushkina)?

SELECT *
FROM Student
WHERE address LIKE 'ul. Pushkina%';

Задание 37: Сколько лет самому молодому обучающемуся?

SELECT MIN(TIMESTAMPDIFF(YEAR, birthday, NOW())) AS year
FROM Student

Задание 38: Сколько Анн (Anna) учится в школе?

SELECT COUNT(*) AS count
FROM Student
WHERE first_name = 'Anna';

Задание 39: Сколько обучающихся в 10 B классе?

SELECT COUNT(*) AS count
FROM Class AS c
	JOIN Student_in_class AS sic ON sic.class = c.id
WHERE name = '10 B';

Задание 40: Выведите название предметов, которые преподает Ромашкин П.П. (Romashkin P.P.).

SELECT name AS subjects
FROM Subject AS sj
	JOIN Schedule AS sc ON sc.subject = sj.id
	JOIN Teacher AS tc ON tc.id = sc.teacher
WHERE last_name = 'Romashkin'
	AND first_name LIKE 'P%'
	AND middle_name LIKE 'P%';

Задание 41: Выясните, во сколько по расписанию начинается четвёртое занятие.

SELECT start_pair
FROM Timepair
WHERE id = 4;

Задание 42: Сколько времени обучающийся будет находиться в школе, учась со 2-го по 4-ый уч. предмет?

SELECT DISTINCT TIMEDIFF(
		(
			SELECT end_pair FROM Timepair
			WHERE id = 4
		),
		(
			SELECT start_pair FROM Timepair
			WHERE id = 2
		)) AS time
FROM Timepair;

Задание 43: Выведите фамилии преподавателей, которые ведут физическую культуру (Physical Culture). Отсортируйте преподавателей по фамилии в алфавитном порядке.

SELECT last_name
FROM Teacher AS t
	JOIN Schedule AS sc ON sc.teacher = t.id
	JOIN Subject AS sj ON sj.Id = sc.subject
WHERE name = 'Physical Culture'
ORDER BY last_name;

Задание 44: Найдите максимальный возраст (количество лет) среди обучающихся 10 классов на сегодняшний день. Для получения текущих даты и времени используйте функцию NOW().

SELECT MAX(TIMESTAMPDIFF(YEAR, birthday, NOW())) AS max_year
FROM Student AS st
	JOIN Student_in_class sc ON sc.student = st.id
	JOIN Class cl ON cl.id = sc.class
WHERE name LIKE '10 %';

Задание 45: Какие кабинеты чаще всего использовались для проведения занятий? Выведите те, которые использовались максимальное количество раз.

SELECT classroom
FROM Schedule
GROUP BY classroom
HAVING COUNT(classroom) = (
		SELECT COUNT(classroom) AS COUNT
		FROM Schedule
		GROUP BY classroom
		ORDER BY COUNT DESC LIMIT 1);

Задание 46: В каких классах введет занятия преподаватель "Krauze"?

SELECT DISTINCT c.name
FROM Schedule AS s
	JOIN Teacher t ON t.id = s.teacher
	JOIN Class c ON c.id = s.class
WHERE t.last_name = 'Krauze';

Задание 47: Сколько занятий провел Krauze 30 августа 2019 г.?

SELECT COUNT(*) AS COUNT
FROM Schedule AS s
	JOIN Teacher AS t ON t.id = s.teacher
WHERE t.last_name = 'Krauze'
	AND DATE_FORMAT(date, '%e %M %Y') = '30 August 2019';

Задание 48: Выведите заполненность классов в порядке убывания

SELECT name, COUNT(student) AS count
FROM Class AS c
	JOIN Student_in_class AS sic ON sic.class = c.id
GROUP BY name
ORDER BY count DESC;

Задание 53: Измените имя "Andie Quincey" на новое "Andie Anthony".

UPDATE FamilyMembers
SET member_name = 'Andie Anthony'
WHERE member_name = 'Andie Quincey';

Задание 56: Удалить все перелеты, совершенные из Москвы (Moscow).

DELETE FROM Trip
WHERE town_from = 'Moscow';

Задание 74: Выведите идентификатор и признак наличия интернета в помещении. Если интернет в сдаваемом жилье присутствует, то выведите «YES», иначе «NO».

SELECT id,
	IF(has_internet = 1, 'YES', 'NO') AS has_internet
FROM Rooms;

Задание 75: Выведите фамилию, имя и дату рождения студентов, кто был рожден в мае.

SELECT last_name, first_name, birthday
FROM Student
WHERE MONTHNAME(birthday) = 'May';

Name		Name	Last commit message	Last commit date
Latest commit History 7 Commits
README.md		README.md

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Repository files navigation

sql_academy_solutions

About

Releases

Packages

xorty-v/sql-academy_solutions

Folders and files

Latest commit

History

Repository files navigation

sql_academy_solutions

About

Topics

Resources

Stars

Watchers

Forks

Releases

Packages 0

Packages