Несколько упражнений

Начнём с упражнений, приведённых в конце прошлого раздела.

Решения упражнений прошлого раздела

1. Составьте запрос, который выдаёт имена всех людей, у которых есть хотя бы два ребёнка.

Приведём здесь два разных решения. Первое основано на вложенном подзапросе, считающем количество детей:

SELECT name FROM people WHERE 
    (SELECT count(*) FROM parents WHERE parent_id = people.id) >= 2;

Второе решение моделирует ситуацию «есть хотя бы два ребёнка» как двухрёберный подграф графа родственных отношений:

SELECT name FROM
    people,
    parents AS a,
    parents AS b
WHERE
    people.id = a.parent_id AND
    people.id = b.parent_id AND
    a.child_id != b.parent_id
GROUP BY people.id;

2. Составьте запрос, который выдаёт имена детей всех людей, у которых есть хотя бы два ребёнка. Один и тот же человек не должен находиться в выдаче более одного раза!

Приведём пять вариантов решения: сильновложенный, плоский и три промежуточных.

Сильновложенное решение вида «найдём людей, у которых есть хотя бы один родитель, у которого есть хотя бы два ребёнка» :

SELECT name FROM people WHERE
    (SELECT count(*) FROM parents AS p1 WHERE
        p1.child_id  = people.id AND
        (SELECT count(*) FROM parents AS p2 WHERE 
            p2.parent_id = p1.parent_id) >= 2) > 0;

Плоский вариант получается простой манипуляцией со вторым решением прошлого упражнения:

SELECT name FROM
    people,
    parents AS a,
    parents AS b
WHERE
    b.parent_id = a.parent_id AND
    people.id   = b.child_id  AND
    a.child_id != b.parent_id
GROUP BY people.id;

Первый промежуточный вариант состоит в том, чтобы найти тех, кто находится среди детей тех родителей, у которых хотя бы два ребёнка:

SELECT name FROM people WHERE
    people.id IN (SELECT p1.child_id FROM parents AS p1 WHERE
        (SELECT count(*) FROM parents AS p2 WHERE 
            p1.parent_id = p2.parent_id) >= 2);

Второй является выражением той же самой идеологии при помощи GROUP BY:

SELECT name FROM 
    people, 
    (SELECT p1.child_id AS result_id FROM parents as p1 
    WHERE
        (SELECT count(*) FROM parents AS p2 WHERE
            p1.parent_id = p2.parent_id) >= 2
    GROUP BY p1.child_id) 
WHERE people.id = result_id;

Наконец, третий промежуточный вариант, самый простой из всех:

SELECT name FROM
    people,
    parents AS p1
WHERE
    people.id = p1.child_id AND
    (SELECT count(*) FROM parents AS p2 WHERE p2.parent_id = p1.parent_id) >= 2
GROUP BY people.id;

3. Составьте запрос, который выдаёт имена людей вместе с количеством внуков у каждого.

Здесь приведём только решение в виде плоского запроса (вообще говоря, при работе с базами данных плоские запросы являются более предпочтительными при отсутствии существенно более простых неплоских альтернатив).

SELECT name, count(*) FROM
    people,
    parents AS a,
    parents AS b
WHERE
    people.id  = a.parent_id AND
    a.child_id = b.parent_id
GROUP BY people.id;

Отметим, что этот запрос работает только при отсутствии ситуаций вида «некий человек является родителем двух родителей одного и того же человека». При наличии таких ситуаций запрос можно модифицировать следующим образом:

SELECT name, count() FROM
    people,
    (SELECT a.parent_id AS result_id FROM parents AS a, parents AS b 
    WHERE a.child_id = b.parent_id GROUP BY a.parent_id, b.child_id)
WHERE people.id = result_id
GROUP BY people.id;

4. Составьте запрос, который выдаёт имена и возраста всех людей, являющихся внуками тех, у кого более двух детей.

Этот запрос собирается из решений предыдущих задач.

SELECT name, age FROM
    people,
    ages,
    parents AS a,
    parents AS b
WHERE
    people.id   = people_id  AND
    people.id   = a.child_id AND
    a.parent_id = b.child_id AND
    (SELECT count(*) FROM parents AS c WHERE c.parent_id = b.parent_id) > 2
GROUP BY people.id;

Несколько новых упражнений

Эти запросы собраны из различных проверочных работ.

1. Количество людей, у которых хотя бы 3 родителя.
2. Количество внуков Ивана.
3. Количество людей, у которых есть родитель старше 30 лет.
4. Количество братьев/сестёр Ивана.
5. Имена племянников Ивана.
6. Имена и возраста детей родителей Ивана.
7. Количество разных возрастов детей Ивана.