DB Quacks SQL Tutorial · Chapter 4
The Parent Trap: A JOIN Story
Gordon swims back excitedly. Species representation is perfect! Now we need pond-based festival activities where ducks share favorite swimming spots. But duck info and pond visits are in separate tables - help us connect them!
Your task
Show duck names along with their pond visit information by connecting the ducks table with the 'data/pond_visits.csv' file. You will be joining two different data sources to combine duck information with their visit records.
Hint
Use INNER JOIN to connect tables. Match ducks between tables using their names.
Expected result
A table showing duck names and their pond visits.
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/17.md