DB Quacks SQL Tutorial · Chapter 2
The Great Duck Statistics Quest
Dr. Stork, the village statistician, waddles over with a clipboard. I need to prepare the annual health report! How many ducks do we have? Whats the average age? Whos the youngest and oldest? I need these basic statistics for my report!
Your task
Calculate basic statistics about all ducks from the ducks table: total count, average age, minimum age, and maximum age. You will be using aggregate functions to summarize the data.
Hint
Look for functions that can count rows, calculate averages, and find minimum and maximum values.
Expected result
A single row showing count, average, minimum, and maximum age of all ducks.
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[] |
Database: https://dbquacks.com/data/databases/tutorial.duckdb
This page as markdown: https://dbquacks.com/tutorial/10.md