SQL code copied to buffer

SQLtest.online
Task : 
To keep the penguins_by_islands table (created in this task) up to date, create a trigger that fires after rows are deleted from the penguins table and reduces the number of penguins living on the corresponding island.

Write your request in the field below and click the "Check it!" button.

Use SQLite syntax to write your answer. Descriptions of tables are provided at the bottom of the screen.

Get hint Copy code Clear editor

Querynomicon (SQLite)

A compact database for learning the basics of SQL.

The Querynomicon database 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"}
Learning SQL: Generate, Manipulate, and Retrieve Data by Alan Beaulieu
Learning SQL: Generate, Manipulate, and Retrieve Data by Alan Beaulieu
As data floods into your company, you need to put it to work right away―and SQL is the best tool for the job. With the latest edition of this introductory guide, author Alan Beaulieu helps developers get up to speed with SQL fundamentals for writing database applications, performing administrative tasks, and generating reports. You’ll find new chapters on SQL and big data, analytic functions, and working with very large databases.