DB Quacks SQL Tutorial · Chapter 5
UNION Jack and the Quest for Set Operations
Wizard Merlin appears in a flash! News of your festival has spread across the multiverse! Northern and Southern Duck Communities from parallel dimensions want to participate, but they have separate registries. Create a unified guest list!
Your task
Combine results from two duck populations into one unified list by working with 'data/northern_ducks.csv' and 'data/southern_ducks.csv' files. You will be merging data from two separate sources.
Hint
Look for a set operation that can combine rows from multiple SELECT statements.
Expected result
A unified list of ducks from both northern and southern populations.
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/northern_ducks.csv (9 rows)
| column | type |
|---|---|
name | VARCHAR |
region | VARCHAR |
population_id | BIGINT |
migration_pattern | VARCHAR |
data/southern_ducks.csv (9 rows)
| column | type |
|---|---|
name | VARCHAR |
region | VARCHAR |
population_id | BIGINT |
migration_pattern | VARCHAR |
Database: https://dbquacks.com/data/databases/tutorial.duckdb
This page as markdown: https://dbquacks.com/tutorial/23.md