9 мин чтениябазы данныхтаблицыключиВПРSQLзадание 3КЕГЭ
Базы данных и связанные таблицы
Таблицы, ключи, связи «один ко многим», объединение таблиц через ВПР и Python, фильтры по дате и условиям — задание 3.
Открываем тему…
Реляционная модель
В задании 3 дано несколько связанных таблиц (товары, магазины, операции; люди и родственные связи; сотрудники и отделы). Строка таблицы — запись, столбец — поле. Первичный ключ однозначно определяет запись (артикул товара, ID магазина). Внешний ключ — поле другой таблицы, которое ссылается на первичный ключ: так таблицы «склеиваются».
- Связь «один ко многим»: одному товару соответствует много операций, но каждая операция относится к одному товару.
- Таблица связей «родитель — ребёнок» содержит пары ID; поиск потомков и предков — последовательное присоединение этой таблицы к найденным ID.
- Запрос на естественном языке («продано килограммов сахара в Центральном районе за неделю») раскладывается на фильтры по полям разных таблиц и на агрегацию (сумма, количество, среднее).
Алгоритм решения
- Определите, в каких таблицах лежат поля из условия, и найдите ключи, связывающие эти таблицы.
- Добавьте в «главную» таблицу (чаще всего таблицу операций) вспомогательные столбцы с нужными полями других таблиц: =ВПР(ключ; диапазон; номер_столбца; 0) или словарь в Python.
- Задайте фильтры по всем условиям (даты, тип операции, район, наименование).
- Просуммируйте нужную величину (количество упаковок × единиц в упаковке, цена × количество и т. д.).
- Проверьте ответ на 2–3 записях вручную и обратите внимание на единицы измерения.
ПримерВыручка через связь таблиц
Таблица «Товары» (ID, цена за кг): 1 — 80 руб., 2 — 120 руб., 3 — 150 руб. Таблица «Продажи» (ID товара, количество кг): (1; 5), (2; 3), (1; 4), (3; 2). Найдите общую выручку.
Решение
Для каждой продажи берём цену по ID товара: 5·80 + 3·120 + 4·80 + 2·150 = 400 + 360 + 320 + 300 = 1380 руб.
Код:
price = {1: 80, 2: 120, 3: 150}
sales = [(1, 5), (2, 3), (1, 4), (3, 2)]
print(sum(price[i] * kg for i, kg in sales)) # 1380
В Excel: добавить столбец =ВПР(A2; Товары!A:B; 2; 0) * B2 и просуммировать.
ПримерАналог на языке SQL
Как записать тот же запрос на языке SQL, если таблицы называются Goods(id, price) и Sales(good_id, kg)?
Решение
SELECT SUM(Goods.price * Sales.kg)
FROM Sales JOIN Goods ON Sales.good_id = Goods.id;
Условия отбора добавляются через WHERE (например, WHERE Sales.kg > 3), группировка — через GROUP BY. На экзамене SQL не требуется, но логика «соединить, отфильтровать, агрегировать» та же, что и в таблицах Excel.
ПримерПотомки в таблице родства
Таблица «Родство» (родитель → ребёнок): 1→2, 1→3, 2→4, 2→5, 3→6, 4→7. Сколько внуков у человека с ID 1?
Решение
Дети человека 1: 2 и 3. Их дети: 4, 5 (от 2) и 6 (от 3) — это внуки. Человек 7 — правнук (ребёнок внука 4). Ответ: 3.
Код:
rel = [(1, 2), (1, 3), (2, 4), (2, 5), (3, 6), (4, 7)]
kids = lambda p: [c for a, c in rel if a == p]
print(len({g for c in kids(1) for g in kids(c)})) # 3
Множество {…} защищает от двойного учёта: у человека может быть два родителя среди найденных.
ПримерФильтр по датам
Даты операций записаны в формате ГГГГ-ММ-ДД: 2026-06-01, 2026-06-03, 2026-06-08, 2026-06-09. Сколько операций произошло с 2 по 8 июня включительно?
Решение
Даты в таком формате корректно сравниваются как строки: 2026-06-02 ≤ d ≤ 2026-06-08 выполняется для двух дат (06-03 и 06-08).
Код:
ops = ['2026-06-01', '2026-06-03', '2026-06-08', '2026-06-09']
print(sum('2026-06-02' <= d <= '2026-06-08' for d in ops)) # 2
ПримерСумма заказов по городу (JOIN)
Даны две таблицы. «Клиенты» (ID, Фамилия, Город): (1, Иванов, Москва), (2, Петров, Казань), (3, Сидоров, Москва), (4, Орлов, Казань). «Заказы» (ID заказа, ID клиента, Сумма): (101, 1, 500), (102, 2, 300), (103, 1, 700), (104, 3, 200), (105, 4, 400), (106, 3, 600), (107, 2, 100). Найдите общую сумму заказов клиентов из Москвы.
Решение
Связываем таблицы по ключу «ID клиента». Москва: клиенты 1 и 3. Заказы клиента 1: 500 + 700 = 1200; клиента 3: 200 + 600 = 800. Итого 2000.
SQL:
SELECT SUM(Заказы.Сумма) FROM Заказы JOIN Клиенты ON Заказы.ID_клиента = Клиенты.ID WHERE Клиенты.Город = 'Москва';
Python: сначала словарь «ID клиента → город», затем сумма:
city = {1: 'Москва', 2: 'Казань', 3: 'Москва', 4: 'Казань'}
print(sum(s for _, c, s in orders if city[c] == 'Москва'))
ПримерПотомки в таблице «родитель — ребёнок»
В таблице родства записаны пары (родитель, ребёнок): 1→2, 1→3, 2→4, 2→5, 3→6, 4→7, 6→8. Сколько всего внуков и правнуков у человека с номером 1?
Решение
Строим поколения: дети человека 1 — 2, 3; внуки (дети детей) — 4, 5, 6; правнуки — 7, 8. Внуков 3, правнуков 2, вместе 5.
На Python удобно хранить словарь «родитель → список детей»:
kids = {}
for p, c in pairs:
kids.setdefault(p, []).append(c)
gen = kids.get(1, [])
total = 0
for level in range(2):
gen = [c for p in gen for c in kids.get(p, [])]
total += len(gen)
print(total)
Здесь цикл по level выполняется дважды: первый проход даёт внуков, второй — правнуков. Ответ: 5.