Task 67:

Analyze Bill Lengths

SQLite

For all penguins in the little_penguins table, get the species, island, beak length, average beak length in the table, and the difference in beak length for each penguin from the average. Build a result table with the fields species, island, bill_length_mm, avg_bill_length_mm, diff_from_avg_bill_length_mm.
Solve the problem using window functions.

Expected result Columns: 5 · Rows: 10

species	island	bill_length_mm	avg_bill_length_mm	diff_from_avg_bill_length_mm

Sample rows open after wrong checks: 0 of 2

Write your query below and click "Check it!". Tables are described in the right pane.

Tutorial

The query result will appear here. Ctrl+Enter - Run query

Querynomicon Database: table structure and overview

Querynomicon (SQLite) is a compact training database for learning SQL fundamentals with clear and simple examples.

This page presents the tables, key columns, and sample rows for hands-on SQL practice.

The Querynomicon database contains 5 main tables.

More about the Querynomicon database: schema, sample queries and all exercises →

List of tables

department - table of departments.

identDepartment ID
nameDepartment name
buildingBuilding name

ident	name	building
gen	Genetics	Chesson

little_penguins - table of little penguins.

speciesPenguin species
islandIsland of residence
bill_length_mmBill length, mm
bill_depth_mmBill depth, mm
flipper_length_mmFlipper length, mm
body_mass_gBody mass, g
sexSex

species	island	bill_length_mm	bill_depth_mm	flipper_length_mm	body_mass_g	sex
Gentoo	Biscoe	52.1	17	230	5550	MALE

penguins - table of penguins.

speciesPenguin species
islandIsland of residence
bill_length_mmBill length, mm
bill_depth_mmBill depth, mm
flipper_length_mmFlipper length, mm
body_mass_gBody mass, g
sexSex

species	island	bill_length_mm	bill_depth_mm	flipper_length_mm	body_mass_g	sex
Gentoo	Biscoe	52.1	17	230	5550	MALE

staff - table of employees.

identEmployee number
personalEmployee first name
familyEmployee last name
deptDepartment
ageAge

ident	personal	family	dept	age
7	Abram	Chokshi	gen	23

machine - table of machines.

identMachine ID
nameMachine name
detailsJSON with details

ident	name	details
1	WY401	{"acquired": "2023-05-01"}
2	Inphormex	{"acquired": "2021-07-15", "refurbished": "2023-10-22"}
3	AutoPlate 9000	{"note": "needs software update"}