RU
◀ Back to the task description

Send the queries to the database

1. Find all researchers who were born after 1980 and are archaeologists.

Expected Result: A list of explorers with their IDs, names, and dates of birth.
đź’ˇSolution

To get a list of all researchers who were born after 1980 and are archaeologists, select the id, full_name, and date_of_birth columns from the Explorers table using the WHERE clause:

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

2. Calculate how many artifacts are in each condition (Excellent, Good, Damaged, Fragmented).

Expected Result: A list of condition_id values with a count of artifacts for each condition. Each row will represent a unique condition, showing how many artifacts are assigned to that condition.
* To display the condition names, a JOIN is required, which will be covered later.
đź’ˇSolution

This SQL query uses the GROUP BY clause to aggregate rows in the Artifacts table based on their condition_id. The COUNT(id) aggregate function calculates the number of artifacts (by counting their IDs) within each group.

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

3. Retrieve a list of all events, sorted from the newest to the oldest, and then by event type in alphabetical order.

Expected Result: All events from the Events table, ordered primarily by the date column (most recent first). If multiple events share the same date, they should be sorted alphabetically by event type.
đź’ˇSolution

This SQL query uses the ORDER BY clause to arrange the result set. ORDER BY date DESC sorts the events by date in descending order, placing the latest events at the top. The second criterion, event_type ASC, sorts events alphabetically by type when dates are the same.

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

4. Retrieve a list of all artifacts that were found during an expedition, including the relevant expedition information.

Expected Result: A table where each row contains the artifact's name, the expedition's name, and the expedition's start date.
đź’ˇSolution

You need to perform a JOIN between the Artifacts and Expeditions tables using the expedition_id field.

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. Get a list of all artifacts with their current condition (e.g., Excellent, Good, etc.). Display the artifact's name and its condition.

Expected Result: A table where each row contains the artifact's name and its condition.
đź’ˇSolution

You need to perform a JOIN between the Artifacts and Conditions tables on the condition_id and id fields.

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

6. Display a list of all events that have an associated expedition.

Expected Result: A table with the event name and expedition name. Only include events where the expedition_id field is not NULL.
đź’ˇSolution

Perform a JOIN between Events and Expeditions using expedition_id and id, and filter by 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. Retrieve a list of all events that are associated with both a region and an expedition.

Expected Result: For each event that references both a region and an expedition, the list should include information about the event, the region, and the associated expedition.
đź’ˇSolution

You need to join the Events table with the Regions table on region_id, and with the Expeditions table on 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. Retrieve a list of all artifacts for which the explorer who found them and the current artifact condition are specified.

Expected Result: For each artifact with a specified explorer, the list should include the artifact's name, the explorer’s name, and the current artifact condition.
đź’ˇSolution

Join the Artifacts table with the Explorers table on founded_by_id, and with the Conditions table on 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. Retrieve a list of all expedition roles, including the explorer’s name and the name of the expedition in which they participated.

Expected Result: For each expedition role, the list should show the role, the explorer’s name, and the expedition name.
đź’ˇSolution

Join the ExpeditionRoles table with the Explorers table on explorer_id, and with the Expeditions table on 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. Retrieve a list of all artifacts that were found by explorers during expeditions, including the explorer’s name, the name of the expedition, and the country of the expedition’s region.

Expected Result: For each artifact, display its name, the name of the explorer who found it, the expedition's name, and the country of the expedition’s region.
đź’ˇSolution

You need to join the Artifacts table with Explorers (on founded_by_id), with Expeditions (on expedition_id), and Expeditions with Regions (on 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. Retrieve a list of all events related to expeditions, showing the event name, the expedition name, the name of the expedition leader, and the country of the region where the expedition took place.

Expected Result: For each event, display its name, the expedition's name, the name of the expedition leader, and the country of the expedition’s region.
đź’ˇSolution

Join the Events table with Expeditions (on expedition_id), Expeditions with Explorers (on leader_id), and Expeditions with Regions (on 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. Retrieve a list of all expedition participants, showing their role, the explorer’s name, the expedition name, and the country of the expedition’s region.

Expected Result: For each participant, display their role, the explorer's name, the expedition's name, and the country of the expedition’s region.
đź’ˇSolution

Join the ExpeditionRoles table with Explorers (on explorer_id), ExpeditionRoles with Expeditions (on expedition_id), and Expeditions with Regions (on 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. Add a new explorer to the database by filling in all columns in the explorers table.

Expected Result: A new row with complete explorer data should appear in the explorers table, with all columns filled in (id, full_name, date_of_birth, country_of_born, speciality).
đź’ˇSolution

You need to use an INSERT INTO statement, listing all columns of the explorers table and providing values for each of them.

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

You can verify the newly created row by running the following query:

SELECT * FROM explorers WHERE id = 138;

14. Update the data of the created explorer: change his date of birth, country of born, and speciality to new values of your choice.

Expected Result: In the explorers table, the row with created explorer should have updated values in the date_of_birth, country_of_born, and speciality columns according to the new values you specify.
đź’ˇSolution

You should use the UPDATE statement to modify the specified columns for the row with the id you set for your new explorer.

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

You can verify the updated row by running the following query:

SELECT * FROM explorers WHERE id = 138;

15. Delete the explorer that you've created from the explorers table.

Expected Result: The row with the id you set will be removed from the explorers table. After executing the query, the explorer with this id will no longer appear in the table.
đź’ˇSolution

Use the DELETE statement with a WHERE clause to remove only the specified row.

DELETE FROM explorers
WHERE id = 138;

You can verify the deleted row by running the following query:

SELECT * FROM explorers WHERE id = 138;