Задачи

Тут я складываю решение задач по SQL, может кому-то понадобится.

Именно для PostgreSQL есть отдельный раздел тут

Cсылки на задачи

• SQL. Занимательные задачи - отличный список задач с схемой HR отдела. Затрагивает очень много кейсов - от фильтрация, группировка, агрегация. Мастхев для прорешивания в первую очередь.
• SQL-EX - гигабазовый онлаин тренажер с >500 задач.
• SQL Academy - ещё один онлаин тренажер.
• Leetcode - Задачи по БД на литкоде.
• Интерактивный тренажер по SQL, Stepik - подойдет прям для новичков
• SQLBolt

Решения

Задачи с sql-academy.org

1. В какие города можно улететь из Парижа (Paris) и сколько времени это займёт?
   Задача на знание встроенных функций, решается при помощи TIMEDIFF:
   

   SELECT town_to, TIMEDIFF(time_in, time_out) as flight_time 
   FROM trip 
   WHERE town_from = 'Paris'
   

2. Вывести вылеты, совершенные с 10 ч. по 14 ч. 1 января 1900 г.
   Совсем уж легкая задача на фильтрацию, проще всего решить через BETWEEN (т.к. ответ предполагает включение дат):
   

   SELECT * FROM trip 
   WHERE time_out 
     BETWEEN "1900-01-01T10:00:00" AND "1900-01-01T14:00:00"
   

3. Вывести пассажиров с самым длинным именем
   Тут сразу надо оговориться, что формулировка задачи неправильная, ведь можно вывести только ОДНОГО пассажира с самым длинным именем. Сама задача на вложенный SELECT и использование функций:
   

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

4. Узнать, кто старше всех в семьe
   Задачу можно решить через order by получив самую дальнюю дату:
   

   SELECT member_name FROM FamilyMembers
   ORDER BY birthday 
   LIMIT 1
   

5. Найдите самый дорогой деликатес (delicacies) и выведите его стоимость
   Можно сделать проще - соединить таблицы, отсортировать по убыванию и достать первую запись:
   

   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
   

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

   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
   

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

   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
   

8. Вывести имена людей, у которых есть полный тёзка среди пассажиров
   Ещё одна задачка на HAVING:
   

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

9. Вывести отсортированный по количеству перелетов (по убыванию) и имени (по возрастанию) список пассажиров, совершивших хотя бы 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
   

10. Определить, сколько потратил в 2005 году каждый из членов семьи
    

    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
    

11. Определить кто и сколько потратил в июне 2005
    

    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 
    

12. Определить, какие товары не покупались в 2005 году
    Для получения года из даты можно использовать функцию YEAR:
    

    SELECT good_name FROM Goods AS g
    WHERE g.good_id NOT IN (
        SELECT good FROM Payments AS p
        WHERE YEAR(p.date) = 2005
    )
    

13. Определить группы товаров, которые не приобретались в 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
    )
    

14. Узнать, сколько потрачено на каждую из групп товаров в 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
    

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

    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"
    

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

    SELECT pt.trip, count(pt.trip) as count from Pass_in_trip AS pt
    GROUP BY pt.trip
    ORDER BY count(pt.trip) DESC 
    

17. Вывести всех членов семьи с фамилией Quincey.
    

    SELECT * FROM FamilyMembers
    WHERE member_name LIKE "% Quincey"
    

18. Найдите среднюю стоимость икры. В базе данных хранятся данные о покупках красной (red caviar) и черной икры (black caviar).
    

    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")
    

19. Сколько различных кабинетов школы использовались 2.09.2019 в образовательных целях ?
    

    SELECT COUNT(classroom) as count 
    FROM Schedule
    WHERE date = "2019-09-02"
    

20. Сколько лет самому молодому обучающемуся ?
    

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

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

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

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

    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"
    

23. Сколько времени обучающийся будет находиться в школе, учась со 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
    

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

    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
    

25. Найдите максимальный возраст (колич. лет) среди обучающихся 10 классов ?
    JOIN с m2m таблицей:
    

    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%"
    

26. Какой(ие) кабинет(ы) пользуются самым большим спросом?
    Здесь кабинетов может быть несколько, поэтому получаем все классы, группируем их и фильтрируем их по количеству при помощи HAVING, попутно получая самый используемый класс:
    

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

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

    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"
    

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

    SELECT count(id) as count FROM Schedule
    WHERE date = "2019-08-30" AND teacher = (
        SELECT id FROM Teacher WHERE last_name = "Krauze"
    )
    

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

    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 
    

30. Какой процент обучающихся учится в 10 A классе ?

    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"
    

31. Какой процент обучающихся родился в 2000 году? Результат округлить до целого в меньшую сторону.
    

    SELECT FLOOR(COUNT(id) * 100 / (
        SELECT COUNT(id) FROM Student
    )) as percent
    FROM Student
    WHERE YEAR(birthday) = 2000
    

32. Выведите идентификаторы преподавателей, которые хотя бы один раз за всё время преподавали в каждом из одиннадцатых классов.
    Здесь нужно использовать очень хитрую конструкцию вида COUNT(DISTINCT c.name) - она уберет дубликаты классов (11 A и 11 B). То есть, если 2 пары велись в 11 A, а 3 пары велись в 11 B, обычный COUNT(c.name) выдаст 5, этот же - 2:

    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
    

33. Удалить компании, совершившие наименьшее количество рейсов.

    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
        )
    )
    

34. Добавить отзыв с рейтингом 5 на жилье, находящиеся по адресу "11218, Friel Place, New York", от имени "George Clooney"

    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
    

35. Перенести расписание всех занятий на 30 мин. вперед.

    UPDATE Timepair
    SET
        start_pair = start_pair + INTERVAL 30 MINUTE,
        end_pair  = end_pair  + INTERVAL 30 MINUTE
    

36. Выведите список комнат, которые были зарезервированы в течение 12 недели 2020 года.

    SELECT 
        Rooms.* 
    FROM 
        Rooms
    JOIN 
        Reservations ON Reservations.room_id = Rooms.id 
    WHERE 
        WEEK(Reservations.start_date, 1) = 12 AND YEAR(start_date) = 2020
    

37. Вывести в порядке убывания популярности доменные имена 2-го уровня, используемые пользователями для электронной почты. Полученный результат необходимо дополнительно отсортировать по возрастанию названий доменных имён. Регулярку я нагуглил, если что.
    

    SELECT 
        REGEXP_SUBSTR(email, '(?<=@)[^.]*.[^.]*(?=\.).') as domain,
        COUNT(REGEXP_SUBSTR(email, '(?<=@)[^.]*.[^.]*(?=\.).')) as count
    FROM 
        Users
    GROUP BY 
        domain
    ORDER BY count DESC, domain
    

38. Вывести список комнат со всеми удобствами (наличие ТВ, интернета, кухни и кондиционера), а также общее количество дней и сумму за все дни аренды каждой из таких комнат.

    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
    

39. Вывести идентификаторы всех владельцев комнат, что размещены на сервисе бронирования жилья и сумму, которую они заработали

    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 
    

40. Выведите id тех комнат, которые арендовали нечетное количество раз

    SELECT
        room_id,
        COUNT(room_id) as count
    FROM 
        Reservations
    GROUP BY 
        room_id 
    HAVING 
        MOD(count, 2)
    

41. Вывести количество бронирований по каждому месяцу каждого года, в которых было хотя бы 1 бронирование. Результат отсортируйте в порядке возрастания даты бронирования.

    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