SQL ACADEMY ответы и решения заданий (часть 2, задания 23-44)
SQL Academy (ответы и решения заданий 23-44)
ВКонтакте WhatsApp Pinterest Facebook Email
Задание 23. Найдите самый дорогой деликатес (delicacies) и выведите его стоимость.
SELECT g.good_name, p.unit_price FROM Goods AS g JOIN Payments AS p ON (g.good_id=p.good) JOIN GoodTypes AS gt ON (g.type=gt.good_type_id) WHERE p.unit_price = (SELECT MAX(p.unit_price) FROM Payments AS p JOIN Goods AS g ON (g.good_id=p.good) JOIN GoodTypes AS gt ON (g.type=gt.good_type_id) WHERE gt.good_type_name='delicacies');
Задание 24. Определить кто и сколько потратил в июне 2005.
SELECT member_name, SUM(amount*unit_price) AS costs FROM FamilyMembers AS fm JOIN Payments AS p ON fm.member_id=p.family_member WHERE MONTH(date) = 06 AND YEAR(date) = 2005 GROUP BY member_name;
Задание 25. Определить, какие товары имеются в таблице Goods, но не покупались в течение 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 good_type_id FROM GoodTypes JOIN Goods ON good_type_id=type JOIN Payments ON good_id=good WHERE YEAR(date)=2005);
Задание 27. Узнать, сколько потрачено на каждую из групп товаров в 2005 году. Вывести название группы и сумму
SELECT good_type_name, SUM(amount*unit_price) AS costs FROM GoodTypes JOIN Goods ON good_type_id=type JOIN Payments ON good_id=good 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 Passenger JOIN Pass_in_trip ON Pass_in_trip.passenger=Passenger.id JOIN Trip ON Trip.id=Pass_in_trip.trip 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(YEAR(CURRENT_DATE) - YEAR(birthday))) AS age FROM FamilyMembers;
Задание 33. Найдите среднюю стоимость икры. В базе данных хранятся данные о покупках красной (red caviar) и черной икры (black caviar).
SELECT AVG(unit_price) AS cost FROM Payments WHERE good IN (SELECT good_id FROM Goods WHERE good_name LIKE '%caviar');
Задание 34. Сколько всего 10-ых классов
SELECT COUNT(name) AS count FROM Class WHERE name LIKE '10%';
Задание 35. Сколько различных кабинетов школы использовались 2.09.2019 в образовательных целях?
SELECT COUNT(classroom) AS count FROM Schedule WHERE date='2019-09-02';
Задание 36. Выведите информацию об обучающихся живущих на улице Пушкина (ul. Pushkina)?
SELECT * FROM Student WHERE address LIKE 'ul. Pushkina%';
Задание 37. Сколько лет самому молодому обучающемуся ?
SELECT MIN(TIMESTAMPDIFF(YEAR,birthday,CURRENT_DATE)) AS year FROM Student;
Задание 38. Сколько Анн (Anna) учится в школе?
SELECT COUNT(first_name) AS count FROM Student WHERE first_name='Anna';
Задание 39. Сколько обучающихся в 10 B классе ?
SELECT COUNT(student) AS count FROM Student_in_class JOIN Class ON Student_in_class.class=Class.id WHERE Class.name = '10 B';
Задание 40. Выведите название предметов, которые преподает Ромашкин П.П. (Romashkin P.P.) ?
SELECT name AS subjects FROM Subject JOIN Schedule ON Subject.id=Schedule.subject JOIN Teacher ON Schedule.teacher=Teacher.id WHERE Teacher.last_name='Romashkin' AND Teacher.first_name LIKE 'P%' AND Teacher.middle_name LIKE 'P%';
Задание 41. Во сколько начинается 4-ый учебный предмет по расписанию ?
SELECT start_pair FROM Timepair WHERE >Задание 42. Сколько времени обучающийся будет находиться в школе, учась со 2-го по 4-ый уч. предмет ?
SELECT DISTINCT TIMEDIFF( (SELECT end_pair FROM Timepair WHERE (SELECT start_pair FROM Timepair WHERE ) AS time FROM Timepair;
Задание 43. Выведите фамилии преподавателей, которые ведут физическую культуру (Physical Culture). Отсортируйте преподавателей по фамилии.
SELECT last_name FROM Teacher JOIN Schedule ON Teacher.id=Schedule.teacher JOIN Subject ON Schedule.subject=Subject.id WHERE Subject.name = 'Physical Culture' ORDER BY Teacher.last_name
Задание 44. Найдите максимальный возраст (колич. лет) среди обучающихся 10 классов ?
SELECT MAX(TIMESTAMPDIFF(YEAR,birthday,CURRENT_DATE)) as max_year FROM Student JOIN Student_in_class ON Student.id=Student_in_class.student JOIN Class ON Student_in_class.class=Class.id WHERE Class.name LIKE '10%';
SQL получение записей не входящих в другие таблицы
Елена Талалаева Ученик (180) Аглая Шниц, equipment — это группа товаров, ответ который мы должны получить. То есть в одну группу входят разные товары, нам нужно найти ту группу , из которой не покупался ни один товар, а по вашему запросу , если хотя бы один товар из группы товаров не покупался, она выдает эту группу в ответ
Задачи¶
Тут я складываю решение задач по SQL, может кому-то понадобится.
Именно для PostgreSQL есть отдельный раздел тут
Cсылки на задачи¶
- SQL. Занимательные задачи — отличный список задач с схемой HR отдела. Затрагивает очень много кейсов — от фильтрация, группировка, агрегация. Мастхев для прорешивания в первую очередь.
- SQL-EX — гигабазовый онлаин тренажер с >500 задач.
- SQL Academy — ещё один онлаин тренажер.
- Leetcode — Задачи по БД на литкоде.
- Интерактивный тренажер по SQL, Stepik — подойдет прям для новичков
- SQLBolt
Решения¶
Задачи с sql-academy.org¶
- В какие города можно улететь из Парижа (Paris) и сколько времени это займёт?
Задача на знание встроенных функций, решается при помощи TIMEDIFF :
SELECT town_to, TIMEDIFF(time_in, time_out) as flight_time FROM trip WHERE town_from = 'Paris'
SELECT * FROM trip WHERE time_out BETWEEN "1900-01-01T10:00:00" AND "1900-01-01T14:00:00"
SELECT name FROM passenger WHERE LENGTH(name) = (SELECT MAX(LENGTH(name)) FROM passenger);
SELECT member_name FROM FamilyMembers ORDER BY birthday LIMIT 1
SELECT good_name, unit_price FROM Payments JOIN Goods ON Goods.good_id = Payments.good JOIN GoodTypes ON GoodTypes.good_type_id = Goods.type WHERE GoodTypes.good_type_name = "delicacies" ORDER BY unit_price DESC LIMIT 1
SELECT fm.status, fm.member_name, SUM(p.amount * p.unit_price) as costs FROM FamilyMembers as fm JOIN Payments AS p ON fm.member_id = p.family_member JOIN Goods as g ON g.good_id = p.good JOIN GoodTypes AS gt ON gt.good_type_id = g.type WHERE gt.good_type_name = "entertainment" GROUP BY fm.status, fm.member_name
SELECT g.good_name FROM Payments as p JOIN Goods as g ON p.good = g.good_id GROUP BY g.good_name HAVING COUNT(good_name) > 1
SELECT name FROM Passenger GROUP BY name HAVING COUNT(name) > 1
SELECT name, count(pt.id) as count FROM Pass_in_trip AS pt JOIN Passenger AS p ON pt.passenger = p.id GROUP BY pt.passenger ORDER BY count desc, name asc
SELECT member_name, status, SUM(p.amount * p.unit_price) as costs FROM Payments AS p JOIN FamilyMembers AS fm ON fm.member_id = p.family_member WHERE p.date BETWEEN "2005-01-01" AND "2005-12-31" GROUP BY p.family_member
SELECT fm.member_name, sum(p.amount * p.unit_price) AS costs FROM Payments AS p JOIN FamilyMembers AS fm ON fm.member_id = p.family_member WHERE p.date BETWEEN "2005-06-01" AND "2005-06-30" GROUP BY p.family_member
SELECT good_name FROM Goods AS g WHERE g.good_id NOT IN ( SELECT good FROM Payments AS p WHERE YEAR(p.date) = 2005 )
SELECT good_type_name FROM GoodTypes WHERE good_type_name NOT IN ( SELECT DISTINCT gt.good_type_name FROM Payments as p JOIN Goods AS g ON p.good = g.good_id JOIN GoodTypes AS gt ON g.type = gt.good_type_id WHERE YEAR(p.date) = 2005 )
SELECT gt.good_type_name, SUM(p.amount * p.unit_price) as costs FROM Payments AS p JOIN Goods AS g ON p.good = g.good_id JOIN GoodTypes AS gt ON g.type = gt.good_type_id WHERE YEAR(p.date) = 2005 GROUP BY gt.good_type_id
SELECT DISTINCT p.name FROM Trip AS t JOIN Pass_in_trip AS pt ON t.id = pt.trip JOIN Passenger AS p ON pt.passenger = p.id WHERE t.town_to = "Moscow" AND t.plane = "TU-134"
SELECT pt.trip, count(pt.trip) as count from Pass_in_trip AS pt GROUP BY pt.trip ORDER BY count(pt.trip) DESC
SELECT * FROM FamilyMembers WHERE member_name LIKE "% Quincey"
SELECT AVG(p.unit_price) as cost FROM Payments AS p JOIN Goods AS g ON p.good = g.good_id WHERE g.good_name IN ("red caviar", "black caviar")
SELECT COUNT(classroom) as count FROM Schedule WHERE date = "2019-09-02"
SELECT MIN(TIMESTAMPDIFF(YEAR, birthday, CURRENT_DATE)) AS year FROM Student
SELECT FLOOR(AVG(TIMESTAMPDIFF(YEAR, birthday, CURRENT_DATE))) AS age FROM FamilyMembers
SELECT s.name as subjects FROM Subject AS s JOIN Schedule AS sc ON sc.subject = s.id JOIN teacher AS t On sc.teacher = t.id WHERE t.last_name = "Romashkin"
SELECT DISTINCT TIMEDIFF( ( SELECT end_pair FROM Timepair WHERE id = 4 ), ( SELECT start_pair FROM Timepair WHERE id = 2 ) ) as time from Timepair
SELECT last_name from Teacher AS t JOIN Schedule AS sc ON sc.teacher = t.id JOIN Subject as s ON sc.subject = s.id WHERE s.name = "Physical Culture" ORDER BY t.last_name
SELECT MAX( TIMESTAMPDIFF(YEAR, s.birthday, CURRENT_DATE) ) as max_year FROM Student_in_class AS sc JOIN Class AS c ON sc.class = c.id JOIN Student AS s ON sc.student = s.id WHERE c.name LIKE "10%"
SELECT classroom FROM Schedule GROUP BY classroom HAVING COUNT(classroom) = ( SELECT COUNT(classroom) FROM Schedule GROUP BY classroom ORDER BY classroom DESC LIMIT 1 )
SELECT DISTINCT name FROM Class AS c JOIN Schedule AS sc ON sc.class = c.id JOIN Teacher AS t ON sc.teacher = t.id WHERE t.last_name = "Krauze"
SELECT count(id) as count FROM Schedule WHERE date = "2019-08-30" AND teacher = ( SELECT id FROM Teacher WHERE last_name = "Krauze" )
SELECT c.name as name, count(c.id) as count FROM Student_in_class AS sc JOIN Class AS c ON sc.class = c.id JOIN Student AS s ON sc.student = s.id GROUP BY c.name ORDER BY count(c.id) DESC
SELECT COUNT(student) * 100 / ( SELECT COUNT(student) FROM Student_in_class ) as percent FROM Student_in_class AS si JOIN Class AS c ON si.class = c.id WHERE c.name = "10 A"
SELECT FLOOR(COUNT(id) * 100 / ( SELECT COUNT(id) FROM Student )) as percent FROM Student WHERE YEAR(birthday) = 2000
SELECT sc.teacher FROM Schedule AS sc JOIN Class AS c ON c.id = sc.class WHERE c.name LIKE "11%" GROUP BY sc.teacher HAVING COUNT(DISTINCT c.name) = 2
DELETE FROM Company WHERE Company.id IN ( SELECT company FROM Trip GROUP BY company HAVING COUNT(id) = (SELECT MIN(count) FROM (SELECT count(*) as count FROM Trip GROUP BY Trip.company) as min ) )
INSERT INTO Reviews SET id = (SELECT COUNT(*)+1 FROM Reviews as a), reservation_id = ( SELECT Reservations.id FROM Reservations JOIN Users ON Users.id = Reservations.user_id JOIN Rooms ON Rooms.id = Reservations.room_id WHERE Users.name = "George Clooney" AND Rooms.address = "11218, Friel Place, New York" ), rating = 5
UPDATE Timepair SET start_pair = start_pair + INTERVAL 30 MINUTE, end_pair = end_pair + INTERVAL 30 MINUTE
SELECT Rooms.* FROM Rooms JOIN Reservations ON Reservations.room_id = Rooms.id WHERE WEEK(Reservations.start_date, 1) = 12 AND YEAR(start_date) = 2020
SELECT REGEXP_SUBSTR(email, '(?<=@)[^.]*.[^.]*(?=\.).') as domain, COUNT(REGEXP_SUBSTR(email, '(?<=@)[^.]*.[^.]*(?=\.).')) as count FROM Users GROUP BY domain ORDER BY count DESC, domain
SELECT Rooms.home_type, Rooms.address, IFNULL(SUM(DATEDIFF(Reservations.end_date, Reservations.start_date)), 0) as days, IFNULL(SUM(total), 0) as total_fee FROM Rooms LEFT JOIN Reservations ON Rooms.id = Reservations.room_id WHERE Rooms.has_tv = true AND has_internet = true AND has_kitchen = true AND has_air_con = true GROUP BY Rooms.id
SELECT owner_id, IFNULL(SUM(Reservations.total), 0) as total_earn FROM Rooms LEFT JOIN Reservations ON Reservations.room_id = Rooms.id GROUP BY owner_id
SELECT room_id, COUNT(room_id) as count FROM Reservations GROUP BY room_id HAVING MOD(count, 2)
SELECT YEAR(start_date) as year, MONTH(start_date) as month, COUNT(*) as amount FROM Reservations GROUP BY YEAR(start_date), MONTH(start_date) ORDER BY YEAR(start_date) ASC
SQL ACADEMY ответы и решения заданий (часть 1, задания 1-22)
SQL Academy (ответы и решения заданий 1-22)
ВКонтакте WhatsApp Pinterest Facebook Email
Ниже представлены наши варианты решения заданий из онлайн тренажера на сайте SQL ACADEMY (sql-academy.org). Ответы на 66 заданий представленные в онлайн тренажере разбиты на 3 части. Здесь, в первой части, представлены ответы на первые 22 задания.
Задание 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(*) AS count FROM Trip WHERE plane = 'TU-134';
Задание 6. Какие компании совершали перелеты на Boeing
SELECT DISTINCT name FROM Company JOIN Trip ON Company.id=Trip.company WHERE plane = 'Boeing';
Задание 7. Вывести все названия самолётов, на которых можно улететь в Москву (Moscow)
SELECT DISTINCT plane FROM Trip WHERE town_to = 'Moscow';
Задание 8. В какие города можно улететь из Парижа (Paris) и сколько времени это займёт?
SELECT town_to, TIMEDIFF(time_in, time_out) AS flight_time FROM Trip WHERE town_from = 'Paris';
Задание 9. Какие компании организуют перелеты с Владивостока (Vladivostok)?
SELECT DISTINCT name FROM Company JOIN Trip ON Company.id=Trip.company WHERE town_from = 'Vladivostok';
Задание 10. Вывести вылеты, совершенные с 10 ч. по 14 ч. 1 января 1900 г.
SELECT * FROM Trip WHERE time_out BETWEEN '1900-01-01 10:00:00' AND '1900-01-01 14:00: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 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 WHERE name = 'Bruce Willis';
Задание 15. Во сколько Стив Мартин (Steve Martin) прилетел в Лондон (London)?
SELECT time_in FROM Trip JOIN Pass_in_trip ON Trip.id=Pass_in_trip.trip JOIN Passenger ON Pass_in_trip.passenger=Passenger.id WHERE name='Steve Martin' AND town_to='London';
Задание 16. Вывести отсортированный по количеству перелетов (по убыванию) и имени (по возрастанию) список пассажиров, совершивших хотя бы 1 полет.
SELECT name, COUNT(*) AS count FROM Passenger JOIN Pass_in_trip ON Passenger.id=Pass_in_trip.passenger 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 JOIN Payments ON FamilyMembers.member_id=Payments.family_member WHERE YEAR(date) = 2005 GROUP BY member_name, status;
Задание 18. Узнать, кто старше всех в семьe
SELECT member_name FROM FamilyMembers WHERE birthday = (SELECT MIN(birthday) FROM FamilyMembers);
Задание 19. Определить, кто из членов семьи покупал картошку (potato)
SELECT DISTINCT status FROM FamilyMembers JOIN Payments ON FamilyMembers.member_id=Payments.family_member JOIN Goods ON Payments.good=Goods.good_id WHERE good_name = 'potato';
Задание 20. Сколько и кто из семьи потратил на развлечения (entertainment). Вывести статус в семье, имя, сумму
SELECT fm.status, fm.member_name, SUM(p.amount*p.unit_price) AS costs FROM FamilyMembers AS fm JOIN Payments AS p ON fm.member_id=p.family_member JOIN Goods AS g ON p.good=g.good_id JOIN GoodTypes AS gt ON g.type=gt.good_type_id WHERE good_type_name = 'entertainment' GROUP BY fm.status, fm.member_name;
Задание 21. Определить товары, которые покупали более 1 раза
SELECT good_name FROM Goods JOIN Payments ON Goods.good_id=Payments.good GROUP BY good HAVING COUNT(good) > 1;
Задание 22. Найти имена всех матерей (mother)
SELECT member_name FROM FamilyMembers WHERE status = 'mother';