New month, new goals. Your help keeps the project moving forward. 🖥️ Support sqltest →
SQL code copied to buffer

SQLtest.online

Practice

Lessons

Playground

Test

Querynomicon database (SQLite): penguins, tables and SQL exercises

Querynomicon is a small SQLite database for learning SQL from scratch: the Palmer penguins dataset plus a tiny laboratory with staff, experiments and assay plates. On SQLtest.online you can query it right in your browser: solve exercises with automatic checking and run your own queries in the playground, with nothing to install.

  • 13 tables
  • 344 penguins
  • 50 experiments
  • 49 SQL exercises
Solve Querynomicon exercises Open Querynomicon in the playground

What is Querynomicon

The database comes from the Querynomicon, Greg Wilson's free tutorial "An Introduction to SQL for Wary Data Scientists". Its main table holds the Palmer penguins: measurements of 344 penguins of three species from three islands in Antarctica.

The data is small and easy to read, but it has the quirks of real data: missing values (NULL) in measurements and in the sex column. That makes it a good place to learn filtering, sorting, grouping, NULL handling and the basics of DDL and DML.

What's inside

The tables fall into two groups.

Penguins

penguins with all 344 birds and little_penguins, a 10-row sample for quick experiments.

Laboratory

department, staff, experiment, performed (who ran which experiment), plate and invalidated, plus machine, usage, person and contact.

The penguin tables have no keys: each row is one bird. The laboratory tables are linked by numeric identifiers, and performed links staff and experiments many-to-many.

How much data the tables hold:

TableRowsContents
penguins344penguins and their measurements
little_penguins10a sample of 10 penguins
department4departments
staff10staff
experiment50experiments
performed65staff ↔ experiments
plate256assay plates
invalidated30invalidated plates
machine3lab machines
person15people
usage8machine usage log
contact8contacts

Table structure

Click a table to see its columns, a sample row and its keys.

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"}

Sample queries

These queries show how the data is connected. Copy any of them and run it in the playground.

Experiments and plates: a one-to-many relationship with a LEFT JOIN and a count.

SELECT e.ident, e.kind, e.started, COUNT(p.ident) AS plates
FROM experiment e
LEFT JOIN plate p ON p.experiment = e.ident
GROUP BY e.ident
ORDER BY e.ident
LIMIT 3;
identkindstartedplates
1calibration2023-08-251
2calibration2023-02-141
3trial2023-02-2210

Who ran an experiment: a many-to-many relationship through performed.

SELECT s.personal, s.family, e.kind, e.started
FROM performed pf
JOIN staff s ON s.ident = pf.staff
JOIN experiment e ON e.ident = pf.experiment
ORDER BY e.ident, s.ident
LIMIT 3;
personalfamilykindstarted
NityaLalcalibration2023-08-25
IndransSridharcalibration2023-02-14
KartikGuptatrial2023-02-22

SQL exercises by topic

There are 49 exercises on the Querynomicon database, from the first SELECT to views, indexes and triggers. Solutions are checked automatically on a real SQLite. The number on the right is how many exercises a topic has; the colored dots show its difficulty range.

Where to start

The first exercises on the Querynomicon database:

  1. Retrieve All Departments
  2. Staff Names
  3. Sort Penguins
  4. Penguin Species
  5. Lightest Weight Penguins
  6. Penguins Data Retrieval
  7. Penguin Species Distribution by Island
  8. Population Distribution (Pivot)
  9. Small Penguins
  10. Small Penguin Species

All Querynomicon exercises →

FAQ

Do I need to install SQLite to use the Querynomicon database?

No. The exercises and the playground on SQLtest.online run your queries on our servers, so a browser is all you need. In the playground, choose SQLite 3 Preloaded.

What are the Palmer penguins?

A popular teaching dataset: measurements of Adelie, Chinstrap and Gentoo penguins collected at Palmer Station, Antarctica. It is often used as a modern replacement for the iris dataset.

Is this database good for beginners?

Yes. The tables are small and the subject needs no explanation, so you can focus on SQL itself: SELECT, WHERE, ORDER BY, GROUP BY and handling NULL.

Start solving exercises Open the playground