AMEND SQL database demo

This page provides an interactive, browser-based demonstration of the Archive of Massachusetts ENvironmental Data SQLite database. Use the form below to test out a SQL query and view results.

AMEND Database Demo

Step 1: Load database

Database location:


Note: The AMEND database is about 60 MB in size and may take a minute to download.

Load database

Step 2: Write query

Enter some SQL
SELECT name, sql FROM 'sqlite_master' WHERE type='table';

Step 3: Execute query

Make sure you have loaded the database (Step 1) and written a SQL query (Step 2).

Execute

Results will be displayed here

Example SQL queries

Copy and paste each of the below tables into the demo at left to test out the query.

List tables in database:

SELECT name, sql
FROM 'sqlite_master'
WHERE type='table';

Print MA DEP budget table:

SELECT * FROM MassBudget_summary;

Calculate total number of MA DEP enforcements per year:

SELECT year, count(*)
FROM MADEP_enforcement
GROUP BY year;

Calculate total number of MA DEP enforcements per year and join with DEP staff per year:

SELECT staff.year, count_enforcement, count_staff FROM
  (SELECT year, count(*) AS count_enforcement
  FROM MADEP_enforcement
  GROUP BY year) enf
JOIN
  (SELECT
    CalendarYear AS year,
    count(*) AS count_staff
  FROM MADEP_staff
  GROUP BY year) staff
ON staff.year=enf.year;