DB Quacks SQL Tutorial · Chapter 4
No Duck LEFT Behind
Social worker duck approaches with concern. Some shy ducks never visited ponds! Our previous reports only showed swimming ducks. I want ALL ducks included, even homebodies who stayed home!
Your task
Show all ducks and their pond visits, including ducks who never visited any pond, by joining the ducks table with 'data/pond_visits.csv'. You will be ensuring no ducks are excluded from the results.
Hint
You need a join that keeps all records from one table, even if there are no matches in the other table.
Expected result
A table showing all ducks and their pond visits (NULL for ducks who never visited).
Tables
duck_diaries (10 rows)
| column | type |
|---|---|
diary_id | BIGINT |
duck_id | BIGINT |
entry_date | DATE |
diary_text | VARCHAR |
mood | VARCHAR |
duck_surveys (5000 rows)
| column | type |
|---|---|
survey_id | BIGINT |
duck_id | BIGINT |
age | BIGINT |
favorite_food | VARCHAR |
favorite_pond | BIGINT |
swimming_rating | BIGINT |
migration_preference | VARCHAR |
social_behavior | VARCHAR |
health_status | VARCHAR |
activity_level | VARCHAR |
survey_date | DATE |
survey_location | BIGINT |
ducks (15 rows)
| column | type |
|---|---|
id | BIGINT |
name | VARCHAR |
age | BIGINT |
species | VARCHAR |
birth_date | DATE |
arrival_date | DATE |
ponds (3 rows)
| column | type |
|---|---|
pond_id | BIGINT |
pond_name | VARCHAR |
location_lat | DOUBLE |
location_lng | DOUBLE |
capacity | BIGINT |
features | VARCHAR[] |
amenities | VARCHAR[] |
Files
data/pond_visits.csv (40 rows)
| column | type |
|---|---|
visit_id | BIGINT |
duck_id | BIGINT |
pond_id | BIGINT |
visit_date | DATE |
duration_minutes | BIGINT |
Database: https://dbquacks.com/data/databases/tutorial.duckdb
This page as markdown: https://dbquacks.com/tutorial/20.md