-- -------------------------------

-- 10. Count number of records by institution

-- -------------------------------

CREATE OR REPLACE VIEW _specimens_10_count_number_of_records_by_institution AS

SELECT specimen_duplicate_institutions, COUNT(*) AS records

FROM "Ecatalog_all"

GROUP BY specimen_duplicate_institutions;

/*

Check: should return 6 rows

*/

-- -------------------------------

-- 11. List of three standard political divisions

-- -------------------------------

CREATE OR REPLACE VIEW _specimens_11_list_of_three_standard_political_divisions AS

SELECT DISTINCT country, "stateProvince", county

FROM "Ecatalog_all";

/*

Check: should return 5232 rows

*/

-- -------------------------------

-- 16. List distinct non-null specimen descriptions

-- -------------------------------

CREATE OR REPLACE VIEW _specimens_16_list_distinct_specimen_descriptions AS

SELECT "specimenDescription" AS "specimenDescription"

FROM "Ecatalog_all"

WHERE "specimenDescription" IS NOT NULL;

/*

Check: should return 158460 records

*/