EN
◀ Назад к описанию задания

Отправьте запросы к базе данных

1. Найдите всех исследователей, которые родились после 1980 года и являются археологами.

Ожидаемый результат: Список исследователей с их ID, именами и датами рождения.
💡Решение

Чтобы получить список всех исследователей, родившихся после 1980 года и являющихся археологами, выберите столбцы id, full_name и date_of_birth из таблицы Explorers с помощью условия WHERE:

SELECT id, full_name, date_of_birth
FROM Explorers
WHERE date_of_birth > '1980-12-31' AND speciality = 'Archaeologist';

2. Посчитайте, сколько артефактов находится в каждом состоянии (Отличное, Хорошее, Повреждено, Фрагментировано).

Ожидаемый результат: Список значений condition_id с количеством артефактов для каждого состояния. Каждая строка будет представлять уникальное состояние и показывать, сколько артефактов ему соответствует.
* Чтобы вывести названия состояний, потребуется JOIN, который будет рассмотрен позже.
💡Решение

Этот SQL-запрос использует GROUP BY для группировки строк в таблице Artifacts по condition_id. Функция COUNT(id) считает количество артефактов (по их ID) в каждой группе.

SELECT condition_id, COUNT(id) AS num_artifacts
FROM Artifacts
GROUP BY condition_id;

3. Получите список всех событий, отсортированных от новых к старым, а затем по типу события в алфавитном порядке.

Ожидаемый результат: Все события из таблицы Events, отсортированные сначала по столбцу date (самые новые в начале). Если несколько событий имеют одинаковую дату, они должны быть отсортированы по типу события в алфавитном порядке.
💡Решение

Этот SQL-запрос использует ORDER BY для сортировки результата. ORDER BY date DESC сортирует события по дате в порядке убывания, помещая последние события наверх. Второй критерий, event_type ASC, сортирует события по типу в алфавитном порядке, если даты совпадают.

SELECT id, name, date, event_type
FROM Events
ORDER BY date DESC, event_type ASC;

4. Получите список всех артефактов, найденных во время экспедиции, включая информацию о соответствующей экспедиции.

Ожидаемый результат: Таблица, где каждая строка содержит название артефакта, название экспедиции и дату её начала.
💡Решение

Необходимо выполнить JOIN между таблицами Artifacts и Expeditions по полю expedition_id.

SELECT a.name AS artifact_name, e.name AS expedition_name, e.start_date AS expedition_start_date
FROM Artifacts a
JOIN Expeditions e ON a.expedition_id = e.id;

5. Получите список всех артефактов с их текущим состоянием (например, Отличное, Хорошее и т.д.). Выведите название артефакта и его состояние.

Ожидаемый результат: Таблица, где каждая строка содержит название артефакта и его состояние.
💡Решение

Необходимо выполнить JOIN между таблицами Artifacts и Conditions по полям condition_id и id.

SELECT a.name, c.name AS condition
FROM Artifacts a
JOIN Conditions c ON a.condition_id = c.id;

6. Выведите список всех событий, связанных с экспедициями.

Ожидаемый результат: Таблица с названием события и названием экспедиции. Включайте только те события, где поле expedition_id не равно NULL.
💡Решение

Выполните JOIN между Events и Expeditions по expedition_id и id, а также фильтруйте по expedition_id IS NOT NULL.

SELECT ev.name AS event_name, exp.name AS expedition_name
FROM Events ev
JOIN Expeditions exp ON ev.expedition_id = exp.id
WHERE ev.expedition_id IS NOT NULL;

7. Получите список всех событий, связанных одновременно с регионом и экспедицией.

Ожидаемый результат: Для каждого события, которое связано и с регионом, и с экспедицией, в списке должна быть информация о событии, регионе и соответствующей экспедиции.
💡Решение

Необходимо выполнить JOIN таблицы Events с Regions по region_id, а также с Expeditions по expedition_id.

SELECT ev.name AS event_name, r.name AS region_name, exp.name AS expedition_name
FROM Events ev
JOIN Regions r ON ev.region_id = r.id
JOIN Expeditions exp ON ev.expedition_id = exp.id;

8. Получите список всех артефактов, для которых указаны исследователь, нашедший их, и текущее состояние артефакта.

Ожидаемый результат: Для каждого артефакта с указанным исследователем в списке должны быть название артефакта, имя исследователя и текущее состояние артефакта.
💡Решение

Выполните JOIN таблицы Artifacts с Explorers по founded_by_id, а также с Conditions по condition_id.

SELECT a.name AS artifact_name, ex.full_name AS explorer_name, c.name AS condition
FROM Artifacts a
JOIN Explorers ex ON a.founded_by_id = ex.id
JOIN Conditions c ON a.condition_id = c.id;

