- Setup sqlite3
- Задача
- Скачайте базу данных packages.db
- Откройте Powershell приложение
- Выполните команду
cd Загрузки - Откройте базу данных используя sqlite3
sqlite3 packages.db
# Откройте базу данных используя sqlite3
sqlite3 packages.db
# .tables показывает все таблицы которые у нас имеются в базе данных
sqlite> .tables
addresses drivers packages scans
sqlite> select *
...> from addresses
...> limit 5;
id address type
----- ------------------------------ ---------------
1 7660 Sharon Street Residential
2 60 Drake Place Residential
3 88 City Point Court Residential
4 266 Dorchester Avenue Residential
5 2 Otis Place Residential
# выйти из базы данных
sqlite> .exit
Выполните эти команды чтобы данные выглядели как здесь вверху показано:
sqlite> .headers on
sqlite> .mode column
Вот полный перевод задания на русский язык, аккуратный и понятный.
✅ Задание
Описание задачи
Вы — почтовый клерк города Бостона и следите за доставкой почты по городу. Обычно все посылки успешно доставляются. Но иногда вам попадается загадка: пропавшая посылка!
Для каждого клиента, который сообщает о пропавшей посылке, вам нужно определить:
- Текущее место нахождения пропавшей посылки
- Тип адреса или места (например, жилой дом (residential), бизнес-центр (business) и т.д.)
- Содержимое посылки (content)
Все, что вам известно — только то, что рассказывает клиент. Чтобы разгадать каждую загадку, вы будете использовать базу данных packages.db, которая содержит информацию о перемещении посылок по городу.
Ваша задача — найти каждую пропавшую посылку, используя только данные базы.
✅ Схема базы
packages.db хранит информацию обо всех недавних доставках в Бостоне. В неё входят следующие таблицы:
- Drivers(Водители), которые доставляют посылки
- Packages(Сами посылки)
- Addresses (Адреса), например "1234 Main Street"
- Scans (Сканирования посылок), которые фиксируют, что водитель забрал (Pick) или доставил (Drop) посылку в определённый момент
Таблицы
addresses
- id — ID адреса
- address — сам адрес (например, 7660 Sharon Street)
- type — тип адреса (жилой(residential), коммерческий(business) и т.п.)
drivers
- id — ID водителя
- name — имя водителя
packages
- id — ID посылки
- contents — содержимое посылки
- from_address_id — ID адреса отправителя
- to_address_id — ID адреса получателя (не всегда куда она дошла!)
scans
- id — ID сканирования
- driver_id — ID водителя, сделавшего скан
- package_id — ID посылки
- address_id — ID адреса, где посылку отсканировали
- action — действие: Pick (забор) или Drop (доставка)
- timestamp — дата и время сканирования
🎯 Задача 1. Найти пропавшее письмо
К вам приходит Аннеке и говорит:
Клерк, меня зовут Аннеке. Я живу на 900 Somerville Avenue. Недавно я отправила особое письмо своей подруге Варше.
Она переехала на 2 Finnegan Street, uptown. (С этим адресом было непросто!)
Письмо — это поздравительная открытка, маленькие бумажные объятия.
Можете проверить, дошло ли оно до неё?
Подсказка: LIKE "С этим адресом было непросто!"
🎯 Задача 2
Второй клиент — добрый дедушка. Он говорит:
Ой, простите, клерк. Я отправлял подарок-сюрприз своей прекрасной внучке, которая живёт на 728 Maple Place.
Было это две недели назад, и дата доставки уже опоздала на целых семь дней, а подарок всё ещё не дошёл.
Я не помню, что было внутри, но знаю, что это было от всего сердца.
Можете ли вы найти посылку, чтобы она порадовала её день?
Отправлял я из своего дома по адресу 109 Tileston Street.
💡 Подсказка
Все задачи можно решить с помощью подзапросов.
✅ Решения
Задача 1
Важная информация:
- Адрес, откуда отправлена посылка: 900 Somerville Avenue
- Адрес, куда должна быть отправлена посылка: 2 Finnegan Street
(Аннеке сказала, что адрес было трудно указать, поэтому используем
LIKE, так как адрес назначения мог быть указан неточно) - Содержимое посылки: поздравительное письмо
-- 1. Получаем id адреса 900 Somerville Avenue
sqlite> select * from addresses
...> where address='900 Somerville Avenue';
id address type
--- --------------------- -----------
432 900 Somerville Avenue Residential
-- 2. Получаем id адреса 2 Finnigan Street
sqlite> select * from addresses
...> where address LIKE '2 Finn%';
id address type
--- ----------------- -----------
854 2 Finnigan Street Residential
-- Обратите внимание: она сказала FinnEgan, а правильный адрес — FinnIgan
-- 3. Получаем id посылки, отправленной с 432 на 854
sqlite> select * from packages
...> where from_address_id=432 and to_address_id=854;
id contents from_address_id to_address_id
--- --------------------- --------------- -------------
384 Congratulatory letter 432 854
-- 4. Проверяем информацию о посылке
sqlite> select * from scans
...> where package_id=384;
id driver_id package_id address_id action timestamp
-- --------- ---------- ---------- ------ --------------------------
54 1 384 432 Pick 2023-07-11 19:33:55.241794
94 1 384 854 Drop 2023-07-11 23:07:04.432178
Итог:
Посылка была забрана с правильного адреса и доставлена по правильному адресу.
Дошла ли посылка до 2 Finnigan Street? — Да.
Решение с использованием подзапросов
sqlite> select * from scans
...> where package_id=(
(x1...> select id from packages
(x1...> where from_address_id=(
(x2...> select id from addresses
(x2...> where address='900 Somerville Avenue'
(x2...> ) and to_address_id=(
(x2...> select id from addresses
(x2...> where address like '2 Finn%'
(x2...> )
(x1...> );
id driver_id package_id address_id action timestamp
-- --------- ---------- ---------- ------ --------------------------
54 1 384 432 Pick 2023-07-11 19:33:55.241794
94 1 384 854 Drop 2023-07-11 23:07:04.432178
✅ Задача 2
Дано:
- Адрес отправителя: 109 Tileston Street
- Адрес получателя: 728 Maple Place
- Содержимое посылки: неизвестно (мы найдём)
-- 1. Получаем адрес отправителя
sqlite> select * from addresses
...> where address='109 Tileston Street';
id address type
---- ------------------- -----------
9873 109 Tileston Street Residential
-- 2. Получаем адрес получателя
sqlite> select * from addresses
...> where address='728 Maple Place';
id address type
---- --------------- -----------
4983 728 Maple Place Residential
-- 3. Получаем все посылки, отправленные с 9873 на 4983
sqlite> select * from packages
...> where from_address_id=9873 and to_address_id=4983;
id contents from_address_id to_address_id
---- -------- --------------- -------------
9523 Flowers 9873 4983
-- 4. Проверяем сканы по посылке 9523
sqlite> select * from scans
...> where package_id=9523;
id driver_id package_id address_id action timestamp
----- --------- ---------- ---------- ------ --------------------------
10432 11 9523 9873 Pick 2023-08-16 21:41:43.219831
10500 11 9523 7432 Drop 2023-08-17 03:31:36.856889
12432 17 9523 7432 Pick 2023-08-23 19:41:47.913410
- ID отправителя: 9873
- ID получателя: 4983
Из анализа таблицы scans:
- Посылка 9523 была забрана с правильного адреса 9873
- Но была доставлена на неправильный адрес — 7432, вместо 4983
- Затем через несколько дней снова была забрана с неверного адреса 7432, вероятно для повторной доставки
Top comments (0)