SQL ACADEMY ответы и решения заданий (часть 2, задания 23-44)

Ниже представлены решения заданий из онлайн тренажера на сайте SQL ACADEMY (sql-academy.org). Здесь представлены ответы на задания 23-44.

Ответы на задания 1-22 (часть 1) здесь.

Ответы на задания 45-66 (часть 3) будут тут.

Ответы на задания 67-76 (часть 4) тут.

Задание 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 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
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%';

Ответы на задания 45-66 здесь.