9. Получите список всех ролей в экспедициях, включая имя исследователя и название экспедиции, в которой он участвовал.

Ожидаемый результат: Для каждой роли в экспедиции в списке должны быть указаны роль, имя исследователя и название экспедиции.
💡Решение

Выполните JOIN таблицы ExpeditionRoles с Explorers по explorer_id, а также с Expeditions по expedition_id.

SELECT er.role, ex.full_name AS explorer_name, exp.name AS expedition_name
FROM ExpeditionRoles er
JOIN Explorers ex ON er.explorer_id = ex.id
JOIN Expeditions exp ON er.expedition_id = exp.id;

10. Получите список всех артефактов, найденных исследователями во время экспедиций, включая имя исследователя, название экспедиции и страну региона экспедиции.

Ожидаемый результат: Для каждого артефакта выведите его название, имя исследователя, который его нашёл, название экспедиции и страну региона экспедиции.
💡Решение

Необходимо выполнить JOIN таблицы Artifacts с Explorers (по founded_by_id), с Expeditions (по expedition_id), а также Expeditions с Regions (по region_id).

SELECT a.name AS artifact_name, ex.full_name AS explorer_name, e.name AS expedition_name, r.country AS region_country
FROM Artifacts a
JOIN Explorers ex ON a.founded_by_id = ex.id
JOIN Expeditions e ON a.expedition_id = e.id
JOIN Regions r ON e.region_id = r.id;

11. Получите список всех событий, связанных с экспедициями, с указанием названия события, названия экспедиции, имени руководителя экспедиции и страны региона, где проходила экспедиция.

Ожидаемый результат: Для каждого события выведите его название, название экспедиции, имя руководителя экспедиции и страну региона экспедиции.
💡Решение

Выполните JOIN таблицы Events с Expeditions (по expedition_id), Expeditions с Explorers (по leader_id), а также Expeditions с Regions (по region_id).

SELECT ev.name AS event_name, exp.name AS expedition_name, ex.full_name AS leader_name, r.country AS region_country
FROM Events ev
JOIN Expeditions exp ON ev.expedition_id = exp.id
JOIN Explorers ex ON exp.leader_id = ex.id
JOIN Regions r ON exp.region_id = r.id;

12. Получите список всех участников экспедиций, указав их роль, имя исследователя, название экспедиции и страну региона экспедиции.

Ожидаемый результат: Для каждого участника выведите его роль, имя исследователя, название экспедиции и страну региона экспедиции.
💡Решение

Выполните JOIN таблицы ExpeditionRoles с Explorers (по explorer_id), ExpeditionRoles с Expeditions (по expedition_id), а также Expeditions с Regions (по region_id).

SELECT er.role, ex.full_name AS explorer_name, exp.name AS expedition_name, r.country AS region_country
FROM ExpeditionRoles er
JOIN Explorers ex ON er.explorer_id = ex.id
JOIN Expeditions exp ON er.expedition_id = exp.id
JOIN Regions r ON exp.region_id = r.id;

13. Добавьте нового исследователя в базу данных, заполнив все столбцы таблицы explorers.

Ожидаемый результат: В таблице explorers появится новая строка с полными данными исследователя, все столбцы будут заполнены (id, full_name, date_of_birth, country_of_born, speciality).
💡Решение

Необходимо использовать оператор INSERT INTO, указав все столбцы таблицы explorers и значения для каждого из них.

INSERT INTO explorers (id, full_name, date_of_birth, country_of_born, speciality)
VALUES (138, 'Slobodan Androvic', '1988-10-08', 'Montenegro', 'Botanist');

Проверьте созданную строку с помощью запроса:

SELECT * FROM explorers WHERE id = 138;

14. Обновите данные созданного исследователя: измените его дату рождения, страну рождения и специальность на новые значения по вашему выбору.

Ожидаемый результат: В таблице explorers строка с созданным исследователем должна содержать обновлённые значения в столбцах date_of_birth, country_of_born и speciality согласно новым данным.
💡Решение

Используйте оператор UPDATE для изменения указанных столбцов в строке с id, который вы задали для нового исследователя.

UPDATE explorers
SET date_of_birth = '1991-05-22', country_of_born = 'Serbia', speciality = 'Сhemist'
WHERE id = 138;

Проверьте обновлённую строку с помощью запроса:

SELECT * FROM explorers WHERE id = 138;

15. Удалите созданного исследователя из таблицы explorers.

Ожидаемый результат: Строка с указанным вами id будет удалена из таблицы explorers. После выполнения запроса исследователь с этим id больше не будет отображаться в таблице.
💡Решение

Используйте оператор DELETE с условием WHERE, чтобы удалить только нужную строку.

DELETE FROM explorers
WHERE id = 138;

Проверьте удалённую строку с помощью запроса:

SELECT * FROM explorers WHERE id = 138;